Can I avoid a cursor

  • I have the following SQL which retrieves a set of translated text through a generic translation stored procedure, but I cannot see a way of translating multiple rows without the use of the cursor.

    Does anyone know a way ?

    DECLARE @Description nvarchar(50)

    DECLARE @PopupText nvarchar(300)

    DECLARE @tablename nvarchar(50)

    DECLARE @keyname nvarchar(50)

    DECLARE @languageid int

    DECLARE @keyvalue int

    DECLARE @usr nvarchar(50)

    SET @usr='brownjohn'

    CREATE TABLE #MyApps (MyDesc nvarchar(50) , MyPop nvarchar(300))

    DECLARE app_cursor CURSOR

    FOR

    SELECT ApplicationID,LanguageID FROM EDB.dbo.tblSecUserAccess WHERE

    EmployeeID in (SELECT EmployeeID FROM EDB.dbo.tblEmployee WHERE NTLogon=@usr)

    OPEN app_cursor

    FETCH NEXT FROM app_cursor

    INTO @keyvalue, @languageid

    WHILE @@FETCH_STATUS = 0

    BEGIN

    EXEC usp_translatetext @tablename = 'tblApplication',

    @keyname = 'ApplicationID',

    @languageID = @languageID,

    @entityID = @keyvalue,

    @Description = @Description OUTPUT,

    @PopupText = @PopupText OUTPUT

    INSERT INTO #MyApps VALUES (@Description, @PopupText)

    -- Get the next application.

    FETCH NEXT FROM app_cursor

    INTO @keyvalue, @languageid

    END

    CLOSE app_cursor

    DEALLOCATE app_cursor

    SELECT * FROM #MyApps

    GO

  • Could you move the logic in the translation stored procedure to a user defined function?

    Regards,

    Andy Jones

    .

  • The logic is in an sp because it is performing a parameterised lookup where the tablename, keyfield name,keyfield value are passed in and the text values are passed out. The sp is building a sql string and then executing the string via the sp_executesql statement

  • At times cursors are the best way to do things. You might also think about using a table variable that has an identity column and then doing a while loop looking up the data using the identity column value. I've done that several times and had performance improvements. The only way to really know if it is faster for that scenario is to do it both ways and test to see what way is faster.




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • Yes! you can avoid cursors most of the time. I found this "cursor-less cursor" script on one of the SQL Sites, either this one, Swynk or another, a few months ago and have used it a few times since. It works great! My only regret is I have lost the original and so I can't remember TO WHOM it should be credited. If the author sees this and would step forward to claim credit, that would be cool, too.

    Anyway, here is (more or less) what your same cursor would look like using this technique. I may have dropped a variable or something but this is the general gist of it.

    /********** begin T-SQL ******************/

    /*Create temporary table using SELECT INTO statement*/

    DECLARE @Description nvarchar(50)

    DECLARE @PopupText nvarchar(300)

    DECLARE @tablename nvarchar(50)

    DECLARE @keyname nvarchar(50)

    SET @I = 1

    SET NOCOUNT ON

    SELECT IDENTITY(int, 1,1) AS tmpID,

    x.ApplicationID, x.LanguageID

    INTO #TempTable

    FROM

    (SELECT ApplicationID,LanguageID

    FROM EDB.dbo.tblSecUserAccess

    WHERE EmployeeID in

    (SELECT EmployeeID

    FROM EDB.dbo.tblEmployee

    WHERE NTLogon=@usr)) x

    SET @Row_Count = (SELECT COUNT(*) FROM #TempTable)

    WHILE @I <= @Row_Count

    BEGIN

    DECLARE @languageid int

    DECLARE @keyvalue int

    /*Simulate fetch statement*/

    SELECT ApplicationID, LanguageID

    FROM #TempTable

    WHERE tmpID = @I

    SET @languageid = #TempTable.LanguageID

    SET keyvalue = #TempTable.ApplicationID

    EXEC usp_translatetext @tablename = 'tblApplication',

    @keyname = 'ApplicationID',

    @languageID = @languageID,

    @entityID = @keyvalue,

    @Description = @Description OUTPUT,

    @PopupText = @PopupText OUTPUT

    /*Show the result*/

    SELECT @tmpID, ApplicationID, LanguageID

    /* set the counter */

    SET @I = @I + 1

    END

    print 'Total rows: ' + Cast(@Row_Count as varchar(8))

    DROP TABLE #TempTable

    SET NOCOUNT OFF

    /************ end T-SQL ******************/

    G. Milner

  • Which is worse? I suggest that the use of the temporary table, which is being created with a SELECT INTO with all of it's inherent problems with locking, is actually going to cause you more trouble than the cursor...

  • While I have done the while loop on the identity column as I stated before, I never use Select Into. I always manually create the table or declare the table ahead of time at the top of the SP. I also try to make sure to specify if the column is nullable or not.

    When I converted from 6.5 to 7.0 a couple years ago I found that using Select Into almost always caused a recompile of the sp. In fact I also found that having the temp table created at the top of the sp usually helped lessen the number of recompiles and so I have tried to make sure to put all declarations at the top of sp's ever since.

    Gary Johnson

    DBA

    Sr. DB Engineer




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • Unfortunately I cannot convert this to a UDF because the SELECT statement is parameterised and is run using the SP_EXECUTESQL command.

    This cannot be run from within a function.

    I cannot return a table variable from within the stored procedure.

  • If the cursor is not that big you could always use a memory table....

    Works just like a real table but is faster than a cursor...

    Declare @Table Table

    ( id int,

    text varchar(100)

    )

    will create a memtable you can loop a mem table by it's id....

    The nice thing is the mem table will only exist in the sproc, so when the sproc finishes the table is gone as well.....

  • You could well find that having a permanently defined table to hold the working data set will be far faster because you can put indexes on it.

    Variable of table type look good, but just look at a query plan with a big stored procedure that uses them. See all those Table Scans (=big performance hit)?

    The other problem could be memory impact - a few rows in a table variable is no big deal, but a few thousand long rows is going to eat memory.

  • That are you doing inside of the usp_translatetext? You are calling it 1 line at a time, thus requiring a cursor like structure. Could usp_translatetext perhaps be changed to translate multiple lines of text at one time?

  • The procedure performs the following

    CREATE PROCEDURE usp_translatetext

    @tablename sysname,

    @keyname sysname,

    @languageID int,

    @entityID int,

    @Description nvarchar(50) OUTPUT,

    @PopupText nvarchar(300) OUTPUT

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @sqlstr nvarchar(2000)

    SET @sqlstr = 'if exists (SELECT [Description], PopupText FROM EDB.dbo.' + @tablename + 'Translation WHERE ' + @keyname + '= @entityID AND LanguageID = @languageID)

    SELECT @Description = [Description], @PopupText = PopupText FROM EDB.dbo.' + @tablename + 'Translation WHERE ' + @keyname + '= @entityID AND LanguageID = @languageID

    else

    SELECT @Description = [Description], @PopupText = PopupText FROM EDB.dbo.' + @tablename + ' WHERE ' + @keyname + '= @entityID'

    EXEC sp_executesql @sqlstr, N'@languageID int, @entityID int, @Description varchar(50) OUTPUT, @PopupText varchar(300) OUTPUT', @languageID, @entityID, @Description OUTPUT, @PopupText OUTPUT

    SET NOCOUNT OFF

    END

  • Do a SELECT with a WHILE loop.

    PlanetJam Media Group


    PlanetJam Media Group

  • While cursors are best avoided in most situations, there are processes that benefit from a cusor. It is one of many tools that you can use, so don't be compelled to never use a cursor.

    In the past, we have used a While loop similar to that outlined by gmilner above. But if the SARG field is not indexed, you could end up with a table scan everytime it looped.

    Perhaps the most common use of a curosr is to avoid any concurrency issues when you are doing a large update of a table. With a cursor, locks are kept to a minimum. Better a cursor, than to lock out users.

    Always focus on the business problem first, technology second.

    Andrew


    What's the business problem you're trying to solve?

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

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