Avoid cursor....

Question

Post reply

Avoid cursor....

NewBeeSQL

SSCrazy

Points: 2170
More actions
July 25, 2010 at 10:39 pm

#221649

I have a procedure which is executing the following code for every database inside a cusror.
SELECT Master,serverproperty('servername'),a.*, b.GenTableCode, d.FirstName, d.SurName , e.Firstname as ClientFirstname, e.Surname as ClientSurname, f.*, g.ReportTitle
FROM master..AuditTrail a
LEFT JOIN master..GenTable b ON a.TableNumber = b.TableNumber
LEFT JOIN master..GenUser c ON a.UserNumber = c.UserNumber
LEFT JOIN master..GenPerson d ON c.GenUserID = d.GenPersonID
LEFT JOIN master..Client e ON a.ClientID = e.ClientID
LEFT JOIN master..AuditTrailReportParameters f ON a.SequenceID = f.AuditSequenceID AND a.RowID = f.ReportRowID
LEFT JOIN master..GenReports g ON a.RowID = g.ReportID
WHERE b.GenTableCode = 'GenReports'
And a.ActionDateTime>'27 Nov 2004 00:00:00' And a.ActionDateTime <'3 Dec 2010 21:00:00' -- Amend accordingly
ORDER BY a.ActionDateTime
I want to avoid cursor and the above code should be executed for every database (including system databases)..
In the above example, this was executing for Master db..
any help would be highly appreciated..
TIA
-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

You can create a stored procedure for this code and use sp_MSforeachdb to execute it against each database.

Example:

EXEC SP_MSFOREACHDB @COMMAND1 ='SELECT DB_NAME(),DATABASEPROPERTYEX([?],'IsAutoClose''

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 2

Thanks for the reply. But this wont solve my work as my code is filtering databases in where clause while selecting the cursor..

declare CURSOR FOR SELECT [name]

FROM master..sysdatabases

WHERE [name] Like 'xyz_%' and [Name] NOT IN ('abcd', 'pqr')

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

Adiga One Orange Chip Points: 27284 More actions · Answer 3

It can be achieved using the same logic. Here is an example

EXEC SP_MSFOREACHDB @COMMAND1 ='use [?]

if db_name()like ''%temp%''

begin

exec sp_helpfile

end

'

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 4

No luck with your code..can you please generate the code which was mentioned in my first mail with your logic...

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

Adiga (7/25/2010)
You can create a stored procedure for this code and use sp_MSforeachdb to execute it against each database.
Example:
EXEC SP_MSFOREACHDB @COMMAND1 ='SELECT DB_NAME(),DATABASEPROPERTYEX([?],'IsAutoClose''

That doesn't actually avoid a cursor. MSForEachDB has a huge cursor buried in it's code.

--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)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 6

NewBeeSQL (7/25/2010)
No luck with your code..can you please generate the code which was mentioned in my first mail with your logic...

This is one of those places where a cursor isn't actually a bad thing.

--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)

jvanderberg SSCertifiable Points: 6762 More actions · Answer 7

You can't really apply set-based logic at the database level because they're not rows in a table, they are full databases. Even if you could, the engine would still have to run your code against each database individually. There would be no performance bonus to such a construct. SP_MSFOREACHDB is a great example.

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 8

Jeff / Jvanderberg...Thanks a lot for your explantions...

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 9

NewBeeSQL (7/26/2010)
Jeff / Jvanderberg...Thanks a lot for your explantions...

You bet. Thank you for the feedback.

As a side bar... "one" of the exceptions to what JVanderberg and I have been talking about is the use of synonymns, "snynonymn views", and partitioned views. It takes a bit of work to set them up (actually, not much if you're good with dynamic SQL) but they can and do simplify some rather complex code across databases. As with everything else, "It Depends".

--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)

jezman SSChasing Mays Points: 611 More actions · Answer 10

unless you have millions of dabases to loop through... what's wrong with a cursor?

It's always better to understand why cursors aren't optimal and then you can decide if it is appropriate to use them.

Chris Howarth-536003 SSCommitted Points: 1775 More actions · Answer 11

No need for a cursor:

DECLARE @SQL VARCHAR(MAX)

SET @SQL = ''

SELECT @SQL = @SQL + 'SELECT ''' + name + ''',serverproperty(''servername''),a.*, b.GenTableCode, d.FirstName, d.SurName , e.Firstname as ClientFirstname, e.Surname as ClientSurname, f.*, g.ReportTitle

FROM master..AuditTrail a

LEFT JOIN [' + name + ']..GenTable b ON a.TableNumber = b.TableNumber

LEFT JOIN [' + name + ']..GenUser c ON a.UserNumber = c.UserNumber

LEFT JOIN [' + name + ']..GenPerson d ON c.GenUserID = d.GenPersonID

LEFT JOIN [' + name + ']..Client e ON a.ClientID = e.ClientID

LEFT JOIN [' + name + ']..AuditTrailReportParameters f ON a.SequenceID = f.AuditSequenceID AND a.RowID = f.ReportRowID

LEFT JOIN [' + name + ']..GenReports g ON a.RowID = g.ReportID

WHERE b.GenTableCode = ''GenReports''

And a.ActionDateTime>''27 Nov 2004 00:00:00'' And a.ActionDateTime <''3 Dec 2010 21:00:00'' -- Amend accordingly

ORDER BY a.ActionDateTime;

'

FROM sys.databases

WHERE state = 0

ORDER BY name

EXEC(@SQL)

Chris

jvanderberg SSCertifiable Points: 6762 More actions · Answer 12

That is a really creative way of avoiding using a cursor, but is it really getting around any of the problems with cursors? The primary problems with cursors are locking and the fact that the engine can't really optimize a cursor. If you're performing operations on a per-database level, the first problem doesn't apply and the second one is unavoidable. You're not going to cause lock contention on a system view, and the system's going to have to come up with a query plan for each iteration no matter what. Your example, while highly creative, just generates a bunch of SQL scripts, each of which needs its own query plan. You haven't really gotten around anything, you've just generated an overly-complicated query with no real performance benefit. Don't get me wrong: that's some great outside-of-the-box thinking, I just think it's a little misplaced. You'd be far better off optimizing something that can benefit more from it.

--J