Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


where fldValue=123 vs join to table with one row


where fldValue=123 vs join to table with one row

Author
Message
souLTower
souLTower
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 559
I have a huge DB and a view that pulls records from multiple tables, some as big as 2M records. I have great indexes. I can query: select from myView where fldValue = 123.

It returns 3K records in .3 seconds. Happy. Now I need to return data for multiple fldValue values.

Select from myView where fldValue IN (123,456,789)

Works great. However this stinks:

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

So for a test, I reduced the temp table to one record. Still falls on its face. I have a few whacky solutions. My question is, why would this happen and is there a non whacky way to make the join to the temp table perform correctly?

Thanks
ST
Roland Alexander STL
Roland Alexander STL
Mr or Mrs. 500
Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)

Group: General Forum Members
Points: 517 Visits: 382
souLTower (1/23/2013)
I have a huge DB and a view that pulls records from multiple tables, some as big as 2M records. I have great indexes. I can query: select from myView where fldValue = 123.

It returns 3K records in .3 seconds. Happy. Now I need to return data for multiple fldValue values.

Select from myView where fldValue IN (123,456,789)

Works great. However this stinks:

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

So for a test, I reduced the temp table to one record. Still falls on its face. I have a few whacky solutions. My question is, why would this happen and is there a non whacky way to make the join to the temp table perform correctly?

Thanks
ST



What's the DDL for that temp table? Do you have the same PK and indexing on the temp table as you do on the tables underlying the view? Or are you leaving the temp table in a heap?

Roland Alexander
The Developing World


There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 3575
Can you post the actual execution plan for both queries here?

A wild guess is that parallell execution with hash match or sort is involved, and that the distribution of processed rows between each thread is so skewed that some threads run out of memory to store the hash buckets and therefore spilling them to tempdb.

Look for parallellism operators in the actual exec plan, and see if number of rows processed by each thread is very skewed. Also run a profiler trace on your spid with Sort Warnings and Hash Warnings included when executing the query.
souLTower
souLTower
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 559
The execution plans are pretty big. I'm not sure how to post them.
I get completely different execution plans between fldValue=123 and fldValue IN (select value from tempTable). I know that the optimizer is a mystery but to me, I'm asking for the same thing. It boils down to:

A) where fldValue=CONSTANT
B) where fldValue IN (CONSTANT, CONSTANT2, etc)
C) where fldValue IN (Select from table with 1 row)

Execution plan and performace for A and B is the same. Very fast.
C is terrible

Is there a hint or something I can try? I've tried adding an index to the temp table, using a table variable, no joy. I'm left with:

Create the IN () list as a SQL variable and run the query dynamically.
Query the temp table, get the value I want row by row, query the main query for each value and put the results in a temp table. Then query the temp table.

All of those ideas seem stupid to me.

Thanks for any additional thoughts.
ST
Roland Alexander STL
Roland Alexander STL
Mr or Mrs. 500
Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)

Group: General Forum Members
Points: 517 Visits: 382
souLTower (1/23/2013)
The execution plans are pretty big. I'm not sure how to post them.
I get completely different execution plans between fldValue=123 and fldValue IN (select value from tempTable). I know that the optimizer is a mystery but to me, I'm asking for the same thing. It boils down to:

A) where fldValue=CONSTANT
B) where fldValue IN (CONSTANT, CONSTANT2, etc)
C) where fldValue IN (Select from table with 1 row)

Execution plan and performace for A and B is the same. Very fast.
C is terrible

Is there a hint or something I can try? I've tried adding an index to the temp table, using a table variable, no joy. I'm left with:

Create the IN () list as a SQL variable and run the query dynamically.
Query the temp table, get the value I want row by row, query the main query for each value and put the results in a temp table. Then query the temp table.

All of those ideas seem stupid to me.

Thanks for any additional thoughts.
ST



Still like to know how that temp table is organized...

Roland Alexander
The Developing World


There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
souLTower
souLTower
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 559
Temp table is a heap with 1 row, 2 columns of int, int.

Looking for how to post an execution plan.
souLTower
souLTower
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 559
Uploaded the two execution plans.

Thanks!
ST
Attachments
FastPlan.sqlplan (13 views, 547.00 KB)
slowPlan.sqlplan (8 views, 547.00 KB)
souLTower
souLTower
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 559
Well, I can see the difference. I'm interested in knowing if there's a way to improve it.

When I use a constant in my query, the data is filtered at the first query in the execution plan. This greatly reduces the records upstream. When I use IN (temp), the records are filtered about halfway through the execution.
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3938 Visits: 6669
You need to get rid of the massive GROUP BY; you should be able to do the SUM() in a subquery rather than the main query, but I can't be sure which columns come from which views/tables so I can't rewrite any part of the query now.

Until then, at least GROUP BY the ORDER BY columns first, to reduce the sorting required to satisfy the query, viz:


   GROUP BY fldConsulNo, fldCCGroupCd, fldCCSortOrder,
      RC.fldCCCd, fldSubCCNo,
fldCCGroupDesc,
fldConsulName,
tblConsul.fldConsulKey,
      fldCCDesc, fldSubCCName, v.fldCostCenterKey
   ORDER BY fldConsulNo,       fldCCGroupCd,       fldCCSortOrder, fldCCCd , fldSubCCNo



SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
souLTower
souLTower
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 559
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search