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 |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2010-07-30 : 04:34:37
|
| Hi,I am in the process of optimizing a long complicated stored procedure (SP)...The first part I have noticed in SP is a select query which takes about five seconds to return about 350,000 records.The select query is something like:select field1, field2, field3, field4, ...from table1field1 is a pk.There are indexes on some of the fields in the select query but not on all of them.Question1How can I reduce this 5 seconds please?Thanks |
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2010-07-30 : 04:37:10
|
| Can you use those indexes? do u have Where clause?Senthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/ |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-07-30 : 04:48:05
|
| If there's no where clause (as it appears) then no, there's no real way to optimise this. Indexes are only useful if the query has joins or a where clause. You're asking for the entire table.What's happening with those 350000 rows? Not sent back to a user interface I hope? No user's going to read through that.--Gail ShawSQL Server MVP |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2010-07-30 : 05:03:29
|
quote:
Originally posted by GilaMonster
If there's no where clause (as it appears) then no, there's no real way to optimise this. Indexes are only useful if the query has joins or a where clause. You're asking for the entire table.What's happening with those 350000 rows? Not sent back to a user interface I hope? No user's going to read through that.--Gail ShawSQL Server MVP
Solved by using indexes in the where clause fields |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-30 : 05:44:39
|
| " not in (number1, number2, etc...)"If that NOT IN list is long (maybe a few 100, more probably a problem if its a 1,000+) it will be faster as a JOIN (but you'll need the numbers in a table, of course). |
|
|
|
|
|
|
|
|
Optimization