SQL Server Performance

Set logical store procedure

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by condeba92, May 18, 2007.

  1. condeba92 New Member

    I wanna know if somebody have experience working with T-SQL store procedure, transforming a store procedure builded with procedure logical to a store procedure set logical.
    I need make a code like this.
    Thank's
  2. satya Moderator

    What you mean by logical, do you have any example in your cae?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. condeba92 New Member

    Transform a cursor to a temporal table for example, an then both behaviors must be equal. It's just a example, have a lot code SQL with this kind of problem.
    I want to take of a behavior T-SQL to pl-SQL, in other words.
    Can you help me ?
  4. FrankKalis Moderator

    How should we say if we can help, when we don't see your code? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />But you are not talking about Oracle's PL/SQL, right?<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  5. satya Moderator

    Cursors & Temp table method are different to each other, both have pros & cons.
    http://www.eggheadcafe.com/articles/20010823.asp

    My experience is to work with temp. tables & joins clauses (mostly) by default. I tend to incline towards cursors to solve problems not solvable by other means. That said, I don't seem to have encountered terribly poor performance using cursors. When you say 'better' you haven't really identified your criteria, whether it's performance or ease of coding.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  6. condeba92 New Member

    I have a store procedure with a cursor like this:

    DECLARE cur_acciones CURSOR FOR
    SELECT
    PER_ID,
    ...

    i can change it this way:

    CREATE TABLE #CUR_ACCIONES
    (
    ID1INTPRIMARY KEYIDENTITY(1,1),
    PER_IDINT,
    ...

    and then execute a INSERT instruction:

    INSERT #CUR_ACCIONES
    SELECT
    PER.per_id,
    ...

    It's all. Then, i can use this temporal table like i did use the cursor.
    Both of them have the same behavor, but the second structure is more efficient.

    I have other structure more complex with the same problem.
    It was inderstood ?, i need this kind of solutions.

    Anybody of yours can help me converting store procedure with procedural logical store procedure to set logical store procedure?
  7. satya Moderator

    Are you looking for performance or ease of porting data ?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  8. condeba92 New Member

    I'm looking for perfomance, it's more important, but i don't wanna loose logical behaivor.
  9. satya Moderator

    Have you looked at the execution plan of the queries you are comparing?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  10. condeba92 New Member

    Yes, i don't have any doubt about it. I did prove this in a lot of situations.
    This kind of work really improve query.
    Can you help with this kind of work ?
  11. MohammedU New Member

    Post the procedure/tsql code to get the correct recommendation...

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  12. Adriaan New Member

    One example of cursor abuse is when people start writing T-SQL as if it was a programming language like VB, usually lacking knowing of basic query syntax like JOIN:

    DECLARE @i INT, @var VARCHAR(10), @lookup @VARCHAR(100)

    DECLARE abc CURSOR
    FOR
    SELECT int_col, var_col FROM source_table
    OPEN abc
    FETCH NEXT FROM abc INTO @i, @var

    WHILE @@FETCH_STATUS = 0
    BEGIN

    SELECT @lookup = lookup_col FROM other_table WHERE key_col = @var

    INSERT INTO target_table (int_col, other_col)
    VALUES (@i, @lookup)

    FETCH NEXT FROM abc INTO @i, @var

    END

    CLOSE abc
    DEALLOCATE abc

    *****************************

    The set-based version of that is:

    INSERT INTO target_table (int_col, other_col)
    SELECT s.int_col, o.lookup_col
    FROM source_table AS s
    INNER JOIN other_table AS o
    ON s.var_col = o.key_col

    Not only is the code more compact, there will be just one insert action, instead of one for each row in the source table.

    If you get paid for each line of code that you write, re-negotiate so that they pay you extra for code that performs well.
  13. condeba92 New Member

    oK, this is what i do, when i got a code with procedural logical or cursor-base code:

    --------------------------------------------------------------------
    CREATE TABLE #ABC
    (
    int_colintPRIMARY KEY,
    var_col varchar(10),
    lookup varchar(100)

    )
    CREATE INDEX IDX_ABC_VAR_COL ON #ABC(var_col)
    -------------------------------------------------------------------
    INSERT #ABC
    SELECT int_col, var_col,'' AS 'lookup' FROM source_table
    -------------------------------------------------------------------
    UPDATE #ABC
    SET lookup = B.lookup_col
    FROM #ABC A, other_table B
    WHERE
    A.var_col=B.key_col
    -------------------------------------------------------------------
    INSERT target_table (int_col, other_col)
    SELECT int_col, lookup FROM #ABC
    -------------------------------------------------------------------
    DROP TABLE #ABC
    -------------------------------------------------------------------

    Perhaps, this kind of programming code working better with a more complex code, but you now what i mean.

    Maybe, we can see this example in another point of view like this:
    INSERT target_table (int_col, other_col)
    SELECT A.int_col, B.lookup
    FROM source_table A, other_table B
    WHERE
    A.var_col=B.key_col

    But, Adriaan really understood what i was talking about.
    And now you have a code set-based logical (i hope no mistake).
    So, you can improve it now with Query analizer.
    Are you Adriaan working with this kind of problem ? I have a lot of code about this kind of cursor-based code.

    Thank's all of yours!
  14. Adriaan New Member

    Correct, you do not have to use a temp table.<br /><br />Your final statement is fine, although you are strongly encouraged to use JOIN syntax instead of faking it in a WHERE clause. Use the WHERE clause for filtering. This will keep things neatly organized, and easier to understand for any DBA who ever takes a look at those procedures.<br /><br />I think it was your phrase "logical procedure" that got people confused.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]

Share This Page