December 7, 2016 at 3:48 am
Chris Harshman (12/6/2016)
sqlandbiuser (12/6/2016)
Procedure is still slow and taking more time for more records.. For 150K records, given select proc takes more than 10 mins to get the data.Any further inputs will be highly appreciated!
Have you replaced the multi-statement TVF with an inline TVF yet, such as Jeff Moden's referenced in this article?
http://www.sqlservercentral.com/articles/Tally+Table/72993/
it would also help if those parameters and variables weren't VARCHAR(MAX). Is there a need to have parameters that large? There's also a type mismatch between these parameters and variables being VARCHAR with the actual table columns which are NVARCHAR, causing implicit conversion.
so then you can use set based logic in your main query instead of repeatedly calling the splitter function within the query.
Hi Chris, I've put new function in place as suggested, and have modified the SP. But its not giving any records when I select all parameters for this. I have put updated plan and updated SP at same place
https://drive.google.com/open?id=0BxYEMI40aU_aRjZaVzJyMXJfb0k
EstPlan_NewSplitFunction
NewSP_NewSplitFunction
Please let me know if I am missing anything here......... many thanks!
December 7, 2016 at 2:48 pm
I notice you've rewritten part of the stored procedure, USP_ICMExtendedSummaryReport_Temp, now has:
...
from dbo.EOS_Reports c with (nolock)
INNER JOIN #TempUserAccess T on T.AccessID = c.OrgPathID AND T.AccessType = 'NONSHARED'
where
(c.OrgPathID in (select AccessID from #TempUserAccess where AccessType = 'NONSHARED')
or c.SharedOrgPathID in (select AccessID from #TempUserAccess where AccessType = 'SHARED')
or c.EntityInstanceID IN (select distinct AccessID from #TempUserAccess where AccessType = 'TEAM'))
This definitely reduces the number of table scans on EOS_Reports since you only reference it once instead of 3 times in UNIONs, but having all those OR conditions will probably make it more likely to keep the table scan on EOS_Reports. The first of the three condition in the OR also seems to match the second condition on your INNER JOIN #TempUserAccess.
Have you compared the 2 different inexes on #TempUserAccess that you have? I notice a commented out version is:
CREATE NONCLUSTERED INDEX [NCI_TempAccessType] ON #TempUserAccess ([AccessType]) INCLUDE ([AccessID])
I'm thinking that or
CREATE NONCLUSTERED INDEX [NCI_TempAccessType] ON #TempUserAccess (AccessType,AccessID)
may perform better than indexing on AccessID first but it's difficult to tell without knowing the cardinality of data within the temp table for each AccessType.
December 13, 2016 at 7:11 am
Chris Harshman (12/7/2016)
I notice you've rewritten part of the stored procedure, USP_ICMExtendedSummaryReport_Temp, now has:
...from dbo.EOS_Reports c with (nolock)
INNER JOIN #TempUserAccess T on T.AccessID = c.OrgPathID AND T.AccessType = 'NONSHARED'
where
(c.OrgPathID in (select AccessID from #TempUserAccess where AccessType = 'NONSHARED')
or c.SharedOrgPathID in (select AccessID from #TempUserAccess where AccessType = 'SHARED')
or c.EntityInstanceID IN (select distinct AccessID from #TempUserAccess where AccessType = 'TEAM'))
This definitely reduces the number of table scans on EOS_Reports since you only reference it once instead of 3 times in UNIONs, but having all those OR conditions will probably make it more likely to keep the table scan on EOS_Reports. The first of the three condition in the OR also seems to match the second condition on your INNER JOIN #TempUserAccess.
Have you compared the 2 different inexes on #TempUserAccess that you have? I notice a commented out version is:
CREATE NONCLUSTERED INDEX [NCI_TempAccessType] ON #TempUserAccess ([AccessType]) INCLUDE ([AccessID])I'm thinking that or
CREATE NONCLUSTERED INDEX [NCI_TempAccessType] ON #TempUserAccess (AccessType,AccessID)may perform better than indexing on AccessID first but it's difficult to tell without knowing the cardinality of data within the temp table for each AccessType.
Hi Chris, I have uploaded the latest query plan along with the procedure which reads data from the underlying table
Same location, folder 12_12_2016
https://drive.google.com/drive/folders/0BxYEMI40aU_aakdGRGtVSU9hRUk
Issue is still there. .. can you please provide your valuable inputs
December 13, 2016 at 12:14 pm
sqlandbiuser (12/13/2016)
Issue is still there. .. can you please provide your valuable inputs
I guess I'm confused as to what has changed from last week? You still have this confusing code:
from dbo.EOS_Reports c with (nolock)
INNER JOIN #TempUserAccess T on T.AccessID = c.OrgPathID AND T.AccessType = 'NONSHARED'
where
(c.OrgPathID in (select AccessID from #TempUserAccess where AccessType = 'NONSHARED')
or c.SharedOrgPathID in (select AccessID from #TempUserAccess where AccessType = 'SHARED')
or c.EntityInstanceID IN (select distinct AccessID from #TempUserAccess where AccessType = 'TEAM'))
since your join from EOS_Reports to #TempUserAccess is on the same condition as the first one of your OR conditions here in the WHERE clause, that's the only condition that really applies. In other words, it doesn't matter if AccessID matches SharedOrgPathID or AccessID matches EntityInstanceID because AccessID must match OrgPathID to meet the INNER JOIN criteria. Either this doesn't do what you're expecting it to do or all 3 of these subqueries can be removed. If you look in the execution plan, that's why the rowcount is 68646 before and after the Nested Loops join to the concatenated #TempUserAccess table scans.
Also, do you have an index on EOS_Reports that has OrgPathID as the first column? With the JOIN condition you have, this is the only way you won't be doing a full table scan on EOS_Reports.
December 13, 2016 at 11:41 pm
Chris Harshman (12/13/2016)
I guess I'm confused as to what has changed from last week? You still have this confusing code: .
Kindly read this join as below :
from dbo.EOS_Reports c with (nolock)
where
(c.OrgPathID in (select AccessID from #TempUserAccess where AccessType = 'NONSHARED')
or c.SharedOrgPathID in (select AccessID from #TempUserAccess where AccessType = 'SHARED')
or c.EntityInstanceID IN (select distinct AccessID from #TempUserAccess where AccessType = 'TEAM'))
and 1 = (CASEWHEN LEN(@AgreementName_p) > 0 AND ISNULL(AgreementName,'NA') in (select token from #AgreementName_p) THEN 1
WHEN LEN(@AgreementName_p) = 0 then 1 ELSE 0 END)
and 1 = (CASEWHEN LEN(@TypeOfContract_p) > 0 AND ISNULL(ContractType,'NA') in (select token from #TypeOfContract_p) THEN 1
WHEN LEN(@TypeOfContract_p) = 0 then 1 ELSE 0 END)
and 1 = (CASEWHEN LEN(@AccountName_p) > 0 AND ISNULL(ICMAccount,'NA') in (select token from #AccountName_p) THEN 1
WHEN LEN(@AccountName_p) = 0 then 1 ELSE 0 END)
and 1 = (CASEWHEN LEN(@TypeofDocument_p) > 0 AND ISNULL(TypeOfDocument,'NA') in (select token from #TypeOfDocument_p) THEN 1
WHEN LEN(@TypeofDocument_p) = 0 then 1 ELSE 0 END)
and (1 = (CASE WHEN LEN(@SummaryCategories_p) > 0 AND SummaryCategories in (select token from #SummaryCategories_p) THEN 1
WHEN LEN(@SummaryCategories_p) = 0 then 1 ELSE 0 END)
OR
1 = (CASE WHEN @SummaryCategories_p like '%NA%' AND SummaryCategories IS NULL THEN 1 ELSE 0 END)
)
order by Contracttype,AgreementCode, RowOrdinal
.
Also, do you have an index on EOS_Reports that has OrgPathID as the first column? With the JOIN condition you have, this is the only way you won't be doing a full table scan on EOS_Reports.
Yes, I do have index on EOS_Reports with OrgPathID as first column
December 14, 2016 at 7:17 am
sqlandbiuser (12/13/2016)
Chris Harshman (12/13/2016)
I guess I'm confused as to what has changed from last week? You still have this confusing code: .
Kindly read this join as below :
from dbo.EOS_Reports c with (nolock)
where
(c.OrgPathID in (select AccessID from #TempUserAccess where AccessType = 'NONSHARED')
or c.SharedOrgPathID in (select AccessID from #TempUserAccess where AccessType = 'SHARED')
or c.EntityInstanceID IN (select distinct AccessID from #TempUserAccess where AccessType = 'TEAM'))
and 1 = (CASEWHEN LEN(@AgreementName_p) > 0 AND ISNULL(AgreementName,'NA') in (select token from #AgreementName_p) THEN 1
WHEN LEN(@AgreementName_p) = 0 then 1 ELSE 0 END)
and 1 = (CASEWHEN LEN(@TypeOfContract_p) > 0 AND ISNULL(ContractType,'NA') in (select token from #TypeOfContract_p) THEN 1
WHEN LEN(@TypeOfContract_p) = 0 then 1 ELSE 0 END)
and 1 = (CASEWHEN LEN(@AccountName_p) > 0 AND ISNULL(ICMAccount,'NA') in (select token from #AccountName_p) THEN 1
WHEN LEN(@AccountName_p) = 0 then 1 ELSE 0 END)
and 1 = (CASEWHEN LEN(@TypeofDocument_p) > 0 AND ISNULL(TypeOfDocument,'NA') in (select token from #TypeOfDocument_p) THEN 1
WHEN LEN(@TypeofDocument_p) = 0 then 1 ELSE 0 END)
and (1 = (CASE WHEN LEN(@SummaryCategories_p) > 0 AND SummaryCategories in (select token from #SummaryCategories_p) THEN 1
WHEN LEN(@SummaryCategories_p) = 0 then 1 ELSE 0 END)
OR
1 = (CASE WHEN @SummaryCategories_p like '%NA%' AND SummaryCategories IS NULL THEN 1 ELSE 0 END)
)
order by Contracttype,AgreementCode, RowOrdinal
.
Also, do you have an index on EOS_Reports that has OrgPathID as the first column? With the JOIN condition you have, this is the only way you won't be doing a full table scan on EOS_Reports.
Yes, I do have index on EOS_Reports with OrgPathID as first column
Okay, how about we give the optimizer an easier way to shortcut some of those CASE statements?
FROM dbo.EOS_Reports AS c WITH (NOLOCK)
WHERE (
c.OrgPathID IN (SELECT AccessID FROM #TempUserAccess WHERE AccessType = 'NONSHARED')
OR
c.SharedOrgPathID IN (SELECT AccessID FROM #TempUserAccess WHERE AccessType = 'SHARED')
OR
c.EntityInstanceID IN (SELECT DISTINCT AccessID FROM #TempUserAccess WHERE AccessType = 'TEAM')
)
AND 1 = (
CASE
WHEN ISNULL(@AgreementName_p, '') = '' THEN 1
WHEN ISNULL(AgreementName,'NA') IN (SELECT token FROM #AgreementName_p) THEN 1
ELSE 0
END)
AND 1 = (
CASE
WHEN ISNULL(@TypeOfContract_p, '') = '' THEN 1
WHEN ISNULL(ContractType,'NA') IN (SELECT token FROM #TypeOfContract_p) THEN 1
ELSE 0
END)
AND 1 = (
CASE
WHEN ISNULL(@AccountName_p, '') = '' THEN 1
WHEN ISNULL(ICMAccount,'NA') IN (SELECT token FROM #AccountName_p) THEN 1
ELSE 0
END)
AND 1 = (
CASE
WHEN ISNULL(@TypeofDocument_p, '') = '' THEN 1
WHEN ISNULL(TypeOfDocument,'NA') IN (SELECT token FROM #TypeOfDocument_p) THEN 1
ELSE 0
END)
AND (
1 = (
CASE
WHEN ISNULL(@SummaryCategories_p, '') = '' THEN 1
WHEN LEN(@SummaryCategories_p) > 0 AND SummaryCategories IN (SELECT token FROM #SummaryCategories_p) THEN 1
ELSE 0
END)
OR
1 = (CASE WHEN @SummaryCategories_p LIKE '%NA%' AND SummaryCategories IS NULL THEN 1 ELSE 0 END)
)
ORDER BY Contracttype,AgreementCode, RowOrdINal;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 14, 2016 at 10:20 am
This distinct is not necessary and has not effect on the result. It probably won't affect the query time as the optimizer is likely ignoring it, but better to remove it.
OR c.EntityInstanceID IN (SELECT DISTINCT AccessID FROM #TempUserAccess WHERE AccessType = 'TEAM')
Can the parameters be validated prior to the query?
WHEN ISNULL( @AgreementName_p, '' ) = '' THEN ...
WHEN LEN(@AgreementName_p) = 0 THEN ...
It may improve the plan if the query can use this instead:
WHEN @AgreementName_p = ''
I've never tested performance of wildcard comparisons on a parameter, but I expect Like '%' has more slightly more overhead than = ''.
WHEN @SummaryCategories_p LIKE '%NA%'
Validating the parameters prior to query would also allow the Case test to remove the LEN statement.
CASE WHEN @TypeOfContract_p= '' THEN 1
Is the ISNULL necessary? I don't know how #TypeOfContract_p is populated, but I'm guessing NA is either always a part of it or never. AND ISNULL( ContractType, 'NA' )IN (SELECT token FROM #TypeOfContract_p)
It would improve performance and optimization if that can be changed toWHEN ContractType IS NULL THEN 1 -- Or 0 if NA is never in the temp table
Getting rid of case statements may also help some.
All together now:
Select @AgreementName_p = CASE LEN(@AgreementName_p) WHEN 0 THEN '' Else @AgreementName_p END,
@TypeOfContract_p = case LEN(@TypeOfContract_p) WHEN 0 THEN '' Else @TypeOfContract_p END
...
SELECT *
FROM dbo.EOS_Reports AS c WITH (NOLOCK)
WHERE ( c.OrgPathID IN (SELECT AccessID FROM #TempUserAccess WHERE AccessType = 'NONSHARED')
OR c.SharedOrgPathID IN (SELECT AccessID FROM #TempUserAccess WHERE AccessType = 'SHARED')
OR c.EntityInstanceID IN (SELECT DISTINCT AccessID FROM #TempUserAccess WHERE AccessType = 'TEAM')
)
AND ( @AgreementName_p = ''
OR AgreementName IN (SELECT token FROM #AgreementName_p)
OR AgreementName IS NULL
)
AND ( @TypeOfContract_p = ''
OR ContractType IN (SELECT token FROM #TypeOfContract_p) --Null won't match using an IN statement,
OR ContractType IS NULL --if null is a valid condition; remove this if not.
)
...
Wes
(A solid design is always preferable to a creative workaround)
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply