SQL Server Performance

how to reduce the execution time.

Discussion in 'SQL Server 2005 General Developer Questions' started by sureshk, Apr 25, 2008.

  1. sureshk New Member

    In my server there are two databases.
    i want to select record with some conditons from 1'st db and then insert that records in to the second db.
    but the select record contains near 2 lacs.
    the query i was written takes much more time (near 2-3 minutes). how can i reduce that..
    thanks in advance.
  2. Luis Martin Moderator

    The duration is in select or in insert?
    If in select, did you try DTA for some indexes?
  3. madhuottapalam New Member

    There are two actions here. One selecting the records from source table and the one is Inserting into target table. Select performance can be increased by having proper indexes. Probably Covering Index may increase the performance. ALso select only the columns required. Also use WITH NOLOCK inSelect statement.Select Column1,Column2,column3 FROM YourTable WITH (NOLOCK) Where Column4='Somevalue'in the above statement covering index will include Column1,column2,column3,column4 (ofcourse the most selective column should come first). WITH NOLOCK is depends upon the issolation level you want. Read about this HINT in BOL.How can you improve the insert performance? Its the opposite of the select, ie Remove/Disable all the indexes(if you have any) while inserting. Once the records are inserted recreate the index.ALso check the Execution plan for which step is really taking time.Madhu
  4. satya Moderator

    If this is an ongoing process then you could take help of SSIS package to perform such actions by limiting the number of rows to update/insert in order to take care of transaction log sizes.
    http://sqlserver-qa.net/blogs/t-sql/archive/2007/12/07/2877.aspx refers about blocking that might occur in this case, check it out.

Share This Page