SQL Server Performance Forum – Threads Archive

How to improve this query…

I was hoping I could get some insight on how one might go about improving this query: SELECT DISTINCT u.FirstName, u.LastName, u.PostalCode, ZipCodes.Latitude, ZipCodes.Longitude
FROM Users u LEFT JOIN ZipCodes ON ZipCodes.ZipCode=LEFT(u.PostalCode, 5)
LEFT JOIN aspnet_Users ON u.UserID=aspnet_Users.UserID
WHERE ApplicationID = ‘someapplicationid’ Basically it’s just grabbing a set of users with the latitude and longitude of their zip code.
The profile information is stored in the Users table with 60,000+ records. The ZipCodes table has 70,000+ zip/latitude/longitude records. As is, the query takes 8 seconds to execute. Unfortunately the PostalCode in the Users table cannot be limited to 5 characters and the ZipCodes table depends on a 5-character zip code which necessitates the LEFT operation. Can anyone recommend a way of trimming down the execution time?
Thanks.

Run Index tuning wizard for this query and check for the improvements.

Why can’t it be limited?
Have you considered adding an indexed computed column on LEFT(u.PostalCode,5) and join on that column? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Well, I’ve got it down to 4-5 seconds by adding indexes to the UserID and computed 5-digit zip code (thanks FrankKalis). The tuning wizard had no recommendatinos although I need to read up on it more to make sure I’m using it properly. I’m not sure if I can squeeze any more performance out of it. Thanks for the suggestions.

ApplicationID is column from users table, right? Do you have index on ApplicationID?
How about the index on applicationID, UserID and PostalCode? If you want to give it a try make sure execution plan uses new index.

How do you add an index on a computed column?? i hadn’t heard of this until now! ‘I reject your reality and substitute my own’ – Adam Savage

<blockquote id="quote">quote:<hr height="1" noshade id="quote"> How do you add an index on a computed column?? i hadn’t heard of this until now! <hr height="1" noshade id="quote"></blockquote id="quote"> Come on… Look at BOL computed columns -> Creating Indexes on Computed Columns [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

]]>