Post reply

SQL performance problem

  • May 19, 2007 at 3:48 pm

    #178592

    Hi,

     

    I have a stored procedure witch counts some sort events in Audit table. This procedure is called from an asp.net application and is used to generate reports. One of these events is an error that can be solved during the process event(on the DB). If an error is processed means one error less. Since I want to report the number of errors (Not processed) on that day, I have to loop trough the gigantic audit table to see if this ever is processed. If not I have to report it as an error. Looping trough the audit table with simple sql statements is nightmare concerning the performance. Is it a way to get out of this problem? What would you suggest to do?    

  • May 19, 2007 at 4:09 pm

    #707658

    Add another column called PROCESSED then update the column when the it is processed...

    You can use GROUP BY clause to get the info instead of looping through...

     

    MohammedU
    Microsoft SQL Server MVP

  • May 21, 2007 at 2:17 am

    #707747

    Thanks for you reply MohammedUddin.

    I am not authorized to add columns in the audit table, but I will try to add the processed errors in a new table, using triggers. This trigger must listen if an error is processed.

     

  • May 22, 2007 at 11:03 am

    #708135

    Mohamed we really need significantly more information to help you here.  Table definition(s), indexes, constraints, sample data, rowcount.  I will state without that though that if you desire to check all previous data against each row that is attemted to be inserted into the table it WILL be slow, and will only get worse with data growth.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

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

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