Fun with Report Queries, Part 2 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Fun with Report Queries, Part 2

Hello again,<br /><br />This topic is around just in case there is a solution to Part 1. If you haven’t read Part 1, I’m basically at a dead-end trying to set up some views/queries for reporting. The cause of all my grief is that all the record filtering has to be done in the WHERE clause of the query.<br /><br />I have 2 tables, a and b. Table a has products, and table b has annual prices for each product. Table b has zero to many records for each record in table a. Part 1 is the task of retrieving all of a small set of records from table a and all matching records in table b in an efficient manner, all without using FROM clause subqueries or table-valued functions.<br /><br />Part 2 refines the Part 1 query slightly, returning at most 1 record from table b (a price for a specific year) for each record in table a. This could easily be done in the ON section of the JOIN clause, but again, the reporting restrictions dictate WHERE clause filtering only.<br /><br />Given the query from Part 1:<pre id="code"><font face="courier" size="2" id="code">select *<br />from a<br /> left hash join b<br /> on a.k1 = b.k1<br /> and a.k2 = b.k2<br /> and a.k3 = b.k3<br />where a.k1 = 1<br /> and a.k2 = 1</font id="code"></pre id="code">one might consider adding<pre id="code"><font face="courier" size="2" id="code">…<br /> and (b.k4 = 2000 or b.k4 is null)<br />…</font id="code"></pre id="code">However, this approach will eliminate rows where there is a price for 2002 and later, but no price for 2000.<br /><br />The closest I’ve gotten on this one is a view that cross joins table a with the invaluable numbers table:<pre id="code"><font face="courier" size="2" id="code">create view a_year<br />as<br /> select a.*, convert( smallint, nums.n ) year<br /> from a<br /> cross join nums</font id="code"></pre id="code">This view is then left joined to table b:<pre id="code"><font face="courier" size="2" id="code">select *<br />from a_year a<br /> left hash join b<br /> on a.k1 = b.k1<br /> and a.k2 = b.k2<br /> and a.k3 = b.k3<br /> and a.year = b.k4<br />where a.k1 = 1<br /> and a.k2 = 1<br /> and a.year = 2000</font id="code"></pre id="code">Using this setup generates the following plan:<pre id="code"><font face="courier" size="2" id="code"> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1006]=Convert([nums].[n])))<br /> |–Hash Match(Left Outer Join, <br /> HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[a].[k1], [a].[k2], [a].[k3], [Expr1007])<br /> =(<b>.[k1], <b>.[k2], <b>.[k3], <b>.[k4]), <br /> RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />(([a].[k1]=<b>.[k1] AND [a].[k2]=<b>.[k2]) <br /> AND [a].[k3]=<b>.[k3]) <br /> AND [Expr1007]=<b>.[k4]))<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1007]=Convert([nums].[n])))<br /> | |–Nested Loops(Inner Join)<br /> | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[db].[dbo].[a].[PK_a]), <br /> SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[a].[k1]=1 AND [a].[k2]=1) <br /> ORDERED FORWARD)<br /> | |–Nested Loops(Inner Join, <br /> OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1008], [Expr1009], [Expr1010]))<br /> | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1008]=Convert(2000)-1, <br /> [Expr1009]=Convert(2000)+1, <br /> [Expr1010]=If (Convert(2000)-1=NULL) then 0 else 6<br /> |If (Convert(2000)+1=NULL) then 0 else 10))<br /> | | |–Constant Scan<br /> | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[db].[dbo].[Nums].[PK_nums]), <br /> SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[nums].[n] &gt; [Expr1008] AND [nums].[n] &lt; [Expr1009]),<br /> WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />Convert([nums].[n])=2000) <br /> ORDERED FORWARD)<br /> |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[db].[dbo].<b>.[PK_b]))</font id="code"></pre id="code">The query appears to return the correct data, but the Index Scan on table b is a real performance killer (table b has about 20 million rows). There’s a little strangeness in the plan due to the conversion of the int type in the numbers table to the smallint that is used in table b.<br /><br />Any suggestions on perhaps another way to approach this?<br /><br /><br />-Hartmut5
]]>