execution plan of view changes with use of variable
-
platform: Sql Server 2005 std
I had 1 large table of 10m records that i broke into 10 tables of ~1m records each. (I couldn't use partitioning since i have std ed) each table has a constraint limiting a key value called source.
I have a view that unions all 10 tables together.
I have a performance issue with a stored proc, so digging around I managed figured out what the problem is, but not sure how to fix it.
When using a hard coded source value the view uses only 1 table, but if i switch that hard coded value to a variable the view looks at all 10 tables. please see the screen shot to illustrate what I am seeing.
any ideas would be very welcome. creating a massive case statement to hard code the variable value is out of the question 🙂
Thanks in advance
-
Is there a constraint that limits which tables the Source value 123 can be in?
Or at least an index on that column in each table?
If so, then it probably won't have any significant impact on actual performance of the query.
The execution plan has to take into account the fact that the value in the variable is going to change from time to time (after all, that's what "variable" means). If the column is limited by either indexes or constraints (or both), actual query performance should be okay even with that taken into account.
Make sense?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
each table has an index on the source, and a constraint limiting the range a source can be in.
as an example: table [A] can have sources 1-10 table 11-20 and so forth.
So when a select against the view is placed with a hard coded value the query analyzer is smart enough to select from only 1 table because of the constraints on each of the tables and works brilliantly.
however when i pass that same single value as a variable in the where clause the query analyzer ignores the constraints and essentially runs the select 10 times creating the 10% vs 90% load in the screen grab.
:angry:
-
Don't worry about the discrepancy in estimated cost. Most of the time, that's a meaningless value. It's one of those things I kind of wish Microsoft would just get rid of.
Try running each of the queries with statistics time and io turned on. Compare and post the results.
In case you aren't familiar with those:
set statistics io on; set statistics time on;Copy and paste that line to the top of the query before you run it.
Do that with each query, run in separate windows (connections). See what you get. That'll tell you a lot more useful information than the cost estimation.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
:w00t:
Thanks!
I was beating my head against the wall based on the display of the execution plan and both made 4 reads despite how the plan was displayed! Below is a screen grab of the statistics. I also ran the same clustered index search against my original table to show the performance increase i got splitting a 10m table into 10 1m tables.
screen grab:
old table set up:
-
Partitioning was pretty much invented to do exactly that!
Good job on getting it working.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply