Post reply

slow running program

  • February 10, 2009 at 8:15 am

    #127457

    Hi all,

    I'm new to sql server, I'm not sure how I can handle this problem. I'm researching on methods to trace the problem that could be causing performance slow down. The program runs from the client side application, Selects n records from a table and updates n records in that table. I used SQL Server Profiler to collect the trace, I had all the columns checked under performance in the trace file. It is a huge file, I'm able to see Showplan Text,Showplan All, Showplan Text,Showplan Statistics Profile,Showplan All for Query Compile, Performance Statistics in the trace. I'm not sure how I can proceed further in tracing the problem here.. Please give me your thoughts or suggestions.

    Thanks a lot for all your help.

  • February 10, 2009 at 8:38 am

    #941290

    Below are the sql queries ran from the program:

    SELECT oitm,co from risg where cls <>'Y' and co >= :v1 and co <= :v2 order by co ASC;

    OUTPUT:n records

    update risg set cls = :v1 , cls_dt = :v2 where oitm = :v3

    OUTPUT: updates the n records

    Table risg contains 430000 records, I ran the program to update 10999 records in the table, it took about 2 minutes. It is taking long time compared to before and in the oracle database for the same number of records it is taking much lesser time.

    It could be the application code that is causing the slowness.But I'm not sure how to determine that.

    Please help. Thank you

  • February 10, 2009 at 3:09 pm

    #941697

    We just faced another performance issue with 2 other programs using the same table risg. Could it be something to do with the indexes in the table?? Does it needs to be rebuild or the table needs to be organized??

    Please give me your thoughts/suggestions.

    Thanks a lot

  • February 10, 2009 at 10:10 pm

    #941797

    The table have the bulk of data when set the index , it will not work out some time . The table creating time you have to set the index.

    rename old table --> create new table --> set index -->transfer data from old to new

    set index

    ---------

    set primary key or

    "where condition" what are the column did used , those column should come to index.

    Note : if you create the more index it will affect the SQL server performance, index upto below 8 (40 k, 50 k data),no problem,

    when delete or update the data , SQL server back ground process take arrange the data based on index.

Viewing 4 posts - 1 through 3 (of 3 total)

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