May 26, 2004 at 3:46 am
Hello,
I have a Table with 680,000 rows in within it.
If I create a view that only contains this Table and select the Primary Key Columns and the 5 date columns then run a select * in Query Analyser, the execution time to return all records is 23 seconds.
However, if I select the PK Columns and 5 other Columns none of which are dates the execution time to return the 680,000 rows in Query Analyser is 9 seconds?
Please could someone inform me as to why selecting dates from a Table has such a bad effect on execution time.
Neil
May 26, 2004 at 7:35 am
That is interesting. I would have thought performance would be similar. This is a simple view, not an indexed view? Is there an index on these columns?
Perhaps the query processor has tricks with views.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 26, 2004 at 8:10 am
I would confirm that there are indexes on all of the date columns. If not, just create simple nonclustered indexes on each individual date column.
When selecting from the view I would explicitly list out the columns you are selecting instead of doing the SELECT *. This will speed things up a little bit.
Also, datetime(8) columns are twice as big as integer(4) columns. I wonder if some of the slowness is because you're passing back essentially twice the amount of data.
casey
May 26, 2004 at 12:26 pm
Look at each execution plan as well to see if it uses an index to get it's data as opposed to the table or clustered index. Or if they are the same.
May 27, 2004 at 9:08 am
Indexing aside - and I suspect indexing is not the point here as you did not discuss any WHERE clauses -
Casey had it right in his reply; it is linked to the size of the dataset you are returning. Datetimes are huge in comparision with most other datatypes and require much more network/read etc.. time to pass back.
If you really want to prove that limit your query to the top 64k, drop the result set in Excel (you can cheat and do a cut and paste, I won't tell) - do the same for both datetime and 'other' columns, and check out the file sizes.
In short, it's like comparing apples and oranges, or more likely grapes and mellons.
Chuck
Thanks, and don't forget to Chuckle
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply