Query optimisation

Hi,
Below query is performing slow in last two lines i commented. I believe it is ISNULL and CASE function in where clause which is making them slow. Any suggestions please?

DECLARE @statusId INT=13
DECLARE @isMinorDraft BIT=0
DECLARE @countryId INT = 76
DECLARE @brandId INT = NULL
DECLARE @universeId INT = NULL
DECLARE @languageId INT = NULL
DECLARE @partnerCode NVARCHAR(200) = null
DECLARE @partnerName NVARCHAR(200) = NULL

--DECLARE @triggerStateId = Select fkTriggerStateId from PartnerTemplate

Select distinct TOP 1000
p.PArtnerId AS OwnerId,
p.PartnerName + case when pl.LocationName is null then '' else ' - ' + pl.LocationName end AS OwnerName,
isnull(pt.PartnerTemplateId,0) AS TemplateId,
pdt.fkDataTemplateId AS DataTemplateId,
dt.DataTemplateName AS DataTemplateName,
l.LanguageName AS LanguageName,
l.LanguageId AS LanguageId,
pt.fkTriggerStateId

FROM
Partner p
JOIN PartnerProduct pp ON p.PartnerId = pp.fkPartnerId and pp.Active =1
JOIN product ppp ON ppp.ProductId = pp.fkProductId AND ppp.fkTriggerStateId = 4
JOIN ProductUniverse cpu ON ppp.ProductId = cpu.fkProductId
JOIN RelatedProduct rp ON rp.fkProduct1Id = ppp.ProductId and rp.fkRelationTypeId = 20 AND rp.Active = 1
JOIN ProductDataTemplate pdt ON pdt.fkProductId = rp.fkProductId and pdt.isPartnerTemplate =1 and (pdt.fkUniverseId is null or pdt.fkUniverseId = cpu.fkUniverseId )
JOIN DataTemplate dt ON pdt.fkDataTemplateId = dt.DataTemplateId
JOIN Language l ON l.LanguageId = pdt.fkLanguageId
left join PartnerTemplate pt on p.PartnerId = pt.fkPartnerId and pt.fkDataTemplateId = pdt.fkDataTemplateId
and pt.IsDeleted = 0 and pt.fkLanguageId = pdt.fkLanguageId
LEFT JOIN ProductUniverse pu ON pu.fkProductId = rp.fkProductId
LEFT JOIN ProductCountry pc ON pc.fkProductId = rp.fkProductId
LEFT JOIN ProductBrand pb ON pb.fkProductId = rp.fkProductId
LEFT JOIN PartnerLocation pl ON p.PartnerId = pl.fkPartnerId and pl.Active = 1
WHERE
(@languageId IS NULL OR @languageId = pdt.fkLanguageId)
AND (@partnerCode IS NULL OR @partnerCode = p.PartnerCode )
AND (@partnerName IS NULL OR p.PartnerName like '%' + @partnerName + '%')
AND (@universeId IS NULL OR @universeId = pu.fkUniverseId)
AND (@countryId IS NULL OR @countryId = pc.fkCountryId)
AND (@brandId IS NULL OR @brandId = pb.fkBrandId)
--AND ISNULL(pt.fkTriggerStateId,12) = @statusId --PROBLEM, It is very slow
--AND (CASE WHEN pt.fkPartnerTemplateId IS NULL THEN 0 ELSE 1 END) = @isMinorDraft -- PROBLEM, It is very slow
AND p.PartnerStatus = 1
group by
p.PartnerId,
p.Partnername,
pl.LocationName,
pt.PartnerTemplateId,
pdt.fkDataTemplateId,
dt.DataTemplateName,
l.LanguageName,
l.LanguageId,
pt.fkTriggerStateId

Do you have index on fkTriggerStateId and fkPartnerTemplateId columns ?

Hi Ahmed,

Yes i do have indexes on both these columns

check the index fragmentation for those two indexes.

How to check that? I believe if we are using ISNULL inside where clause it just completely ignores the indexes

true, isnull might be causing the issue here.

Any suggestions how to avoid or resolve it?

Re-factor the query a bit:

1.Remove the problematic and conditions & the group by clause and dump the data into a temp table; also select pt.fkPartnerTemplateId in your select
2. Update the 2 columns fkPartnerTemplateId and fkTriggerStateId and assign 1/0 or 12 respectively depending upon if the value is null or not (for fkPartnerTemplateId you can also put the case in the select itself n check if it performs ok or not, instead of the update)
3. Select from the temp table by applying the 2 filters and add the group by clause

So try something on the above lines, the idea is to isolate those 2 conditions and re-write the query..hopefully should work

Edit: do add a distinct in the first step

Thanks rocknpop

If you look at the query i am fetching top 1000 rows based on the filters. However if i take those 2 conditions out from query to store data in #Temp table and the update the 2 Columns as suggested then my data is reduced to mere 50 rows :frowning:
Any other suggestion you have?
Thanks

Are you removing the top clause when dumping into the temp table, as you need to, sorry I did not mention this. You just might end with more data in the temp table and also remember you don't have an order by in the query. So you need the complete data set, add those updates, apply the filters and do a distinct again with your Top clause (don't need group by as I don't see any use of it here unless I missed something)

If i remove top clause then query takes too long as there are millions of records in the table :frowning:

Ok got it.
I guess you need to break your query in smaller batches. Some of the left joins (pu,pc,pb) are conditional based on param values and are converted to inner joins in case param is not null.

Few things you can do: remove the left joins and take only the rest of the columns (try taking only the id columns and not text as these you can get back later, fetching only integers is much faster), do an IF condition check for the 3 params and if they are available join back the tables with temp table (add indexes if required) using an inner join.Also, you have a like in the where clause with a %value% format which you should refactor too. There can be few ways of breaking up your query and I hope you get the gist of it and try out different ways and then check how it performs. Can also remove that case statement for now and do that in the end.

No luck :frowning:

Are the indexes in place in your inner joins (assuming you are only focusing on the inner joins first) and statistics up-to-date? Filtered indexes is another thing you can look at but first try to get only one ID column in your select and then keep adding the other columns, this way you will know where it is taking the most amount of time.

I just noticed that there is another OR clause in ProductDataTemplate join. Such or conditions can turn to scans by not really utilizing the index, if any. So try to debug step by step and you will know the pain area.

I have just had a quick look at this. Given the number of tables and the number of OR conditions in the WHERE clause you are probably overloading the optimizer.
If you posted the actual query plan we could look at adding hints but this would probably be difficult to maintain. Personally I would be inclined to look at using Dynamic SQL to simplify the query.

To make life easier, create a view of the main query:

SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE VIEW dbo.Yourview
AS
SELECT p.partnerid AS OwnerId
    ,p.partnername
		+ CASE
			WHEN pl.locationname IS NULL
			THEN ''
			ELSE ' - ' + pl.locationname 
		END AS OwnerName
    ,COALESCE(pt.partnertemplateid, 0) AS TemplateId
    ,pdt.fkdatatemplateid AS DataTemplateId
    ,dt.datatemplatename AS DataTemplateName
    ,l.languagename AS LanguageName
    ,l.languageid AS LanguageId
    ,pt.fktriggerstateid
	,p.partnercode
	,p.partnername
	,pu.fkuniverseid
	,pc.fkcountryid
	,pb.fkbrandid
FROM dbo.[partner] p
    JOIN dbo.partnerproduct pp
        ON p.partnerid = pp.fkpartnerid
        AND pp.active = 1
    JOIN dbo.product ppp
        ON ppp.productid = pp.fkproductid
        AND ppp.fktriggerstateid = 4
    JOIN dbo.productuniverse cpu
        ON ppp.productid = cpu.fkproductid
    JOIN dbo.relatedproduct rp
        ON rp.fkproduct1id = ppp.productid
        AND rp.fkrelationtypeid = 20
        AND rp.active = 1
    JOIN dbo.productdatatemplate pdt
        ON pdt.fkproductid = rp.fkproductid
        AND pdt.ispartnertemplate = 1
        AND ( pdt.fkuniverseid IS NULL
                OR pdt.fkuniverseid = cpu.fkuniverseid )
    JOIN dbo.datatemplate dt
        ON pdt.fkdatatemplateid = dt.datatemplateid
    JOIN dbo.[language] l
        ON l.languageid = pdt.fklanguageid
    LEFT JOIN dbo.partnertemplate pt
        ON p.partnerid = pt.fkpartnerid
            AND pt.fkdatatemplateid = pdt.fkdatatemplateid
            AND pt.isdeleted = 0
            AND pt.fklanguageid = pdt.fklanguageid
    LEFT JOIN dbo.productuniverse pu
        ON pu.fkproductid = rp.fkproductid
    LEFT JOIN dbo.productcountry pc
        ON pc.fkproductid = rp.fkproductid
    LEFT JOIN dbo.productbrand pb
        ON pb.fkproductid = rp.fkproductid
    LEFT JOIN dbo.partnerlocation pl
        ON p.partnerid = pl.fkpartnerid
            AND pl.active = 1
WHERE p.partnerstatus = 1;
GO

Now dynamic SQL can be setup as something like:

DECLARE @statusId int = 13
	,@isMinorDraft bit = 0
	,@countryId int = 76
	,@brandId int = NULL
	,@universeId int = NULL
	,@languageId int = NULL
	,@partnerCode nvarchar(200) = NULL
	,@partnerName nvarchar(200) = NULL;

DECLARE @SQL varchar(8000);
SELECT @SQL = 'SELECT DISTINCT TOP (1000) OwnerId, OwnerName, TemplateId, DataTemplateId, DataTemplateName, LanguageName, LanguageId, fktriggerstateid' + CHAR(13) +CHAR(10)
	+ 'FROM dbo.YourView' + CHAR(13) +CHAR(10)
	+ 'WHERE 1=1' + CHAR(13) +CHAR(10)
	+ CASE
		WHEN @languageId IS NOT NULL
		THEN CHAR(9) + 'AND LanguageId = @languageId' + CHAR(13) +CHAR(10)
		ELSE ''
	END
	+ CASE
		WHEN @partnerCode IS NOT NULL
		THEN CHAR(9) + 'AND partnercode = @partnerCode' + CHAR(13) +CHAR(10)
		ELSE ''
	END
	+ CASE
		WHEN  @partnerName IS NOT NULL
		THEN CHAR(9) + 'AND partnername LIKE ''%'' + @partnerName + ''%''' + CHAR(13) +CHAR(10)
		ELSE ''
	END
	+ CASE
		WHEN  @universeId IS NOT NULL
		THEN CHAR(9) + 'AND fkuniverseid = @universeId' + CHAR(13) +CHAR(10)
		ELSE ''
	END
	+ CASE
		WHEN  @countryId IS NOT NULL
		THEN CHAR(9) + 'AND fkuniverseid = @countryId' + CHAR(13) +CHAR(10)
		ELSE ''
	END
	+ CASE
		WHEN @brandId IS NOT NULL
		THEN CHAR(9) + 'AND fkbrandid = @brandId' + CHAR(13) +CHAR(10)
		ELSE ''
	END
	+ CASE
		WHEN @statusId = 12
		THEN CHAR(9) + 'AND COALESCE(fkTriggerStateId, 12) = @statusId' + CHAR(13) +CHAR(10)
		WHEN @statusId IS NOT NULL
		THEN CHAR(9) + 'AND fkTriggerStateId = @statusId' + CHAR(13) +CHAR(10)
		ELSE ''
	END
	+ CASE
		WHEN @isMinorDraft IS NOT NULL
		THEN CHAR(9) + 'AND TemplateId = @isMinorDraft' + CHAR(13) +CHAR(10)
		ELSE ''
	END
	+ ';'

--print @SQL;

EXEC sp_executesql @SQL
	,'@languageId int, @partnerCode nvarchar(200), @partnerName nvarchar(200), @universeId int, @countryId int, @brandId int, @statusId int, @isMinorDraft bit'
	,@languageId, @partnerCode, @partnerName, @universeId, @countryId, @brandId, @statusId, @isMinorDraft;
1 Like

Thanks Ifor

This looks nice, i will defo give it a try and come back to you...appreciate it!!!

Hi Ifror,
My last condition is as below
AND (CASE WHEN pt.fkPartnerTemplateId IS NULL THEN 0 ELSE 1 END) = @isMinorDraft which is a field in PartnerTempalate table, How to achieve this in CASE statement?

Thanks

Add the following to the view

	,COALESCE(pt.fkPartnerTemplateId, 0) AS fkPartnerTemplateId

so that becomes:

	+ CASE
		WHEN @isMinorDraft IS NOT NULL
		THEN CHAR(9) + 'AND fkPartnerTemplateId = @isMinorDraft' + CHAR(13) +CHAR(10)
		ELSE ''
	END

That's amazing Ifor, it really made the difference...Five star :clap:

Thanks for help

Glad it helped.

I normally try to avoid dynamic SQL, as it can get fiddly, but with catch all reporting queries it is usually the lesser evil.

1 Like