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 |
|
BigDaddyPooBah
Starting Member
2 Posts |
 Posted - 2006-12-28 : 11:47:44
|
| Hi folks,I am not sure if I have optimized my table as I am hoping I can get the results faster.My situation: I have a canadian postal code database (over 800,000 records) with 3 fields: postal code and longitude and latitdue.I have a primary key on the postal code as it is unique. I have a nonclusterd index on the longitude and latitude fields.When I do the following query, it is VERY FAST which is not surprising since I have the primary key.============================================================SELECT * FROM postalcodes where postalcode = '90210'============================================================However, when I need to lookup postal codes within a range, my query is as follows:============================================================SELECT * FROM postalcodes where(latitude between 38.00 and 39.00} AND(longitude between 132.00 and 135.00}============================================================This second query takes over 16 seconds to produce about 40,000 records against the table (again over 800,000 records)Is this about right or is there anyway I can make this faster. I love the speed of the first query but I am not happy with the speed of the second.Thans alot. |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-28 : 11:54:20
|
| It depends on which query you use more often. If you use the latitude, longitude query a lot and need it to specifically be faster and if you typically query individual codes rather than ranges of codes then you should make the index by latitude, longitude your clustered index and make the index on postal code a non-clustered index.Many people think that the primary key has to be the clustered index, but that is not the case. You have to have a unique index for the primary key, but it can be non-clustered and it sounds like in your case that will be better. It will slow down range queries by postal code, it should not make a noticeable difference to queries for a single postal code. But having a clustered index by latitude, longitude will dramatically improve the performance of your range query on latitude, longitude. |
 |
|
|
BigDaddyPooBah
Starting Member
2 Posts |
Posted - 2006-12-28 : 13:41:44
|
| thanks so much for your response.What my goal is to perform a radius search: user enters a postal code and I then find other postal codes within a 10 mile radius.So I need to first find the long/lat points of the starting postal code (user enters it) and then I use a calculation to find the long/lat range. My postal code table already exists with 800,000 records. Using transact SQL, how to I create the non-clustered primary index and the cluster index against long/lat fields?Thanks again for all your help.P.S. With this database containing 800K records, how fast would you think I can retrieve records of 50K, 100K?Thanks. |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-28 : 14:28:23
|
You could do it in Enterprise Manager or run code something like this. It's easiest and fastest to temporarily drop the primary key constraint. ALTER TABLE dbo.PostalCodes DROP CONSTRAINT PK_PostalCodeGOCREATE CLUSTERED INDEX IX_LogitudeLatitude ON dbo.PostalCodes ( longitude, latitude ) ON [PRIMARY]GOALTER TABLE dbo.PostalCodes ADD CONSTRAINT PK_PostalCode PRIMARY KEY NONCLUSTERED ( PostalCode ) ON [PRIMARY]GO The speed will depend on what you do with the result, on my desktop machine, a query that returns 100,000 rows with three columns takes 10 seconds, but to put those same 100,000 rows into a temp table (not return them to the client), completes in less than a second. |
|
|
|
|
|
|
|
|
HELP with query !!