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 |
|
icarlson
Starting Member
3 Posts |
 Posted - 2011-03-11 : 10:31:47
|
| I have two queries that select from an underlying view. The second of which returns in about 5 seconds, whereas the first takes over 40 seconds. Originally I thought that the first query was doing something strange like trying to order, but if I change the getdate()-90 and getdate() to paramaters, the query is lightning quick again. What could I be missing, this seems totally out of the ordinary:1. select closedate from my_view where closedate between getdate()-90 and getdate() --> 40 second return2. select # from my_view where closedate between getdate()-90 and getdat() --> 5 second returnIvan C. Carlson |
|
|
icarlson
Starting Member
3 Posts |
Posted - 2011-03-11 : 10:38:24
|
| crud, the second query should be:select * from my_view where closedate between getdate()-90 and getdate()Ivan C. Carlson |
 |
|
|
BruceT
Yak Posting Veteran
78 Posts |
Posted - 2011-03-11 : 11:01:13
|
| Are you running them one after the other? After the first query executes data would be cached and so the second query should return much faster.To make sure you're comparing apples to apples clear the cache buffers and cached plan before executing each one. |
|
|
|
icarlson
Starting Member
3 Posts |
Posted - 2011-03-11 : 11:05:52
|
quote:
Originally posted by BruceT
Are you running them one after the other? After the first query executes data would be cached and so the second query should return much faster.To make sure you're comparing apples to apples clear the cache buffers and cached plan before executing each one.
Currently yes, but when we discovered the length of the query return, we were only running the first one. We ran the select * in a new instance and saw the results much faster.Ivan C. Carlson |
|
|
|
BruceT
Yak Posting Veteran
78 Posts |
Posted - 2011-03-11 : 11:07:44
|
| Have you compared the two query plans? |
|
|
|
|
|
|
Query Optimization