February 2, 2010 at 2:39 am
Hi all,
To improve performances on SQL Katmai (2K8), I'm searching a way to eliminate a cursor in a SP that produces a table MD5 signature.
For the master database, I profile that it uses 106 reads, no writes, no CPU and a 1 in duration.
If any guru can help to find a less resource consuming way to do it... (I think that it may be also useful for other users), here's the code part:
[font="Courier New"]USE [master]
GO
-- code to get all the tables MD5 "signature" in a database
DECLARE @tblSignature NVARCHAR(MAX) = ''
, @currentTableName NVARCHAR(128) = '';
DECLARE @signatureMD5 NVARCHAR(MAX) = ''
, @scriptLen INT
, @scriptLoop INT;
-- declare a cursor --<<< is there another way???
DECLARE CursorTable CURSOR FORWARD_ONLY FOR
SELECT t.[name]
FROM sysobjects t WITH (NOLOCK)
WHERE t.[type] = 'U';
-- open cursor
OPEN CursorTable;
-- process is done table by table
FETCH NEXT FROM CursorTable INTO @currentTableName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- initialize variables
SET @signatureMD5 = '';
SET @tblSignature = '';
-- builds the signature by adding elements
SELECT @tblSignature = @tblSignature + N'|' + c.[name] + N',' + CONVERT(NVARCHAR(1) , c.is_identity)
+ CONVERT(NVARCHAR(10) , c.system_type_id) + CONVERT(NVARCHAR(10) , c.max_length)
FROM sys.[columns] c WITH (NOLOCK)INNER
JOIN sys.objects o
ON o.[object_id] = c.[object_id]
WHERE o.[name] = @currentTableName
ORDER BY RANK() OVER(PARTITION BY c.[object_id] ORDER BY c.column_id);
-- to avoid error, split the result by NVARCHAR(4000)
SELECT @scriptLen = LEN(@tblSignature)
, @scriptLoop = 1;
WHILE (@scriptLoop < @scriptLen)
BEGIN
SET @signatureMD5 = @signatureMD5 +
CONVERT(NVARCHAR(MAX), HashBytes('MD5' , SUBSTRING(@tblSignature , @scriptLoop , 4000)), 1);
SET @scriptLoop += 4000;
END
-- prints out the result
PRINT @signatureMD5;
--- fetch next element
FETCH NEXT FROM CursorTable INTO @currentTableName;
END
CLOSE CursorTable;
DEALLOCATE CursorTable;
GO
[/font]
Thanks all for your valuable advices.
--
Philippe RUELLO
Database Project Manager
February 2, 2010 at 6:47 am
for step by step you can replace the cursor with a while loop. But to be honest " if it ain't broke don't fix it ".
Do you want to remove the cursor because you think you should to look cool?
Do you suffer from a cursor phobia - anyone know if there's an official name ?
Is it a challenge ?
Do you win a prize for doing so?
Seriously though - it's a really simple query to replace with a while which suggests to me you may not be very familiar with T SQL.
Essentially you put the contents of the cursor select into say a table variable with an identity column and then sequence through the ids in a while loop - which is exactly what a cursor does of course.
i'm not convinced you can use sets to do your query and sometimes you just want to do sequential ops - I suggest this may be one of them
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 2, 2010 at 9:36 am
Thanks a lot colin.Leversuch-Roberts for the answer you did.
No price to win 😉 , sorry.
As you wrote I'm not convinced myself that using a while loop (or a set) would be always better. In this peculiar case, I prefer using a cursor.
But as we often read that cursor is the bad way :angry: I posted this to get other opinions on the subject.
Kind regards,
--
Philippe RUELLO
Database Project Manager
February 4, 2010 at 3:30 am
I think that sometimes the desire to achieve a goal masks the requirement. I've seen many hours spent trying to replace cursor statements on pieces of code which run once a day, e.g. stepping through backup up all your databases ( although that's probably a poor example ) My view is that if it works, is easy to understand and doesn't cause problems on the database/server then don't rewrite for the sake of rewriting.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 5, 2010 at 4:37 pm
colin.Leversuch-Roberts (2/4/2010)
I think that sometimes the desire to achieve a goal masks the requirement. I've seen many hours spent trying to replace cursor statements on pieces of code which run once a day, e.g. stepping through backup up all your databases ( although that's probably a poor example ) My view is that if it works, is easy to understand and doesn't cause problems on the database/server then don't rewrite for the sake of rewriting.
well said
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 7, 2010 at 4:11 pm
Well, I observe that there's a strange behaviour using this script on over than 100 tables. In fact SQL 2008 creates a #Worktable for the part "SELECT @tblSignature = @tblSignature + N'|' +[...]" which is 1.500.000 reads each times.
Does someone knows why?
--
Philippe RUELLO
Database Project Manager
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply