February 17, 2008 at 10:22 am
How Can I accomplish this without using CURSOR?? This is running extreemly slow since the t_ATM table has over a million records. Appreciate any help!
DECLARE
@VMID INT,
@VMCA CHAR(1)
BEGIN
DECLARE C_TEMP CURSOR FOR
SELECT DISTINCT MID, MCA FROM t_ATM WHERE Date BETWEEN @lTime AND @ntime
FOR READ ONLY
END
CREATE TABLE #TT_MUM
(
MID int
,MCA CHAR(1)
)
OPEN C_TEMP
FETCH C_TEMP
INTO @VMID, @VMCA
WHILE @@SQLSTATUS = 0
BEGIN
INSERT INTO #TT_MUM
(
MID
,MCA
)
VALUES
(
@VMID
,@VMCA
)
FETCH C_TEMP
INTO @VMID, @VMCA
END
CLOSE C_TEMP
DEALLOCATE CURSOR C_TEMP
February 17, 2008 at 10:25 am
ebuss_2004 (2/17/2008)
How Can I accomplish this without using CURSOR?? This is running extreemly slow since the t_ATM table has over a million records. Appreciate any help!DECLARE
@VMID INT,
@VMCA CHAR(1)
BEGIN
DECLARE C_TEMP CURSOR FOR
SELECT DISTINCT MID, MCA FROM t_ATM WHERE Date BETWEEN @lTime AND @ntime
FOR READ ONLY
END
CREATE TABLE #TT_MUM
(
MID int
,MCA CHAR(1)
)
OPEN C_TEMP
FETCH C_TEMP
INTO @VMID, @VMCA
WHILE @@SQLSTATUS = 0
BEGIN
INSERT INTO #TT_MUM
(
MID
,MCA
)
VALUES
(
@VMID
,@VMCA
)
FETCH C_TEMP
INTO @VMID, @VMCA
END
CLOSE C_TEMP
DEALLOCATE CURSOR C_TEMP
Um... thanks for posting the code... but, instead of me analyzing the code, it would be very helpful if you provided a simple description of what the code does. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 17, 2008 at 10:31 am
Never mind... I just had another cup of coffee...
Here's the untested answer...
--===== If the temp table exists, drop it
IF OBJECT_ID('TempDB..#tt_Mum','U') IS NOT NULL
DROP TABLE #tt_Mum
--===== Transfer the required data into a new temp table, on the fly
SELECT MID,MCA
INTO #TT_Mum
FROM t_Atm
WHERE Date BETWEEN @lTime AND @nTime
--Jeff Moden
Change is inevitable... Change for the better is not.
February 17, 2008 at 7:55 pm
Thanks. I am new at this cursor and trying to modify someone else code so that it run faster. I wonder why would someone use Cursor if we can do this just like the way you did it here very simple way to achieve the same result.
February 17, 2008 at 10:17 pm
ebuss_2004 (2/17/2008)
Thanks. I am new at this cursor and trying to modify someone else code so that it run faster. I wonder why would someone use Cursor if we can do this just like the way you did it here very simple way to achieve the same result.
A standing ovation and a tip of the hat for your, Sir! I have no clue why someone would use a cursor to do such a, as you said, very simple task. It's great to see someone cleaning stuff like that up. I always say "Tune the code. That's where the performance is!" 😉
Thank you for the feedback. If you need additional help trying to cleanup you code, please don't hesitate to post... keep in mind, a description of the undocumented code would do wonders. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 18, 2008 at 4:43 am
Be careful you just lost the DISTINCT clause which may or may not be important or required, only the data can tell you.
Still can't see why anyone would do this with a cursor though.
February 18, 2008 at 6:21 am
I'm not defending the cursor, but wouldn't the proposed solution cause potential Tempdb locking problems?
I always create the temp table first, then INSERT into it to avoid such issues.
e.g.
--Create temp table...
SELECT TOP 0 MID,MCA
INTO #TT_Mum
FROM t_Atm
--Fill with data...
INSERT INTO #TT_Mum (MID,MCA)
SELECT MID,MCA
FROM t_Atm
...
February 18, 2008 at 11:13 am
Schnurdle (2/18/2008)
I'm not defending the cursor, but wouldn't the proposed solution cause potential Tempdb locking problems?I always create the temp table first, then INSERT into it to avoid such issues.
e.g.
--Create temp table...
SELECT TOP 0 MID,MCA
INTO #TT_Mum
FROM t_Atm
--Fill with data...
INSERT INTO #TT_Mum (MID,MCA)
SELECT MID,MCA
FROM t_Atm
...
It will, absolutely, cause some locking in TempDB... but, since TempDB is in the Simple Recovery mode, you can get some super speed out of the transfer.
For example...
SET STATISTICS TIME ON
SELECT *
INTO #DropMe
FROM Belution.dbo.JBMTest
SET STATISTICS TIME OFF
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(1000000 row(s) affected)
SQL Server Execution Times:
CPU time = 4203 ms, elapsed time = 4875 ms.
Of course... that would be for a batch job that runs maybe once a day... you certainly wouldn't move that many rows to support a GUI.
As you mention, you can still get some pretty good speed out of creating the table first and then doing a normal insert. Problem is if you have a lot of columns, people get lazy and don't want to do all of that. As you're probably aware, there is a compromise that works very well...
SET STATISTICS TIME ON
--===== This creates the table without all the work
-- and the lock time is virtually non-existant
SELECT *
INTO #DropMe
FROM dbo.JBMTest
WHERE 1=0
--===== Then, copy the date. Since temp DB is in SIMPLE
-- Recovery mode, this will still be very fast. The
-- tab lock on the temp table allows bulk insert speeds.
INSERT INTO #DropMe WITH (TABLOCKX)
SELECT *
FROM dbo.JBMTest
SET STATISTICS TIME OFF
... unless an IDENTITY column is present...
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Server: Msg 8101, Level 16, State 1, Line 13
An explicit value for the identity column in table '#DropMe' can only be specified when a column list is used and IDENTITY_INSERT is ON.
... and, when you try to over come that...
SET STATISTICS TIME ON
--===== This creates the table without all the work
-- and the lock time is virtually non-existant
SELECT *
INTO #DropMe
FROM dbo.JBMTest
WHERE 1=0
--===== Then, copy the date. Since temp DB is in SIMPLE
-- Recovery mode, this will still be very fast. The
-- tab lock on the temp table allows bulk insert speeds.
SET IDENTITY_INSERT #DropMe ON
INSERT INTO #DropMe WITH (TABLOCKX)
SELECT *
FROM dbo.JBMTest
SET STATISTICS TIME OFF
... you run into the problem of having to define all of the columns, anyway...
Server: Msg 8101, Level 16, State 1, Line 14
An explicit value for the identity column in table '#DropMe' can only be specified when a column list is used and IDENTITY_INSERT is ON.
... and, you might as well write a script to create the table and do the insert with discrete columns names, anyway.
So, my trade off is (especially for one-off code), get in and get out as fast as you can... 1 million rows in 4- 1/5 seconds is probably tolerable. 🙂
Just for reference, here's the transfer times for my JBMTest table and Select/Into...
[font="Courier New"]
100 rows - <1 ms (shows as 0 ms)
1,000 rows - <1ms - 7 ms
10,000 rows - 31-44 ms
100,000 rows - 407 - 426 ms
1,000,000 rows - ~4250 ms (4.25 seconds)
[/font]
So, is locking a problem for this? It depends and how, when, and why you're using it. You certainly wouldn't want a 20 minute batch job to keep TempDB locked up for it's entire run nor would you want to use it in a GUI interface that's going to hit tempdb 10 thousand times a second. But for normal batch jobs and reasonable GUI queries, why not?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 18, 2008 at 11:46 am
Ken Gaul (2/18/2008)
Be careful you just lost the DISTINCT clause which may or may not be important or required, only the data can tell you.Still can't see why anyone would do this with a cursor though.
Sorry about that... here's the corrected code that includes the DISTINCT.
--===== If the temp table exists, drop it
IF OBJECT_ID('TempDB..#tt_Mum','U') IS NOT NULL
DROP TABLE #tt_Mum
--===== Transfer the required data into a new temp table, on the fly
SELECT DISTINCT MID,MCA
INTO #TT_Mum
FROM t_Atm
WHERE Date BETWEEN @lTime AND @nTime
--Jeff Moden
Change is inevitable... Change for the better is not.
February 18, 2008 at 2:00 pm
Thanx for everyone's input. Appreciate it.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply