How to make this function faster

Transact-SQL
1

Hello

i have the following query but it takes long can you help me please?
what should i do to make it faster?

set language german;
set dateformat ymd

declare @restaurantID uniqueidentifier
set @restaurantID = '5CB89A18-2091-4A42-B56C-6BC18F927208'
declare @startdate datetime
declare @enddate datetime

set @startdate = '2011-02-01'
set @enddate = '2011-02-02'


-- CH & CA Zahler (nur Stützungsanteil)

select DAT_Bon.ID as BonID,
	   DAT_Bon.CreationDate,
	   --DAT_CustomerGroup_HISTArchive.CustomerGroup_ID as CustomerGroupID, 
       DAT_CustomerGroup_HISTArchive.Name as CustomerGroupName, 
	   DAT_CustomerGroup_HISTArchive.CustomerGroupNumber as CustomerGroupNumber,
	   --{1} AS SubGroupName, 
	   --{2} AS SubGroupID,
       DAT_BonLine.VATValue as vat, 
		SUM(Subsidy * (1 / (1 + VATValue))) netPrice,
		-- SUM(Round(Round(Subsidy * (1 / (1 + VATValue)), 2) * (1 + DAT_bonline.VATValue), 2)) grossPrice,
		0.0 grossPrice,
		-- SUM(Round(Round(Subsidy * (1 / (1 + VATValue)), 2) * DAT_bonline.VATValue, 2)) VATValuePrice,
		0.0 VATValuePrice,
       sum(DAT_bonline.Quantity) as Quantity 

FROM DAT_Bon WITH (NOLOCK) 
	inner join DAT_Person_HISTArchive WITH (NOLOCK) on dbo.GetPersonArchiveID(DAT_Bon.Person_ID, DAT_Bon.CreationDate) = DAT_Person_HISTArchive.id	
	inner join DAT_CustomerGroup_HISTArchive WITH (NOLOCK) on dbo.GetCustomerGroupArchiveID(DAT_Person_HISTArchive.CustomerGroup_ID, DAT_Bon.CreationDate) = DAT_CustomerGroup_HISTArchive.ID	
	inner join INV_InvoiceReceiver_CustomerGroup WITH (NOLOCK) on INV_InvoiceReceiver_CustomerGroup.CustomerGroup_ID = DAT_CustomerGroup_HISTArchive.CustomerGroup_ID
    inner join DAT_BonLine WITH (NOLOCK) on DAT_BonLine.Bon_ID = DAT_Bon.ID
    inner join DAT_Article_HISTArchive WITH (NOLOCK) on dbo.GetArticleArchiveID(DAT_Bonline.Article_ID, DAT_Bon.CreationDate) = DAT_Article_HISTArchive.ID
    inner join LOV_ArticleGroup WITH (NOLOCK) on LOV_ArticleGroup.ID = DAT_Article_HISTArchive.ArticleGroup_ID

	-- {3} => inner join DAT_Card_HISTArchive on dbo.GetCardArchiveID(DAT_Bon.card_id, DAT_Bon.CreationDate) = DAT_Card_HISTArchive.ID if card filtering is active, otherwise no join
   
where DAT_Bon.CreationDate between @startdate and @enddate
 AND DAT_Article_HISTArchive.Restaurant_ID = @restaurantID
 and Subsidy != 0 -- to include BO-Storno in the month the storno was made, here are also negative subsidy amounts allowed
 AND dat_bon.id in 
 (select bon_id from DAT_Bon_PaymentType  WITH (NOLOCK)
 inner join LOV_PaymentType on LOV_PaymentType.ID = DAT_Bon_PaymentType.PaymentType_ID
 where Bon_ID = DAT_Bon.ID and 
	LOV_PaymentType.ShortName in ('CH', 'CA','ID')) -- only CH and CA
and DAT_BonLine.Cancel_ID IS NULL and DAT_Bon.IsCancel <> 1

-- only decrement card transactions
and DAT_Bon.ID not in
	 (SELECT Bon_ID FROM DAT_CardTransaction WITH (NOLOCK) INNER JOIN                                
	 LOV_TransactionType WITH (NOLOCK) ON LOV_TransactionType.ID = DAT_CardTransaction.TransactionType_ID                           
	 WHERE DAT_bon.ID = DAT_CardTransaction.bon_id AND LOV_TransactionType.TransactionType <> 1)  
-- not already in a valid (not cancelled) invoice
and DAT_Bon.ID not in 
	(SELECT Bon_ID FROM INV_Invoice_Bon WITH (NOLOCK) INNER JOIN
                        INV_Invoice WITH (NOLOCK) ON INV_Invoice_Bon.invoice_ID = INV_Invoice.ID
                   WHERE DAT_bon.ID = INV_Invoice_Bon.bon_id and INV_Invoice.Cancel_ID IS NULL)
Group by DAT_Bon.ID, DAT_CustomerGroup_HISTArchive.CustomerGroup_ID, DAT_CustomerGroup_HISTArchive.CustomerGroupNumber, DAT_CustomerGroup_HISTArchive.Name, DAT_Bonline.VATValue, DAT_Bon.CreationDate
2

It looks to me that the functions you are using (GetPersonArchiveID, GetCustomerGroupArchiveID, GetArticleArchiveID) in the ON portion of your joins are the likely culprits. They'll need to be evaluated for every row. Can you find a way to either:

  • Use an APPLY instead of a JOIN
  • Pre-select the rows that would be combined with the other tables via a WITH preamble or a #Temp table
  • Move the logic out of the function and into the join
  • Any combination of the above
3

You should get rid of those functions completely and do the lookup with in-line code.

If you can't do that, because of restrictions or the complexity of the code, change them from scalar functions to in-line table-valued functions. I can help with this if you provide the code for the functions.

Also, prefix all columns with the correct table alias -- remember, other forum members know nothing about the structure of your db, so it's never "obvious" to us which column belongs to which table. In particular, for these SUMs:

...
		SUM(Subsidy * (1 / (1 + VATValue))) netPrice,
...
   sum(DAT_bonline.Quantity) as Quantity 
...

The huge GROUP BY is also going to be a big performance hit, and to remove it we need to move the SUMs down to the appropriate table(s); but we need to know which table(s) the columns come from in order to do that.

4

Always worries me when I see that ...

... are you aware that your report will, sometimes, miss some rows altogether and include some rows twice on others? i.e. the data included in your report will NOT be the data you expect, and your users have specified. The situation occurs infrequently (but not "never" unless you are very lucky e.g. due to the type of indexes on the tables), and is impossible to reproduce (because it is timing-critical) so virtually impossible to debug.

(This is not quite the same thing as Dirty Reads, it is index blocks being split, by another process, at the same time as they are being read by your process)

I suggest you solve your concurrency problem a different way - probably the easiest way is to set the database to Read Committed Snapshot, provided that does not cripple you because of increased use of TEMPDB and there is a very good chance that that will require zero code changes (except to remove all the NOLOCK statements in your code natch! )