August 10, 2008 at 11:54 pm
Hi
I have an sp which deletes records from various tables.
The sp has many nested select statements and synamic sql statements
when my code (.net) calls this sp , after some time it gives timeout error.
Is this only because the sp is taking longer time for deletion
I have checked all the timeout properties and they are all for unlimited, 1800 sec.
Please help
August 11, 2008 at 1:12 am
Not sure about the timeout settings (I'm not a .net developer), but timeout in general means something took too long. You can either change the timeout settings or you can optimise the procedure. I tend to prefer the latter. If you need help with that, post the query here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2008 at 2:17 am
I have tried modifying the cursor to loop but could not change much ...
some developer has written it .. and it has come to me for optimization.
still the query takes abt 4-5 min for exe.
Any help wud be greatly appreciated
CREATE PROCEDURE [dbo].[spd_DeleteBuilding]
@DeleteRequest as VarChar(1000)
--List of parameters
as
DECLARE @intError int
--Variable Declaration
DECLARE @TypeCode VarChar(3)
DECLARE @sItemId varChar(1000)
DECLARE @SQLTORUN varchar(1500)
DECLARE @SIM_QUERY as varchar(1500)
DECLARE @SimulationId as int
DECLARE @iStart int
DECLARE @iEnd int
BEGIN
WHILE(LEN(@DeleteRequest) > 0)
BEGIN
set @TypeCode = LTRIM(RTRIM(SUBSTRING(@DeleteRequest,1, CHARINDEX(',', @DeleteRequest) - 1)))
-- Used at specific places to handle STA & SIM cases
-- set @DeleteRequest = replace(@DeleteRequest, SUBSTRING(@DeleteRequest,1, CHARINDEX(',', @TypeCode+',')), '')
If (@TypeCode = 'BLD')
BEGIN
set @DeleteRequest = replace(@DeleteRequest, SUBSTRING(@DeleteRequest,1, CHARINDEX(',', @TypeCode+',')), '')
set @sItemId = LTRIM(RTRIM(SUBSTRING(@DeleteRequest,1, CHARINDEX('!', @DeleteRequest) - 1)))
set @DeleteRequest = replace(@DeleteRequest , left(@DeleteRequest, len(@sItemId+'!')), '')
-- 1. Delete the studies associated with all revisions of buildingid
-- 1.a Delete the simulations and associaltions
---- Get the Simulation Id's in an string
if exists(select * from sysobjects where name = 'tempDeleteBuilding')
BEGIN
Drop Table tempDeleteBuilding
END
set @SIM_QUERY = '(Select db_Simulation_Id into tempDeleteBuilding from tbl_simulation where db_revision_id in (select db_Revision_Id from tbl_revision where db_Building_Id in (' + @sItemId + ')))'
EXEC (@SIM_QUERY)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
-- Ask spd_DeleteBuilding_DeleteSDSimulation to delete the records related to Simulation
EXEC spd_DeleteBuilding_DeleteSDSimulation
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
Drop Table tempDeleteBuilding
set @SQLTORUN = 'Delete tbl_static_analysis where db_revision_id in (select db_Revision_Id from tbl_revision where db_Building_Id in (' + @sItemId + '))'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
-- 2. Delete the groups associated with all revisions of buildingid
-- set @SQLTORUN = 'select * from tbl_group where db_revision_id in (select db_Revision_Id from tbl_revision where db_Building_Id in (' + @sItemId + '))'
set @SQLTORUN = 'delete Tbl_Group_Serviceability where db_Group_id in (select db_Group_Id from tbl_Group where db_revision_id in (select db_Revision_Id from tbl_revision where db_Building_Id in ('+ @sItemId +')))'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
-- Double Deck Addition
if exists(select * from sysobjects where name = 'tempDeleteBuilding')
BEGIN
Drop Table tempDeleteBuilding
END
set @SIM_QUERY = '(Select db_DoubleDeck_Simulation_Input_Id into tempDeleteBuilding from tbl_Double_Deck_Simulation_input where db_Group_id in (select db_Group_Id from tbl_Group where db_revision_id in (select db_Revision_Id from tbl_revision where db_Building_Id in ('+ @sItemId +'))))'
EXEC (@SIM_QUERY)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
EXEC spd_DeleteBuilding_DeleteDDSimulation
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
Drop Table tempDeleteBuilding
-- DD Ends
set @SQLTORUN = 'delete Tbl_Simulated_HC_Output where db_Simulation_HC_Id in (select db_Simulation_HC_Id from Tbl_Simulated_HC where db_Simulation_Id in (Select db_Simulation_Id from tbl_Simulation where db_Revision_Id in (Select db_Revision_Id from tbl_Revision where db_Building_Id in(' + @sItemId + '))))'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
set @SQLTORUN = 'delete Tbl_Simulated_HC where db_Simulation_Id in (Select db_Simulation_Id from tbl_Simulation where db_Revision_Id in (Select db_Revision_Id from tbl_Revision where db_Building_Id in(' + @sItemId + ')))'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
set @SQLTORUN = 'delete Tbl_Simulation where db_Revision_Id in (select db_Revision_Id from tbl_revision where db_Building_Id in (' + @sItemId + '))'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
set @SQLTORUN = 'delete Tbl_Floor_Information where db_revision_id in (select db_Revision_Id from tbl_revision where db_Building_Id in (' + @sItemId + '))'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
set @SQLTORUN = 'delete tbl_Group where db_revision_id in (select db_Revision_Id from tbl_revision where db_Building_Id in (' + @sItemId + '))'
EXEC (@SQLTORUN)
-- -- 3. Delete the Optimaizations associated with
set @SQLTORUN = 'delete Tbl_Optimization_Groups where db_Optimization_Id in (Select db_Optimization_Id from tbl_Optimization where db_Revision_Id in (Select db_Revision_Id from tbl_Revision where db_Building_Id in (' + @sItemId + ')))'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
set @SQLTORUN = 'delete Tbl_Optimization_Output where db_Optimization_Id in (Select db_Optimization_Id from tbl_Optimization where db_Revision_Id in (Select db_Revision_Id from Tbl_Revision where db_Building_Id in(' + @sItemId + ')))'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
set @SQLTORUN = 'delete Tbl_Optimization where db_Revision_Id in (select db_Revision_Id from tbl_revision where db_Building_Id in (' + @sItemId + '))'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
-- 4. Delete the revision associated with buildingid
-- set @SQLTORUN = 'select * from tbl_revision where db_Building_Id in (' + @sItemId + ')'
set @SQLTORUN = 'delete tbl_Revision where db_Building_Id in (' + @sItemId + ')'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
-- 4. Delete the building associated with buildingid
-- set @SQLTORUN = 'select * from tbl_building where db_Building_Id in (' + @sItemId + ')'
set @SQLTORUN = 'delete tbl_Building where db_Building_Id in (' + @sItemId + ')'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
END
else if (@TypeCode = 'REV')
BEGIN
set @DeleteRequest = replace(@DeleteRequest, SUBSTRING(@DeleteRequest,1, CHARINDEX(',', @TypeCode+',')), '')
set @sItemId = LTRIM(RTRIM(SUBSTRING(@DeleteRequest,1, CHARINDEX('$', @DeleteRequest) - 1)))
set @DeleteRequest = replace(@DeleteRequest , left(@DeleteRequest, len(@sItemId+'$')), '')
---- Get the Simulation Id's in an string
if exists(select * from sysobjects where name = 'tempDeleteBuilding')
BEGIN
Drop Table tempDeleteBuilding
END
set @SIM_QUERY = '(Select db_Simulation_Id into tempDeleteBuilding from tbl_simulation where db_revision_id in (' + @sItemId + '))'
EXEC (@SIM_QUERY)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
-- Ask spd_DeleteBuilding_DeleteSDSimulation to delete the records related to Simulation
EXEC spd_DeleteBuilding_DeleteSDSimulation
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
Drop Table tempDeleteBuilding
set @SQLTORUN = 'Delete tbl_static_analysis where db_revision_id in (' + @sItemId + ')'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
-- 2. Delete the groups associated with all revisions of buildingid
-- set @SQLTORUN = 'select * from tbl_group where db_revision_id in (select db_Revision_Id from tbl_revision where db_Building_Id in (' + @sItemId + '))'
set @SQLTORUN = 'delete Tbl_Group_Serviceability where db_Group_id in (select db_Group_Id from tbl_Group where db_revision_id in (' + @sItemId + '))'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
-- Double Deck Addition
if exists(select * from sysobjects where name = 'tempDeleteBuilding')
BEGIN
Drop Table tempDeleteBuilding
END
set @SIM_QUERY = '(Select db_DoubleDeck_Simulation_Input_Id into tempDeleteBuilding from tbl_Double_Deck_Simulation_input where db_Group_id in (select db_Group_Id from tbl_Group where db_revision_id in (' + @sItemId + ')))'
EXEC (@SIM_QUERY)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
EXEC spd_DeleteBuilding_DeleteDDSimulation
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
Drop Table tempDeleteBuilding
-- Double Deck Ends
set @SQLTORUN = 'delete Tbl_Simulated_HC_Output where db_Simulation_HC_Id in (select db_Simulation_HC_Id from Tbl_Simulated_HC where db_Simulation_Id in (Select db_Simulation_Id from tbl_Simulation where db_Revision_Id in (' + @sItemId + ')))'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
set @SQLTORUN = 'delete Tbl_Simulated_HC where db_Simulation_Id in (Select db_Simulation_Id from tbl_Simulation where db_Revision_Id in (' + @sItemId + '))'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
set @SQLTORUN = 'delete Tbl_Floor_Information where db_Revision_Id in (' + @sItemId + ')'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
set @SQLTORUN = 'delete Tbl_Simulation where db_Revision_Id in (' + @sItemId + ')'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
set @SQLTORUN = 'delete tbl_Group where db_Revision_Id in (' + @sItemId + ')'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
-- -- 3. Delete the Optimaizations associated with
set @SQLTORUN = 'delete Tbl_Optimization_Groups where db_Optimization_Id in (Select db_Optimization_Id from tbl_Optimization where db_Revision_Id in (' + @sItemId + '))'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
set @SQLTORUN = 'delete Tbl_Optimization_Output where db_Optimization_Id in (Select db_Optimization_Id from tbl_Optimization where db_Revision_Id in (' + @sItemId + '))'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
set @SQLTORUN = 'delete Tbl_Optimization where db_Revision_Id in (' + @sItemId + ')'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
-- 3. Delete the revision associated with
-- set @SQLTORUN = 'select * from tbl_revision where db_Building_Id in (' + @sItemId + ')'
set @SQLTORUN = 'delete tbl_revision where db_Revision_Id in (' + @sItemId + ') AND db_Default_Revision <> 1'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
END
else if (@TypeCode = 'GRP')
BEGIN
--set @DeleteRequest = replace(@DeleteRequest, SUBSTRING(@DeleteRequest,1, CHARINDEX(',', @TypeCode+',')), '')
--set @sItemId = LTRIM(RTRIM(SUBSTRING(@DeleteRequest,1, CHARINDEX('~', @DeleteRequest) - 1)))
--set @DeleteRequest = replace(@DeleteRequest , left(@DeleteRequest, len(@sItemId+'~')), '')
-- Updated as with SIM
set @iStart = 1
set @iEnd = CHARINDEX('~', @DeleteRequest)
set @sItemId = SUBSTRING(@DeleteRequest, 5, CHARINDEX('~', @DeleteRequest)-5)
set @DeleteRequest = replace(@DeleteRequest, 'GRP,'+@sItemId+'~', '')
print @sItemId
--
---- Get the Simulation Id's in an string
if exists(select * from sysobjects where name = 'tempDeleteBuilding')
BEGIN
Drop Table tempDeleteBuilding
END
set @SIM_QUERY = '(Select db_Simulation_Id into tempDeleteBuilding from tbl_simulation where db_Group_id in (' + @sItemId + '))'
print @SIM_QUERY
EXEC (@SIM_QUERY)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
-- Ask spd_DeleteBuilding_DeleteSDSimulation to delete the records related to Simulation
EXEC spd_DeleteBuilding_DeleteSDSimulation
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
Drop Table tempDeleteBuilding
print @sItemId
set @SQLTORUN = 'Delete tbl_static_analysis where db_Group_id in (' + @sItemId + ')'
print @SQLTORUN
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
-- 2. Delete the groups associated with all revisions of buildingid
-- set @SQLTORUN = 'select * from tbl_group where db_revision_id in (select db_Revision_Id from tbl_revision where db_Building_Id in (' + @sItemId + '))'
print @sItemId
set @SQLTORUN = 'delete Tbl_Group_Serviceability where db_Group_id in ('+ @sItemId +')'
print @SQLTORUN
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
-- Double Deck Addition
if exists(select * from sysobjects where name = 'tempDeleteBuilding')
BEGIN
Drop Table tempDeleteBuilding
END
print @sItemId
set @SIM_QUERY = '(Select db_DoubleDeck_Simulation_Input_Id into tempDeleteBuilding from tbl_Double_Deck_Simulation_input where db_Group_id in (' + @sItemId + '))'
print @SIM_QUERY
EXEC (@SIM_QUERY)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
EXEC spd_DeleteBuilding_DeleteDDSimulation
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
Drop Table tempDeleteBuilding
-- Double Deck Ends
print @sItemId
set @SQLTORUN = 'delete Tbl_Simulated_HC_Output where db_Simulation_HC_Id in (select db_Simulation_HC_Id from Tbl_Simulated_HC where db_Simulation_Id in (Select db_Simulation_Id from tbl_Simulation where db_Group_Id in (' + @sItemId + ')))'
print @SQLTORUN
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
print @sItemId
set @SQLTORUN = 'delete Tbl_Simulated_HC where db_Simulation_Id in (Select db_Simulation_Id from tbl_Simulation where db_Group_Id in (' + @sItemId + '))'
print @SQLTORUN
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
print @sItemId
set @SQLTORUN = 'delete Tbl_Simulation where db_Group_id in (' + @sItemId + ')'
print @SQLTORUN
EXEC (@SQLTORUN)
SET @intError = @@ERROR---
IF (@intError <> 0) GOTO ERR_HANDLER
-- If groups are deleted then update revision table for group's count.
print @sItemId
set @SQLTORUN = 'Select db_revision_id, count(*) No_Of_Groups into tmpRevisionGroupCount from tbl_group where db_Group_Id in (' + @sItemId + ') group by db_revision_id'
print @SQLTORUN
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
DECLARE @tempRevision int
DECLARE @tempNoOfGroups int
--KHK Modified cursor to while loop
-- WHILE LOOP starts here
DECLARE @Count INT
DECLARE @Count_two INT
SET @Count = 0
SELECT @Count_two= Count(db_revision_id) from tmpRevisionGroupCount
WHILE @COUNT = @COUNT_two
Begin
Update tbl_Revision Set db_No_Of_Groups = (db_No_Of_Groups - @tempNoOfGroups)
where db_Revision_Id = @tempRevision
SET @count=@Count+1
End
Drop Table tmpRevisionGroupCount
-- WHILE LOOP ends here
--KHK Modification ends
print @sItemId
set @SQLTORUN = 'delete tbl_Group where db_Group_id in (' + @sItemId + ')'
print @SQLTORUN
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
END
else if (@TypeCode = 'SIM')
BEGIN
set @iStart = 1
set @iEnd = CHARINDEX('^', @DeleteRequest)
set @sItemId = SUBSTRING(@DeleteRequest, 5, CHARINDEX('^', @DeleteRequest)-5)
set @DeleteRequest = replace(@DeleteRequest, 'SIM,'+@sItemId+'^', '')
---- Get the Simulation Id's in an string
if exists(select * from sysobjects where name = 'tempDeleteBuilding')
BEGIN
Drop Table tempDeleteBuilding
END
set @SIM_QUERY = '(Select db_Simulation_Id into tempDeleteBuilding from tbl_simulation where db_Simulation_Id in (' + @sItemId + '))'
EXEC (@SIM_QUERY)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
EXEC spd_DeleteBuilding_DeleteSDSimulation
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
Drop Table tempDeleteBuilding
-- Ask spd_DeleteBuilding_DeleteSDSimulation to delete the records related to Simulation
set @SQLTORUN = 'Delete tbl_Simulation where db_Simulation_id in (' + @sItemId + ')'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
END
else if (@TypeCode = 'DDS')
BEGIN
set @iStart = 1
set @iEnd = CHARINDEX('@', @DeleteRequest)
set @sItemId = SUBSTRING(@DeleteRequest, 5, CHARINDEX('@', @DeleteRequest)-5)
set @DeleteRequest = replace(@DeleteRequest, 'DDS,'+@sItemId+'@', '')
---- Get the Simulation Id's in an string
if exists(select * from sysobjects where name = 'tempDeleteBuilding')
BEGIN
Drop Table tempDeleteBuilding
END
set @SIM_QUERY = '(Select db_DoubleDeck_Simulation_Input_Id into tempDeleteBuilding from tbl_Double_Deck_Simulation_input where db_DoubleDeck_Simulation_Input_Id in (' + @sItemId + '))'
EXEC (@SIM_QUERY)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
EXEC spd_DeleteBuilding_DeleteDDSimulation
Drop Table tempDeleteBuilding
END
else if (@TypeCode = 'STA')
BEGIN
set @iStart = 1
set @iEnd = CHARINDEX('&', @DeleteRequest)
set @sItemId = SUBSTRING(@DeleteRequest, 5, CHARINDEX('&', @DeleteRequest)-5)
set @DeleteRequest = replace(@DeleteRequest, 'STA,'+@sItemId+'&', '')
set @SQLTORUN = 'delete Tbl_Static_Analysis where db_Static_Analysis_Id in ('+ @sItemId +')'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
END
else if (@TypeCode = 'OPT')
BEGIN
set @iStart = 1
set @iEnd = CHARINDEX('*', @DeleteRequest)
set @sItemId = SUBSTRING(@DeleteRequest, 5, CHARINDEX('*', @DeleteRequest)-5)
set @DeleteRequest = replace(@DeleteRequest, 'OPT,'+@sItemId+'*', '')
set @SQLTORUN = 'Delete Tbl_Optimization_Groups where db_Optimization_Id in ('+ @sItemId +')'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
set @SQLTORUN = 'delete Tbl_Optimization_Output where db_Optimization_Id in ('+ @sItemId +')'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
set @SQLTORUN = 'delete Tbl_Optimization where db_Optimization_Id in ('+ @sItemId +')'
EXEC (@SQLTORUN)
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
END
SET @intError = @@ERROR
IF (@intError <> 0) GOTO ERR_HANDLER
END
END
ERR_HANDLER:
IF (@intError <> 0 )
BEGIN
DECLARE @ErrDesc VARCHAR(500)
EXEC spw_Error_GetErrorMessage @intError,
@ErrDesc OUT
EXEC spa_Error_InsertErrorDetails 'Delete Building',
'spd_DeleteBuilding',
@intError,
@ErrDesc
END
August 11, 2008 at 3:57 am
re-check your indices, and try to break this into pieces. i.e. try to break the calls (4 to 5 calls.) Also, set the query timeout to 0 (if necessary).
Atif Sheikh
August 11, 2008 at 9:37 pm
The time out is set to 0(unlimited).
Also when I run the sp from QA it takes approx 3-4 min to exe successfully, but when this sp is called by my application it gives timeout error.
What other factors should I look into
August 12, 2008 at 9:07 am
Check the execution plan of stored procedure and under that check for queries whose cost is high(parameter: query cost relative to batch percentage)...
Manu
August 12, 2008 at 11:16 am
khushbu.kumar (8/11/2008)
I have tried modifying the cursor to loop but could not change much ...some developer has written it .. and it has come to me for optimization.
still the query takes abt 4-5 min for exe.
Do you have specs for it? It would be much easier to just rewrite this from the specs than to try to fix it.
Also, any attempt to de-loop or de-cursor it would need the definitions of the procedure: spd_DeleteBuilding_DeleteSDSimulation.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 12, 2008 at 10:08 pm
alas!! I dont have specs for it and the deveper has long left widout documenting things.
I know its hard to optimize this sp. The child SP is fine and runs much faster.
My main problem is timeout. Is there a way that a timeout period can be increased so that application keeps on pinging db without getting timeout
August 13, 2008 at 7:00 am
The problem is that it is not clear what timeout timer is coming into play here. You already said that you had set all of the timeouts to 0 or 1800 seconds.
In order begin to figure that out we need some more information:
1. What is the error message that you app gets when the timeout occurs?
2. How long is the timeout?
3. How long does the stored procedure take to run?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 13, 2008 at 7:36 am
ans
1)Timeout expired.The timeout period elapsed prior to completion of the operation or the server is not responding.
2) approx after 50-60 sec
3)SP takes approx 3-4 in QA
August 13, 2008 at 7:54 am
OK, this is almost certainly something in your app and/or on the client-side and not in SQL Server itself. Can you show us your connection string and ADO.net connection parameters?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 13, 2008 at 8:13 am
khushbu.kumar (8/13/2008)
ans1)Timeout expired.The timeout period elapsed prior to completion of the operation or the server is not responding.
2) approx after 50-60 sec
3)SP takes approx 3-4 in QA
3-4 seconds or 3-4 minutes?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply