Stored Procedure Takes a Long Time As A Scheduled Task
-
Hi Everyone,
When I run the SP I have copied below in Query Analzyer, it runs in about 15 mins. That's okay as it's a batch process. When I run it as a step in a SQL Scheduled Task it takes more then 1 hour. I can't figure out what the difference is. The step in the task is EXEC CreatePolygonTileIndex.
Here is the sp:
CREATE Procedure [dbo].[CreatePolygonTileIndex]
as
BEGIN TRANSACTION
DROP TABLE PolygonTileIndex
CREATE TABLE PolygonTileIndex (TIX int, TIY int, MSLINK int)
DECLARE Coalesce_Cursor CURSOR FAST_FORWARD FOR
SELECT MSLINK, CAST(XLOW/1000 AS Int), CAST(YLOW/1000 AS Int), CAST(XHIGH/1000 AS Int), CAST(YHIGH/1000 AS Int)
FROM Polygon
DECLARE @mslink int
DECLARE @xlow int
DECLARE @ylow int
DECLARE @xhigh int
DECLARE @yhigh int
DECLARE @xi int
DECLARE @yi int
OPEN Coalesce_Cursor
WHILE (1 = 1)
BEGIN
FETCH NEXT FROM Coalesce_Cursor INTO @mslink, @xlow, @ylow, @xhigh, @yhigh
IF (@@fetch_status = -1) BEGIN BREAK END
IF (@@fetch_status <> -2)
BEGIN
SET @xi = @xlow
WHILE (@xi <= @xhigh)
BEGIN
SET @yi = @ylow
WHILE (@yi <= @yhigh)
BEGIN
INSERT PolygonTileIndex (TIX, TIY, MsLink)
VALUES (@xi, @yi, @mslink)
SET @yi = @yi + 1
END
SET @xi = @xi + 1
END
END
END
DEALLOCATE Coalesce_Cursor
CREATE CLUSTERED INDEX IX_PolygonTileIndex_TileIndex ON PolygonTileIndex (TIX, TIY)
COMMIT TRANSACTION
GO
-
4 things.
1. the drop and create may be causing the SP to re-compile. try to move this outside the SP.
2. your dropping/creating of 1 table....has implications for running 2 people this SP at the same time...ie it won't work....and has implications for security...ie any rights granted will be dropped as well. (the command TRUNCATE may work faster and achieve the same result)
3. Always qualify references to tables with "dbo."....faster, improves ability to use cache, etc.
4. your real problem is with the cursor....there should be a way that this could be done in a set-based technique far faster. Please post table design, some sample input data and sample expected results....and we may be able to get this to run 100times faster.
-
Thanks for the input.
I will try truncating the table in place of the drop and recreate this morning.
The drop and create is not an issue as this is not a user stored procedure. Kicked off by one batch job that I schedule.
I will add the references to dbo.
Here is the design of the table.
CREATE TABLE [dbo].[PolygonTileIndex] (
[TIX] [int] NULL ,
[TIY] [int] NULL ,
[MSLINK] [int] NULL
) ON [PRIMARY]
GO
The result of the SP is a load PolygonTileIndex. Here is a couple samples of data:
TIX TIY MSLINK
----------- ----------- -----------
-329 5970 903912
-329 5970 903942
Sample Input used in the Select Part of the SP:
MSLINK XLow YLow Xhigh Yhigh
----------- ----------- ----------- ----------- -----------
1 -315 6163 -315 6163
2 -315 6164 -315 6164
Again, I'm still confused why this SP runs very fast in Query Analyzer but not as a scheduled task. Thanks for the hlep.
-
"Here is the design of the table.
CREATE TABLE [dbo].[PolygonTileIndex] (
[TIX] [int] NULL ,
[TIY] [int] NULL ,
[MSLINK] [int] NULL
) ON [PRIMARY]
GO"
and what indices are in place?????
with regard to the sample data....is that the complete set to match the output shown?....if not, then any attempt by somebody on this site to recreate your output is doomed to failure.
re whay it runs fast in QA....I wouldn't call 15mins fast unless I was working on a 286pc....or working with 15million records in my dataset.
What's the execution plan for the SP?
-
The only index is the one created at the bottom of the SP.
CREATE CLUSTERED INDEX IX_PolygonTileIndex_TileIndex ON PolygonTileIndex (TIX, TIY)
I guess given that I am now truncating the table instead of dropping and recreating I will drop the index as well at the beginning.
I can't send the entire result set. It's 4+ million rows.
Would you like the execution plan copied into a post? Can I even do that?
Thanks
-
Post the plan in text form....they usually are fairly readable.
regarding the data....can you drum up a mini-set approx10 rows and line it up with a matching expected result. 4m rows doesn't interest me.
re the table defenition...I meant (if it wasn't stated) the table supporting the 'cursor'.
You may also get some success on another forum I participate in called "www.sqlteam.com"
-
Here is the design of the Polygon table. The one supporting the cursor:
CREATE TABLE [dbo].[Polygon] (
[MSLINK] [int] IDENTITY (1, 1) NOT NULL ,
[CLASSTYPE] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SYSTEMID] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PID] [int] NOT NULL ,
[XLABEL] [float] NULL ,
[YLABEL] [float] NULL ,
[XLOW] [float] NULL ,
[YLOW] [float] NULL ,
[XHIGH] [float] NULL ,
[YHIGH] [float] NULL ,
[ISLAND] [bit] NOT NULL ,
[GEOM] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MouseOver] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GeomBlob] [image] NULL ,
[FileArea] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I must be missing something simple but couldn't sort out how to get the plan in plain text from query analyzer.
Here are 10 rows from the select in the cursor:
MSLINK xlow ylow xhigh yhigh
----------- ----------- ----------- ----------- -----------
1697573 286 6036 287 6036
1697574 15 6036 16 6036
1697575 323 6036 324 6036
1697576 78 6036 79 6036
1697577 78 6036 79 6036
1697578 310 6036 311 6036
1697579 -18 6036 -17 6036
1697580 153 6036 154 6036
1697581 -87 6036 -86 6036
1697582 16 6036 17 6036
The results in PolygonTileIndex:
TIX TIY MSLINK
----------- ----------- -----------
323 6036 1697575
324 6036 1697575
78 6036 1697576
79 6036 1697576
78 6036 1697577
79 6036 1697577
310 6036 1697578
311 6036 1697578
-18 6036 1697579
-17 6036 1697579
153 6036 1697580
154 6036 1697580
-87 6036 1697581
-86 6036 1697581
16 6036 1697582
17 6036 1697582
Thanks for the help. I hope we find something. I also posted on the other forum you suggested.
-
a couple of issues.
1. where's the indices on the source table...polygon?
2. your 'expected results don't line up with your input data....where's the output for 1697573 and 1697574?
2a) in general it makes it easier to debug if you can also supply the input data in the form of insert statements...small thing for future issues.
3. your input looks a bit understated in that a very simple way of getting the output shown, can be got by the code below....(but that MAY be because in the data supplied every xlow is 1 less then the xhigh....is this the case in real life?)
INSERT PolygonTileIndex (TIX, TIY, MsLink)
SELECT xlow, ylow, mslink from Polygon
union
select xhigh, yhigh, mslink from Polygon
4. I typed in your data, ran the code supplied and got the output below. have you got some other data in there to get the result you display?
TIX TIY MSLINK
----------- ----------- -----------
0 6 1697573
0 6 1697574
0 6 1697575
0 6 1697576
0 6 1697577
0 6 1697578
0 6 1697579
0 6 1697580
0 6 1697581
0 6 1697582
(10 row(s) affected)
5. in the execution plan, I see TABLE SCANS on EACH selection from the CURSOR...absolutely a NIGHTMARE....unless you have indices you're not telling me about.
6. can you step back a bit and describe what you are trying to achieve in business terms....ie what's the purpose of the SP...it looks like it's trying to transform some input data?
-
1. Index and PK on Polygon Table:
ALTER TABLE [dbo].[Polygon] ADD
CONSTRAINT [DF_Polygon_ISLAND] DEFAULT (0) FOR [ISLAND],
CONSTRAINT [PK_Polygon] PRIMARY KEY NONCLUSTERED
(
[MSLINK]
 
WITH FILLFACTOR = 90 ON [PRIMARY]GO
CREATE INDEX [IX_Polygon_SystemID_PID] ON [dbo].[Polygon]([SYSTEMID], [PID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
2. Another attempt at data output:
Insert [dbo].[PolygonTileIndex] (TIX, TIY, MsLink)VALUES (286,6036,1697573)
Insert [dbo].[PolygonTileIndex] (TIX, TIY, MsLink)VALUES (287,6036,1697573)
Insert [dbo].[PolygonTileIndex] (TIX, TIY, MsLink)VALUES (15,6036,1697574)
Insert [dbo].[PolygonTileIndex] (TIX, TIY, MsLink)VALUES (16,6036,1697574)
Insert [dbo].[PolygonTileIndex] (TIX, TIY, MsLink)VALUES (323,6036,1697575)
Insert [dbo].[PolygonTileIndex] (TIX, TIY, MsLink)VALUES (324,6036,1697575)
Insert [dbo].[PolygonTileIndex] (TIX, TIY, MsLink)VALUES (78,6036,1697576)
Insert [dbo].[PolygonTileIndex] (TIX, TIY, MsLink)VALUES (79,6036,1697576)
Insert [dbo].[PolygonTileIndex] (TIX, TIY, MsLink)VALUES (78,6036,1697577)
Insert [dbo].[PolygonTileIndex] (TIX, TIY, MsLink)VALUES (79,6036,1697577)
Insert [dbo].[PolygonTileIndex] (TIX, TIY, MsLink)VALUES (310,6036,1697578)
Insert [dbo].[PolygonTileIndex] (TIX, TIY, MsLink)VALUES (311,6036,1697578)
Insert [dbo].[PolygonTileIndex] (TIX, TIY, MsLink)VALUES (-18,6036,1697579)
Insert [dbo].[PolygonTileIndex] (TIX, TIY, MsLink)VALUES (-17,6036,1697579)
Insert [dbo].[PolygonTileIndex] (TIX, TIY, MsLink)VALUES (153,6036,1697580)
Insert [dbo].[PolygonTileIndex] (TIX, TIY, MsLink)VALUES (154,6036,1697580)
Insert [dbo].[PolygonTileIndex] (TIX, TIY, MsLink)VALUES (-87,6036,1697581)
Insert [dbo].[PolygonTileIndex] (TIX, TIY, MsLink)VALUES (-86,6036,1697581)
Insert [dbo].[PolygonTileIndex] (TIX, TIY, MsLink)VALUES (16,6036,1697582)
Insert [dbo].[PolygonTileIndex] (TIX, TIY, MsLink)VALUES (17,6036,1697582)
TIX TIY MSLink
286 6036 1697573
287 6036 1697573
15 6036 1697574
16 6036 1697574
323 6036 1697575
324 6036 1697575
78 6036 1697576
79 6036 1697576
78 6036 1697577
79 6036 1697577
310 6036 1697578
311 6036 1697578
-18 6036 1697579
-17 6036 1697579
153 6036 1697580
154 6036 1697580
-87 6036 1697581
-86 6036 1697581
16 6036 1697582
17 6036 1697582
In terms of what the business if trying to do here I'm totatlly sure as we just inherited this SP from a third party vendor. The Polygon table contains coordinates and the PolygonTileINdex table is loaded with those coordinates to be used by a mapping component in the application. Sorry. I know that's not a very good explanation.
Thanks.
-
1...data INPUT is what's needed....(but you're on the right track in style for future problems)
2. my code @ 3 above will give your output....BUT more complex input data MAY invalidate my code....(what you provided was very limited in terms of values)....
With your input data, and your code...I also DON'T get your output....so I'm very confused!!!
I work in Ireland....so it's going home time now....hope this problem improves for you over the weekend.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply