SQLServerCentral / Discuss Content Posted by Leo Peysakhovich / Article Discussions / Article Discussions by Author / Performance Tips Part 1 / Latest Posts

RE: Performance Tips Part 1

André Cardoso — Wed, 08 Mar 2006 05:34:00 GMT

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

RE: Performance Tips Part 1

Leo Peysakhovich — Thu, 10 Mar 2005 18:50:00 GMT

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.

RE: Performance Tips Part 1

RD Francis — Thu, 10 Mar 2005 16:45:00 GMT

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.

RE: Performance Tips Part 1

Leo Peysakhovich — Wed, 09 Mar 2005 19:21:00 GMT

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.

RE: Performance Tips Part 1

Calvin Lawson — Wed, 09 Mar 2005 19:11:00 GMT

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...

RE: Performance Tips Part 1

Leo Peysakhovich — Wed, 09 Mar 2005 19:04:00 GMT

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'

RE: Performance Tips Part 1

Calvin Lawson — Wed, 09 Mar 2005 17:51:00 GMT

Actually...it is significantly different, per my optimizer it costs 2.5% of the total cost of both (although this includes creating the table variable, which I would HOPE you had in for testing purposes only).

I would agree that this is not significant if it's under a 100 loops or so (which is the case in your example), but it is significant with more loops. I might add, under a 100 loops there is no significant difference between a cursor and either while loop.

I certainly wasn't saying you should modify your existing code. But since your article was about optimization, I figured you'd be interested. Seems like you are a little defensive instead. Oh well...

RE: Performance Tips Part 1

Leo Peysakhovich — Wed, 09 Mar 2005 17:07:00 GMT

Possible that optimizer shows that the break is more effective but it is like 10 or 10.01 miles/gal. Is it different? For testing purpose on factory - yes. In real world ????? When I am working, I am trying to cut worst parts and advice developers to focus on important changes. This one I am not consider as important because there is no major time cut and it is not influence another processes.

RE: Performance Tips Part 1

Calvin Lawson — Wed, 09 Mar 2005 12:25:00 GMT

In the real world I have some issues with object renaming. I have seeing the cases where after renaming an object and creating another one with the same name some applications start erroring with message that it can't find an object (even I can see it and work with it in Query Analyzer). Personally, I do avoid renaming in production databases.

Per BOL..."Important After renaming stored procedures and views, flush the procedure cache to ensure all dependent stored procedures and views are recompiled."

Notice the "DBCC FREEPROCCACHE" at the end of the rename script.

On another topic, I noticed you didn't comment on the different while loop. What do you think about using a break instead of min and max variables? If you check the optimizer it's definitely more efficient.

RE: Performance Tips Part 1

Leo Peysakhovich — Wed, 09 Mar 2005 09:40:00 GMT

Thanks for the warm words. I do like good solutions and your's is good for the discussion. I do follow your's advise while I am doing quick and dirty tests. In the real world I have some issues with object renaming. I have seeing the cases where after renaming an object and creating another one with the same name some applications start erroring with message that it can't find an object (even I can see it and work with it in Query Analyzer). Personally, I do avoid renaming in production databases.

Second part will be soon. It is under review.

RE: Performance Tips Part 1

SW21_Bob — Wed, 09 Mar 2005 06:59:00 GMT

I liked your article, so I hope you don't think I was being criticle. You gave some good ideas about things that can effect performance. My thought on renaming tables was simply adding to the discussion, not trying to say there was a better way. It seemed reasonable that instead of moving 3,000,000 records, it would be more effecient to move 10,000 and renaming would allow that. Clearly, any real world system may have additional complexities such as you pointed out.

Again, it was a good article both in its content and in the discussion it created. I'm looking forward to part two.

RE: Performance Tips Part 1

Leo Peysakhovich — Tue, 08 Mar 2005 17:59:00 GMT

I do understand your point. But as an example I get criteria (not the best one) just to illustrate the point.

If you look my first performance article http://www.sqlservercentral.com/columnists/lPeysakhovich/overviewofperformance.asp)

you will see that I am using the idea you suggesting to populate a seed table with the PK values

RE: Performance Tips Part 1

Leo Peysakhovich — Tue, 08 Mar 2005 17:49:00 GMT

Yes, I can do it. Rename indexes, tables, databases, and so on.

But the main point of the article is to show that a simple change may have a big effect. And another point, that developer who is writing an archiving process have no permissions to rename objects. Plus this is only a small part of the process. Process consists with tonns of pages of code and this is the only one small part of the logic.

RE: Performance Tips Part 1

Calvin Lawson — Tue, 08 Mar 2005 16:30:00 GMT

You can rename indexes and other objects:

use pubs

if object_ID('test_rename_HIS') is not null drop table test_rename_HISif object_ID('test_rename_ARC') is not null drop table test_rename_ARC

create table test_rename_HIS (ID int Not Null constraint PK_HIS Primary Key, Name varchar(255))

create index I_HIS on test_rename_HIS (Name)

exec sp_rename @objname = 'test_rename_HIS' , @newname = 'test_rename_ARC' , @objtype = 'Object'

exec sp_rename @objname = 'PK_HIS' , @newname = 'PK_ARC' , @objtype = 'Object'

exec sp_rename @objname = 'test_rename_ARC.I_HIS' , @newname = 'I_ARC' , @objtype = 'Index'

DBCC FREEPROCCACHE

RE: Performance Tips Part 1

Leo Peysakhovich — Tue, 08 Mar 2005 16:05:00 GMT

You right if it is the simple table in air. In our case each table has the corresponding names for the PK and indexes. For example, table t_customer will have PK named as I_PK_T_Customer_10. Index may have a name I_IN_T_Customer_20. By renaming the table I will ruin the database standartization.

RE: Performance Tips Part 1

Leo Peysakhovich — Tue, 08 Mar 2005 16:01:00 GMT

Very nice decision. But in my case this is just a quick example.

This article I placed as a general guideline for the developers because I am tired with obvious performance glitches. Probably some parts can be done better. But my main point was to show that small thoughts can make a difference.

RE: Performance Tips Part 1

Leo Peysakhovich — Tue, 08 Mar 2005 15:55:00 GMT

Good solution as well. And achieve the same point.

Second comment. I am writing this article mainly for the application developers and/or report writers. Most of them have no idea what mean effect of concurrency & transaction isolation. But they need a general guide line to make better performance.

RE: Performance Tips Part 1

SW21_Bob — Tue, 08 Mar 2005 13:28:00 GMT

For number 3, rather than moving the data into the archive table, rename the history table to archive then move the relatively few records into a new history table. This should work OK since you said the tables have no defined relationships. Or does renaming a table do things internally that I don't know about that make this a bad idea?

RE: Performance Tips Part 1

Calvin Lawson — Tue, 08 Mar 2005 12:22:00 GMT

Pretty good article, with some nice tips. Regarding PW's note, it's a valid option, but no real performance increase over using variables.

Situation 3 looks dicey, though. I understand what you're trying to do, and if your "Where" clause is expensive it could perform better. What you really need to do in this case is populate a seed table with the PK values, then use this to move rows around. It will perform better than moving the whole row one extra time, especially if it's a big row.

Situation 4 is nice...I've been harping on the uselessness of fast forward cursors for a "while" (oops.... I usually use a different form, though; The performance is significantly better.

----------------------------------------------------------------------------------

declare @name sysname

While 1 = 1

begin

select top 1 @Name = name from master..sysdatabases where Name > isnull(@Name, '') order by Name

if @@RowCount = 0 Break

select @Name Name

end

RE: Performance Tips Part 1

PW-201837 — Tue, 08 Mar 2005 10:42:00 GMT

Situation #1

Use a Derived Table (Or a Common Table Expression [CTE] in SqlServer 2005) and lose the variables:

SELECT t1.col1, t1.col2, dt.col1, dt.col2 FROM Customer t1 CROSS JOIN ( SELECT TOP 1 col1, col2 FROM GeneralInfo t2 \) dtWHERE t1.status > 0

Situation #2

Discussion is incomplete without mentioning effect of concurrency & transaction isolation. Yes, splitting into smaller queries may reduce lock hold time and reduce contention, but what does that gain if the end result is *different* than 1 large statement, because other processes changed data between the steps of a broken out process ?

Performance Tips Part 1

Leo Peysakhovich — Fri, 25 Feb 2005 10:31:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lPeysakhovich/performancetipspart1.asp