SchmidtZ28
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Anthony Perkins
As CGLuttrell has indicated, by resorting to Dynamic SQL you have defeated the two big advantages of using Stored Procedures: Performance and Security.
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.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Award points to CGLuttrell http:#a24153619 & http:#a24300789