Post reply

Stored procedure performance tuning

  • June 30, 2011 at 2:04 am

    #240066

    Hello Friends,

    I have build a stored procedure with some parameters inwhich i have build 40 or 50 dynamic update statements that further executed by exec (@command) as follows:

    declare @sqlbulkupdate varchar(max)

    set @sqlbulyupdate=@sqlbulkupdate+"Update statement1"

    set @sqlbulyupdate=@sqlbulkupdate+"Update statement2"

    set @sqlbulyupdate=@sqlbulkupdate+"Update statement3"

    set @sqlbulyupdate=@sqlbulkupdate+"Update statement4"

    ....

    .....

    exec (@sqlbulkupdate)

    but when i execute these batch of update statement by exec statement it will run 3 or 4 minuts approx

    how to tuneup my stored procedure performance or how to optimize the performance of SP.

    plz help me and send some suggestion and send some sample code for execution of batch DML statements..

    Thanks in advance to spent your valuable time to read this post.. plz plz plz ..send a reply

  • June 30, 2011 at 2:17 am

    #1346365

    write here your full update statement

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • June 30, 2011 at 2:23 am

    #1346370

    update statements are just like

    set @sqlstatement=@sqlstatement+' '+'update TB_Class_result set ['+@subjectName+'_Class Test I]'+'=ts.[CL I] from TB_Class_result t join TB_RESULT_TERMI ts on ts.stu_id=t.stu_id and ts.subject_id='''+@subjectid+''' and ts.session='''+@session+''''

    set @sqlstatement=@sqlstatement+' '+'update TB_Class_result set ['+@subjectName+'_Class Test I]'+'=''CT I'' where stu_id=-2'

    set @sqlstatement=@sqlstatement+' '+'update TB_Class_result set ['+ @subjectName+'_Assessment I]'+'=ts.[CA I] from TB_Class_result t join TB_RESULT_TERMI ts on ts.stu_id=t.stu_id and ts.subject_id='''+@subjectid+''' and ts.session='''+@session+''''

    set @sqlstatement=@sqlstatement+' '+'update TB_Class_result set ['+ @subjectName+'_Assessment I]'+'=''CA I'' where stu_id=-2'

    --

    set @sqlstatement=@sqlstatement+' '+'update TB_Class_result set ['+ @subjectName+'_Class Test II]'+'=ts.[CL II] from TB_Class_result t join TB_RESULT_TERMI ts on ts.stu_id=t.stu_id and ts.subject_id='''+@subjectid+''' and ts.session='''+@session+''''

    set @sqlstatement=@sqlstatement+' '+'update TB_Class_result set ['+ @subjectName+'_Class Test II]'+'=''CT II'' where stu_id=-2'

    --

    set @sqlstatement=@sqlstatement+' '+'update TB_Class_result set ['+ @subjectName+'_Class Test II]'+'=upper('''+@subjectName+''') where stu_id=-1'

    set @sqlstatement=@sqlstatement+' '+'update TB_Class_result set ['+ @subjectName+'_Assessment II]'+'=ts.[CA II] from TB_Class_result t join TB_RESULT_TERMI ts on ts.stu_id=t.stu_id and ts.subject_id='''+@subjectid+''' and ts.session='''+@session+''''

    set @sqlstatement=@sqlstatement+' '+'update TB_Class_result set ['+ @subjectName+'_Assessment II]'+'=''CA II'' where stu_id=-2'

    --

    set @sqlstatement=@sqlstatement+' '+'update TB_Class_result set ['+ @subjectName+'_Summative I]'+'=ts.[SA I] from TB_Class_result t join TB_RESULT_TERMI ts on ts.stu_id=t.stu_id and ts.subject_id='''+@subjectid+''' and ts.session='''+@session+''''

    set @sqlstate

  • June 30, 2011 at 10:27 pm

    #1346851

    Hi Shubham,

    Can u check your exec plan? Are you seeing any scans, can you share it here?

    Regards - Yasub

  • July 1, 2011 at 1:18 am

    #1346891

    Please post table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/

    p.s. Do you know your code is vulnerable to SQL injection?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 1, 2011 at 6:29 am

    #1346986

    you must have indexes on the columns which those are using in WHERE clause and in joins

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • July 3, 2011 at 5:16 pm

    #1347588

    It generall looks like there are 2 different update statements being called. Run each type separately and see how long they take. we need to find out which one is causing the issue (guessing the one with the joins). And when I saw run them separately, I mean run them outside of the dynamic SQL and take a look at the execution plan to see what is going on. If you need to read up on execution plans, please see the following link:

    Execution plans

    On the update statement with the joins, make sure that stu_id and sub_id have indexes on the columns (if needed). This will help speed things up. If you are unfamiliar with indexes, I would suggest doing some reading up on it. I have posted a link below:

    Index Basics

    Both of those links should get you started in the right direction. Let us know how things go.

    Jason

    Webmaster at SQL Optimizations School

Viewing 7 posts - 1 through 6 (of 6 total)

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