Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

where fldValue=123 vs join to table with one row Expand / Collapse
Author
Message
Posted Thursday, January 24, 2013 8:36 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
souLTower (1/24/2013)
That helps but still doesn't explain why the
where IN (sub select)

has such a different execution plan than

WHERE IN (constants)

What I've done to improve the performance (and I'm not really proud of) is to loop over the list of values, query the main view into a temp table using WHERE fldValue = syntax, then query the temp table for the final output. I tried changing indexes, join hints, lots of things but this worked best and was extensible whether querying against 1 value or 100.


When you operate with constants SQL Server builds an execution plan considering the actual values assigned to the constants.
When it selects it from the table it jhas no idea what kind of values are in there (because the table is a heap) and it has to build more "pessimistic", but more universal plan, which might be not that good in this particular case but will perform better in average in other cases.

Add an index to your lookup table. It should help the optimizer in building more effective plan considering the current data in the table.
Post #1411441
Posted Friday, January 25, 2013 2:16 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:37 AM
Points: 996, Visits: 3,089
I have downloaded the plans and they are exactly the same. I downloaded them twice in case of PEBKAC issues.



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1411540
Posted Friday, January 25, 2013 4:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 10:38 AM
Points: 103, Visits: 448
Sorry, I just realized that the fast and slow executions are included in each file. Compare the top one to the bottom one. You can see in the fast one that the execution plan starts by using "Where tblConsul.fldPlanKey=30472"

In the slower plan, the index on the temp table (IDX_TMP) isn't introduced until midway through the execution. This is after 7+ million rows have been queried to the right of this execution point.

So my question, which I understand may not be able to be answered, is why is a different table used to determine the filter in the two different cases AND, is there a way to prevent it or to let the optimizer know that there is a more efficient strategy?

ST
Post #1411601
Posted Friday, January 25, 2013 5:16 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:37 AM
Points: 996, Visits: 3,089
My impression is that your query is too complicated and the query optimizer has no chance of compiling the most efficient plan. You can see this in the execution plan you posted.

StatementOptmEarlyAbortReason="TimeOut"

This means that the optimizer timed out while evaluating plans and occurs in both of your queries.

You mention that you want to know the difference between
Select from myView where fldValue IN (123,456,789)

and
Select from myView V inner join myTemp T ON V.fldValue = T.fldValue

However your queries are so complicated it is preventing you from comparing an apple with an apple.

If I were you I wouldn't be at all interested in the above comparison, but rather in breaking up the query to give the optimizer a better chance.

Most alarming to me is MemoryGrant="25184" for query 1 and MemoryGrant="529712" for query 2.

http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx

Query 1 has 9 hash matches while query 2 has 14 hash matches.




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1411625
Posted Friday, January 25, 2013 5:19 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:37 AM
Points: 996, Visits: 3,089
Can you tell me how the following two queries compare please.

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.

SELECT
fldConsulNo,
fldConsulName,
fldCCGroupCd,
fldCCGroupDesc,
fldCCSortOrder,
fldCCDesc,
fldSubCCNo,
fldSubCCName,
v.fldCostCenterKey,
fldAmount
v.fldPlanKey
v.fldCostCenterKey
v.fldConsulKey
v.fldSubCostCenterKey
FROM
vwREPQICASSCostsByCC v
WHERE
v.fldStageKey = 4
AND v.fldPLanKey IN (30472);

SELECT
fldConsulNo,
fldConsulName,
fldCCGroupCd,
fldCCGroupDesc,
fldCCSortOrder,
fldCCDesc,
fldSubCCNo,
fldSubCCName,
v.fldCostCenterKey,
fldAmount
v.fldPlanKey
v.fldCostCenterKey
v.fldConsulKey
v.fldSubCostCenterKey
FROM
vwREPQICASSCostsByCC v
WHERE
v.fldStageKey = 4
AND v.fldPLanKey IN (SELECT X.fldIMPPlanKey FROM #tmpPlan X);





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1411627
Posted Friday, January 25, 2013 8:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 10:38 AM
Points: 103, Visits: 448
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


  Post Attachments 
Execution2.sqlplan (3 views, 283.78 KB)
Post #1411773
Posted Friday, January 25, 2013 8:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:44 PM
Points: 2,324, Visits: 3,500
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1411774
Posted Saturday, January 26, 2013 1:07 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:37 AM
Points: 996, Visits: 3,089
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?

SELECT
v.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);





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1412069
Posted Saturday, January 26, 2013 1:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 10:38 AM
Points: 103, Visits: 448
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_PlanKey clustered located on PRIMARY fldPlanKey


  Post Attachments 
Execution3.sqlplan (3 views, 10.98 KB)
Post #1412073
Posted Saturday, January 26, 2013 3:50 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:37 AM
Points: 996, Visits: 3,089
Can you please supply DDL for tblConsul and #tmpPlan, including all indexes.

While you are there, can you supply the view definition.




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1412081
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse