Post reply

Calculating Insert Rate on a Database

  • June 22, 2006 at 4:27 pm

    #75001

    Hi Everyone,
     
     
    I have a situation here, there is process in which data from one database is being transferred onto another database. I have a situation whereby I need to calculate a way in which the transfer rate can be calculated.
     
    For example, how long it takes to transfer a thousand records ?
     
    I also have another question, If I need to know how many transfers  were completed before a specific time, eg how many records transferred before 3PM.
     
    I know I can use a cursor to do this, but I am trying to see if there is another efficient way of doing this ?
     
    Any solutions would be appreciated.
     
    Thanks
     
    Raymond Dean
    Kindest Regards,

    John Burchel (Trainee Developer)

  • June 22, 2006 at 6:41 pm

    #645417

    You could try using SQL Profiler and capture the individual statements and/or SP and their duration over a set period. Then import this data into a database and perform your analysis! If you know how many records were transferred in that time period, then you could easily determine the average transfer rate.

    Bear in mind that the transfer rate will be dependent on how busy the server was during the times it is executed. Therefore, capturing the data over a longer period of time will give you a better estimate of the average transfer rate.

  • June 23, 2006 at 12:38 am

    #645455

    Thanks very much with that idea, the problem is that the inserts would happen evry aftenoon, and this method has to bevery dynamic and automated, so it should not need any intervention.

    Also, would there be a way of me calling profiler evertime there is an insert on dynamically created tables, I think your idea would be more suited for capturing the information, by configuring profiler all the time. But thanks anyway

    I was thinking more like an insert trigger that would log the time it takes to execute every say 100 records.

    Would anyone agree with this method ?

     

    Thanks

     

    Raymond Dean

    Kindest Regards,

    John Burchel (Trainee Developer)

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

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