User Name
Password

Go Back   Planetarion Forums > Non Planetarion Discussions > Programming and Discussion

Reply
Thread Tools Display Modes
Unread 10 Feb 2005, 08:56   #1
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
Database performance tables - query times

I'm looking for some information about query times of databases on particular hardware.
I'm working on a new client program that needs to crunch a lot of data. I'm predicting about two tables of three columns with base-data of well over 100,000 records. I don't expect the client to have big fat servers, a neat celeron or pentium 4 desktop at most, and am aiming at a MS Access (cheap version) or MS SQL (expensive version) database.
Are there any hard numbers available on database performance that can tell me how a normal query will take? Google didn't know.
__________________
"Yay"
Structural Integrity is offline   Reply With Quote
Unread 10 Feb 2005, 10:19   #2
Dante Hicks
Clerk
 
Join Date: Jun 2001
Posts: 13,940
Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.
Re: Database performance tables - query times

What is a "normal" query?

A simple "select a from b where c = 1" query is obviously going to be a lot quicker than a query involving aggregate functions, multiple joins, etc. You're best of writing a range of queries you expect to see in the program (with junk data) and see how various things perform. Access for example is unbelievably slow with certain types of queries (NOT IN springs to mind although I may be mistaken), but you can generally code around those types of performance issues.

It also depends on how you're querying the database. The results you get in the program itself will not necessarily be replicated when you're querying through some sort of API or something.

In short, I don't think you're going to get some kind of generic database "speed" (rows per second ) although I'm far from an expert.
Dante Hicks is offline   Reply With Quote
Unread 10 Feb 2005, 11:13   #3
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
Re: Database performance tables - query times

I agree that it's a tad of a vague question but I'm looking for very generic information. I simply want to predict that my app isn't going to be incredibly slow because I decided I need 100k rows as base-data.
With a "normal query" I was thinking about a simple SELECT and a LEFT JOIN with one or two conditions. No aggregate stuff or sub-queries.
__________________
"Yay"
Structural Integrity is offline   Reply With Quote
Unread 10 Feb 2005, 11:27   #4
pablissimo
Henry Kelly
 
pablissimo's Avatar
 
Join Date: Apr 2000
Posts: 7,374
pablissimo has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.pablissimo has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.pablissimo has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.pablissimo has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.pablissimo has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.pablissimo has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.pablissimo has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.pablissimo has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.pablissimo has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.pablissimo has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.pablissimo has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.
Re: Database performance tables - query times

Consider MSDE instead of MSSQL if you're wanting to do it on the cheap. It's better than Access at least.
__________________
You're now playing ketchup
pablissimo is offline   Reply With Quote
Unread 10 Feb 2005, 11:43   #5
CrashTester
I am an idiot
 
CrashTester's Avatar
 
Join Date: Feb 2003
Posts: 2,145
CrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant future
Re: Database performance tables - query times

I would say it was near impossible to accuratly determine a query time. Apart from the type of query you being a factor, perhaps the biggest consideration is the hardware.

You need a fast hard drive for finding records among the thousands you have and fast processor with large L1 cache to calculate queries.

For example, SCSI drive at 10000rpm is going to find your records faster than a 7200rpm IDE drive! Similarly, if you only have low processing power with low level cache, complicated queries will take longer to be processed before the hard drive is even asked to find the files.

Now, that said, if your database is being used by only 1 person to perform uncomplicated queries, then I would say an average desktop would be more than suffice. However, if the database if to be used by many users accessing the data over a network, then a good server with good spec hardware will undoubtedly reduce wait times for database queries.
CrashTester is offline   Reply With Quote
Unread 10 Feb 2005, 12:01   #6
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
Re: Database performance tables - query times

I'm not expecting many simultaneous users. One, perhaps two. More is more of an exception than a rule.
But if you say that for simple queries and few users there won't be a significant lag, considering tables of >100k records, then I'll take your word for it.
I'd test it if I had a MSSQL set up here. I have the MSDN developer edition somewhere around here but I hoped it'd be easier to find the information on the web than setting it up and testing it myself.
__________________
"Yay"
Structural Integrity is offline   Reply With Quote
Unread 10 Feb 2005, 14:15   #7
JetLinus
Friendly geek of GD :-/
 
JetLinus's Avatar
 
Join Date: Nov 2000
Location: On my metal roid
Posts: 923
JetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud of
Arrow Re: Database performance tables - query times

Quote:
Originally Posted by CrashTester
For example, SCSI drive at 10000rpm is going to find your records faster than a 7200rpm IDE drive!
How's that? Ok, 10k rpm are faster than 7.2k rpm, and SCSI is better than IDE.
BUT, it's not all about harddrive access times!

In databases you have indexes etc, that are "pre-cached" and so on. They might as well be just one "content-listing-index" with a couple of MB of size. And then the HDD-speed difference is unsignificant.

Or, who says who big the db is? A few 100k records doesn't necessarily mean that the db can't stay in RAM all the time (especially where 512MB or 1GB is not uncommon).
__________________
[»] Entropy increases! :-/
JetLinus is offline   Reply With Quote
Unread 10 Feb 2005, 17:27   #8
CrashTester
I am an idiot
 
CrashTester's Avatar
 
Join Date: Feb 2003
Posts: 2,145
CrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant future
Re: Database performance tables - query times

Quote:
Originally Posted by JetLinus
How's that? Ok, 10k rpm are faster than 7.2k rpm, and SCSI is better than IDE.
BUT, it's not all about harddrive access times!

In databases you have indexes etc, that are "pre-cached" and so on. They might as well be just one "content-listing-index" with a couple of MB of size. And then the HDD-speed difference is unsignificant.

Or, who says who big the db is? A few 100k records doesn't necessarily mean that the db can't stay in RAM all the time (especially where 512MB or 1GB is not uncommon).
I never actually said it was all about hard drive access times, I asked him to consider the hardware as being the most important factor. And to be honest, looking at your post you agree with me;

You acknowledge my reasoning on the hard drive speed and architecture. Then you acknowledge the fact that a database uses CACHE, which is hardware and something I specifically mentioned. And lastly you point out that the records could all be stored in RAM - which the last time I looked was again hardware.

So where is your point?
CrashTester is offline   Reply With Quote
Unread 10 Feb 2005, 18:51   #9
Caesar2
Commander
 
Caesar2's Avatar
 
Join Date: Sep 2001
Location: Netherlands
Posts: 146
Caesar2 is just really niceCaesar2 is just really niceCaesar2 is just really niceCaesar2 is just really nice
Re: Database performance tables - query times

If you go for MS SQL, use views/stored procedures and create indexes on your tables
__________________
Quote:
Originally posted by Cochese
Cathaar are not overpowered.

You were just "bashed", live with it.
Caesar2 is offline   Reply With Quote
Unread 10 Feb 2005, 18:56   #10
Dante Hicks
Clerk
 
Join Date: Jun 2001
Posts: 13,940
Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.
Re: Database performance tables - query times

This is a really stupid question but what is the benefit of stored procedures (performance wise)?

e.g. On one of our MS SQL databases we have a sql query saved as a procedure and then a web page which calls the procedure. Is this significantly faster than just called the sql query direct from the web page. Why? Is the procedure response "precalculated" or something?
Dante Hicks is offline   Reply With Quote
Unread 10 Feb 2005, 19:22   #11
Caesar2
Commander
 
Caesar2's Avatar
 
Join Date: Sep 2001
Location: Netherlands
Posts: 146
Caesar2 is just really niceCaesar2 is just really niceCaesar2 is just really niceCaesar2 is just really nice
Re: Database performance tables - query times

MS SQL can make an execution plan (more or less what you call "precalculated") and reuse it other times, therfore it is faster, maybe you can't always notice this, but in large sql's you can generally see better performance.
Another reason is that you seperate you db logic and programming logic.
Further you can give someone access to a stored procedure without giving them access to the entire table.
There are more and google can explain it better, but this is it in a nutshell.

It wonders me that your query is faster than a stored procedure, maybe your usage of a stored procedure isn't optimal?
__________________
Quote:
Originally posted by Cochese
Cathaar are not overpowered.

You were just "bashed", live with it.
Caesar2 is offline   Reply With Quote
Reply


Thread Tools
Display Modes

Forum Jump


All times are GMT +1. The time now is 16:36.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2002 - 2018