July 21, 2004 at 10:14 am
Hi there,
Bit of a newbie to sql so bear with me...
I've created a sp that worked fine on local/test server machines at work.
The system with said sp worked fine, up until today when a client running it from a hosted site reported a fault. User gets a:
System.Web.Services.Protocols.SoapException:Server was unable to process request-->System.Data.SqlClient.SqlException: Timeout expired. etc....
If i run the sp in sql server query analyser it takes 48 seconds to extract the data out!!
However, if I copy and paste the code it is running in the sp, and run that in the query analyser it takes about 2 seconds.
Below is the offending sample of the sp:
SELECT INCIDENT_ACTION.*, SYSTEM_PARAMETERS_LEVEL.AUTOMATIC_DATE_WARNING_DAYS AS DaysAdvance,
PRIORITY.DESCR AS Priority_Descr, EMPLOYEE.SURNAME AS PersonRs_surname, EMPLOYEE.FORENAME AS PersonRs_Forename,
INCIDENT_DETAILS.SURNAME AS empl_surname, INCIDENT_DETAILS.FORENAME AS empl_forename,
INCIDENT_DETAILS.INCIDENT_NO AS inc_no, INCIDENT_DETAILS.INCIDENT_DATE AS inc_date
FROM INCIDENT_DETAILS INNER JOIN
INCIDENT_ACTION ON INCIDENT_DETAILS.INCIDENT_DETAILS = INCIDENT_ACTION.INCIDENT_DETAILS LEFT OUTER JOIN
SYSTEM_PARAMETERS_LEVEL ON INCIDENT_DETAILS.LEVEL3 = SYSTEM_PARAMETERS_LEVEL.[LEVEL]
LEFT OUTER JOIN
PRIORITY ON INCIDENT_ACTION.PRIORITY = PRIORITY.PRIORITY LEFT OUTER JOIN
EMPLOYEE ON INCIDENT_ACTION.PERSON_RESPONSIBLE = EMPLOYEE.EMPLOYEE
WHERE ((INCIDENT_DETAILS.LEVEL1 IN (SELECT LEVEL1 FROM NWUSER_LEVEL1 WHERE NWUSER_LEVEL1.nwuser=@gUserID)) AND
(INCIDENT_DETAILS.LEVEL2 IN (SELECT LEVEL2 FROM NWUSER_LEVEL2 WHERE NWUSER_LEVEL2.nwuser=@gUserID)) AND
(INCIDENT_DETAILS.LEVEL3 IN (SELECT LEVEL3 FROM NWUSER_LEVEL3 WHERE NWUSER_LEVEL3.nwuser=@gUserID)) AND
(INCIDENT_DETAILS.LEVEL4 IN (SELECT LEVEL4 FROM NWUSER_LEVEL4 WHERE NWUSER_LEVEL4.nwuser=@gUserID)) AND
(INCIDENT_DETAILS.LEVEL5 IN (SELECT LEVEL5 FROM NWUSER_LEVEL5 WHERE NWUSER_LEVEL5.nwuser=@gUserID)) AND
(INCIDENT_ACTION.COMPLETED_DATE IS NULL) AND (INCIDENT_ACTION.TARGET_DATE< DateAdd(DAY, SYSTEM_PARAMETERS_LEVEL.AUTOMATIC_DATE_WARNING_DAYS, GETUTCDATE())) AND
(SYSTEM_PARAMETERS_LEVEL.AUTOMATIC_DATE_WARNING_3=1 OR @bAutoCheck=0))
I pass in parameters for the @gUserID and @bAutoCheck
The above is a section of the full sp, this contains sligtly different from clause dependant on the parameters passed into the sp, but the above code is the bit that is being run (and causing the problem!!)
Any help appreciated. Is there a server time out setting we can change, or is there a better way of the doing the above sql.
Regards
Dave
July 22, 2004 at 7:13 am
You can check the execution plan. Foucus on the step that takes most of the time and check indexes used or not. Add indexes if necessary to your tables.
If the IN subclause returns lots records, the performance will be impacted. You can use INNER JOIN instead.
If any table has lots of records ( say 100 millions), query the data from this table with proper conditions and save into a table variable, then JOIN the variable table with others will be benifit to the performance.
July 22, 2004 at 8:14 am
Thanks newbie for the response. I added:
set nocount on
after the AS in my sp and this seems to have sorted it!!
Are there any other 'settings' as such that I can switch on/off etc that will help the execution speed.
Cheers
Dave
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply