March 9, 2005 at 7:04 pm
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'
March 9, 2005 at 7:11 pm
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...
cl
Signature is NULL
March 9, 2005 at 7:21 pm
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.
March 10, 2005 at 4:45 pm
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
March 10, 2005 at 6:50 pm
select call_log_id, event_desc, event_cd , eventdt, getdate()
from Call_Log_Event_Archive
where datediff(dd,eventdt, getdate()) <= 10
March 8, 2006 at 5:34 am
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