Transact SQL – Warning – No Join Predicates in “SQL Statement – Select Clause” Scaler Functions

Introduction

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.

Background

I am here trying to troubleshoot slow queries and narrowed things down to one specific query that I will like to specifically discuss here.

Sample Query



  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]

Explanation

Quick knows:

  • 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

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

Possible Resolutions

  • 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

Conclusion

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.  

Here is my public thanks to Grant Fritchey (Scary DBA) – http://www.scarydba.com/ , Paul White http://sqlblog.com/blogs/paul_white/, and the rest of the public “committers”:

 

“As you quickly forget where you got it from, unless you quickly acknowledge who, where, and when”

Addendum

2015-01-12

 

References

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s