How to reduce the time


  • CREATE PROCEDURE [student ] ( @stufees money,
      @stuID integer

    UPDATE student
    SET fees= @stufees

    WHERE stu_ID = @stuID;

    this procedure takes more time and I like to how to increase the performance and reduce time.

    Wednesday, October 29, 2008 6:39 AM

All replies

  • Your SP is ok, but how many records is there in the "student" table? Is there any indexes on this table? Is there any update triggers?

    Wednesday, October 29, 2008 6:56 AM
  • we have 89287 records and and we have indexs also.

    Wednesday, October 29, 2008 8:55 AM
  • Look at the execution plan. Does it use the index on stu_ID? The table is not quite big. I doubt there could be triggers on the table that might be causing the delay.


    Wednesday, October 29, 2008 9:42 AM
  • How to trace the execution plan?
    Wednesday, October 29, 2008 10:42 AM
  • Here is an example:
    Code Snippet

    set showplan_text on; go select * from sys.tables

    or you can look at the graphical plan by selecting the toolbar or from the 'query' menu of SSMS
    Wednesday, October 29, 2008 10:45 AM
  • Upon viewing the execution plan results you will probably see index/table scan because of heavy index fragmentation or out of date statistics.  The update statement you provided should be seeking.  Trying rebuilding your indexes and make sure all stats are up to date.

    Wednesday, October 29, 2008 12:42 PM
  • from the execution plan how we can find??


    I am runing the applicaiton from vb and from sql profiler I am tracing it and how can I trace the execution for each process.

    Thursday, October 30, 2008 6:03 AM
  • run the query in SQL Server query analyzer or Management studio and generate the execution plan in TEXT format (using the command I gave earlier). Then post the execution plan so that we can have a look at it.


    Thursday, October 30, 2008 8:45 AM
  • As stated, 82K rows of data is not all that much, even if was doing a table scan. Do as was suggested, ensure you have index on the stu_id field. If you already do, try rebuilding your indexes, just for fun. Check your execution plan to ensure it's going against the index. Run the process again. It is fairly straight-forward, unless there is more to the update code that what's been presented so far.


    Thursday, October 30, 2008 8:26 PM