where fldValue=123 vs join to table with one row
-
I've run this test previously with similar results. I've attached the execution plan. I removed some columns so that we're just dealing with the view in question.
Thanks! I appreciate the help.
ST
-
Sean Pearce (1/25/2013)
PLEASE NOTE: You have not aliased all your columns so I have no idea which tables they come from. You must edit the following queries to remove any non-aliased columns that are not from vwREPQICASSCostsByCC.
Exactly! As I noted above, that's why I didn't try a re-write yet. I'm certain the query needs at least some re-write, but w/o knowing which table/view every column comes from, it's like coding in the dark.
So, good general rules to follow are:
1) Always alias tables when using multiple tables/views in one query;
2) Always prefix every column with the appropriate alias when doing joins.
This not only helps anyone else looking at the query, it can help you if/when you have to look at it 6 mths later and may not remember all the table details yourself.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
souLTower (1/25/2013)
I've run this test previously with similar results. I've attached the execution plan. I removed some columns so that we're just dealing with the view in question.Thanks! I appreciate the help.
ST
My original response still holds true. The view definition is not giving the optimizer a chance to find the optimum plan.
StatementOptmEarlyAbortReason="TimeOut"What does the execution plan for the following look like?
SELECTv.fldConsulKey,
v.fldPlanKey
FROM
tblConsul v
WHERE
v.fldPLanKey IN (SELECT X.fldIMPPlanKey FROM #tmpPlan X);
SELECT
v.fldConsulKey,
v.fldPlanKey
FROM
tblConsul v
WHERE
v.fldPLanKey IN (30472);
-
Thanks for the response. I've attached the execution plans.
I find it weird that the optimizer chose in both cases to use the non-clustered index for the seek. The table has a clustered index on fldPlanKey precisely to avoid this.
IDX_tblConsul_PlanKeyclustered located on PRIMARYfldPlanKey
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply