• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 509
  • Last Modified:

Sql server 2005 query taking a long time to execute

I need some help on tuning my database in order to return results quicker, i've attached my sql server stored procedure below, this query currently takes well over a minute to return the results the field i'm searching is:

Cnt_OptComp.Company

which is a field which contains over 20,000 records, its the Company Name within my Company Contact table, i'm assuming the delay is b/c of the volume of data and it not being indexed properly, becuase if i take the same query and substitute a different field in the where clause for another table which holds a 1000 records the results is immediate.

What is confusing to me is that i have queries with very similar structures and joins and the results are returned in a few seconds, however this query drags for a few minutes, please advise. thanks
ALTER Procedure [dbo].[Src_ClmInsured_Rs]
@Search varchar(50),
@UserId int,
@SelectId int
 
As
 
 
Declare @ExecString As varchar(8000)
Set @ExecString = 'SELECT     Uwp_OptInsurance.InsurId, Cnt_OptComp.Company, Uwp_OptInsurance.PolicyNo, SUBSTRING(Clm_OptOccurrence.ClaimNo, 12, 5) AS ClaimNo, 
                      Clm_OptOccurrence.ClaimId, Clm_OptOccurrence.DOL, Uwp_RefState.State AS ClaimSt, Clm_InsuredDriver_Rs.ContactName, 
                      Clm_OptOccurrence.AccLoc, Clm_InsuredVeh_Rs.VinNo, Clm_InsuredVeh_Rs.Model, Clm_InsuredVeh_Rs.Year, Clm_InsuredVeh_Rs.Plate, 
                      Clm_RefClaimStatus.ClaimStatus, Clm_AmtPaidSum_Rs.AmtPaid, Clm_ReserveSum_Rs.Reserve
FROM         Clm_AmtPaidSum_Rs RIGHT OUTER JOIN
                      Clm_OptOccurrence LEFT OUTER JOIN
                      Clm_ReserveSum_Rs ON Clm_OptOccurrence.ClaimId = Clm_ReserveSum_Rs.ClaimId ON 
                      Clm_AmtPaidSum_Rs.ClaimId = Clm_OptOccurrence.ClaimId LEFT OUTER JOIN
                      Clm_RefClaimStatus ON Clm_OptOccurrence.Status = Clm_RefClaimStatus.ClaimStatusId LEFT OUTER JOIN
                      Clm_InsuredVeh_Rs ON Clm_OptOccurrence.ClaimId = Clm_InsuredVeh_Rs.ClaimId LEFT OUTER JOIN
                      Src_SelStatus ON Clm_OptOccurrence.Status = Src_SelStatus.StatusId LEFT OUTER JOIN
                      Clm_InsuredDriver_Rs ON Clm_OptOccurrence.ClaimId = Clm_InsuredDriver_Rs.ClaimId LEFT OUTER JOIN
                      Uwp_RefState ON Clm_OptOccurrence.State = Uwp_RefState.StateId FULL OUTER JOIN
                      Uwp_RefCounty RIGHT OUTER JOIN
                      Uwp_OptInsurance ON Uwp_RefCounty.CountyId = Uwp_OptInsurance.CountyId LEFT OUTER JOIN
                      Sst_AccessCntType RIGHT OUTER JOIN
                      Cnt_OptComp ON Sst_AccessCntType.CntType = Cnt_OptComp.TypeId LEFT OUTER JOIN
                      Sst_AccessState ON Cnt_OptComp.State = Sst_AccessState.StateId LEFT OUTER JOIN
                      Sst_AccessRep ON Cnt_OptComp.AcctMang = Sst_AccessRep.AcctMang LEFT OUTER JOIN
                      Sst_Users ON Cnt_OptComp.AcctMang = Sst_Users.UserId ON Uwp_OptInsurance.CompId = Cnt_OptComp.CompId ON 
                      Clm_OptOccurrence.InsurId = Uwp_OptInsurance.InsurId
WHERE     (Sst_AccessRep.Access = 1) AND (Sst_AccessRep.UserId = ' + cast(@UserId as varchar) + ') AND 
                      (Src_SelStatus.SelectId = ' + cast(@SelectId as varchar) + ') OR
                      (Src_SelStatus.SelectId = ' + cast(@SelectId as varchar) + ') AND (Sst_AccessState.Access = 1) AND 
                      (Sst_AccessState.UserId = ' + cast(@UserId as varchar) + ') OR
                      (Src_SelStatus.SelectId = ' + cast(@SelectId as varchar) + ') AND (Sst_AccessCntType.Access = 1) AND 
                      (Sst_AccessCntType.UserId = ' + cast(@UserId as varchar) + ')
GROUP BY Uwp_OptInsurance.InsurId, Cnt_OptComp.CompId, Cnt_OptComp.Company, Uwp_OptInsurance.PolicyNo, Uwp_RefState.State, 
                      Clm_OptOccurrence.ClaimNo, Clm_OptOccurrence.ClaimId, Clm_OptOccurrence.DOL, Clm_InsuredDriver_Rs.ContactName, Clm_OptOccurrence.AccLoc,
					  Clm_InsuredVeh_Rs.VinNo, Clm_InsuredVeh_Rs.Model, Clm_InsuredVeh_Rs.Year, Clm_InsuredVeh_Rs.Plate,
					  Clm_RefClaimStatus.ClaimStatus, Clm_AmtPaidSum_Rs.AmtPaid, Clm_ReserveSum_Rs.Reserve
HAVING      (Cnt_OptComp.Company Like ''%' + @Search + '%'') OR
                      (Cnt_OptComp.Company Like ''%' + @Search + '%'') OR
                      (Cnt_OptComp.Company Like ''%' + @Search + '%'')'
 
Print(@ExecString)
Exec(@ExecString)
 
 
Return

Open in new window

0
SchmidtZ28
Asked:
SchmidtZ28
  • 3
  • 2
2 Solutions
 
Chris LuttrellSenior Database ArchitectCommented:
I am not sure why you built a string and tried to exec that, but you were turning all you numeric ID fields into strings which will kill the optimizer's use of all indexes.  Try the one below and see if it performs better.  Of course you can't print the string but performance is what matters.
ALTER Procedure [dbo].[Src_ClmInsured_Rs]
@Search varchar(50),
@UserId int,
@SelectId int
 
As
 
 
--Declare @ExecString As varchar(8000)
--Set @ExecString = 
SELECT     Uwp_OptInsurance.InsurId, Cnt_OptComp.Company, Uwp_OptInsurance.PolicyNo, SUBSTRING(Clm_OptOccurrence.ClaimNo, 12, 5) AS ClaimNo, 
                      Clm_OptOccurrence.ClaimId, Clm_OptOccurrence.DOL, Uwp_RefState.State AS ClaimSt, Clm_InsuredDriver_Rs.ContactName, 
                      Clm_OptOccurrence.AccLoc, Clm_InsuredVeh_Rs.VinNo, Clm_InsuredVeh_Rs.Model, Clm_InsuredVeh_Rs.Year, Clm_InsuredVeh_Rs.Plate, 
                      Clm_RefClaimStatus.ClaimStatus, Clm_AmtPaidSum_Rs.AmtPaid, Clm_ReserveSum_Rs.Reserve
FROM         Clm_AmtPaidSum_Rs RIGHT OUTER JOIN
                      Clm_OptOccurrence LEFT OUTER JOIN
                      Clm_ReserveSum_Rs ON Clm_OptOccurrence.ClaimId = Clm_ReserveSum_Rs.ClaimId ON 
                      Clm_AmtPaidSum_Rs.ClaimId = Clm_OptOccurrence.ClaimId LEFT OUTER JOIN
                      Clm_RefClaimStatus ON Clm_OptOccurrence.Status = Clm_RefClaimStatus.ClaimStatusId LEFT OUTER JOIN
                      Clm_InsuredVeh_Rs ON Clm_OptOccurrence.ClaimId = Clm_InsuredVeh_Rs.ClaimId LEFT OUTER JOIN
                      Src_SelStatus ON Clm_OptOccurrence.Status = Src_SelStatus.StatusId LEFT OUTER JOIN
                      Clm_InsuredDriver_Rs ON Clm_OptOccurrence.ClaimId = Clm_InsuredDriver_Rs.ClaimId LEFT OUTER JOIN
                      Uwp_RefState ON Clm_OptOccurrence.State = Uwp_RefState.StateId FULL OUTER JOIN
                      Uwp_RefCounty RIGHT OUTER JOIN
                      Uwp_OptInsurance ON Uwp_RefCounty.CountyId = Uwp_OptInsurance.CountyId LEFT OUTER JOIN
                      Sst_AccessCntType RIGHT OUTER JOIN
                      Cnt_OptComp ON Sst_AccessCntType.CntType = Cnt_OptComp.TypeId LEFT OUTER JOIN
                      Sst_AccessState ON Cnt_OptComp.State = Sst_AccessState.StateId LEFT OUTER JOIN
                      Sst_AccessRep ON Cnt_OptComp.AcctMang = Sst_AccessRep.AcctMang LEFT OUTER JOIN
                      Sst_Users ON Cnt_OptComp.AcctMang = Sst_Users.UserId ON Uwp_OptInsurance.CompId = Cnt_OptComp.CompId ON 
                      Clm_OptOccurrence.InsurId = Uwp_OptInsurance.InsurId
WHERE     (Sst_AccessRep.Access = 1) AND (Sst_AccessRep.UserId = @UserId) AND 
                      (Src_SelStatus.SelectId = @SelectId) OR
                      (Src_SelStatus.SelectId = @SelectId) AND (Sst_AccessState.Access = 1) AND 
                      (Sst_AccessState.UserId = @UserId) OR
                      (Src_SelStatus.SelectId = @SelectId) AND (Sst_AccessCntType.Access = 1) AND 
                      (Sst_AccessCntType.UserId = @UserId)
GROUP BY Uwp_OptInsurance.InsurId, Cnt_OptComp.CompId, Cnt_OptComp.Company, Uwp_OptInsurance.PolicyNo, Uwp_RefState.State, 
                      Clm_OptOccurrence.ClaimNo, Clm_OptOccurrence.ClaimId, Clm_OptOccurrence.DOL, Clm_InsuredDriver_Rs.ContactName, Clm_OptOccurrence.AccLoc,
					  Clm_InsuredVeh_Rs.VinNo, Clm_InsuredVeh_Rs.Model, Clm_InsuredVeh_Rs.Year, Clm_InsuredVeh_Rs.Plate,
					  Clm_RefClaimStatus.ClaimStatus, Clm_AmtPaidSum_Rs.AmtPaid, Clm_ReserveSum_Rs.Reserve
HAVING      (Cnt_OptComp.Company Like '%' + @Search + '%') OR
                      (Cnt_OptComp.Company Like '%' + @Search + '%') OR
                      (Cnt_OptComp.Company Like '%' + @Search + '%')
 
--Print(@ExecString)
--Exec(@ExecString)
 
 
Return

Open in new window

0
 
Anthony PerkinsCommented:
As CGLuttrell has indicated, by resorting to Dynamic SQL you have defeated the two big advantages of using Stored Procedures: Performance and Security.
0
 
Anthony PerkinsCommented:
Just a slight correction that could make a big difference, the HAVING clause should be a WHERE clause. Also, you really need to use aliases that query is difficult to read as it is.  Finally, you may have confused the prioroties for AND/OR and in addition (Src_SelStatus.SelectId = @SelectId) is in there twice.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
SchmidtZ28Author Commented:
Ok, i still can not determine what is the problem here. As a test I removed declaring the Stored Procedure as a string. There was no difference. I do not feel the string is the problem, I've created hundred of stored procedures in this fashion without any issues.

There were four joins that I was using to retrieve some data:
Clm_AmtPaidSum_Rs
Clm_ReserveSum_Rs
Clm_InsuredVeh_Rs
Clm_InsuredDrv_Rs

These above references are Views i created that are linked to a primary key. If i remove to above joins my stored procedure executes immediately. There joins are not very complex.

Additionally, I removed the joins and instead of retrieving the data through the Views and a join on the query i created a Scalar-valued Function by passing in the Id field and returning the desired value and the query executes immediately. I do not see a reason why these views would cause the query to not return results in a timely manner, even if i add only one of the four views, there still a major time delay
0
 
Chris LuttrellSenior Database ArchitectCommented:
see if this article helps any, it is one of the first that I found that gives some concrete explanations to suden procedure slow downs.
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/controlling-stored-procedure-caching-with-dyanmic-sql.aspx
0
 
Anthony PerkinsCommented:
Award points to CGLuttrell http:#a24153619 & http:#a24300789
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now