Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Performance Tips Part 1 Expand / Collapse
Author
Message
Posted Friday, February 25, 2005 10:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:33 AM
Points: 138, Visits: 280
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lPeysakhovich/performancetipspart1.asp


Post #164150
Posted Tuesday, March 8, 2005 10:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, March 8, 2014 4:25 PM
Points: 1,415, Visits: 1,228

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 ?

 

 

Post #166310
Posted Tuesday, March 8, 2005 12:22 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 10:03 AM
Points: 577, Visits: 102

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
Post #166360
Posted Tuesday, March 8, 2005 1:28 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 2:05 PM
Points: 308, Visits: 55
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
Post #166380
Posted Tuesday, March 8, 2005 3:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:33 AM
Points: 138, Visits: 280

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.




Post #166412
Posted Tuesday, March 8, 2005 4:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:33 AM
Points: 138, Visits: 280

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.




Post #166413
Posted Tuesday, March 8, 2005 4:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:33 AM
Points: 138, Visits: 280
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.


Post #166415
Posted Tuesday, March 8, 2005 4:30 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 10:03 AM
Points: 577, Visits: 102

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
Post #166420
Posted Tuesday, March 8, 2005 5:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:33 AM
Points: 138, Visits: 280

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.




Post #166429
Posted Tuesday, March 8, 2005 5:59 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:33 AM
Points: 138, Visits: 280

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




Post #166430
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse