Avoid cursor....

  • 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]

  • 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

  • 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]

  • 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

  • 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]

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

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

  • 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.

  • 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]

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

  • 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.

  • 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

  • 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

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

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