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 |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2007-06-11 : 13:44:50
|
| I have a SELECT statement that takes around two minutes the first time I run it, and no more than two seconds on subsequent executions. It's a simple query with a WHERE clause on an indexed column, but the table is fairly large and contains a lot of text data. Do you think this occurs because the statistics are out of date, and the first time I run it they get updated? Or could it be that SQL Server still has the data in the cache for subsequent executions? I'm not sure how long it takes before the query takes a long time again, I would guess maybe a week or two of not running it might do it. |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-06-11 : 13:59:03
|
| Stats dont get updated for SELECTs. Its creating a query plan when you run it the first time. The data could also be cached for subsequent compilations. Perhaps you can create a stored proc so the plan to be cached too.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2007-06-11 : 22:22:02
|
| Two minutes? How many rows in the table?--Jeff Moden |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2007-06-12 : 11:49:13
|
| 200,000 rows, but I really think it's more about the BLOBs it contains than the number of rows. |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2007-06-13 : 22:44:31
|
| Maybe, maybe not... could just be code that isn't capable of using indexes or maybe there are no indexes...--Jeff Moden |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2007-06-14 : 11:44:06
|
| It should be using the index that I have in my WHERE clause, but I'm not sure. Query Analyzer tells me that 99% of the query cost is from a table scan, and most of that cost (totaling 26.45) is from I/O (26.3). I don't know what to make of that. |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-06-14 : 11:50:34
|
| Try to run two versions of the query: one with TEXT columns and other without selecting TEXT columns and check the difference between execution time.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2007-06-14 : 11:59:13
|
| There's a huge difference, it takes way longer when I select the text column. |
|
|
|
|
|
|
query runs slow the first time