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 |
|
dhw
Constraint Violating Yak Guru
332 Posts |
 Posted - 2008-05-16 : 20:11:25
|
| Hello AllI have inherited another team's database and I'm going through the process of documenting and looking for things to optimize and tune.I just ran across a query like this:Select AccountName, AccountNumber, DepartmentName, AccountStartDate,AccountEndDate From tblAccount Where AccountNumber IN ( and there is a list of over 2,500 accounts listed. I couldn't believe my eyes. I gotta' believe that this query would not peform very well under these conditions. Plus the table doesn't have a primary key or even an index.I just had to rant to someone.Thanks! - will |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-05-16 : 21:14:06
|
| Are the 2500 accounts hard-coded? Spin them off to an indexed table for an easy performance boost.e4 d5 xd5 Nf6 |
 |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2008-05-16 : 21:50:40
|
| Put the 2500 acounts in the table like blindman says then change the query to thisSelect AccountName, AccountNumber, DepartmentName, AccountStartDate,AccountEndDate From tblAccount ainner JointblNewTablewithaccounts bon a.AccountNumber = B.AccountNumberYou should see a drastic improvment. |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2008-05-17 : 02:35:37
|
| Oh yes, the 2500+ account numbers are hard coded. My plan was to move them into another table and just do the join, buy then I found that the accounts are already in another table too. When I saw this query today I had to simply shout to the heavens and then post a message here, more for spiritual relief. I just could not believe the things I have found so far. And this one is not the worse.Thanks. - will |
|
|
|
|
|
|
|
|
Unruly query with 2500+ in the "IN" list