Performance Tips Part 1

  • I am not defencive, but trying to be realistic. If I need over 200 -500 loops then I need to look an alternative to the loop solution and in most cases I was able to find it. For example, instead of writing a loop to get permission for all tables 

    grant SELECT on table1 to XYZ

    Grant SELECT on table2 to XYZ


    it can be achived by


    declare @cmd varchar(8000)

    set @cmd = ''

    select @cmd = @cmd + '

    grant SELECT on ' + name + ' on XYZ '  from sysobjects where type = 'U'

  • Definitely a set-based solution is always best, and you're right that many loops can be converted into set-based processing.

    That's not what we were talking about, though; we were talking about replacements for cursors when a loop is needed.  I always appreciate seeing a "while" loop instead of a cursor (and i appreciate seeing a set-based solution even more), as they are cleaner and (I think) easier to read.  I commend you on this, but I was also pointing out that using a "break" instead of checking variables is more efficient, especially when using "Select min, max" to set the loop control variables.

    I'm not trying to piss you off or anything...


    Signature is NULL

  • Let it be this way. Next time I will use your loop control when I can't awoid a big looping structure. Good solution. I am fine with all solutions as far as they are not completely off a practical use. I have seing many solutions that are good in theory but do not really help in reality. They are way off the practical use.

  • Talking more about situation three.

    First, just to be completely pedantic, step 4 in your example seems to have the table names reversed, so that instead of copying the latest data back to the Call_Log_Event table, you're copying data from the (empty) Call_Log_Event table to Call_Log_Event_Archive again. Of course, since no one else has mentioned this, maybe I'm badly misreading things.

    Second, in cases where there is a need to archive data, there is usually a need to do it more than once. Your process is a one-time solution to what will probably become a repeating problem.

    You could continue by inserting the current data from Call_Event_Log into Call_Event_Log_Archive as you've done in this instant. However, as the archive table grows larger and larger, I believe you will hit a point where searching for the records to be copied back, then removed will take much longer than simply deleting the records in the first place. It's possible that, with proper indexing, this wouldn't be significantly worse for some time, but I believe it would happen.

    Of course, if one of the concerns is how fast you can release Call_Event_Log for use, your method still might work well if the Call_Event_Log data was always written to a temp table instead of to the full Archive table. Restore the data from the temp table to Call_Event_Log first, commit, then write the data to be moved to Call_Event_Log_Archive, and drop the temp teble. This would almost certainly be slower than your solution overall (two writes of the archived data), but you can lock one table at a time, keeping things flowing as freely as possible. As you point out, the time when critical resources are locked can be far more important than the actual time it takes to complete an operation.

    R David Francis

  • Good catch.
    It should be
    insert into Call_Log_Event (call_log_id, event_desc, event_cd , eventdt, archivedt)

    select call_log_id, event_desc, event_cd , eventdt, getdate()

      from Call_Log_Event_Archive

      where datediff(dd,eventdt, getdate()) <= 10 

    I placed only an idea to show that many ways around to get better solution than the one (actually many people using) I described. Depend on the situation different decision may be taken. You are right.
  • In the archiving situation, I think that it is missing a transaction (or else you risk losing log events, between the insert into the archive and the truncate).

    I don't agree that it is a one time solution, because you normally archive by year, so every year you create a new table (if it's only for archiving, it's more manageable).

    André Cardoso

Viewing 6 posts - 16 through 20 (of 20 total)

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