2

I am trying to optimize the speed of a query which uses a redundant query block. I am trying to do a row-wise join in sql server 2008 using the query below.

Select * from 
(<complex subquery>) cq
join table1 t1 on (cq.id=t1.id)
union
Select * from  
<complex subquery> cq
join table2 t2 on (cq.id=t2.id)

The <complex subquery> is exactly the same on both the union sub query pieces except we need to join it with multiple different tables to obtain the same columnar data.

Is there any way i can either rewrite the query to make it faster without using a temporary table to cache results?

Whimsical
  • 5,985
  • 1
  • 31
  • 39
  • 2
    CTE's perhaps? Google for it, and see if it suites your needs. – leppie Nov 30 '10 at 12:07
  • 1
    @leppie - CTEs will not generally materialize the results without using a plan guide as described here http://explainextended.com/2009/05/28/generating-xml-in-subqueries/ – Martin Smith Nov 30 '10 at 12:14

3 Answers3

3

Why not use a temporary table and see if that improves the execution stats?

In some circumstances the Query Optimizer will automatically add a spool to the plan that caches sub queries this is basically just a temporary table though.

Have you checked your current plan to be sure that the query is actually being evaluated more than once?

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Temp table is the way to go, even though you might need to make sure you don't trash your tempdb thus slowing the rest of your DB. – Pavel Urbančík Nov 30 '10 at 13:02
  • The query mentioned above is used on a search box, which means at the present user load we will have this query fired anywhere between 100 to 1000 times per second...Do you think the tempDb can handle that ? – Whimsical Dec 04 '10 at 05:52
1

Without a concrete example it's difficult to help, but try a WITH statement, something like:

WITH csq(x,y,z) AS (
  <complex subquery>
)
Select * from 
 csq
join table1 t1 on (cq.id=t1.id)
union
Select * from  
 csq
join table2 t2 on (cq.id=t2.id)

it sometimes speeds things up no end

smirkingman
  • 6,167
  • 4
  • 34
  • 47
0

Does the nature of your query allow you to invert it? Instead of "(join, join) union", do "(union) join"? Like:

Select * from
(<complex subquery>) cq
join (
    Select * from table1 t1
    union 
    Select * from table2 t2
) ts
on cq.id=ts.id

I'm not really sure if double evaluation of your complex query is actually what's wrong. But, as per your question, this would be a form of the query that would encourage SQL to only evaluate <complex query> once.

Matt
  • 4,388
  • 1
  • 15
  • 8
  • Unfortunately not...the results from the complex query is required in both the sql union snippets....and yes im trying to avoid multiple evaluation of complex subquery – Whimsical Dec 01 '10 at 09:16