Performance Tips Part 1
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lPeysakhovich/performancetipspart1.asp
-
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
\) dt
WHERE 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 ?
-
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
Signature is NULL
-
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?
Bob
SuccessWare Software -
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.
-
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.
-
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.
-
You can rename indexes and other objects:
use pubs
if object_ID('test_rename_HIS') is not null drop table test_rename_HIS
if 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))
go
create index I_HIS on test_rename_HIS (Name)
go
exec sp_rename
@objname = 'test_rename_HIS' ,
@newname = 'test_rename_ARC' ,
@objtype = 'Object'
go
exec sp_rename
@objname = 'PK_HIS' ,
@newname = 'PK_ARC' ,
@objtype = 'Object'
go
exec sp_rename
@objname = 'test_rename_ARC.I_HIS' ,
@newname = 'I_ARC' ,
@objtype = 'Index'
go
DBCC FREEPROCCACHE
Signature is NULL
-
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.
-
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
-
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.
Bob
SuccessWare Software -
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.
-
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.
Signature is NULL
-
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.
-
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...
cl
Signature is NULL
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply