I'm working with a medium sized database (table with 3 million records) and queries I throw at it seem to be taking an AWEFULLY long time to complete. I'm not certain if databases are always this slow when dealing with tables this size, or if there is something I can do to speed things up. I'm hoping one of you DB experts can enlighten me. Here's our sever specs: 3.4GHz Pentium 4 512 MB RAM 155GB (78GB free) RAID Hdd Here's the database specs: MS SQL Server 2000 Table in question has 51 columns and 3 million records. I'm trying to do a simple query like this:

SELECT In_Amount from InvItem_IDX where Type = 'P'

I'm only returning one column, the Type column IS indexed in the table, and yet it's taking 1:55 to return about 56,000 records. This is being ran in Query Analyzer on the server itself, so network lag isn't an issue. I tried narrowing the query down some like this:

SELECT In_Amount from InvItem_IDX where Type = 'P' AND Out_Date BETWEEN '4/1/2005' AND '5/10/2005'

This returned 25,000 records and takes about 44 seconds to complete, which is still an awefully long time for just 25,000 records, IMO. The strange thing is, if I change the column in the SELECT clause to an indexed column, then it takes just 2 seconds to return 150,000 records. I thought that indexing only helped in JOIN, WHERE and ORDER BY clauses. FYI, we have another duplicate database with the same table but containing only 36,000 records, and it takes just 4 seconds to return ALL those records. So I guess my two main question are: Is it normal for a database with a few million records to take THAT long in returning 56,000 records? Any ideas on what I can to speed this thing up? Thanks for any help you guys can give! - Houdini

My guess is that _jinx_ eluded to the issue when he said there may not be enough RAM on the server. When you change the select to an indexed column you see the performance you desire, with the non-indexed column you don't, this is because the indexed column is in memory, but the non-indexed column is not. When you select the non-indexed column it must be read from disk which = slow!

You also state that from a table with 36000 records the query performs well. This is more evidence that memory is the issue. Here all columns are able to be put in memory, not just the indexed ones.

Good Luck!

Thanks for all the suggestions!

WanMaster: I checked the drive and it's pretty badly defragmented at 29%. I'm defragging it now to see if it makes a difference.

I'll also try re-indexing the table. There are 10 index on that table, with a total 16 columns in those indexes, which seems like a lot. I'm going to try removing all but one or two that I use with my query to see if that makes a difference.

stu_pb, _jinx_: Yeah, 512MB ram really doesn't seem like a lot for a server with a decent sized database. I'll see what I can do about getting more ram put into that machine.

LouisHirst: Thanks for the link. That book may be worth adding to my library!


Well, at least now I know that it should NOT be taking this long for the queries to run. I'll try all your suggestions and let you guys know what worked!


- Houdini