Cursor takes 4 min to open
-
Is there anyone who would know why the following cursor takes 4 minutes to open? Note that only part of the code is displayed. The code runs immediately when teh CURSOR is not involved. I have pinned it down to exactly the OPEN csr_import statement. Any help much appreciated.
DECLARE
@importIdentity INT, @userIdentity INT
SET @importIdentity = 10
SET @userIdentity = 43822
DECLARE @myXML XML
SELECT @myXML = myXML
FROM tbl_importXML (NOLOCK)
WHERE (importIdentity = @importIdentity)
DECLARE @itemIdentity BIGINT
, @url VARCHAR( 255 )
, @title VARCHAR( 80 )
, @description VARCHAR( 8000 )
, @quantity SMALLINT
, @summary VARCHAR( 100 )
, @category VARCHAR( 1000 )
, @price SMALLMONEY
, @classifiedIdentity INT
, @categoryIdentity INT
, @localityIdentity INT
, @imported BIT
DECLARE csr_import INSENSITIVE CURSOR
FOR
SELECT myRow.myColumn.value( '@Id[1]', 'BIGINT' ) AS itemIdentity
, myRow.myColumn.value( 'Url[1]', 'VARCHAR(255)' ) AS url
--, RTRIM( LTRIM( myRow.myColumn.value( 'Description[1]', 'VARCHAR(8000)' ) ) ) AS description
, RTRIM( LTRIM( myRow.myColumn.value( 'Description[1]', 'VARCHAR(80)' ) ) ) AS title
, RTRIM( LTRIM( myRow.myColumn.value( 'Caption[1]', 'VARCHAR(8000)' ) ) ) AS description
, myRow.myColumn.value( 'Quantity[1]', 'SMALLINT' ) AS quantity
, RTRIM( LTRIM( myRow.myColumn.value( 'Caption[1]', 'VARCHAR(100)' ) ) ) AS summary
, myRow.myColumn.value( 'Category[1]', 'VARCHAR(1000)' ) AS category
, CONVERT( SMALLMONEY, REPLACE( myRow.myColumn.value( '(Pricing/BasePrice)[1]', 'VARCHAR( 255 )' ), 'AU $', '' ) ) AS price
FROM @myXML.nodes('//Product') myRow( myColumn )
OPEN csr_import
-
1. It's a cursor
(Seriously)2. It's XML
(Pretty sure cursors and XML don't get along so well)--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
yes, they are good for something.
Didn't get much out of your post
-
Can you test with CTE and see whether that works?
-
Hi, I'm not sure what CTE is?
-
Not a problem. I have put together this example for you to study at your leisure:
DECLARE @xVar XML
SET @xVar =
'<book genre="security" publicationdate="2002" ISBN="0-7356-1588-2">
<title>Writing Secure Code</title>
<author>
<first-name>Michael</first-name>
<last-name>Howard</last-name>
</author>
<author>
<first-name>David</first-name>
<last-name>LeBlanc</last-name>
</author>
<price>39.99</price>
</book>';
DECLARE csr_import INSENSITIVE CURSOR
FOR
WITH cte_books (FirstName, LastName)
AS (
SELECT nref.value('first-name[1]', 'nvarchar(50)') FirstName,
nref.value('last-name[1]', 'nvarchar(50)') LastName
FROM @xVar.nodes('//author') AS R(nref)
)
SELECT * FROM cte_books
OPEN csr_import
DECLARE @fname sysname, @lname sysname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @fname + ' ' + @lname
FETCH NEXT FROM csr_import INTO @fname, @lname
END
CLOSE csr_import
DEALLOCATE csr_import
HTH
Paul
-
If it takes 4 minutes to open such a simple cursor, you still think cursors are good for something?
I did't get much from your post either Seriously, what are you going to do with the cursor (besides open it, I mean
) once you get it to open in a timely fashion?--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The sql statement run alone always executes fine? I had to look up INSENSITIVE, here's the BOL:
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications. When SQL-92 syntax is used, if INSENSITIVE is omitted, committed deletes and updates made to the underlying tables (by any user) are reflected in subsequent fetches.
Depending on the size of the resultset, maybe it's blocking/slow perf in TempDB. I'd try running through the cursor variations to see if eliminating TempDB usage affects the query time.
-
Hi all,
the sample code posted ran fine, executed in one sec.
I've tried the cursor without the INSENSITIVE hint, no difference.
Following is the complete code, if anyone can see how to perform the same action without a cursor, please let me know.
set
ANSI_NULLS ON
set
QUOTED_IDENTIFIER ON
go
ALTER
PROCEDURE [dbo].[sp_importXML]
@filePath
VARCHAR( 255 )
, @userIdentity INT
, @uuid CHAR( 32 )
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql VARCHAR( 4000 ), @importIdentity INT
INSERT INTO tbl_importXML (uuid, userIdentity)
VALUES (@uuid, @userIdentity)
SET @importIdentity = SCOPE_IDENTITY()
SET @sql = '
UPDATE tbl_importXML
SET userIdentity = '
+ CAST( @userIdentity AS VARCHAR( 10 ) ) + '
, myXML = ( SELECT x.* FROM OPENROWSET ( BULK '''
+ @filePath + ''', SINGLE_CLOB ) AS x )
WHERE (uuid = '''
+ @uuid + ''')'
EXEC (@sql)
/*
DECLARE @importIdentity INT, @userIdentity INT
SET @importIdentity = 10
SET @userIdentity = 43822
*/
DECLARE @myXML XML
SELECT @myXML = myXML
FROM tbl_importXML (NOLOCK)
WHERE (importIdentity = @importIdentity)
DECLARE @itemIdentity BIGINT
, @url VARCHAR( 255 )
, @title VARCHAR( 80 )
, @description VARCHAR( 8000 )
, @quantity SMALLINT
, @summary VARCHAR( 100 )
, @category VARCHAR( 1000 )
, @price SMALLMONEY
, @classifiedIdentity INT
, @categoryIdentity INT
, @localityIdentity INT
, @imported BIT
, @classifiedCountLeft INT
DECLARE csr_import INSENSITIVE CURSOR
FOR
SELECT myRow.myColumn.value( '@Id[1]', 'BIGINT' ) AS itemIdentity
, myRow.myColumn.value( 'Url[1]', 'VARCHAR(255)' ) AS url
--, RTRIM( LTRIM( myRow.myColumn.value( 'Description[1]', 'VARCHAR(8000)' ) ) ) AS description
, RTRIM( LTRIM( myRow.myColumn.value( 'Description[1]', 'VARCHAR(80)' ) ) ) AS title
, RTRIM( LTRIM( myRow.myColumn.value( 'Caption[1]', 'VARCHAR(8000)' ) ) ) AS description
, myRow.myColumn.value( 'Quantity[1]', 'SMALLINT' ) AS quantity
, RTRIM( LTRIM( myRow.myColumn.value( 'Caption[1]', 'VARCHAR(100)' ) ) ) AS summary
, myRow.myColumn.value( 'Category[1]', 'VARCHAR(1000)' ) AS category
, CONVERT( SMALLMONEY, REPLACE( myRow.myColumn.value( '(Pricing/BasePrice)[1]', 'VARCHAR( 255 )' ), 'AU $', '' ) ) AS price
FROM @myXML.nodes('//Product') myRow( myColumn )
OPEN csr_import
SELECT @localityIdentity = localityIdentity
FROM tbl_address (NOLOCK)
WHERE (userIdentity = @userIdentity)
IF (@localityIdentity IS NULL)
BEGIN
RETURN
END
-- create table to hold duplicates
DECLARE @tbl_duplicate TABLE (
itemIdentity
BIGINT NOT NULL
)
-- create table to hold messages
DECLARE @tbl_message TABLE (
[message]
VARCHAR( 255 ) NOT NULL
)
-- create table to mappings not found
DECLARE @tbl_categoryMappingNotFound TABLE (
[categoryName]
VARCHAR( 1000 ) NOT NULL
)
DECLARE @tbl_classifiedCountLeft TABLE (
classifiedCountLeft
INT NOT NULL
)
FETCH NEXT
FROM csr_import
INTO @itemIdentity
, @url
, @title
, @description
, @quantity
, @summary
, @category
, @price
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @categoryIdentity = categoryIdentity
FROM tbl_importCategoryMapping (NOLOCK)
WHERE (remoteCategory = @category)
INSERT INTO @tbl_classifiedCountLeft
EXEC [sp_getClassifiedCountLeft] @userIdentity = @userIdentity
SELECT @classifiedCountLeft = classifiedCountLeft
FROM @tbl_classifiedCountLeft
-- if the item identity exists and the classified identity is not 0 then its already imported
IF ( EXISTS ( SELECT itemIdentity FROM tbl_importedXML WHERE itemIdentity = @itemIdentity AND classifiedIdentity > 0 ) )
BEGIN
SET @imported = 1
INSERT INTO @tbl_duplicate
VALUES (@itemIdentity)
INSERT INTO @tbl_message
VALUES ('The item with the following item identity is already imported: "' + CAST( @itemIdentity AS VARCHAR(50) ) )
END
ELSE
BEGIN
SET @imported = 0
INSERT INTO @tbl_message
VALUES ('Trying to import item with identity: "' + CAST( @itemIdentity AS VARCHAR(50) ) )
END
-- make sure that;
-- there is a category identity
-- the classified is not already imported previously
-- the user has classifieds left to upload
IF (NOT @categoryIdentity IS NULL AND @imported = 0 AND ( SELECT classifiedCountLeft FROM @tbl_classifiedCountLeft ) > 0 )
BEGIN
INSERT INTO tbl_classified (
categoryIdentity
, userIdentity
, quantity
, title
, summary
, description
, price
, localityIdentity
, statusIdentity
)
VALUES (
@categoryIdentity
, @userIdentity
, @quantity
, COALESCE( @title, '' )
, @summary
, @description
, @price
, @localityIdentity
, 4 -- 4 = awaiting scanning, ie negative keyword check
)
SET @classifiedIdentity = SCOPE_IDENTITY()
END
ELSE
BEGIN
IF ( @categoryIdentity IS NULL )
BEGIN
INSERT INTO @tbl_message
VALUES ('The following category mapping could not be found SMS will be contacted: "' + @category + '"')
INSERT INTO @tbl_categoryMappingNotFound
VALUES ('The following category mapping could not be found: "' + @category + '"')
END
IF ( @classifiedCountLeft <= 0 )
BEGIN
INSERT INTO @tbl_message
VALUES ('Tried to import item: ' + CAST( @itemIdentity AS VARCHAR( 50 ) ) + ' but you have no ads left on your account')
END
INSERT INTO @tbl_message
VALUES ('The following item could not be imported: "' + CAST( COALESCE( @itemIdentity, '' ) AS VARCHAR(50) ) + ', category identity: ' + CAST( COALESCE( @categoryIdentity, '' ) AS VARCHAR( 10 ) ) )
END
INSERT INTO tbl_importedXML (
importIdentity
, itemIdentity
, classifiedIdentity
)
VALUES (
@importIdentity
, @itemIdentity
, COALESCE( @classifiedIdentity, 0 ) -- 0 means the classified is not imported
)
FETCH NEXT FROM csr_import
INTO @itemIdentity
, @url
, @title
, @description
, @quantity
, @summary
, @category
, @price
DELETE
FROM @tbl_classifiedCountLeft
SET @classifiedIdentity = 0
END
CLOSE csr_import
DEALLOCATE csr_import
UPDATE tbl_importXML
SET completeDate = GETDATE()
WHERE (importIdentity = @importIdentity)
-- return the identity of the import
SELECT @importIdentity AS importIdentity
-- return duplicates
SELECT itemIdentity
FROM @tbl_duplicate
-- return messages
SELECT [message]
FROM @tbl_message
-- return category mappings not found
SELECT [categoryName]
FROM @tbl_categoryMappingNotFound
END
-
You need to change your mind set to perform that process by SET *not* by ROW just import the XML File ENTIRELY in a temp table then update/delete/insert into detination table(S)
just don't do it row-by-agonazing-row ...
* Noel -
Just a suggestion...should you change your mind about using a cursor, here's an article about code that loops without using one.
http://www.sqlservercentral.com/columnists/kAli/eliminatingcursors.asp
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply