SQL Server Performance

stored procedure performance

Discussion in 'T-SQL Performance Tuning for Developers' started by Chilibowl, Nov 1, 2007.

  1. Chilibowl New Member

    Hi,
    I am trying to improve the performance of a stored procedure in SQL Server 2000 by modifying 3 important queries within it, but I do not know much about database performance.
    In the procedure the 3 queries are in a format similar to this:
    INSERT INTO SomeTable
    SELECT Column1, Column2, Column3,
    Column4, Column5, Column6,
    Column7, Column8, Column9,
    Column10, Column11, Column12, Column13
    FROM Employee e (NOLOCK)
    INNER JOIN Table1
    ON ...
    INNER JOIN Table2
    ON ...
    INNER JOIN Table3
    ON ...
    INNER JOIN Table4
    ON ...
    INNER JOIN Table5
    ON ...
    INNER JOIN Table6
    ON ...
    WHERE Column3 = @SomeInitialParameter
    The SELECT statement just gets columns from many different tables and inserts them as a row into SomeTable. The problem is the amount of rows the SELECT statement returns. Right now it varies between 10 to 2000 rows, but in the future it could be many thousands returned.
    Could anyone suggest what I could do to improve this? Right now the procedure takes 11 seconds to execute, but with many thousands of rows it could take a bit longer. Someone suggested I use cursors but I tried that and it took a couple of minutes for the whole procedure to finish.

  2. FrankKalis Moderator

    I don't think you have given enough information to suggest something useful. So the variety of possible answer might lie between adding appropriate indices to slightly denormalize your schema (or rethink it).
  3. ranjitjain New Member

    You can hardly modify such type of simple select using joins on many tables query.
    As Frank said, if possible, can relook over denormalizing table design, else in SQL 2000 can have a look over index recommendations by Index tuning advisor.

Share This Page