Optimization of this query

1

hello

I have the following query but it is coplex and slow
can you help me to optimize it please?
thank you

indent preformatted text by 4 spaces
SELECT
CONVERT(VARCHAR(20), DAT_Bon.CreationDate, 104) + ' ' + CONVERT(VARCHAR(20), DAT_Bon.CreationDate, 108) Erstellungsdatum,
DAT_Article_HISTArchive.ArticleNumber Artikelnummer,
DAT_CashPoint_HISTArchive.CashPointNumber Kassennummer,
DAT_BonLine.Quantity Multiplikator,
REPLACE(CONVERT(decimal(10,2),DAT_BonLine.Subsidy), '.', ',') Subvention,
REPLACE(CONVERT(decimal(10,2),DAT_BonLine.Discount), '.', ',') Rabatt,
CONVERT(INT, DAT_BonLine.VATValue * 100) Umsatzsteuer,
REPLACE(CONVERT(decimal(10,2),DAT_BonLine.Quantity * DAT_BonLine.NetPrice * (1+DAT_BonLine.VATValue) * (1-DAT_BonLine.Discount)), '.', ',') Bruttosumme,
DAT_Bon.CardNumber Kartennummer,
ISNULL(LOV_PriceLevel_Restaurant.PriceLevelNumber, -1) Preislevelnummer,
SEC_User.PersonalNumber Personennummer,
LOV_ArticleGroup.ArticleGroupNumber Artikelgruppennummer,
LOV_ProductGroup.ProductGroupNumber Produktgruppennummer,
ISNULL(EVENT_Event.EventNumber,'') Eventnummer,
DAT_Bon.GeneratedBonNumber Bonnummer

FROM
(
select DAT_Bon.*,
case
when cancelBon.CreationDate < DAT_Bon.CreationDate then cancelBon.CreationDate
else DAT_Bon.CreationDate
end OriginalCreationDate from DAT_Bon
INNER JOIN DAT_Bon cancelBon on DAT_Bon.CancelBon_ID = cancelBon.ID

   UNION ALL

   select DAT_Bon.*, DAT_Bon.CreationDate OriginalCreationDate from DAT_Bon
   WHERE DAT_Bon.CancelBon_ID IS NULL

)DAT_Bon
--DAT_Bon b
INNER JOIN dbo.DAT_BonLine ON DAT_BonLine.Bon_ID = DAT_Bon.ID
INNER JOIN dbo.DAT_CashPoint_HISTArchive ON dbo.GetCashPointArchiveID(DAT_Bon.CashPoint_ID, DAT_Bon.OriginalCreationDate) = DAT_CashPoint_HISTArchive.ID
INNER JOIN dbo.DAT_Restaurant_HISTArchive ON dbo.GetRestaurantArchiveID(DAT_CashPoint_HISTArchive.Restaurant_ID, DAT_Bon.OriginalCreationDate) = DAT_Restaurant_HISTArchive.ID
INNER JOIN dbo.DAT_Article_HISTArchive ON dbo.GetArticleArchiveID_New(DAT_BonLine.Article_ID, DAT_Bon.OriginalCreationDate) = DAT_Article_HISTArchive.ID
INNER JOIN dbo.LOV_ArticleGroup ON LOV_ArticleGroup.ID = DAT_Article_HISTArchive.ArticleGroup_ID
INNER JOIN dbo.LOV_ProductGroup ON LOV_ProductGroup.ID = DAT_Article_HISTArchive.ProductGroup_ID
INNER JOIN dbo.DAT_Person_HISTArchive ON dbo.GetPersonArchiveID_NEW(DAT_Bon.Person_ID, DAT_Bon.OriginalCreationDate) = DAT_Person_HISTArchive.ID
INNER JOIN dbo.DAT_CustomerGroup_HISTArchive ON dbo.GetCustomerGroupArchiveID_New(DAT_Person_HISTArchive.CustomerGroup_ID, DAT_Bon.OriginalCreationDate) = DAT_CustomerGroup_HISTArchive.ID
INNER JOIN dbo.SEC_User ON SEC_User.ID = DAT_Bon.CreationUser_ID
LEFT JOIN dbo.LOV_PriceLevel_Restaurant ON LOV_PriceLevel_Restaurant.ID = DAT_Bon.PriceLevelRestaurant_ID
LEFT JOIN dbo.LOV_PriceLevel ON LOV_PriceLevel.ID = LOV_PriceLevel_Restaurant.PriceLevel_ID
LEFT JOIN dbo.EVENT_Event_Bon ON EVENT_Event_Bon.Bon_ID = DAT_Bon.ID
LEFT JOIN dbo.EVENT_Event ON EVENT_Event.ID = EVENT_Event_Bon.Event_ID

where dat_bon.creationdate between '2016-01-01' and '2016-01-10'
AND DAT_Bon.IsCancel = 0 AND DAT_BonLine.Cancel_ID IS NULL AND (DAT_CustomerGroup_HISTArchive.CustomerGroupNumber >= 0 OR DAT_Bon.CardNumber = 'EVENT') AND DAT_Article_HISTArchive.ArticleNumber > 0
ORDER BY DAT_Bon.CreationDate, DAT_CashPoint_HISTArchive.CashPointNumber, DAT_Article_HISTArchive.ArticleNumber, DAT_BonLine.Quantity

2

What indexes do you have?

SELECT CONVERT(VARCHAR(20), DAT_Bon.creationdate, 104) + ' ' + CONVERT(VARCHAR(20), DAT_Bon.creationdate, 108) Erstellungsdatum, dat_article_histarchive.articlenumber Artikelnummer, dat_cashpoint_histarchive.cashpointnumber Kassennummer, dat_bonline.quantity Multiplikator, Replace(CONVERT(DECIMAL(10, 2), dat_bonline.subsidy), '.', ',') Subvention, Replace(CONVERT(DECIMAL(10, 2), dat_bonline.discount), '.', ',') Rabatt, CONVERT(INT, dat_bonline.vatvalue * 100) Umsatzsteuer, Replace(CONVERT(DECIMAL(10, 2), dat_bonline.quantity * dat_bonline.netprice * ( 1 + dat_bonline.vatvalue ) * ( 1 - dat_bonline.discount )), '.', ',') Bruttosumme, DAT_Bon.cardnumber Kartennummer, Isnull(lov_pricelevel_restaurant.pricelevelnumber, -1) Preislevelnummer, sec_user.personalnumber Personennummer, lov_articlegroup.articlegroupnumber Artikelgruppennummer, lov_productgroup.productgroupnumber Produktgruppennummer, Isnull(event_event.eventnumber, '') Eventnummer, DAT_Bon.generatedbonnumber Bonnummer FROM (SELECT dat_bon.*, CASE WHEN cancelBon.creationdate < dat_bon.creationdate THEN cancelBon.creationdate ELSE dat_bon.creationdate END OriginalCreationDate FROM dat_bon INNER JOIN dat_bon cancelBon ON dat_bon.cancelbon_id = cancelBon.id UNION ALL SELECT dat_bon.*, dat_bon.creationdate OriginalCreationDate FROM dat_bon WHERE dat_bon.cancelbon_id IS NULL)DAT_Bon --DAT_Bon b INNER JOIN dbo.dat_bonline ON dat_bonline.bon_id = DAT_Bon.id INNER JOIN dbo.dat_cashpoint_histarchive ON dbo.Getcashpointarchiveid(DAT_Bon.cashpoint_id, DAT_Bon.originalcreationdate) = dat_cashpoint_histarchive.id INNER JOIN dbo.dat_restaurant_histarchive ON dbo.Getrestaurantarchiveid( dat_cashpoint_histarchive.restaurant_id, DAT_Bon.originalcreationdate) = dat_restaurant_histarchive.id INNER JOIN dbo.dat_article_histarchive ON dbo.Getarticlearchiveid_new(dat_bonline.article_id, DAT_Bon.originalcreationdate) = dat_article_histarchive.id INNER JOIN dbo.lov_articlegroup ON lov_articlegroup.id = dat_article_histarchive.articlegroup_id INNER JOIN dbo.lov_productgroup ON lov_productgroup.id = dat_article_histarchive.productgroup_id INNER JOIN dbo.dat_person_histarchive ON dbo.Getpersonarchiveid_new(DAT_Bon.person_id, DAT_Bon.originalcreationdate) = dat_person_histarchive.id INNER JOIN dbo.dat_customergroup_histarchive ON dbo.Getcustomergrouparchiveid_new( dat_person_histarchive.customergroup_id, DAT_Bon.originalcreationdate) = dat_customergroup_histarchive.id INNER JOIN dbo.sec_user ON sec_user.id = DAT_Bon.creationuser_id LEFT JOIN dbo.lov_pricelevel_restaurant ON lov_pricelevel_restaurant.id = DAT_Bon.pricelevelrestaurant_id LEFT JOIN dbo.lov_pricelevel ON lov_pricelevel.id = lov_pricelevel_restaurant.pricelevel_id LEFT JOIN dbo.event_event_bon ON event_event_bon.bon_id = DAT_Bon.id LEFT JOIN dbo.event_event ON event_event.id = event_event_bon.event_id WHERE dat_bon.creationdate BETWEEN '2016-01-01' AND '2016-01-10' AND DAT_Bon.iscancel = 0 AND dat_bonline.cancel_id IS NULL AND ( dat_customergroup_histarchive.customergroupnumber >= 0 OR DAT_Bon.cardnumber = 'EVENT' ) AND dat_article_histarchive.articlenumber > 0 ORDER BY DAT_Bon.creationdate, dat_cashpoint_histarchive.cashpointnumber, dat_article_histarchive.articlenumber, dat_bonline.quantity

3

I don't have indexes for this

4

Please show functions:

  • dbo.getcashpointarchiveid
  • dbo.getrestaurentarchiveid
  • dbo.getarticlearchiveid
  • dbo.getpersonarchiveid_new
  • dbo.getcustomergrouparchiveid_new
5

•dbo.getcashpointarchiveid

ALTER FUNCTION  [dbo].[GetCashPointArchiveID]
(
	@CashPointID uniqueidentifier,
	@CheckDate datetime
)
RETURNS UNIQUEIDENTIFIER
AS
BEGIN

	DECLARE @ArchiveID uniqueidentifier
	DECLARE @MaxModificationDate datetime

	SELECT @MaxModificationDate = max(HIST_ModificationDate) 
	FROM DAT_CashPoint_HISTArchive
	WHERE CashPoint_ID = @CashPointID
		AND NOT Exists(SELECT 1 FROM DAT_CashPoint_HISTArchive WHERE CashPoint_ID = @CashPointID AND HIST_ActionType = 'D')


--	PRINT ''MaxMod''
--	PRINT @MaxModificationDate

	IF @MaxModificationDate IS NULL 
	BEGIN
		SET @ArchiveID = NULL
		-- sonderfall
	END
	ELSE
	if @MaxModificationDate < @checkDate
	BEGIN
		SELECT TOP 1 @ArchiveID = ID FROM DAT_CashPoint_HISTArchive where HIST_ModificationDate = @MaxModificationDate
			AND CashPoint_ID = @CashPointID
	END
	ELSE 
	BEGIN 
		SELECT @ArchiveID = ha1.id
		FROM DAT_CashPoint_HISTArchive ha1,   
			DAT_CashPoint_HISTArchive ha2  
		WHERE ha1.CashPoint_ID = @CashPointID AND ha2.CashPoint_ID = @CashPointID
		AND ha1.HIST_ModificationDate <= @checkdate  
		AND ha2.HIST_MOdificationdate > @checkdate  
		AND ha2.HIST_ModificationDATE = 
			(	SELECT MIN(HIST_ModificationDate) 
				FROM DAT_CashPoint_HISTArchive haInner     
				WHERE haInner.HIST_ModificationDate > ha1.HIST_MOdificationDate
					AND haInner.CashPoint_ID = @CashPointID
			) 
	END
	RETURN @ArchiveID
END
6

•dbo.getrestaurentarchiveid

ALTER FUNCTION [dbo].[GetRestaurantArchiveID]
(
@RestaurantID uniqueidentifier,
@CheckDate datetime
)
RETURNS UNIQUEIDENTIFIER
AS
BEGIN

DECLARE @ArchiveID uniqueidentifier
DECLARE @MaxModificationDate datetime

SELECT @MaxModificationDate = max(HIST_ModificationDate) 
FROM DAT_Restaurant_HISTArchive
WHERE Restaurant_ID = @RestaurantID
	AND NOT Exists(SELECT 1 FROM DAT_Restaurant_HISTArchive WHERE Restaurant_ID = @RestaurantID AND HIST_ActionType = 'D')

IF @MaxModificationDate IS NULL 
BEGIN
	SET @ArchiveID = NULL
	-- sonderfall
END
ELSE
if @MaxModificationDate < @checkDate
BEGIN
	SELECT TOP 1 @ArchiveID = ID FROM DAT_Restaurant_HISTArchive where HIST_ModificationDate = @MaxModificationDate
		AND Restaurant_ID = @RestaurantID
END
ELSE 
BEGIN 
	SELECT @ArchiveID = ha1.id
	FROM DAT_Restaurant_HISTArchive ha1,   
		DAT_Restaurant_HISTArchive ha2  
	WHERE ha1.Restaurant_ID = @RestaurantID AND ha2.Restaurant_ID = @RestaurantID
	AND ha1.HIST_ModificationDate <= @checkdate  
	AND ha2.HIST_MOdificationdate > @checkdate  
	AND ha2.HIST_ModificationDATE = 
		(	SELECT MIN(HIST_ModificationDate) 
			FROM DAT_Restaurant_HISTArchive haInner     
			WHERE haInner.HIST_ModificationDate > ha1.HIST_MOdificationDate
				AND haInner.Restaurant_ID = @RestaurantID
		) 
END
RETURN @ArchiveID

END

7

•dbo.getarticlearchiveid

ALTER FUNCTION [dbo].[GetArticleArchiveID]
(
@ArticleID uniqueidentifier,
@CheckDate datetime
)
RETURNS UNIQUEIDENTIFIER
AS
BEGIN

DECLARE @ArchiveID uniqueidentifier
DECLARE @MaxModificationDate datetime

SELECT @MaxModificationDate = max(HIST_ModificationDate) 
FROM DAT_Article_HISTArchive
WHERE Article_ID = @ArticleID
	AND NOT Exists(SELECT 1 FROM DAT_Article_HISTArchive WHERE Article_ID = @ArticleID AND HIST_ActionType = 'D')

IF @MaxModificationDate IS NULL 
BEGIN
	SET @ArchiveID = NULL
	-- sonderfall
END
ELSE
if @MaxModificationDate < @checkDate
BEGIN
	SELECT TOP 1 @ArchiveID = ID FROM DAT_Article_HISTArchive where HIST_ModificationDate = @MaxModificationDate
		AND Article_ID = @ArticleID
END
ELSE 
BEGIN 
	SELECT @ArchiveID = ha1.id
	FROM DAT_Article_HISTArchive ha1,   
		DAT_Article_HISTArchive ha2  
	WHERE ha1.Article_ID = @ArticleID and ha2.Article_ID = @ArticleID
	AND ha1.HIST_ModificationDate <= @checkdate  
	AND ha2.HIST_MOdificationdate > @checkdate  
	AND ha2.HIST_ModificationDATE = 
		(	SELECT MIN(HIST_ModificationDate) 
			FROM DAT_Article_HISTArchive haInner     
			WHERE haInner.HIST_ModificationDate > ha1.HIST_MOdificationDate 
			and haInner.Article_ID = @ArticleID
		) 
END
RETURN @ArchiveID

END

8

•dbo.getpersonarchiveid_new

ALTER FUNCTION [dbo].[GetPersonArchiveID_NEW]
(
@PersonID uniqueidentifier,
@CheckDate datetime
)
RETURNS UNIQUEIDENTIFIER
AS
BEGIN

DECLARE @ArchiveID uniqueidentifier

SELECT top 1 @ArchiveID = ID
FROM DAT_Person_Histarchive
WHERE Person_ID = @PersonID and HIST_ModificationDate <= @checkDate
order by HIST_ModificationDate desc


RETURN @ArchiveID

END

9

•dbo.getcustomergrouparchiveid_new

ALTER FUNCTION [dbo].[GetCustomerGroupArchiveID_New]
(
@CustomerGroupID uniqueidentifier,
@CheckDate datetime
)
RETURNS UNIQUEIDENTIFIER
AS
BEGIN

DECLARE @ArchiveID uniqueidentifier
SELECT top 1 @ArchiveID = ID
FROM DAT_CustomerGroup_Histarchive
WHERE CustomerGroup_ID = @CustomerGroupID and HIST_ModificationDate <= @checkDate
order by HIST_ModificationDate desc


RETURN @ArchiveID

END

10

I sent all of the functions

11

Wow :astonished:
I'll take a close look i the weekend, as this will take some time to go thru (which I don't have time for right now).
I'll have to say it again - wow!

12

did you find a way for optimization of this query?

13

Those functions will be killing you; we used functions in that way for quite a long time until we discovered how terribly inefficient they are :frowning:

I would find a way to program them out. You could just unwind them, in line in the query, but obviously that doesn't centralise that code. It might be necessary to pre-process some/lots-of data into TEMP tables to then use those TEMP tables in the main query. For example your JOIN to the DAT_CashPoint_HISTArchive table.

You will get some help from having Indexes on the columns that are used in your joins - that might be your best "quick win"

INNER JOIN dbo.SEC_User ON SEC_User.ID = DAT_Bon.CreationUser_ID

e.g. in this example make sure you have an index on SEC_User.ID. If there are any other columns in SEC_User which appear in the WHERE clause, and perhaps to a lesser extent in the ORDER BY, then include those in the index too, but usually put the column(s) used in the JOIN first.

Make sure that you have indexes that "cover" the WHERE clause. Best one here is probably dat_bon.creationdate as you are doing a Range Test on that (it might be that making that the Clustered Index would be good, but that depends A LOT on what other activity there is in that table, so don't change the Clustered Index in a hurry! If you have NO Clustered Index then you could try using that date.

I would also put the additional columns in the WHERE clause in that index i.e. DAT_Bon.IsCancel, DAT_BonLine.Cancel_ID and so on.

EDIT: My mistake, you cannot put DAT_BonLine.Cancel_ID in that index, its in a different table!! I meant DAT_Bon.IsCancel and DAT_Bon.CardNumber etc.

If any of those conditions is rare - i.e. this report is looking for EXCEPTIONS in the data, and there will not be many matching rows, then it would be worth putting that column first in the index. So if 90% of your data is DAT_Bon.IsCancel = 1, but this query is looking for DAT_Bon.IsCancel = 0, then having DAT_Bon.IsCancel first in the index would help. Alternative you could create a Filtered Index which ONLY included rows where DAT_Bon.IsCancel = 0 (again, only worth doing that where it matches a small percentage of your rows)

I predict (assuming your database is "Large-ish", or "Big" and given that you have NO suitable indexes at present) that the right indexes will get you between 10x and 100x improvement in performance. Finding the "right" idnexes can be a bit of a black art though ...

Getting rid of those functions could perhaps get your another 10x.