Hi Ahmed,
Yes i do have indexes on both these 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
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
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
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;
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
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.
I am new to this group, should i close this thread or just leave as is?
Also, i have one more query where i would require your input, should i open a new thread or can post it here
Thanks
Hi Ifor
I created a new thread if you would like to have a look