none
CTE & HASH UNION query hint

    Question

  • Hi everybody,

    I am updating a query for a report. The original query has an index hint for the JOIN and also a HASH UNION hint for the query (the query consists of two parts with UNION operator). Now, on the client we do one extra simple query to add a total amount for each group.

    My first thought was to remove this extra processing on the client (though I suspect it's rather quick) and add totals using cte of the original query and partition by group clause.

    Now, 1) Do you think this idea is good at all to add this extra column directly from SQL Server

    2) How (and there) should I add this query hint? It doesn't allow me to do so in CTE as I'm getting a syntax error expression.

    In the meantime I'm also experimenting with this query on a huge database with adding/removing hints.

    Thanks in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, May 12, 2011 8:17 PM
    Moderator

Answers

  • I'm not sure why you have the HASH UNION hint. The query is a UNION query, but thatnks to this Type column, this is fact a UNION ALL query, and the optimizer should be smart enough to recognize this.

    Also, it's a lot easier to read the time conditions if you use a 24-hour clock. Or even better write them as

      start_date >= '20110513' AND start_date < '20110513'

    By the way, in the first part of the UNION the interval for start_date is 11 years ago. Is this intententional?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 13, 2011 9:53 PM
  • Hi Naomi,

    Got a chance to look at the query and after analyzing them I would say -

    1. There seems no harm in adding the Total column in the query itself until the overall user experience is enhanced.
    2. For query hint, you can consider Erland's suggestions.

    Apart form the above points, you can also try modifying the t-sql as under -

    CREATE NONCLUSTERED INDEX idxDCI
     ON #SavedTrans (department, category, item, type)
    

    as this is the sequence in which the JOIN clause is written.

    Secondly, the last tsql could be changed as under to remove an extra JOIN -

    SELECT
     pb.*,
     g.first_name, g.last_name
    FROM (Select 
        p.trans_no, p.guest_no, p.pass_no, -0000000000000001. as booking_id,
        p.start_date as date_time, 'P' as [type]
       from 
        gst_pass p 
       where 
        exists (select 1 FROM #SavedTrans S WHERE S.trans_no = p.trans_no)
       UNION ALL
       Select 
        b.trans_no, b.guest_no, -0000000000000001. as pass_no,
        b.booking_id, b.start_time as date_time,
        'B' as [type]
       from 
        b_sched b 
       where 
        exists (select 1 FROM #SavedTrans S WHERE S.trans_no = b.trans_no)) pb
    left outer join guests g 
     on pb.guest_no=g.guest_no
    

    Hope, these inputs will be of some help to you.

     


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Saturday, May 14, 2011 6:18 AM

All replies

  • The query hint can only be applied at the end of the query.

    Whether it is a good idea to add the colunm to the query, it's hard to say since I don't know the query. But it seems that the query already has performance problems with all those hints, so maybe not.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, May 12, 2011 10:20 PM
  • I've changed this query a bit. I now first select into a temp table using this hint and then add extra column. I also moved one JOIN to the end. I think I'm having a performance improvement comparing with what I've started from. I'm concentrating on the SET STATISTICS ON results for now.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, May 12, 2011 11:01 PM
    Moderator
  • Could you please post the query if possible, for us to have a look at the actual query and then comment on it.
    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Friday, May 13, 2011 4:58 AM
  • > I'm concentrating on the SET STATISTICS ON results for now.

    I think the only thing worth looking at is wallclock time. SET STATISTICS IO can give some indication, but for instance, it will not tell you the difference between a merge join and a hash join.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 13, 2011 7:47 AM
  • The query after I made all the improvements is this:

    SET STATISTICS IO ON
    SET STATISTICS TIME ON
     IF OBJECT_ID('TempDB..#SavedTrans','U') IS NOT NULL DROP TABLE #SavedTrans
     	
    	 Select s.sale_no, s.date_time, s.first_name, s.last_name,
    	  s.acct_name, s.orig_sp as salespoint, s.orig_op as operator,
    	  t.trans_no, t.invoice_no, t.extension as amount, t.quantity, t.admissions,
    	  t.department, t.category, t.item, t.start_date, 'T' as [type]
    	  INTO #SavedTrans
    	  from sh_save s join tr_save t on s.sale_no=t.sale_no
    	  where 1=1 and t.start_date between '20000513 12:00:00 AM' and '20110513 11:59:59 PM'
    	  and t.finalized =0 and t.quantity <> 0 
    	  and 1=1 --and s.orig_op between 'BOB  ' and 'BOB  '
        AND not exists (select 1 from resrvatn r WHERE r.reserv_no = s.reserv_no and r.finalized = 1)		
    	 Union 
    	 Select s.sale_no, s.date_time, s.first_name, s.last_name, s.acct_name, 
    	 s.orig_sp as salespoint, s.orig_op as operator,
    	  t.trans_no, s.invoice_no, t.extension as amount, t.quantity, t.admissions,
    	  t.department, t.category, t.item, t.date_time as start_date, 'I' as [type]
    	  from sh_save s join transact t WITH (index = invoice_no) on s.invoice_no=t.invoice_no 
    	  and t.message LIKE 'SSNO%'
    	  where s.finalized=0 and s.invoice_no <> 0 
    	  and s.sale_no in (select sale_no from tr_save t where 1=1 and t.start_date between '20000513 12:00:00 AM' and '20110513 11:59:59 PM') 
    	  and 1=1 --and s.orig_op between 'BOB  ' and 'BOB  ' 
    	  AND not exists (select 1 from resrvatn r WHERE r.reserv_no = s.reserv_no and r.finalized = 1)	
     		OPTION (HASH UNION) 
     -- Add totals
     CREATE NONCLUSTERED INDEX idxtrans_no
     ON #SavedTrans (trans_no)
     CREATE NONCLUSTERED INDEX idxsale_no
     ON #SavedTrans (sale_no) 
     CREATE NONCLUSTERED INDEX idxDCI
     ON #SavedTrans (item, category, department, type)
     
     SELECT *, 
     CASE WHEN Type = 'I' THEN SPACE(25) ELSE isnull(i.descrip, 'description not available') END as descrip,
     SUM(amount) OVER(Partition BY sale_no) as Total FROM #SavedTrans t
     left outer join (SELECT department, category, item, descrip FROM items) i 
     on t.department=i.department and t.category=i.category and t.item=i.item AND t.TYPE = 'T'
     order by t.acct_name, t.last_name, t.first_name, t.date_time, t.start_date, t.trans_no;
    -- Get Passes and Bookings
    
    Select p.trans_no, p.guest_no, p.pass_no, -0000000000000001. as booking_id,
    p.start_date as date_time, g.first_name, g.last_name, 'P' as [type]
    from gst_pass p left outer join guests g on p.guest_no=g.guest_no
    where exists (select 1 FROM #SavedTrans S WHERE S.trans_no = p.trans_no)
    UNION ALL
    Select b.trans_no, b.guest_no, -0000000000000001. as pass_no,
    b.booking_id, b.start_time as date_time,
    g.first_name, g.last_name, 'B' as [type]
    from b_sched b left outer join guests g on b.guest_no=g.guest_no
    where exists (select 1 FROM #SavedTrans S WHERE S.trans_no = b.trans_no)
    SET STATISTICS TIME OFF
    SET STATISTICS IO OFF
    
    

    with the following output:

    Table 'resrvatn'. Scan count 10, logical reads 40456, physical reads 0, read-ahead reads 19, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'sh_save'. Scan count 5, logical reads 92098, physical reads 221, read-ahead reads 10347, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'tr_save'. Scan count 10, logical reads 136976, physical reads 7, read-ahead reads 136327, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'transact'. Scan count 1053, logical reads 155581, physical reads 17, read-ahead reads 11339, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

     

    (7603 row(s) affected)

     

    (1 row(s) affected)

     

    SQL Server Execution Times:

       CPU time = 7575 ms,  elapsed time = 29775 ms.

    SQL Server parse and compile time: 

       CPU time = 0 ms, elapsed time = 1 ms.

    Table '#SavedTrans_________________________________________________________________________________________________________000000000801'. Scan count 1, logical reads 156, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

     

    SQL Server Execution Times:

       CPU time = 15 ms,  elapsed time = 114 ms.

     

    SQL Server Execution Times:

       CPU time = 15 ms,  elapsed time = 117 ms.

    SQL Server parse and compile time: 

       CPU time = 0 ms, elapsed time = 1 ms.

    Table '#SavedTrans_________________________________________________________________________________________________________000000000801'. Scan count 1, logical reads 156, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

     

    SQL Server Execution Times:

       CPU time = 32 ms,  elapsed time = 115 ms.

     

    SQL Server Execution Times:

       CPU time = 32 ms,  elapsed time = 128 ms.

    SQL Server parse and compile time: 

       CPU time = 0 ms, elapsed time = 1 ms.

    Table '#SavedTrans_________________________________________________________________________________________________________000000000801'. Scan count 1, logical reads 156, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

     

    SQL Server Execution Times:

       CPU time = 47 ms,  elapsed time = 149 ms.

     

    SQL Server Execution Times:

       CPU time = 47 ms,  elapsed time = 151 ms.

     

    (7603 row(s) affected)

    Table '#SavedTrans_________________________________________________________________________________________________________000000000801'. Scan count 5, logical reads 156, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 12950, logical reads 61981, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'items'. Scan count 0, logical reads 5575, physical reads 839, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

     

    (1 row(s) affected)

     

    SQL Server Execution Times:

       CPU time = 875 ms,  elapsed time = 24959 ms.

     

    (6043 row(s) affected)

    Table '#SavedTrans_________________________________________________________________________________________________________000000000801'. Scan count 10, logical reads 116, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'gst_pass'. Scan count 5, logical reads 15882, physical reads 0, read-ahead reads 15872, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'guests'. Scan count 10, logical reads 55176, physical reads 0, read-ahead reads 27588, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'b_sched'. Scan count 7598, logical reads 65157, physical reads 50, read-ahead reads 2250, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

     

    (1 row(s) affected)

     

    SQL Server Execution Times:

       CPU time = 1843 ms,  elapsed time = 14969 ms.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, May 13, 2011 1:21 PM
    Moderator
  • I'm not sure why you have the HASH UNION hint. The query is a UNION query, but thatnks to this Type column, this is fact a UNION ALL query, and the optimizer should be smart enough to recognize this.

    Also, it's a lot easier to read the time conditions if you use a 24-hour clock. Or even better write them as

      start_date >= '20110513' AND start_date < '20110513'

    By the way, in the first part of the UNION the interval for start_date is 11 years ago. Is this intententional?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 13, 2011 9:53 PM
  • So stupid of me again. I wrote the response, then in the middle decided to vote your post and of course lost my response. The double stupidity is the fact I did it before in the past and thought I took a note to myself to never do this again. And forgot... :)

    Anyway, to repeat. Yes, it was a good catch of the type column, I didn't notice. I'll get rid of the UNION and hint and switch to UNION ALL and re-test. Too bad I already posted the changes and they were already 'deployed', but not a big deal, I'll re-do.

    As for the dates - the dates are coming generated from our application, so I can not change the format. I can, of course, do it in the tests I run.

    Under normal circumstances this report is usually run for the current date (or, say, for a week worth of data). But since I don't have lots of records in my test data, I'm picking the big range to make sure to get data. Then I just was using the same query on a big production database. I can play with shorter date ranges also to make it more realistic.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, May 13, 2011 10:33 PM
    Moderator
  • Hi Naomi,

    Got a chance to look at the query and after analyzing them I would say -

    1. There seems no harm in adding the Total column in the query itself until the overall user experience is enhanced.
    2. For query hint, you can consider Erland's suggestions.

    Apart form the above points, you can also try modifying the t-sql as under -

    CREATE NONCLUSTERED INDEX idxDCI
     ON #SavedTrans (department, category, item, type)
    

    as this is the sequence in which the JOIN clause is written.

    Secondly, the last tsql could be changed as under to remove an extra JOIN -

    SELECT
     pb.*,
     g.first_name, g.last_name
    FROM (Select 
        p.trans_no, p.guest_no, p.pass_no, -0000000000000001. as booking_id,
        p.start_date as date_time, 'P' as [type]
       from 
        gst_pass p 
       where 
        exists (select 1 FROM #SavedTrans S WHERE S.trans_no = p.trans_no)
       UNION ALL
       Select 
        b.trans_no, b.guest_no, -0000000000000001. as pass_no,
        b.booking_id, b.start_time as date_time,
        'B' as [type]
       from 
        b_sched b 
       where 
        exists (select 1 FROM #SavedTrans S WHERE S.trans_no = b.trans_no)) pb
    left outer join guests g 
     on pb.guest_no=g.guest_no
    

    Hope, these inputs will be of some help to you.

     


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Saturday, May 14, 2011 6:18 AM
  • > As for the dates - the dates are coming generated from our application, so I can not change the format. I can, of course, do it in the tests I run.

    I suppose that you are aware of that with the current format, there is a small bug: you will not get events that happened in the last minute of the day.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, May 14, 2011 9:35 AM
  • Yes, I am sure aware of the problem. But that code and the application was written long ago before I joined the team, so it's not worth the trouble to make a wave over this problem. Besides, other developers also are aware. There are other problems, say, we embed parameters into the query which is generated. It's all other the place and so can not be changed as well. Of course, I'm well aware it's a bad practice and always try to warn anyone in the forums against it.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Sunday, May 15, 2011 2:26 AM
    Moderator
  • Hi Vinay,

    Thanks a lot for your pointers. I think I played with some modifications in the second query (inner join vs. where exists), but haven't moved the last join outside.

    I'll play with your ideas, thanks a lot.

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Sunday, May 15, 2011 2:29 AM
    Moderator
  • I'm trying the changed query, but the original seems to execute faster. Do you see what may be a problem? I can also try to test separately idea for the first and for the second query.

    Results

    Max Min Avg Test Count TestType

    67 67 67 2 HASH UNION

    261 253 257 2 UNION ALL

     

    CREATE TABLE #Res (Elapsed INT, TestType VARCHAR(20))
    DECLARE @Elapsed INT, @StartTime DATETIME, @Loop INT
    
    SET @Loop = 1
    WHILE @LOOP <=2
    begin
    SET @StartTime = CURRENT_TIMESTAMP
    
     IF OBJECT_ID('TempDB..#SavedTrans','U') IS NOT NULL DROP TABLE #SavedTrans
     	
    	 IF OBJECT_ID('TempDB..#SavedTrans','U') IS NOT NULL DROP TABLE #SavedTrans
     	
    	 Select s.sale_no, s.date_time, s.first_name, s.last_name,
    	 s.acct_name, s.orig_sp as salespoint, s.orig_op as operator,
    	 t.trans_no, t.invoice_no, t.extension as amount, t.quantity, t.admissions,
    	 t.department, t.category, t.item, t.start_date, 'T' as [type]
    	 INTO #SavedTrans
    	 from sh_save s join tr_save t on s.sale_no=t.sale_no
    	 where 1=1 and t.start_date between '20000516 12:00:00 AM' and '20110516 11:59:59 PM'
    	 and t.finalized =0 and t.quantity <> 0 
    	 and 1=1 
      AND not exists (select 1 from resrvatn r WHERE r.reserv_no = s.reserv_no and r.finalized = 1)		
    	 Union 
    	 Select s.sale_no, s.date_time, s.first_name, s.last_name, s.acct_name, 
    	 s.orig_sp as salespoint, s.orig_op as operator,
    	 t.trans_no, s.invoice_no, t.extension as amount, t.quantity, t.admissions,
    	 t.department, t.category, t.item, t.date_time as start_date, 'I' as [type]
    	 from sh_save s join transact t WITH (index = invoice_no) on s.invoice_no=t.invoice_no 
    	 and t.message LIKE 'SSNO%'
    	 where s.finalized=0 and s.invoice_no <> 0 
    	 and s.sale_no in (select sale_no from tr_save t where 1=1 and t.start_date between '20000516 12:00:00 AM' and '20110516 11:59:59 PM') 
    	 and 1=1 
    	 AND not exists (select 1 from resrvatn r WHERE r.reserv_no = s.reserv_no and r.finalized = 1)	
     		OPTION (HASH UNION) 
     -- Add totals
     CREATE NONCLUSTERED INDEX idxtrans_no
     ON #SavedTrans (trans_no)
     CREATE NONCLUSTERED INDEX idxsale_no
     ON #SavedTrans (sale_no) 
     CREATE NONCLUSTERED INDEX idxDCI
     ON #SavedTrans (item, category, department, type)
     
     SELECT *, 
     CASE WHEN Type = 'I' THEN SPACE(25) ELSE isnull(i.descrip, 'description not available') END as descrip,
     SUM(amount) OVER(Partition BY sale_no) as Total FROM #SavedTrans t
     left outer join (SELECT department, category, item, descrip FROM items) i 
     on t.department=i.department and t.category=i.category and t.item=i.item AND t.TYPE = 'T'
     order by t.acct_name, t.last_name, t.first_name, t.date_time, t.start_date, t.trans_no;
    -- Get Passes and Bookings
    
    Select p.trans_no, p.guest_no, p.pass_no, -0000000000000001. as booking_id,
    p.start_date as date_time, g.first_name, g.last_name, 'P' as [type]
    from gst_pass p left outer join guests g on p.guest_no=g.guest_no
    where exists (select 1 FROM #SavedTrans S WHERE S.trans_no = p.trans_no)
    UNION ALL
    Select b.trans_no, b.guest_no, -0000000000000001. as pass_no,
    b.booking_id, b.start_time as date_time,
    g.first_name, g.last_name, 'B' as [type]
    from b_sched b left outer join guests g on b.guest_no=g.guest_no
    where exists (select 1 FROM #SavedTrans S WHERE S.trans_no = b.trans_no)
    
    SET @Elapsed = DATEDIFF(s, @StartTime, CURRENT_TIMESTAMP)
    
    INSERT INTO #Res
      ( Elapsed, TestType )
    VALUES ( @Elapsed, 'HASH UNION' 
       )
     SET @Loop = @Loop+1  
    END
    GO
    
    DECLARE @Elapsed INT, @StartTime DATETIME, @Loop INT
    SET @Loop = 1
    WHILE @LOOP <=2
    begin
    SET @StartTime = CURRENT_TIMESTAMP
    
     IF OBJECT_ID('TempDB..#SavedTrans','U') IS NOT NULL DROP TABLE #SavedTrans	 
     	
    	 Select s.sale_no, s.date_time, s.first_name, s.last_name,
    	 s.acct_name, s.orig_sp as salespoint, s.orig_op as operator,
    	 t.trans_no, t.invoice_no, t.extension as amount, t.quantity, t.admissions,
    	 t.department, t.category, t.item, t.start_date, 'T' as [type]
    	 INTO #SavedTrans
    	 from sh_save s join tr_save t on s.sale_no=t.sale_no
    	 where 1=1 and t.start_date between '20000516 12:00:00 AM' and '20110516 11:59:59 PM'
    	 and t.finalized =0 and t.quantity <> 0 
    	 and 1=1 
      AND not exists (select 1 from resrvatn r WHERE r.reserv_no = s.reserv_no and r.finalized = 1)		
    	 Union ALL
    	 Select s.sale_no, s.date_time, s.first_name, s.last_name, s.acct_name, 
    	 s.orig_sp as salespoint, s.orig_op as operator,
    	 t.trans_no, s.invoice_no, t.extension as amount, t.quantity, t.admissions,
    	 t.department, t.category, t.item, t.date_time as start_date, 'I' as [type]
    	 from sh_save s join transact t WITH (index = invoice_no) on s.invoice_no=t.invoice_no 
    	 and t.message LIKE 'SSNO%'
    	 where s.finalized=0 and s.invoice_no <> 0 
    	 and s.sale_no in (select sale_no from tr_save t where 1=1 and t.start_date 
    	 between '20000516 12:00:00 AM' and '20110516 11:59:59 PM') 
    	 and 1=1 
    	 AND not exists (select 1 from resrvatn r WHERE r.reserv_no = s.reserv_no and r.finalized = 1)	
     
     -- Add totals
     CREATE NONCLUSTERED INDEX idxtrans_no
     ON #SavedTrans (trans_no)
     CREATE NONCLUSTERED INDEX idxsale_no
     ON #SavedTrans (sale_no) 
     CREATE NONCLUSTERED INDEX idxDCI
     ON #SavedTrans (item, category, department, type)
     
     SELECT *, 
     CASE WHEN Type = 'I' THEN SPACE(25) ELSE isnull(i.descrip, 'description not available') END as descrip,
     SUM(amount) OVER(Partition BY sale_no) as Total FROM #SavedTrans t
     left outer join (SELECT department, category, item, descrip FROM items) i 
     on t.item=i.item and t.category=i.category and t.department=i.department AND t.TYPE = 'T'
     order by t.acct_name, t.last_name, t.first_name, t.date_time, t.start_date, t.trans_no;
    -- Get Passes and Bookings
    
    SELECT *,
    g.first_name, g.last_name
    FROM (Select p.trans_no, p.guest_no, p.pass_no, -0000000000000001. as booking_id,
    p.start_date as date_time, 'P' as [type]
    from gst_pass p 
    where exists (select 1 FROM #SavedTrans S WHERE S.trans_no = p.trans_no)
    UNION ALL
    Select b.trans_no, b.guest_no, -0000000000000001. as pass_no,
    b.booking_id, b.start_time as date_time,
     'B' as [type]
    from b_sched b 
    where exists (select 1 FROM #SavedTrans S WHERE S.trans_no = b.trans_no)) pb
    left outer join guests g on pb.guest_no=g.guest_no
    
    SET @Elapsed = DATEDIFF(s, @StartTime, CURRENT_TIMESTAMP)
    
    INSERT INTO #Res
      ( Elapsed, TestType )
    VALUES ( @Elapsed, 'UNION ALL' 
       )
     SET @Loop = @Loop+1  
    END
    GO
    SELECT MAX(Elapsed) AS [Max], MIN(Elapsed) AS [Min], AVG(Elapsed) AS [Avg],
    COUNT(*) AS [Test Count],
     TestType 
    FROM #Res   
    GROUP BY TestType
    
    

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, May 16, 2011 10:10 PM
    Moderator
  • > I'm trying the changed query, but the original seems to execute faster. Do you see what may be a problem? I can also try to test separately idea for the first and for the second query.

    It's a fairly complicated query, and I don't have table and index definitions.

    But it occurs to me that if you extend the intervall with 11 extra years, you are changing the situation quite a bit. It is not likely that you will get the same plan then the interval is a single day as when the interval is years, at least not if you skip the hints. Are you sure that you are testing the right thing?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, May 17, 2011 10:00 PM
  • I've tested with smaller intervals also. Basically, it seems to me that I was getting very similar performance with UNION and a hint vs. UNION ALL and no hint. So, I did update the code today to get rid of UNION with the hint and keep UNION ALL and no hint.

    Trying to join with Guests later as Vinaj suggested seemed to degrade the performance, so I kept the original. Switching the order in the JOIN (e.g. item = item and category = category and department = department) to match the syntax I'm creating seems to speed up a bit (although I'm not sure I understand why order of the fields in the JOIN condition matters), so I applied this minor change as well.

    I think I'll keep this version for now

     

     IF OBJECT_ID('TempDB..#SavedTrans','U') IS NOT NULL DROP TABLE #SavedTrans
     		Select s.sale_no, s.date_time, s.first_name, s.last_name, s.acct_name,
    		s.orig_sp as salespoint, s.orig_op as operator,
    		t.trans_no, t.invoice_no, t.extension as amount, t.quantity, t.admissions,
    		t.department, t.category, t.item, t.start_date, 'T' as [type]
    		into #SavedTrans
    		from sh_save s join tr_save t on s.sale_no=t.sale_no		
    		where t.finalized=0 and t.quantity<>0 and 
    		 s.date_time between '20110101 5:26:44 PM' and '20110517 11:59:59 PM'
      	  AND not exists (select 1 from resrvatn r WHERE r.reserv_no = s.reserv_no and r.finalized = 1)			
    		union all
    		Select s.sale_no, s.date_time, s.first_name, s.last_name,
    		s.acct_name, s.salespoint, s.operator,
    		t.trans_no, s.invoice_no, t.extension as amount, t.quantity, t.admissions,
    		t.department, t.category, t.item, t.date_time as start_date, 'I' as type
    		from sh_save s join transact t WITH (index = invoice_no) on s.invoice_no=t.invoice_no and
    		t.message LIKE 'SSNO%'
    		where s.finalized=0 and s.invoice_no <> 0 and 
    		 s.date_time between '20110101 5:26:44 PM' and '20110517 11:59:59 PM' 
    	  AND not exists (select 1 from resrvatn r WHERE r.reserv_no = s.reserv_no and r.finalized = 1)	
    	 
     -- Add totals
     CREATE NONCLUSTERED INDEX idxtrans_no
     ON #SavedTrans (trans_no)
     CREATE NONCLUSTERED INDEX idxsale_no
     ON #SavedTrans (sale_no) 
     CREATE NONCLUSTERED INDEX idxDCI
     ON #SavedTrans (item, category, department, type)
     
     SELECT *, 
     CASE WHEN Type = 'I' THEN SPACE(25) ELSE isnull(i.descrip, 'description not available') END as descrip,
     SUM(amount) OVER(Partition BY sale_no) as Total FROM #SavedTrans t
     left outer join (SELECT department, category, item, descrip FROM items) i 
     on t.item=i.item and t.category=i.category AND t.department=i.department and t.TYPE = 'T'
     order by t.acct_name, t.last_name, t.first_name, t.date_time, t.start_date, t.trans_no;
    -- Get Passes and Bookings
    
    Select p.trans_no, p.guest_no, p.pass_no, -0000000000000001. as booking_id,
    p.start_date as date_time, g.first_name, g.last_name, 'P' as [type]
    from gst_pass p left outer join guests g on p.guest_no=g.guest_no
    where exists (select 1 FROM #SavedTrans S WHERE S.trans_no = p.trans_no)
    UNION ALL
    Select b.trans_no, b.guest_no, -0000000000000001. as pass_no,
    b.booking_id, b.start_time as date_time,
    g.first_name, g.last_name, 'B' as [type]
    from b_sched b left outer join guests g on b.guest_no=g.guest_no
    where exists (select 1 FROM #SavedTrans S WHERE S.trans_no = b.trans_no)
    
    unless you see some possible problems right away.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, May 17, 2011 10:24 PM
    Moderator
  • Implement the following optimization quidelines:

    http://www.sqlusa.com/articles/query-optimization/

    Let us know if helpful.

     

     


    Kalman Toth, SQL Server & BI Training; SQL 2008 GRAND SLAM
    Sunday, May 22, 2011 5:43 PM
    Moderator