Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chuck
Starting Member
1 Post |
 Posted - 2003-08-21 : 20:34:34
|
| I am querying a table which contains approx. 1/2 Million records. It is a basic select statement with 2 conditions for suburb and state fields. The query is simular to below:SELECT TOP 1 contact_id, company, address, phoneFROM tblContactWHERE suburb = @SUBURBORDER BY date_createdHowever, this takes around 20 seconds to respond. I am calling this from an asp.net page using a sproc. I am not sure if it because it is the large amount of records in the table, but i would appreciate any suggestions on how to improve this situation. ThanksRory |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-08-21 : 21:04:01
|
| Maybe suburb and date_created need to be indexed?CREATE INDEX tblContactSuburb ON dbo.tblContact (suburb)ditto for date_createdIt ought to run lickety split.Sam |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-21 : 21:07:20
|
| Adding indexes on suburb and date_created should speed this up. If you already have indexes on them, this version of the query might be a little faster:SELECT contact_id, company, address, phone FROM tblContactWHERE contact_id=(SELECT TOP 1 contact_id FROM tblContact WHERE suburb=@suburb ORDER BY date_created)The subquery will hit only the indexes and will return the contact_id, and should eliminate table scans in the outer query. |
|
|
|
|
|
|
slow query