September 2, 2011 at 3:08 pm
In our site, we use Business Objects XIR3 to generate reports from an SS2K5 database. Business Objects automatically generates SQL based on a metadata layer that we've developed. It's worked for years until we added some additional security features.
Prior to the upgrade, the SQL that got generated looked something like this:
select EmployeeID
from DWData..DimEMployee a
JOIN DimDEpartment b on b.DEpartmentKey = a.HomeDepartmentKey
WHERE a.HomeDepartmentKey IN (SELECT DepartmentKey FROM Common.dbo.AuthorizedDepartments('PROA4','PP.IHEE') )
This would return the desired results set in 3 seconds.
After the upgrade,the SQL looks like this:
select EmployeeID
from DWData..DimEMployee a
JOIN DimDepartment b on b.DepartmentKey = a.HomeDepartmentKey
WHERE a.HomeDepartmentKey IN (SELECT DepartmentKey FROM Common.dbo.AuthorizedDepartments('PROA4','PP.IHEE') )
AND a.HomeDepartmentKey IN (SELECT DepartmentKey FROM Common.dbo.AuthorizedDepartments('PROA4','PP.IHEE'))
Notice that the bolded text is exactly the same as the previous line in the WHERE clause. The SELECT returns the identical results, except that it takes 20 seconds.
I've run this through QTA many times, and have implemented the suggestions, only to see no improvement. Of note is that the Execution Plan for query #1 is pretty complicated. The resulting query plan for Query #2 resembles the schematic for a nuclear plant.
How could adding a duplicate line break the query so badly, and what things should be looking at to fiz it? Removing the security changes we've had to put in is a last resort, but at the same time, we have very little power over how Business Objects generates the SQL.
Any help will be appreciated!
September 4, 2011 at 6:01 am
I'm assuming that Common.dbo.AuthorizedDepartments is a table valued function, right? Is it a multi-statement table valued function? If so, that's the problem right there. Based on what you're saying about the complexity of the execution plan though, I suspect it's not. But, it sounds like it's a very complex query. Normally, the query optimizer goes through a process called simplification where it finds and eliminates unnecessary tables, joins, etc. that are not needed to satisfy a query. It's pretty cool stuff. But, as queries get more complex, the ability for the optimizer to perform simplification gets radically truncated. If you have this TVF and it's really complex and you're joining to it twice, the optimizer is probably not going to figure out that it's duplicated and ignore it. Instead, it's going to process the whole thing, twice. Further, whatever simplification you were getting from the original query is also probably lost, leading to the larger increase in performance.
To fix it, if it's generated code that's causing the problem, that's probably where you need to go. However, a TVF that is that complex could be causing your problems all by itself. The answer to that is to reduce the complexity of the code. Your IN statement is effectively a JOIN. Try making it one. See what that does to the query. If that doesn't work, try eliminating the function and make the second query a derived table inside your query. If that doesn't work, you need to assess what and how that query is performing in order to attempt to tune it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply