February 11, 2008 at 6:32 am
Hi
I would like to know how to get rid of cursor usage in the following situation. While I won't be changing this code right now as I am implemtning at the customer tomrrow, I would like to know how to better it. I was given data to be imported by the customer. The cursor below has to be run four times, three times on a table consisting of +- 40 000 records where the cursor finishes in about 30 seconds or less as it has nice large groups to work on, and once on another table of +-40 000 records that is currently taking 14 minutes 45seconds and counting.
CREATE TABLE [tbl_Small] (
[SmallID] int IDENTITY(1, 1) NOT NULL,
[SmallDesc] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
)
ON [PRIMARY]
GO;
CREATE TABLE [tbl_Big] (
[BigID] int IDENTITY(1, 1) NOT NULL,
[BigCol1] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS,
[BigCol2] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS,
[BigCol3] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS,
[SmallID] int
)
ON [PRIMARY]
GO;
INSERT INTO tbl_Big ( BigCol1, BigCol2, BigCol3) VALUES ("asd", "fgh", "jkl");
INSERT INTO tbl_Big ( BigCol1, BigCol2, BigCol3) VALUES ("asd", "fgh", "jkl");
INSERT INTO tbl_Big ( BigCol1, BigCol2, BigCol3) VALUES ("asd", "fgh", "jkl");
INSERT INTO tbl_Big ( BigCol1, BigCol2, BigCol3) VALUES ("asd", "fgh", "jkl");
INSERT INTO tbl_Big ( BigCol1, BigCol2, BigCol3) VALUES ("asd", "fgh", "jkl");
INSERT INTO tbl_Big ( BigCol1, BigCol2, BigCol3) VALUES ("asd", "fgh", "jkl");
INSERT INTO tbl_Small ( SmallDesc) VALUES ("Desc1");
INSERT INTO tbl_Small ( SmallDesc) VALUES ("Desc2");
INSERT INTO tbl_Small ( SmallDesc) VALUES ("Desc3");
-- Now this is where I come in
-- Change to allow the insertion of the description field
alter table tbl_Big
alter column SmallID
varchar(100)
-- I insert the values of "Desc1-3" into tbl_Big
UPDATE tbl_BIG SET SmallID = 'Desc1' where BigID = 1
UPDATE tbl_BIG SET SmallID = 'Desc2' where BigID = 2
UPDATE tbl_BIG SET SmallID = 'Desc3' where BigID = 3
UPDATE tbl_BIG SET SmallID = 'Desc3' where BigID = 4
UPDATE tbl_BIG SET SmallID = 'Desc2' where BigID = 5
UPDATE tbl_BIG SET SmallID = 'Desc1' where BigID = 6
-- I am using this:
DECLARE @smallString varchar(100)
----------smallCursor----------
DECLARE smallCursor CURSOR FAST_FORWARD FOR
SELECT DISTINCT
tbl_Big.SmallID
FROM
tbl_Big
----------/smallCursor---------
OPEN smallCursor
FETCH NEXT FROM smallCursor INTO @smallString
WHILE (@@Fetch_Status = 0)
BEGIN
UPDATE tbl_Big
SET SmallID = (SELECT SmallID FROM tbl_Small WHERE SmallID = @smallString)
WHERE SmallID = @smallString
FETCH NEXT FROM smallCursor INTO @smallString
END
CLOSE smallCursor
DEALLOCATE smallCursor
alter table tbl_Big
alter column SmallID
int
How do I change the above to something like:
update tbl_Big
set SmallID = Non-Cursor Magic(TM)
This isn't urgent (unless this last flipping run of this last flipping cursor takes more than an hour), but I would like to know how to do this correctly and efficiently.
Thank you
EDIT: one hour and counting... :crazy:
February 11, 2008 at 8:36 am
It sounds to me that you're making this extra complicated by not giving yourself enough columns in your tbl_big staging table (I'm assuming it's a staging table).
If you were to simply make sure you had an extra column like so:
CREATE TABLE [tbl_Big] (
[BigID] int IDENTITY(1, 1) NOT NULL,
[BigCol1] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS,
[BigCol2] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS,
[BigCol3] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS,
SmallidDesc nvarchar(50) NULL COLLATE SQL_Latin1_General_CP1_CI_AS ,
[SmallID] int NULL
)
ON [PRIMARY]
GO;
Then your update becomes much simpler:
update tbl_big
set smallID = s.smallid
from tbl_big
inner join tbl_small s on tbl_big.smallidDesc=s.SmallDesc
You can then opt to either drop the smallIDDesc column, or if you're continuing to use tbl_Big for more loads, simply nulling it out:
update tbl_big
set smalliddesc=null
where smallid is not null
If this is still too slow - then consider indexing the description fields.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 11, 2008 at 8:45 am
At this point it won't work because table_small.smallid is an integer (identity) being compared to a description.
Toni
February 11, 2008 at 9:27 am
WHILE @@Fetch_Status = 0
BEGIN
UPDATEb
SETb.SmallID = CAST((SELECT s.SmallID FROM tbl_Small AS s WHERE s.SmallDesc = @SmallString) AS VARCHAR)
FROMtbl_Big AS b
WHEREb.SmallID = @SmallString
FETCH NEXT FROM smallCursor INTO @smallString
END
N 56°04'39.16"
E 12°55'05.25"
February 11, 2008 at 9:29 am
-- Prepare sample data
CREATE TABLEtbl_Small
(
SmallID INT IDENTITY(1, 1),
SmallDesc NVARCHAR(50) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
)
INSERTtbl_Small
(
SmallDesc
)
SELECT'Desc1' UNION ALL
SELECT'Desc2' UNION ALL
SELECT'Desc3'
CREATE TABLEtbl_Big
(
BigID INT IDENTITY(1, 1),
BigCol1 NVARCHAR(50) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,
BigCol2 NVARCHAR(50) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,
BigCol3 NVARCHAR(50) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,
SmallID INT
)
INSERTtbl_Big
(
BigCol1,
BigCol2,
BigCol3
)
SELECT'asd', 'fgh', 'jkl' UNION ALL
SELECT'asd', 'fgh', 'jkl' UNION ALL
SELECT'asd', 'fgh', 'jkl' UNION ALL
SELECT'asd', 'fgh', 'jkl' UNION ALL
SELECT'asd', 'fgh', 'jkl' UNION ALL
SELECT'asd', 'fgh', 'jkl'
-- Change to allow the insertion of the description field
ALTER TABLEtbl_Big
ALTER COLUMNSmallID VARCHAR(100)
-- I insert the values of 'Desc1-3' into tbl_Big
UPDATE tbl_BIG SET SmallID = 'Desc1' where BigID IN (1, 6)
UPDATE tbl_BIG SET SmallID = 'Desc2' where BigID IN (2, 5)
UPDATE tbl_BIG SET SmallID = 'Desc3' where BigID IN (3, 4)
-- Update the complete table
UPDATEb
SETb.SmallID = s.SmallID
FROMtbl_Big AS b
INNER JOINtbl_Small AS s ON s.SmallDesc = b.SmallID
ALTER TABLEtbl_Big
ALTER COLUMNSmallID INT
SELECT*
FROMtbl_Small
SELECT*
FROMtbl_Big
DROP TABLEtbl_Small,
tbl_Big
N 56°04'39.16"
E 12°55'05.25"
February 11, 2008 at 10:41 am
Thank you very much Peso!
Cursor == 1hr 50 min.
UPDATE b SET b.SmallID = s.SmallID FROM tbl_Big AS b INNER JOIN tbl_Small AS s ON s.SmallDesc = b.SmallID
== 1.25sec
Peso.Reputation++;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply