April 8, 2014 at 1:22 pm
This procedure is used for the drafters to search the amendments by search text and other other criteria. For example, they may want to search 'vehicle', 'transportation', 'white cars' and bring back the amendments that include those words in the amendment text.
But, this procedure takes about 32 seconds to run when nothing is being searched, and 50 seconds when we search one word.
Can you all take a quick look at it and see if you see anything that we can change to help this run faster. The bolded part is more what we are looking at that seems to slow it down.
Thanks for your help.
@BillType varchar(3) = ''
,@BillNumber int = 0
,@SponsorID int = 0
,@BillLRNumber varchar(10) = ''
,@AmendLRNumber varchar(15) = ''
,@AnalystID int = 0
,@SearchText StringListTable READONLY -- this is a user defined table type
,@SessionID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT distinct ah.AmdYear
,ah.BillType
,ah.BillNumber
,ah.AmdDescription
,ras.AmdStatusDescription AS AmdStatus
,RTRIM(empA.emplastname) + ', ' + IsNull(RTrim(NULLIF(empA.EmpPrefName, '')), RTRIM(empA.empfirstname)) AS Analyst
,RTRIM(empH.emplastname) + ', ' + IsNull(RTRIM(NULLIF(empH.empprefname, '')), RTRIM(empH.empfirstname)) as Member
,dbo.CreateLRNumber(ah.LRNumber, ah.LRSource, ah.LRDash, null, ah.AmdSource, ah.AmdSequence) as AmendmentLRNumber
,ah.AmendmentHistory_ID as AmendmentHistory_ID
,1 as SortOrder
FROM HouseCoreData.dbo.AmendmentHistory AS ah
LEFT OUTER JOIN employees.dbo.tblEmployees AS empH ON empH.employeeID = (Case When ah.HandlerID is null or ah.HandlerID = 0 Then ah.SponsorID Else ah.HandlerID End)
LEFT OUTER JOIN employees.dbo.tblEmployees AS empA ON empA.employeeID = ah.AnalystID
LEFT OUTER JOIN HouseCoreData.dbo.refAmendmentStatus ras ON ras.AmdStatus = ah.AmdStatus
Left Outer Join HouseCoreData.dbo.AmendmentText at on ah.AmendmentHistory_ID = at.AmendmentHistory_ID
Left Outer Join (select AmendmentText, row_NUmber() over(partition by amendmenttext order by newid()) as RowNum
from housecoredata.dbo.AmendmentText at join @SearchText d on at.AmendmentText like '%' + d.String + '%') x on x.AmendmentText = at.AmendmentText
Where ah.SessionID = (Case @SessionID When 0 Then ah.SessionID Else @SessionID End) and (ah.BillType like @BillType +'%' or @BillType = '')
and (ah.BillNumber = @BillNumber or @BillNumber = '') and (ah.SponsorID = @SponsorID or @SponsorID = 0)
and (dbo.CreateLRNumber(ah.LRNumber,ah.LRSource,ah.LRDash,'',null,null) like @BillLRNumber +'%' or @BillLRNumber = '')
and (dbo.CreateLRNumber(ah.LRNumber, ah.LRSource, ah.LRDash, null, ah.AmdSource, ah.AmdSequence) like @AmendLRNumber+'%' or @AmendLRNumber = '')
and (ah.AnalystID = @AnalystID or @AnalystID = 0)
and (RowNum = (select count(*) from @SearchText) or (Select COUNT(*) From @SearchText where String != '') = 0 )
Union
Select distinct ag.AmdYear
,'Generic' as BillType
,ag.AmendmentGeneric_ID as BillNumber
,ag.AmdDescription
,ras.AmdStatusDescription AS AmdStatus
,RTRIM(empA.emplastname) + ', ' + IsNull(RTrim(NULLIF(empA.EmpPrefName, '')), RTRIM(empA.empfirstname)) AS Analyst
,RTRIM(empH.emplastname) + ', ' + IsNull(RTRIM(NULLIF(empH.empprefname, '')), RTRIM(empH.empfirstname)) as Member
,'G'+CONVERT(varchar, ag.AmendmentGeneric_ID) as AmendmentLRNumber
,ag.AmendmentGeneric_ID as AmendmentHistory_ID
,2 as SortOrder
From HouseCoreData.dbo.AmendmentGenericag
LEFT OUTER JOIN employees.dbo.tblEmployees AS empH ON empH.employeeID = (Case When ag.HandlerID is null or ag.HandlerID = 0 Then ag.SponsorID Else ag.HandlerID End)
LEFT OUTER JOIN employees.dbo.tblEmployees AS empA ON empA.employeeID = ag.AnalystID
LEFT OUTER JOIN HouseCoreData.dbo.refAmendmentStatus ras ON ras.AmdStatus = ag.AmdStatus
Left Outer Join HouseCoreData.dbo.AmendmentTextGeneric atg on ag.AmendmentGeneric_ID = atg.AmendmentGeneric_ID
Left Outer Join (select AmendmentText, row_NUmber() over(partition by amendmenttext order by newid()) as RowNum
from housecoredata.dbo.AmendmentTextGeneric atg join @SearchText d on atg.AmendmentText like '%' + d.String + '%') x on x.AmendmentText = atg.AmendmentText
Where ag.SessionID = (Case @SessionID When 0 Then ag.SessionID Else @SessionID End) and (ag.SponsorID = @SponsorID or @SponsorID = 0)
and @BillType = '' and @BillNumber = 0 and @BillLRNumber = ''
and (('G'+CONVERT(varchar, ag.AmendmentGeneric_ID) like '%'+REPLACE(@AmendLRNumber,' ','')+'%') or @AmendLRNumber = '')
and (ag.AnalystID = @AnalystID or @AnalystID = 0)
and (RowNum = (select count(*) from @SearchText) or (Select COUNT(*) From @SearchText where String != '') = 0 )
END
April 8, 2014 at 1:40 pm
There are some improvements that can be done but you need to help us first. To get better answers, you should follow the advices on this article: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
You should avoid using UDFs such as dbo.CreateLRNumber() or you could change it into an inline table-valued function which might improve performance (depending on the complexity).
I'm not sure if this code is giving some performance issues trying to order an uniqueidentifier.
row_NUmber() OVER (PARTITION BY amendmenttext ORDER BY newid())
Try changing it to
row_NUmber() OVER (PARTITION BY amendmenttext ORDER BY (SELECT NULL))
Finally, you seem to be using a catch-all-query. Read this article on how to improve performance:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
April 8, 2014 at 3:27 pm
Everything Luis says plus...
You have functions all over the place in your JOIN and WHERE criteria. These are going to absolutely prevent the use of statistics causing the execution plans to be extremely bad. For example:
CONVERT(varchar, ag.AmendmentGeneric_ID)
That's going to cause scans. No choice for the optimizer. There are a bunch of other ones as well. That's one of the first things I'd suggest addressing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 8, 2014 at 3:47 pm
Everything Luis and Grant say plus...
There is lots of low hanging fruit here for performance improvement. Do you need UNION? A UNION ALL seems highly likely to be acceptable here and will help.
You have a scalar function littered throughout your columns. dbo.CreateLRNumber Then you have that same one in your where predicates. This means that not only can you not use any indexing the entire function is going to be called for each and every row in the entire table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 8, 2014 at 4:30 pm
And no one has even mentioned DISTINCT yet.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply