January 7, 2009 at 9:49 am
Again... the reason I did what I did was I couldn't come up with a way to run
DBCC CHECKPRIMARYFILE (N''' + @filename + ''',2)
Which gets the actual name of the database from the database itself.
I had found in my testing that 90% of the databases followed our naming conventions, but the other 10% (400) did not. So I needed to probe the mdf file to find out what the actual database name was. New databases all follow the naming convention. The older databases are used by our customers as well though and we had a very small time window to do this (approx 5 hours, including copying almost 2 TB of Databases to their new homes).
Tracing how SSMS 2K5 does it showed me DBCC CHECKPRIMARYFILE, which only appears to work on an mdf that isn't attached to a SQL server, it's also not documented in BOL (yet it's what MS uses).
Since the DBCC command returns a small result set for a single file at a time, I couldn't come up with a good way to do it other than to run DBCC CHECKPRIMARYFILE for each database mdf file, one at a time.
All told, the attach routine took less than 10 minutes to attach 1000 databases per instance to each instance. The rest of the time was spent copying mdf files to their new homes.
May 27, 2011 at 2:54 am
In all my years of writing database code, the only times I have ever opted to use cursors is when executing a parameterised statement across different servers or databases.
I wouldn't like to try and count the number of times that I have stared into space for minutes at a time trying to work out the required complexities of the joins needed to accomodate a particular task instead of resorting to rbar, but isn't this the point of what DBA's and Database programmers do? We are specialised because the platform requires a specialised skill set and if we don't use it then we are just wasting what the RDBMS can do and, quite frankly, being lazy.
For people not trained in using set based theory it is understandable to use a rbar approach because this is all that they know. But if you do know it then please use it!
Rant over 😉
May 27, 2011 at 2:57 am
Jeff Moden (1/2/2009)
battelofhalfwits (1/1/2009)
Wow...Two things
1) Never use Cursors! Looping through a temp table takes less overhead than a Cursor; especially when you are working on a 24/7/365 server where you really need to watch your resources.
Not true... if you use Forward Only, Read Only (or sometimes Static), the cursor is just as "effecient" as the WHILE loop... and make no doubt about it, neither is effecient. If you use a WHILE loop, you've not done any better than using a cursor. 😉
I use a couple of cursors in my code...
One is for retrieving messages from a service broker queue (multiple messages per conversation handle, retrieved into a temp table and then the cursor processes each retrieved message one by one as they have to be - each message will take a different process path depending on what it contains):
-- Declare cursor
DECLARE MessageCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT ConversationHandle
,MessageTypeName
,MessageBody
FROM @ReceiveTable
ORDER BY QueuingOrder;
-- and a little later in the code...
WAITFOR(
RECEIVE
[queuing_order]
,[conversation_handle]
,[message_type_name]
,CAST([message_body] AS NVARCHAR(MAX))
FROM [EventDetailsTargetQueue]
INTO @ReceiveTable
), TIMEOUT 5000;
-- and process using the cursor --
The other is in a utility procedure that returns record counts and a health check (are there sufficient records) for all the tables involved in a loading process - in this case a table contains the names and minimum acceptable counts for all the loading tables, I use a very simple cursor (SELECT TableName, MinimumRecordCount FROM HealthCheckTables) to retrieve these and for each one I construct a SQL statement to retrieve the count from the table and execute the statement, storing the results in a temp table. This is then used to report the counts, and to throw an error where insufficient records are found in any of the tables (this procedure is used in the load process to halt processing before the live data is affected when we haven't loaded a healthy amount of data)
I do loop through a temp table in another component of my loading process to achieve almost the same thing (disabling/enabling all foreign keys on those same loading tables), driven by the same source table and again constructing a SQL statement for each key and executing it.
i.e. this gets all the related foreign keys:
SELECT OBJECT_NAME(s.constid) AS ConstraintName
,'[' + sch.[name] + '].[' + OBJECT_NAME(s.fkeyid) + ']' AS TableName
INTO #Const_Table
FROM sysforeignkeys s
INNER JOIN HealthCheckTables h
ON h.TableName = OBJECT_NAME (s.rkeyid)
INNER JOIN sys.tables t
ON t.[name] = OBJECT_NAME(s.fkeyid)
INNER JOIN sys.schemas sch
ON sch.[schema_id] = t.[schema_id]
...and for each one I have to construct an "ALTER TABLE abc {NOCHECK|WITH CHECK CHECK} CONSTRAINT xyz" statement and execute it.
These last two, both could use a cursor, or both could use a loop, but essentially they both have to be REBAR processes as far as I can tell, and similarly for the service broker processing.
Is there a way to achieve any of these things in a set-based fashion? I've not found one... though I'm by no means an expert on these matters. In the latter cases the number of tables involved is small enough that the REBAR makes no odds (around 20 or so tables to count or switch keys on or off for), but the principle matters, and if I can do this a better way I'd like to know before I am faced with a much bigger similar situation!
Dave.
May 27, 2011 at 3:30 am
dave.farmer (5/27/2011)
Is there a way to achieve any of these things in a set-based fashion? I've not found one... though I'm by no means an expert on these matters. In the latter cases the number of tables involved is small enough that the REBAR makes no odds (around 20 or so tables to count or switch keys on or off for), but the principle matters, and if I can do this a better way I'd like to know before I am faced with a much bigger similar situation!
I think the frothing at the mouth against cursors does need some qualification.
T-SQL can fall into two general categories; Process (DDL) and Data (DML) based.
Process based could involve using a process table to determine which stored procedures to call or perhaps working through a file list stored in a table to dynamically bulk load files. Or, as you mentioned, going through a series of tables and modifying the indexes/keys.
These process based operations benefit from cursors. They could be done with out cursors but any other method method wouldn't be as elegant (IMO).
Data based operations, i.e. performing inserts, updates etc... should not be done using a cursor
May 27, 2011 at 3:58 am
Anyone who knows what happens when a phonenumber is NULL?
For 2 reasons I would add an extra filter
1) don't select what you don't need as soon as possible, less records passing your filtering will also speed up your process
2) working with NULL-values can give surprising effects, so you have to be aware and check ISNULL-function (or as I did here filter them out)
DECLARE @AllPhones VARCHAR(1000)
SET @AllPhones = ''
SELECT @AllPhones = @AllPhones
+ CASE WHEN P.ListThisNumber = 1 THEN P.PhoneNumber ELSE '***********'END
+ ' & '
FROM dbo.tblPhone P
WHERE codUser = 1
AND P.PhoneNumber IS NOT NULL
SELECT @AllPhonesGO
May 27, 2011 at 6:46 am
A huge and common mistake I have seen is when a trigger is written from the perspective that only one row is affected.
For example:
Select @someVar = SomeValue
From inserted
If the statement above is in a trigger with out some sort of looping around it and the insert/update statement affects more than one row then you only select one value from one of the many rows and skipped all of the others. SQL Server won't error out if you try to jam more than one value into the variable. (Oracle would have thrown an error)
May 27, 2011 at 8:13 am
Whenever possible, I use set-based solutions. But it has occurred in the past where I simply couldn't think one.
For example, this is my answer to a question on this site (no-one was able to produce a set-based alternative) : -
CREATE TABLE #SPWithVarchar
(SPName VARCHAR(MAX), --Display the stored procedure name
LinePos INT, --Display the line number to where the varchar was found.
TextPart VARCHAR(MAX)) --Display the code to where the varchar was found
--First temporary table, required to get the names of the stored-procedures
CREATE TABLE #TestTemp (Code VARCHAR(MAX), sp_name VARCHAR(MAX))
INSERT INTO #TestTemp(Code, sp_name)
--sp_helptext "hides" an ugly while loop, so a set-based version would be
--preferred. I can't think of a way of getting the line numbers without it
--unfortunately.
SELECT 'EXEC sp_helptext ''' + o.name + '''', '''' + o.name + ''''
FROM sysobjects o
WHERE o.xtype = 'P' AND o.category = 0
GROUP BY o.name
DECLARE @code VARCHAR(MAX), @sp_name VARCHAR(MAX)
DECLARE crap CURSOR FOR
SELECT code, sp_name FROM #TestTemp
OPEN crap
FETCH NEXT FROM crap INTO @code, @sp_name
WHILE @@FETCH_STATUS = 0
BEGIN
--Second temporary table, holds the results of "sp_helptext" for each individual
--stored-procedure. Uses an identity column to determine the line number of
--the stored-procedure.
--We then search this table before inserting into your #SPWithVarchar table.
CREATE TABLE #TestTemp2 (Number INT IDENTITY,Line VARCHAR(MAX), sp_name VARCHAR(MAX))
SET @code = 'INSERT INTO #TestTemp2(Line) ' + @code
--Insert results of "sp_help" into second temp table
EXECUTE ('' + @code + '')
--Updates second temporary table with the stored-procedure name.
UPDATE #TestTemp2 SET sp_name = @sp_name
--Inserts the lines from #TestTemp2 where "varchar" appears into #SPWithVarchar.
INSERT INTO #SPWithVarchar(spname, linepos, TextPart)
SELECT sp_name, number, line FROM #TestTemp2 WHERE line LIKE '%varchar%'
--Drop #TestTemp2 so it's ready to be used for the next stored-procedure.
DROP TABLE #TestTemp2
FETCH NEXT FROM crap INTO @code, @sp_name
END
--Clean up
CLOSE crap
DEALLOCATE crap
DROP TABLE #TestTemp
--Display the results
SELECT * FROM #SPWithVarchar
--DROP TABLE #SPWithVarchar
May 27, 2011 at 8:32 am
As a complete beginner I found the article badly mistitled. It was not Cursors for beginners but why not to use cursors for beginners.
A good starting point in an article for beginners is to assume that your reader knows nothing about the subject. This article singularly failed to do this.
May 27, 2011 at 8:46 am
something like this would get you started on a set based solution:
with sp_lines(ObjectId, SPName, CRPos, [LineNo])
as (
select
o.id
,o.name
,t.n as CRPos
,ROW_NUMBER() over (partition by o.name order by t.n) as [LineNo]
from sys.syscomments c
inner join sys.sysobjects o on c.id = o.id
left join Tally t on substring(c.text, t.n, 1) = char(10)
where c.text like '%varchar%'
and o.xtype = 'P'
)
select
current_line.SPName
,substring(c.text, isnull(previous_line.CRPos+1, 1), current_line.CRPos - isnull(previous_line.CRPos+1, 1)) as LineText
--,isnull(previous_line.CRPos+1, 1) as LineStart
--,current_line.CRPos - isnull(previous_line.CRPos+1, 1) as Length
from sp_lines current_line
left join sp_lines previous_line on current_line.ObjectId = previous_line.ObjectId
and current_line.[LineNo] - 1 = previous_line.[LineNo]
inner join sys.syscomments c on current_line.ObjectId = c.id
where substring(c.text, isnull(previous_line.CRPos+1, 1), current_line.CRPos - isnull(previous_line.CRPos+1, 1)) like '%varchar%'
May 27, 2011 at 9:30 am
I thought the article was well presented. I especially enjoyed the responses.
May 27, 2011 at 9:37 am
battelofhalfwits (1/1/2009)
Wow...Two things
1) Never use Cursors! Looping through a temp table takes less overhead than a Cursor; especially when you are working on a 24/7/365 server where you really need to watch your resources.
I want to echo this. I have encountered situations where there was no way around doing row-by-row inserts (database conversion / merger where a stored proc needed to be run to generate values for each line) but when it was first written with cursors our conversion process was going to take about 3 days to run. After replacing the cursors with temp tables and while loops the process was cut down to a few hours. I've never seen a situation where I could not replace a cursor and benefit.
D.
May 27, 2011 at 10:08 am
rothco (5/27/2011)
As a complete beginner I found the article badly mistitled. It was not Cursors for beginners but why not to use cursors for beginners.A good starting point in an article for beginners is to assume that your reader knows nothing about the subject. This article singularly failed to do this.
About the title you are wrigth. Although the purpose off the article seemed explained in the article to me (a non-beginner), avoiding the beginners-reflex for cursors-usage to become a bad habit when there is no need to. (By the way, I started reading this article because I wanted to know if the article also would warn newbiees about the disadvantages off cursors. It did that indeed :-P)
As a experienced user reading the discussion I thought, how the hell this level off discussion comes after a 'beginner-article'. Don't think many beginners will make it to this comment.
May 27, 2011 at 10:59 am
rothco (5/27/2011)
As a complete beginner I found the article badly mistitled. It was not Cursors for beginners but why not to use cursors for beginners.A good starting point in an article for beginners is to assume that your reader knows nothing about the subject. This article singularly failed to do this.
I have to agree with you. I was kind of surprised when I saw the title myself, because I knew the discussion would be cursor bashing, but wasn't expecting the article itself to be.
That said, I have learned a lot here on replacements for cursors and other inefficient methods, and have implemented what I can understand and have the ability to. I will use Tally Tables for split functions as an example.
The reasons that I still use cursors are a tossup between using 2000 and that the majority of the time it is one time scripting
May 27, 2011 at 12:20 pm
Two points:
First off the author, and a number of posts have suggested that "other SQL DBMS systems" are optimized for cursors and that it's only SQL Server that is not. Absolutely, positively, and categorically, this is FALSE! Oracle and DB2 are not magic, they don't somehow process cursors in such a way as to make them fine to use. A set based solution will outperform cursors in those platforms in almost direct proportion to how much it would outperform the same thing in SQL. Oracle in particular does their user community a GREAT disservice by not clarifying this issue.
Second, as much as I dislike cursors, and take pride in my ability to avoid them, there are some (very rare) cases where they are in fact the best solution. Categorically stating that they are always bad or always avoidable is not helpful.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
May 27, 2011 at 3:21 pm
We've had to use cursors in generating notification emails using sp_send_dbmail. We have a list of 5 account execs that get notified when their data is loaded and ready for them to review. I'd love a set based version of sp_send_dbmail.
We also have a batch loading routine that uses a cursor to loop through a rules table in it to write a series of SQL statements that basic unpivot data from columns into records. The SQL statements themselves are set based inserts. It probably could be rewritten to use Unpivot along with some ugly case statements. But no one sees the point since it runs at most daily and isn't even a blip on our resources.
Thanks,
Mwise
Viewing 15 posts - 61 through 75 (of 87 total)
You must be logged in to reply to this topic. Login to reply