Jump to content
  • Advertisement
Sign in to follow this  
Houdini

Slow SQL Server query response

This topic is 4905 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

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

Share this post


Link to post
Share on other sites
Advertisement
Before I even start I must mention I am no professional and don't have a quarted the experiance most of these guys do, however I am fairly good at C++ and SQL especially after working with an Oracle DB. To your question..

First off I will mention that a database with a 3 million recordset is a nice size db. Now 40 sec delay worth, not nearly nig enough. Some tips, narrow down that SQL statement as much as possible to help specify your search. As newbish as it sounds I tend to always make my searched very broad like so:

Select *
From TABLE_1
WHERE NAME LIKE = '%ame';

Instead of simply making it

Select *
FROM TABLE_1
WHERE NAME = 'Name';

Now your sever there may be a problem, your using a pretty small server for a huge recordset. 3 million records 512mb memory may put some weight on her...

I love and prefer C++, but make things a little easier on yourself. Try out Visual Basic it has built in functions and connection's that make database connecting and retrieving very fast and efficient. If you decide not to do that or don't want to research VB, then do the same exact record search in a differnt language for instance ASP and see how it reacts for the query, any way that is my 2 cents. Good Luck

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
Quote:
Is it normal for a database with a few million records to take THAT long in returning 56,000 records?

No, it isn't. I have experience using pretty much the same query on millions of records. And should run a lot faster, the server specs seem descent enough.

Quote:
Any ideas on what I can to speed this thing up?


- Have you tried the execution plan option in the Query Analyzer? It can give some clues, but since it's a really simple query, it maybe wothless in this situation.
- Make sure the hard disk isn't too fragmented. This may sounds strange, but lastweek I managed to speed queries considerably by just defragmenting the drive.
- Try to re-index the table, sometimes MSSQL fails to do this after many insert/updates.
- There are more ways to improve performance, like putting the data on one hard disk, and the index on another, adjusting the fill factor etc., but even without those "radical" solutions, you performance should be way better then this. Something more basic is wrong.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!