Performance Tuning UPDATE Statement
Jul 20, 2005
Below is a simple UPDATE that I have to perform on a table that has
about 2.5 million rows (about 4 million in production) This query
runs for an enourmous amount of time (over 1 hour). Both the
ChangerRoleID and the ChangerID are indexed (not unique). Is there
any way to performance tune this?
Controlling the physical drive of the log file isn't possible at our
client sites (we don't have control) and the recovery model needs to
be set to "Full".
UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID WHERE
ChangerRoleID IS NULL
Any Help would be greatly appreciated!
View 2 Replies
ADVERTISEMENT
Jul 20, 2005
hiFor an unavoidable reason, I have to use row-by-row processing(update) on a temporary table to update a history table every day.I have around 60,000 records in temporary table and about 2 million inthe history table.Could any one please suggest different methods to imporve the runtimeof the query?Would highly appreciate!
View 9 Replies
View Related
May 26, 2004
I'm updating the name data in a large user database with the following UPDATE statement. The staging table was bulk loaded from a flat file and contains 10 million records. The production table (Recipients) contains 15 million records. This worked correctly but this single update statement took an entire ten hours to run which is way too long. While it was running the server was clearly 100% disk bound. CPU activity was near nothing. We've just upgraded RAM from 1GB to 2GB but we expect data sizes to grow significantly and we can't keep adding RAM. Absolutely nothing else is running on this server. Any ideas how I can optimize this?
UPDATE Recipients
SET [First] = Stages.[First]
, [Last] = Stages.[Last]
FROM
Stages
INNER JOIN Recipients ON
(Stages.UserName = Recipients.UserName
AND Stages.DomainID = Recipients.DomainID)
WHERE
(CASE WHEN Stages.[First] IS NULL THEN 1 ELSE 0 END
+ CASE WHEN Stages.[Last] IS NULL THEN 1 ELSE 0 END)
<=
(CASE WHEN Recipients.[First] IS NULL THEN 1 ELSE 0 END
+ CASE WHEN Recipients.[Last] IS NULL THEN 1 ELSE 0 END)
Text execution plan. I've made small annotations with the % information from the graphical execution plan:
|--Clustered Index Update(OBJECT:([Recipients].[dbo].[Recipients].[PK_Recipients]), SET:([Recipients].[First]=[Stages].[First], [Recipients].[Last]=[Stages].[Last]))
|--Top(ROWCOUNT est 0)
|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
14% |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([Stages].[DomainID], [Stages].[UserName])=([Recipients].[DomainID], [Recipients].[UserName]), RESIDUAL:(([Recipients].[UserName]=[Stages].[UserName] AND [Recipients].[DomainID]=[Stages].[Domain
25% |--Clustered Index Scan(OBJECT:([Recipients].[dbo].[Stages].[IX_Stages]), ORDERED FORWARD)
61% |--Clustered Index Scan(OBJECT:([Recipients].[dbo].[Recipients].[PK_Recipients]), ORDERED FORWARD)
Everything I've heard on the subject suggests you change the index scans to index seeks. How do I do this?
Any other tuning advice is greatly appreciated.
Here are the exact statements I used to create the tables:
CREATE TABLE Recipients (
ID INT IDENTITY (1, 1) NOT NULL,
UserName VARCHAR (50) NOT NULL,
DomainID INT NOT NULL,
First VARCHAR (24) NULL,
Last VARCHAR (24) NULL,
StreetAddress VARCHAR (32) NULL,
City VARCHAR (24) NULL,
State VARCHAR (16) NULL,
Postal VARCHAR (10) NULL,
SourceID INT NULL,
CONSTRAINT PK_Recipients PRIMARY KEY CLUSTERED (DomainID, UserName)
)
CREATE TABLE Stages (
ID INT NULL,
UserName VARCHAR(50) NOT NULL,
DomainID INT NULL,
Domain VARCHAR(50) NOT NULL,
First VARCHAR(24) NULL,
Last VARCHAR(24) NULL,
StreetAddress VARCHAR(32) NULL,
City VARCHAR(24) NULL,
State VARCHAR(24) NULL,
Postal VARCHAR(10) NULL
)
CREATE CLUSTERED INDEX IX_Stages ON Stages (DomainID, UserName)
View 11 Replies
View Related
May 8, 2006
Hi,I have an UPDATE statement which runs very slow (about 1-2 minutes) inWeb as well as in Query Analyzer. Very plain UPDATE statement; usesindexes, etc. I though the reason might be the table being unorganizedbecause of the row expansions due to updating an existing column valuewith a larger value. Therefore, I ran a maintanenance plan whichreorganizes data and rebuilds indexes. But, it seems that there is noimprovement.Any thought or advise will be greatly appreciated.Sincerely,Pelin Bali.
View 1 Replies
View Related
Oct 16, 2013
I need to figure out the correct update statement syntax for the following integration.
I have a "Performance Table" which i insert weekly performance numbers into for each store. The table is constructed w/ columns such as Store, Weekenddate, Sales, Refunds, #ofPatients
In a "Averages Table" i have every weekenddate for each store populated. So 52 Weeks for 10 stores = 520 Rows of Store numbers & WeekendDates.
What i would like to do is run a loop or update statement which would update the store average for each weekendate based on the last 13 weeks.
This is my query
update performancestore_avgs set SalesAvg =
(select sum(SalesHit)/Count(Store) from performance_store where performance_store.weekenddate >= performancestore_avgs.weekenddate-84 and performancestore_Avgs.store = performance_store.store)
The update statement runs but the averages are completely wrong.
View 3 Replies
View Related
Jul 9, 2001
Hey all,
I am interested in finding out if anyone out there has experience with extremely high-performance SQL Server applications. The I/O needs of my database server are growing very quickly, and I am on the verge of launching a major upgrade project.
We have done all the standard tuning tasks: proper indexing, stored procedure tuning, etc... and are running on good small-server scale hardware ( dual PIII 700s, 1G RAM, but no RAID). The only path I can see to achieving higher performance are:
- lots of RAID, perhaps on a SAN.
- server upgrade, maybe 4 proc? I've been looking at RAIDZONEs and Netfinity's
- data partitioning ( I REALLY want to avoid this if I can! )
What do you do when you need Major Enterprise scale database performance from SQL Server? I've found lots of resources for Oracle and DB2, but I can't find many case studies for serious SQL Server installations.
Help!
-Dave
View 1 Replies
View Related
Jul 18, 2000
Hi,
does the upgradition SQL Server 6.5 to 7.0 will simply solve some problems which we are facing currently like ODBC errors Insert failed and update failed and also supporting more users ?
We have Access front end to SQL Server backend, so do we need to touch code in front end for optimizations ?
Can any pls guide me on this
Thanks
View 3 Replies
View Related
Nov 15, 1999
Hi, i am working on sql server 6.5 version.actually this is developed just one year back. but now the system is almost dead(low performance).i think the reasons r database design,networking,hardware etc.is it correct. and how to rectify these errors. i am suggesting that upgradation is the best option.so pl give the suggestions asap.
Thanx
Janreddy
View 1 Replies
View Related
Feb 27, 2001
Hi,
Anyone know any articles on Performance Tuning on the web? I'm trying to monitor one of my production boxes and don't know which counters to use.
Thanks!
Joe R.
View 4 Replies
View Related
Feb 13, 2007
Hi All,
What are some of the things i can do to improve query performance if the querey performance is realatively slow today compared to yesterday's performance:
Here are some of things i looked at:
-updating statistics
-checking the execution plan
-DBCC showcontig
View 5 Replies
View Related
May 17, 2007
What all possible ways there have been to maximizing performance of database?
View 4 Replies
View Related
Aug 23, 2007
how to increase performance through management studio
except making indexes
View 4 Replies
View Related
Mar 7, 2008
Hi Guys I need someone to assist me in having full understanding of what performance turning is. My Challenge is interpreting the System Monitor Graph of Performance tools.
I needed to know what does the value on vertical axis represent, while there is non on the horizontal axis rather I have Last, Average, Minimum, Maximum and Duration; Please What does all this value stands for, I indeed observe that when I click on any of the counters selected the value changes, therefore kindly assist me so that I can make meaning of this. Apart from all this please assist me with any material that can explain performance tuning to my maximum benefit, thanks in anticipation.
Pls Check below sample of the file is attached
View 1 Replies
View Related
Jun 11, 2008
Hi All,
I am new to this forum, would like to know about performance tuning methods in sql and which is the best site to read about it?
Thanks
View 5 Replies
View Related
Mar 13, 2007
Hi experts,
I've run sql profiler with %processor time counter .it showed a large value of 75
so please give me steps to tune the long running query with high cpu utilisation.
thanks in advance
View 20 Replies
View Related
Aug 23, 2007
i just wanna know how to reduce the performance of the query
can any one pls help me in the gaining the performance of query
SELECT
tblBankruptcyInfo.MasterID,
tblBankruptcyInfo.bk_Case_Number
INTO #ActiveBK
FROM FNFBSDataMart.dbo.tblReferral tblReferral WITH (NOLOCK)
INNER JOIN FNFBSDataMart.dbo.tblBankruptcyInfo tblBankruptcyInfo WITH (NOLOCK)
ON tblReferral.RefID = tblBankruptcyInfo.RefID
AND tblReferral.CloseDate IS NULL
INNER JOIN FNFBSDataMart.dbo.tblSuperClientFile tblSuperClientFile WITH (NOLOCK)
ON tblReferral.ClientFileID = tblSuperClientFile.ClientFileID
AND tblSuperClientFile.SuperClientVendorID IN (1816,125,127,1706,766,1820,137,141,144,145,1593,1808,146,990,1745,149,1215,1854,1867)
GROUP BY
tblBankruptcyInfo.MasterID,
tblBankruptcyInfo.bk_Case_Number
View 6 Replies
View Related
Nov 19, 2007
Hi all ,
I am doing performance tuning in sql
I have a query which it gives result in 70000 rows and time is taken 7 mints .
But i want one query is not more than 50000 row
my query is :-
SELECT
QPDMADM.LAR_OMEGA_TRANS_SUM.SOURCE_TRANSACTION_ID,
QPDMADM.LAR_OMEGA_TRANS_SUM.ORDER_DATE,
QPDMADM.LAR_OMEGA_TRANS_SUM.SHIP_DATE,
QPDMADM.LAR_OMEGA_TRANS_SUM.FISCAL_PERIOD,
QPDMADM.LAR_OMEGA_TRANS_SUM.DISCOUNT_AGREEMENT_ID,
QPDMADM.LAR_OMEGA_TRANS_SUM.PRODUCT_ID_07,
QPDMADM.LAR_OMEGA_TRANS_SUM.CUSTOMER_ID_14,
QPDMADM.LAR_OMEGA_TRANS_SUM.ISO_COUNTRY_CODE2,
QPDMADM.LAR_OMEGA_TRANS_SUM.ACCOUNT_TYPE_CODE,
QPDMADM.LAR_OMEGA_TRANS_SUM.END_USER_CUSTOMER_ID,
sum(QPDMADM.LAR_OMEGA_TRANS_SUM.NIA_AMT),
QPDMADM.LAR_OMEGA_TRANS_SUM.INDUSTRY_CLUSTER_CODE,
QPDMADM.LAR_OMEGA_TRANS_SUM.SOURCE_SYSTEM_ID,
QPDMADM.LAR_OMEGA_TRANS_SUM.LOAD_DATE
FROM
QPDMADM.LAR_OMEGA_TRANS_SUM
WHERE
(
QPDMADM.LAR_OMEGA_TRANS_SUM.FISCAL_PERIOD = '200712'
)
GROUP BY
QPDMADM.LAR_OMEGA_TRANS_SUM.SOURCE_TRANSACTION_ID,
QPDMADM.LAR_OMEGA_TRANS_SUM.ORDER_DATE,
QPDMADM.LAR_OMEGA_TRANS_SUM.SHIP_DATE,
QPDMADM.LAR_OMEGA_TRANS_SUM.FISCAL_PERIOD,
QPDMADM.LAR_OMEGA_TRANS_SUM.DISCOUNT_AGREEMENT_ID,
QPDMADM.LAR_OMEGA_TRANS_SUM.PRODUCT_ID_07,
QPDMADM.LAR_OMEGA_TRANS_SUM.CUSTOMER_ID_14,
QPDMADM.LAR_OMEGA_TRANS_SUM.ISO_COUNTRY_CODE2,
QPDMADM.LAR_OMEGA_TRANS_SUM.ACCOUNT_TYPE_CODE,
QPDMADM.LAR_OMEGA_TRANS_SUM.END_USER_CUSTOMER_ID,
QPDMADM.LAR_OMEGA_TRANS_SUM.INDUSTRY_CLUSTER_CODE,
QPDMADM.LAR_OMEGA_TRANS_SUM.SOURCE_SYSTEM_ID,
QPDMADM.LAR_OMEGA_TRANS_SUM.LOAD_DATE
Note:- FISCAL_PERIOD is index only
Thank & Regards,
Anil
View 2 Replies
View Related
Feb 7, 2008
Hi,
Can some one please send me the Sql Server 2005 Performance Tuning artilce links?
Thanks
View 1 Replies
View Related
Apr 18, 2000
I have a problem that is more complex than the one that follows, but if someone can answer this question,
I believe I can take it from there in solving my real problem.
Why does:
create table #bbb (c int)
set nocount on
declare @a int
select @a = 1
while (@a < 5000)
begin
select @a = @a + 1
insert into #bbb values (@a)
end
run in about 2 seconds, while:
create table bbb (c int)
set nocount on
declare @a int
select @a = 1
while (@a < 5000)
begin
select @a = @a + 1
insert into bbb values (@a)
end
runs in about 20 seconds? (The only difference is that one uses a temp. table.)
What could I have configured incorrectly to cause this behavior?
Thanks in advance!
Palmer
View 6 Replies
View Related
Oct 5, 1999
Hi !
What are some basic tips and hints for better performance running SQL 7.0 ?
Thanx
View 1 Replies
View Related
Jun 13, 2000
Anybody have a suggestion on text books or other materials that deal with performance tuning in SQL 7.0. From what I have seen SQL 7.0 is far diffrent than 6.5. I'm specificaly interestd in information about query plans, profiler and what a table spool does.
View 1 Replies
View Related
Aug 27, 2014
I have a query which retrieves data from 4-5 tables. To restrict the acess directly to the tables, views have been created on all these tables. These views are just select * from the tables. Two of these 5 tables have 700 Million and 8 Million rows respectively. And all the tables are having indexes. My issue here is that my query on views take three times more then the duration it takes to retrive data directly from tables. e.g. To retrieve 1 Miliion rows, it takes just 7-10 minutes on tables but on views it takes more than 30 minutes. When I check the query plan for both the options, I can see that indexes are being picked up but still the views are very slow.
Creating indexes on views is not feasible option for me as it requires DDL changes and so much testing efforts.
View 14 Replies
View Related
Feb 18, 2004
Hi, I am new to database admin. Actually, I really have no experience what so ever. My boss has asked me to do some tests on our sql server to find bottlenecks or whatever is causing our server to respond so slowly.
Any help would be, ugh helpful.
I am loking for tools or information that will help troubleshoot any problems with the ms sql server 2k. I have tried sql profiler, but I found myself lost, not knowing what to look for.
Thank you
View 4 Replies
View Related
Mar 16, 2004
Hello everyone:
Can anyone list the general performance tuning methods? Thanks a lot.
ZYT
View 1 Replies
View Related
Oct 22, 2007
I'm using the Database Engine Tuning Advisor to do some performance evaluation on my database. I have one particular table that will potentially have a couple million rows. but this may not occur for a few months to a year from now.
when i run the advisor with 1.5 million rows, it recommends that i add an index across all columns in this table (covering index) for an estimated 91% improvement. when i run it with 1500 rows in it, it recommends that i add an index on 2 key columns for an 8% improvement.
how should i reconcile this? can i have both and what does that do to performance?
View 17 Replies
View Related
May 5, 2008
Hi,
I have a stored procedure, I really need some one to help me for performance.
This stored procedure will generate dynamic sql and then paging depending on the @orderbystatement
@orderbystatement could be one column or multiple columns.
Join fields have indexes but execution plan still have hash match on Register table because a lot of duplicate records on
the columns of CalendarAcademicYearId and Worker.
The bottleneck is execution for dynamic sql statement with paging functionality. because all statement after set rowcount 0 only has 10 rows.
This performance task took me a few days and I still didn't get a good idea. Any tip will be appreciated.
Thanks.
Code Snippet
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_MyProc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_MyProc]
GO
CREATE PROCEDURE [dbo].[usp_MyProc]
@Dob DATETIME = NULL,
@CtrlWorkerNo NVARCHAR(20) = NULL,
@Factory NVARCHAR(MAX) = NULL,
@Position VARCHAR(25) = NULL,
@Gender NVARCHAR(10) = NULL,
@Year uniqueidentifier = NULL,
@FamilyName NVARCHAR(50) = NULL,
@GivenNames NVARCHAR(50) = NULL,
@FirstRow INT = NULL,
@MaxRows INT = NULL,
@IsWorker INT = NULL,
@IsAcStaff INT = NULL,
@Order NVARCHAR(255) = 'FamName , FirstName , DOB',
@RegisterStatus NVARCHAR(255) = NULL,
@PersonGuid VARCHAR(36) = NULL,
@IncludeStarted BIT = 0,
@IncludeHistorical BIT = 0,
@IncludeNoRegisters BIT = 0
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #t
(Id INT Identity(1,1),EntityId uniqueidentifier)
SELECT @Factory = REPLACE(@Factory,',',''',''')
SELECT @RegisterStatus = REPLACE(@RegisterStatus,',',''',''')
DECLARE @RegisterYears INT
SET @RegisterYears = NULL
IF (@Factory IS NOT NULL )
OR (@Position IS NOT NULL)
OR (@Year IS NOT NULL)
OR (@RegisterStatus IS NOT NULL)
OR (@IsWorker = 1)
BEGIN
SELECT @RegisterYears = (SELECT DATEPART(year, getDate()))
DECLARE @RegisterYearsHistorical INT
SET @RegisterYearsHistorical = (SELECT HistoricalYears FROM Organisation)
IF (@IncludeHistorical = 0 AND @RegisterYearsHistorical IS NOT NULL AND @IsWorker = 1)
BEGIN
SET @RegisterYears = @RegisterYears - @RegisterYearsHistorical
END
ELSE IF (@IncludeHistorical = 1 OR @IsWorker IS NULL OR @IsWorker = 0)
BEGIN
SET @RegisterYears = NULL
END
END
DECLARE @OrderByStatement NVARCHAR(255)
SET @OrderByStatement = @Order
DECLARE @FactoryJoin nvarchar(1000)
SET @FactoryJoin = ''
IF (PATINDEX('%School%', @Order) > 0 OR @Factory IS NOT NULL)
BEGIN
SET @FactoryJoin = 'LEFT OUTER JOIN dbo.Factory AS Factory ON Worker.School = Factory.Code '
SET @OrderByStatement = REPLACE(@OrderByStatement, 'School', 'Factory.Code')
END
DECLARE @AddressJoin nvarchar(1000)
SET @AddressJoin = ''
IF (PATINDEX('%Suburb%', @Order) > 0)
BEGIN
SET @AddressJoin = 'LEFT OUTER JOIN dbo.hjEntityAddress EntityAddress ON Worker.EntityId=EntityAddress.EntityId AND(EntityAddress.AddressType=''Local'' ) ' +
'LEFT OUTER JOIN dbo.hjAddress [Address] ON EntityAddress.AddressId=[Address].AddressId '
SET @OrderByStatement = REPLACE(@OrderByStatement, 'Suburb', '[Address].Suburb')
END
DECLARE @MainContactJoin nvarchar(1000)
SET @MainContactJoin = ''
IF (PATINDEX('%MainContact%', @Order) > 0)
BEGIN
IF (PATINDEX('%DESC%', @Order) > 0)
BEGIN
SET @Order = REPLACE(@Order, ',MainContact', ' DESC,MainContact')
SET @OrderByStatement = @Order
END
SET @MainContactJoin = 'LEFT OUTER JOIN dbo.hjRelationship Related ON Person.EntityId = Related.EntityID1 AND Related.MainContact=1 AND Related.Active = 1 ' +
'LEFT OUTER JOIN dbo.hjPerson MainContact ON Related.EntityID2 = MainContact.EntityID '
SET @OrderByStatement = REPLACE(@OrderByStatement, 'MainContact', 'MainContact.')
END
DECLARE @IncNoRegister NVARCHAR(MAX)
SET @IncNoRegister = ''
IF (@IncludeNoRegisters = 0)
BEGIN
SET @IncNoRegister = '
INNER JOIN dbo.Register ON dbo.hjWorker.WorkerNo = dbo.Register.Worker
INNER JOIN dbo.YearTable ON dbo.YearTable.CalendarAcademicYearId = dbo.Register.
CalendarAcademicYearId
WHERE (dbo.Register.Factory IN ('''+ISNULL(@Factory, '')+'''))
AND ((dbo.Register.[CalendarAcademicYearId] = @CalendarAcademicYearId_) OR
(@CalendarAcademicYearId_ IS NULL))
AND ((dbo.Register.Position = @Position_) OR (@Position_ IS NULL))
AND ((DATEPART(yy, dbo.YearTable.StartDate) >= @RegisterYears_) OR (@RegisterYears_ IS
NULL) OR (dbo.YearTable.StartDate IS NULL))
AND ((dbo.Register.RegisterStatus IN ('''+ISNULL(@RegisterStatus, '')+''')) OR (@RegisterStatus_
IS NULL))'
END
ELSE
BEGIN
SET @IncNoRegister = '
LEFT JOIN dbo.Register ON dbo.hjWorker.WorkerNo = dbo.Register.Worker
LEFT JOIN dbo.YearTable ON dbo.YearTable.CalendarAcademicYearId = dbo.Register.
CalendarAcademicYearId
WHERE (dbo.Register.RegisterID IS NULL)
OR
(
((dbo.Register.Factory IN ('''+ISNULL(@Factory, '')+''')) OR (@Factory_ IS NULL) )
AND ((dbo.Register.[CalendarAcademicYearId] = @CalendarAcademicYearId_) OR
(@CalendarAcademicYearId_ IS NULL))
AND ((dbo.Register.Position = @Position_) OR (@Position_ IS NULL))
AND ((DATEPART(yy, dbo.YearTable.StartDate) >= @RegisterYears_) OR (@RegisterYears_ IS
NULL) OR (dbo.YearTable.StartDate IS NULL))
AND ((dbo.Register.RegisterStatus IN ('''+ISNULL(@RegisterStatus, '')+''')) OR (@RegisterStatus_
IS NULL))
)
'
END
DECLARE @StartedClause NVARCHAR(100)
SELECT @StartedClause = ''
IF @IncludeStarted = 0
SELECT @StartedClause = ' (Person.StartedDate IS NULL ) AND '
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = N'
set rowcount ' + convert(varchar,@FirstRow + @MaxRows -1) + '
INSERT INTO #t
SELECT Entity.EntityId
FROM dbo.hjEntity Entity
INNER JOIN dbo.hjPerson Person ON Entity.EntityId=Person.EntityId
LEFT OUTER JOIN dbo.hjWorker Worker ON Worker.EntityId=Entity.EntityId '
+ @FactoryJoin
+ @MainContactJoin
+ @AddressJoin
+ '
WHERE Entity.EntityID IN
(
SELECT dbo.hjEntity.EntityID
FROM dbo.hjEntity
LEFT OUTER JOIN dbo.hjWorker ON dbo.hjWorker.EntityId=dbo.hjEntity.EntityId
' + @IncNoRegister + '
)
AND
(' + @StartedClause + '((Person.IsWorker=@IsWorker_ )OR (@IsWorker_ IS NULL))
AND((Person.IsAcStaff=@IsAcStaff_) OR (@IsAcStaff_ IS NULL))
AND((Person.FamName like @FamilyName_) OR (@FamilyName_ IS NULL) )
AND((Person.FirstName like @GivenNames_) OR (Person.PrefName LIKE @GivenNames_) OR
(Person.SecName LIKE @GivenNames_) OR (@GivenNames_ IS NULL) )
AND((Person.DOB LIKE @Dob_) OR (@Dob_ IS NULL) )
AND((Worker.CtrlWorkerNo like @CtrlWorkerNo_) OR (@CtrlWorkerNo_ IS NULL) )
AND((Person.Gender LIKE @Gender_) OR (@Gender_ IS NULL) )
AND((Person.EntityId != @PersonGuid_) OR (@PersonGuid_ IS NULL) )
) ORDER BY ' + @OrderByStatement
print @sql
EXEC sp_executesql @sql,N'@Factory_ NVARCHAR(10),@CalendarAcademicYearId_ uniqueidentifier,@RegisterStatus_ VARCHAR(50),@IsWorker_ INT,@IsAcStaff_ INT,@FamilyName_ NVARCHAR(50),@GivenNames_ NVARCHAR(50),@Dob_ DATETIME,@CtrlWorkerNo_ NVARCHAR(20),@Gender_ NVARCHAR(10),@PersonGuid_ VARCHAR(36),@FirstRow_ INT, @MaxRows_ INT, @RegisterYears_ INT, @Position_ VARCHAR(25)
',@Dob_=@Dob,@CtrlWorkerNo_=@CtrlWorkerNo,@Factory_=@Factory,@Gender_=@Gender,@CalendarAcademicYearId_=@Year,@RegisterStatus_=@RegisterStatus,@FamilyName_=@FamilyName,@GivenNames_=@GivenNames,@IsWorker_=@IsWorker,@IsAcStaff_=@IsAcStaff,@PersonGuid_=@PersonGuid,@FirstRow_=@FirstRow,@MaxRows_=@MaxRows,@RegisterYears_=@RegisterYears,@Position_=@Position
set rowcount 0
SELECT
Entity.EntityId,
Person.Title,
Person.FamName,
Person.FirstName,
Person.SecName,
Person.PrefName,
Person.DOB,
Person.Gender,
Person.EmploymentType,
Person.HighestSchoolLevel,
Person.HighestQualificationLevel,
Worker.School,
Factory.Name as SchoolName,
Worker.CtrlWorkerNo,
[Address].StreetNumber,
[Address].Street1,
[Address].Street2,
[Address].Suburb,
[Address].City,
[Address].State,
[Address].Postcode,
[Address].Country,
RelPerson.FamName as MainContactFamName,
RelPerson.FirstName as MainContactFirstName,
RelPerson.SecName as MainContactSecName,
RelPerson.PrefName as MainContactPrefName,
RelPerson.Title as MainContactTitle,
(SELECT COUNT(dbo.hjContactMethod.ContactMethodID) FROM dbo.hjContactMethod WHERE Worker.EntityId=dbo.hjContactMethod.EntityId AND(dbo.hjContactMethod.Priority='1' )) as ContactMethods,
Person.StartedDate
FROM dbo.hjEntity Entity
INNER JOIN #t t on Entity.EntityId = t.EntityId
INNER JOIN dbo.hjPerson Person ON Entity.EntityId=Person.EntityId
LEFT OUTER JOIN dbo.hjWorker Worker ON Worker.EntityId=Entity.EntityId
LEFT OUTER JOIN dbo.hjRelationship Relationship ON Worker.EntityId=Relationship.EntityId1 AND(Relationship.MainContact=1 )
LEFT OUTER JOIN dbo.hjEntity RelEntity ON Relationship.EntityId2=RelEntity.EntityId
LEFT OUTER JOIN dbo.hjPerson RelPerson ON RelEntity.EntityId=RelPerson.EntityId
LEFT OUTER JOIN dbo.hjEntityAddress EntityAddress ON Entity.EntityId=EntityAddress.EntityId AND(EntityAddress.AddressType='Local' )
LEFT OUTER JOIN dbo.hjAddress [Address] ON EntityAddress.AddressId=[Address].AddressId
LEFT OUTER JOIN dbo.Factory ON Factory.Code = Worker.School
WHERE (t.Id BETWEEN @FirstRow AND (@FirstRow + @MaxRows -1))
Order by t.Id
END
View 4 Replies
View Related
Oct 17, 2006
Which is the best third party tool for SQL Server 2005 Performance Tuning/Optimization.
We purchased the Toad for SQL Server from Quest sofwater but it works only with dbo schemas....So if ur users were in schema xyz then it would not recognize it because they develop the tool for 2005 based on SQL Server 2000 where the schema ownesrhip is tied to the users.
View 1 Replies
View Related
Dec 8, 2006
I have built a solution which runs for two hours on a server with 4CPU 2GHz each and 2GB of RAM on windows 2000 server (CPU utilization almost 70% and almost out of RAM). I moved the two source databases and the solution to a new box runing 8 xeon's at 3GHz each and 16GB of RAM running widows 2003 server 32bit and it still runs for 2 hours (CPU utilization 10% and ample RAM left).
I was expecting it to run much faster. So I started exploring the performance tuning features in SSIS and started tweaking the following:
Control Flow:
DefaultBufferMaxRows: Based on row size and buffer size, calculated the max rows.
DefaultBufferSize: Set this to max 100MB
DataFlow Destination:
Rows Per Batch: Set this to equal to the numbe of rows expected from the source.
Maximum Insert Commit Size: Set this to zero since memory was not an issue
I took the recommendations from other threads on similar issues here including the excellent recommendations at http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx but now the job is running for 6 hours.
Can anyone explain what I am doing wrong here? I have tried each of the above one by one and all together. No matter what combination I try it does not work any faster and both source and destination database are on the same server. Even selects from the same database also slowed down from 10 minutes to one hour.
Any assistance is appreciated, I need to get this job run in an hour.
Thanks!
- Philips.
View 7 Replies
View Related
Jun 9, 2008
Hi everybody,
i would be thankful if anybody posts some references of the web sites
which relates to SQL Server Performance tuning.
Thanks in Advance
Suresh Kumar Goudampally
View 2 Replies
View Related
Jan 9, 2008
hi
how should i monitor performjance of stored procedure and sql statements. i want to know that how much cpu time a query or stored procedure is taking??
r there any system table which give these information
please give suggestion ASAP
thanx
Maneesh
View 1 Replies
View Related
Jul 20, 2005
I have an SP that is big, huge, 700-800 lines.I am not an expert but I need to figure out every possible way thatI can improve the performance speed of this SP.In the next couple of weeks I will work on preparing SQL statementsthat will create the tables, insert sample record and run the SP.I would hope people will look at my SP and give me any hints on howI can better write the SP.In the meantime, after looking at the SP briefly, my first observations are:1- use SET NOCOUNT ON2- avoid using UNION statements3- use WITH (NOLOCK) with each SELECT statement4- avoid using NESTED Select statements5- use #temp tables6- avoid renaming tables in SELECT statements, for example SELECT * FROMtblClients CAm i correct to consider the above 6 points as valid things in terms ofcausingperformance problems?I would appreciate any comments/helpThank you very much
View 12 Replies
View Related
Oct 12, 2015
I have table A and B. A has column ID,A1,A2,A3,A4,A5 columns. B has ID,B1,B2, A1 columns. A table has a trigger. I defined the below trigger.
Solution 1
ALTER TRIGGER [dbo].[Tri_A]
ON [dbo].[A]
for UPDATE
AS
BEGIN
UPDATE B
SET B.A1= i.A1
FROM inserted i
INNER JOIN B
ON B.ID = i.ID
END;
GO
If I change the above solution 1 trigger to solution 2.Can I improve the trigger performance dramatically? I mean only A.A1 is changed then update B.A1. So when the other columns is changed, the update will not be required.
Solution 2
ALTER TRIGGER [dbo].[Tri_A]
ON [dbo].[A]
for UPDATE
AS
BEGIN
IF ( UPDATE (A1) )
UPDATE B
SET B.A1= i.A1
FROM inserted i
INNER JOIN B
ON B.ID = i.ID
END;
GO
View 2 Replies
View Related
Feb 20, 2008
Hello friends,
I am in need to tune my stored procedures to get best performance.
First I have a doubt in some SQL statement execution plan,
-- assume "Table_Heat" has one Million records.
-- assume "Mill" column hold value either 1 or 2.
-- assume "HeatNumber" column is Unique.
1. If I have statement like this,
-----SELECT * FROM Table_Heat WHERE HeatNumber = @nHeat AND Mill = @nMill-----
How WHERE clause filter the result from table???
-- In which order the AND statements of WHERE clause will be executed??
2. If I have created Indices for the table Table_Heat
For the above SQL statement -
-- If Index is for HeatNumber --Execution Speed??
-- If Index is for HeatNumber, Mill -- Execution Speed??
-- If Index is for Mill, HeatNumber -- Execution Speed??
I know how to work with indices, but creating too much indices and unwanted indices also cause performance inefficiency, isn't it?. so please help me on this....
Thanks
Ananth
View 5 Replies
View Related
