stored procedure update with subqueries and grouping

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

stored procedure update with subqueries and grouping

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-02-23 : 09:06:20

TheWizard writes "The following is taking too long to run. Any ideas on how to make it run faster? Using SqlServer Enterprise 2000, with ServicePack 4.

SET QUOTED IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROC DBO.ActivationCleanup

( @defaultStatus bit = 0
)
AS
DECLARE @defaultActiveStatus bit
SET @defaultActiveStatus = 1

UPDATE
T_PROFILE
SET T_PROFILE.PROFILE_ACTIVE = Data.UpdatedStatus
FROM
(
SELECT
History.PROFILE_ID,
CASE
WHEN
(
GETDATE() >= ISNULL(PROFILE_ACTIVE_START_DATE, GETDATE())
AND
GETDATE() <= ISNULL(PROFILE_ACTIVE_END_DATE, GETDATE())
)
THEN @defaultActiveStatus
ELSE @defaultStatus
End as UpdateStatus
FROM
dbo.T_PROFILE_ACTIVE_HISTORY History WITH (NOLOCK)
INNER JOIN
(
SELECT PROFILE_ID, MAX(PROFILE_ACTIVE_ID) maxActiveID,
MAX(PROFILE_ACTIVE_TIME_STAMP) maxTime
FROM dbo.T_PROFILE_ACTIVE_HISTORY WITH (NOLOCK)
GROUP BY PROFILE_ID
) MaxId
ON History.Profile_ID = MaxID.Profile_ID AND
History.Profile_ID = MaxID.MaxActiveID
) Data
WHERE Data.Profile_ID = T_Profile_ID AND
Data.UpdatedStatus <> T_PROFILE.PROFILE_ACTIVE

The above works, but it needs to run faster. The issue I have worked on is attempting to reference Data.UpdatedStatus <> T_PROFILE.PROFILE_ACTIVE to avoid going through all the records in the History table. "

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-23 : 09:23:11

We can help you if you help us. How about:

1) The DDL's of the tables involved, with indexes and primary keys specified

2) A small amount of sample data that covers all possibilities you need to handle

3) A brief description of what this update is doing

4) With the sample data given, what the expected outcome will be.

Other than lack of indexes, I suspect some issues are the fact that there is NO criteria at all limiting the rows in any tables, other than join criteria.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources