Here is one that I wish I can say a lot more about and give solid SQL tables and data on.
But, though I have tried gallantly for the last few days, I have yet to be able to re-produce in a clean room environment.
I am here trying to troubleshoot slow queries and narrowed things down to one specific query that I will like to specifically discuss here.
select tblOrder.[orderNumber] , tblOrder.[storeNumber] , dbo.udf_DistributorDropOffCenter ( tblOrder.[storeNumber] , tblDistributor.[distributorName] ) from [dbo].[order] tblOrder inner join [dbo].[orderDetail] tblOrderDetail on tblOrder.[orderNumber] = tblOrderDetail.[orderNumber] inner join [dbo].[orderDetail] tblOrderDetail on tblOrder.[orderNumber] = tblOrderDetail.[orderNumber] inner join [dbo].[distribuor] tblDistributor on tblOrderDetail.[distributorID] = tblDistributor.[distributorID]
- So what we have is a pretty deep Relational Database Model
- Though, I spent time really trying to understand the SQL Plan, could not isolate the problem until I started dropping columns of the Select Clause. I was then able to isolate the problem to the inter-exchange between the Query and the Scaler function
- As part of the columns we are retrieving, we have included a call to a multi-argument Scaler function
- The function expects a few arguments spinkled across a few tables
- Unfortunately, some of the primary keys are composite and so just passing along one PK column is not always sufficient; we have to pass in more than one attribute to fully identify the row we want referenced
- Determine whether you have passed along all the arguments that will ensure row uniqueness
- Review your Scaler function and re-write it as Table Value Function. Scalers can only return a single cell. On the the other hand, TVFs can return more than 1 cell; in terms of multiple rows and columns
- If you want to stay with Scaler Functions, see whether you can return your column as a comma separated set of columns — please Google for comma separated values in SQL Server
- Stress test your Main SQL and see whether you you have repeating groups that you can avoid using “Group By”
select tblA.[id], count(*) from dbo.tblA tblA inner join dbo.tblB tblB on tblA.id = tblB.id group by tblA.id having count(*) > 1 order by count(*) desc
- Stress test your Scaler Function using the sample given above
Aforementioned, I wish I can say more about this. But, the actual code lines are buried deep within some proprietary code; which in turn is very domain specific.
Like Morgan Heritage (“Man in Love” – http://www.youtube.com/watch?v=QFeIejvNjnY), I have tried twice, yet I can not reproduce in my LAB Environment.
And, so I posted here to acknowledge the work we feed upon.
“As you quickly forget where you got it from, unless you quickly acknowledge who, where, and when”
- Transact SQL – Warning – No Join Predicate
- Scary DBA – No Join Predicate
- What exactly does no Join Predicate mean in SQL Server
- Idera.com – Missing Join Predicates Recommendation
- Should I be alarmed by this no join predicate warning