correlation between record count and speed

alanzhao

Hello everyone,

I just performed a test on MySQL. I saved nearly 200,000 records to a table. When I tried to retrieve records from the table, I realized that the script execution time significantly increased, even I only retrieved one record at a time. So I concluded that the script execution time would increase when the number of record increases.

Is it true? What about other database, like MS SQL, Oracle..

Any comments is welcome!🙂

Sxooter

Lots of missing info here. Do you have an index? Are there any analysis tools that MSSQL needs to run to know about how it's tables are put together, etc...

generally, larger data sets are always slower than smaller ones, but proper indexing can make a world of difference.

alanzhao

There is one primary field:rolleyes:

Sxooter

So, when you "selected one record" did you use the primary key to select the record, or another field?

Please note that if you're returning all 200,000 fields, it will be slow period. Not much you can do to speed up 200,000 fields being retrieved all at once.

Now, if you're operating on a large percentage inside the database, but only delivering a few, (something like select id, avg(score) form table group by id) then the performance characteristics of the database become very important.

alanzhao

yea, I select the primary id to count the total number of record, and select few records.

I checked with phpMyAdmin, it performs faster than my script with almost same method:rolleyes:

Sxooter

counting the total number of records is always gonna cost a bit, since most databases don't index nulls, they often have to sequentually scan the table.

How much performance tuning has your MSSQL database had? I'm sure, that like most databases, it needs tuning to handle >100,000 rows efficiently. However, I'm a postgresql user so I don't have a clue how to tune MSSQL.

alanzhao

I have no idea on how to tune the database

Sxooter

Usually what you're looking for are things like buffer memory, sort memory, things like that. also, fast hard drives help. What kind of machine are you on?

mattr

Please provide the query(s) you are running and the table DDL (CREATE TABLE statements along with any CREATE INDEX statements).

alanzhao

Forgot to mention, I am working on a database layer, like the one from PHPLib, I guess maybe it makes the execution slower, unlike phpMyAdmin, it calls mysql function directly.

The sql are simply like:

$records = $DB->num_rows($DB->query("SELECT id FROM test"));

then

$DB->query("SELECT * FROM test LIMIT 0, 10");

and display the selected data

it's slower than the select in phpMyAdmin:rolleyes:

xblue

since you seem to need just the total (for some pagination calculations or similar I guess?), it should be more efficient to use
SELECT COUNT(id) FROM test.

see http://www.phpbuilder.com/board/showthread.php?s=&threadid=10233374

in case you do not use all fields from the table in your second query it may also be a slight improvement to list all the fields you need instead of selecting everything with *.

alanzhao

Exactaly, I was doing a pagination calculations.

Yea, I tried the COUNT, it's a lot faster than my previous method, great!!!

But I wonder if all databases support COUNT statement?:rolleyes:

xblue

I think most should since it is fairly basic standard sql. I think I remember having used it with oracle, luickily never had to deal with mssql yet though.

links to different database manuals are listed here:
http://sqlzoo.net/

Sxooter

count, avg, and sum are standard aggregate statements in every data base I've ever seen.