SQL Server Performance Forum – Threads Archive

Fun with Reporting Queries, Part 1

Greetings folks, I’m working on some reports for a project and I’ve run into a performance wall. The reporting aspect of this project imposes many restrictions on the construction of queries. The major restriction is that filtering must be done in the WHERE clause, not the ON section of the JOIN clause. Here’s the setup: <pre id="code">begin transaction GO create table a ( k1 int not null — Major group , k2 int not null — Minor group , k3 int not null — Object , k4 int not null — Sub-Object , d1 varchar(20) null — Description , constraint PK_a primary key clustered (k1, k2, k3, k4) ) create table b ( k1 int not null — Major group , k2 int not null — Minor group , k3 int not null — Object , k4 smallint not null — Year , d1 money null — Price , constraint PK_b primary key clustered (k1, k2, k3, k4) ) insert a values (1, 1, 0, 0, ‘abcdefg’) insert a values (1, 1, 1, 0, ‘hijklmnop’) insert a values (2, 2, 2, 0, ‘qrstuv’) insert b values (1, 1, 0, 2000, $25) insert b values (1, 1, 0, 2001, $50) insert b values (1, 1, 0, 2002, $75) insert b values (2, 2, 2, 2000, $5) GO set showplan_text on GO select * from a left hash join b on a.k1 = b.k1 and a.k2 = b.k2 and a.k3 = b.k3 where a.k1 = 1 and a.k2 = 1 GO set showplan_text off GO rollback transaction GO</pre id="code">The goal is to select all records from table a in a specific minor group and any corresponding records in table b. (I specified a hash join to simulate the join type that would be chosen for a large number of table rows.) Here is the execution plan generated:<pre id="code"> |–Hash Match(Left Outer Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[a].[k1], [a].[k2], [a].[k3])=(.[k1], .[k2], .[k3]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />([a].[k1]=.[k1] AND [a].[k2]=.[k2]) AND [a].[k3]=.[k3])) |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[db].[dbo].[a].[PK_a]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[a].[k1]=1 AND [a].[k2]=1 AND [a].[k3]=1) ORDERED FORWARD) |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[db].[dbo]..[PK_b]))</pre id="code">As you can see, the optimizer chooses to do a table scan on the inner table, even though the WHERE clause specifies values for non-nullable columns in the outer table that are directly joined to non-nullable columns in the inner table. Is there some reason that it must do a scan as opposed to a seek on table b? It seems like "forwarding" the selection criteria to the inner table would be a no-brainer for the optimizer. I’ve spent hours trying to find a solution to this problem, but to no avail. Adding <pre id="code">… and b.k1 = 1 and b.k2 = 1 …</pre id="code"> just makes it into an inner join, which is not what I need. It seems like the only way to get the data in a timely manner is to include the filter criteria in the ON section of the JOIN clause, which is not possible given the restrictions of the reporting context. Any ideas on how to optimize this query without violating the reporting restrictions? I can create views, but table-valued functions and FROM clause subqueries are a no-go. Thanks for any advice you can offer. If there is a solution out there for this problem, then there’s always Part 2… -Hartmut5

]]>