Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2007
    Posts
    4

    Unanswered: Performance of stored procedure

    Hi,
    I have an insert query written in a Stored procedure, that has a select statement for getting the records from 4 different tables and inserting.
    The select query has almost 15 columns in the groupby part, b'coz of which the insert takes much longer time. Is there any way to increase the performance of this insert?. Like, can we use a cursor for the select and then loop thr the cursor to insert the records. pls give me suggestions abt how we change this insert statement.
    Note : the select statement contains tables that has high volume of data.

    thanx in advance..

    example :

    INSERT INTO table1
    (
    column1,
    column2,
    column3
    column4,
    column5,
    ..........
    )
    SELECT column1,
    column2,
    column3,
    column4,
    column5,
    ...........

    FROM table2,
    table3,
    table4,
    .........
    WHERE ...............
    .................
    ...................

    GROUP BY column1,
    column2,
    column3,
    column4,
    column5,
    ..........

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    It is impossible to tell what the problem might be with the information you give.

    The slow part is most likely the SELECT query. You need to check the query plan used by this query to see if you are maybe missing an index, or if there are any other issues.

    You should start by running the SELECT query with SET SHOWPLAN ON and SET STATISTICS IO ON to see what it does. This may immediately show some obvious issues - and if not some further digging may be necessary.

    Michael

  3. #3
    Join Date
    Feb 2007
    Posts
    4
    hi michael,
    Thanx for ur reply maan. But wat I smell is the problem would be b'coz of the group by part in the select query. Can we put the select in a cursor and then loop it and insert into the table. Will this way increases the performance atleast a bit. ???

  4. #4
    Join Date
    Jul 2006
    Posts
    115
    normally, cursor is the last choice as it performs much slower than insert/update/delete directly. Except, long insertion time in table1 will "block" other users query in table1.

    Normally, if your query contains "where" condition, sybase will extract the data into tempdb based on "where" condition and then "grouping" the result to insert into table1.

    As mpepper said, we dont know what you coded in "where" condition. so pls take mpeper's suggestion to check ur query execution plan to see whether ur query optimized or not.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •