Sql statement tuning - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Administration

Sql statement tuning

Author

Topic

mhdmehraj
Starting Member

6 Posts

Posted - 2010-10-22 : 03:07:04

Hi , iam basically an oracle dba but new to sql server administration . I cant able to understand the eecution plan and its keywords here .
This below query tooks 1 min to get executed which is need to be tuned.

no of rows in PLD_PlanDays=120788
no of rows in PLA_Plan= 421

SELECT TOP 100 PERCENT PLD.*, PLA.PLA_PLT_ID, PLA.PLA_InitialPlanID
FROM dbo.PLD_PlanDays AS PLD INNER JOIN
dbo.PLA_Plan AS PLA ON PLD.PLD_PLA_ID = PLA.PLA_ID
AND PLA.PLA_ID IN (dbo.FindLatestApprovedPlanID(PLA.PLA_PLT_ID, PLD.PLD_DayDate, 0))
ORDER BY PLD.PLD_DayDate

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-22 : 03:28:22

Show us the source code of dbo.FindLatestApprovedPlanID() please.

No, you're never too old to Yak'n'Roll if you're too young to die.

mhdmehraj
Starting Member

6 Posts

Posted - 2010-10-22 : 05:04:26

CREATE FUNCTION [dbo].[FindLatestApprovedPlanID]
(
@PLT_ID uniqueidentifier,
@DayDate datetime,
@ActualsCollection bit
)
RETURNS uniqueidentifier
AS
BEGIN
-- Declare the return variable here
DECLARE @PLA_ID uniqueidentifier
DECLARE @InitialPlanID uniqueidentifier
DECLARE @DateLimitedPlan bit

SELECT @DateLimitedPlan = PLT_DateLimited
FROM PLT_PlanType
WHERE PLT_ID = @PLT_ID

IF (@DateLimitedPlan = 1)
BEGIN
DECLARE @MaxDate smalldatetime
DECLARE @SelectedMaxDate smalldatetime

SET @MaxDate = '2079-06-06'

if (@ActualsCollection = 1)
BEGIN
SELECT TOP 1 @InitialPlanID = PAPC_InitialPlanID
,@SelectedMaxDate = ISNULL(PAPC_ValidTo,@MaxDate)
FROM PAPC_PlanActualsProcessingConfiguration
WHERE PAPC_PLT_ID = @PLT_ID
AND ISNULL(PAPC_EnableActualsCollection,0) = 1
AND @DayDate >= PAPC_ValidFrom AND @DayDate <= ISNULL(PAPC_ValidTo,@MaxDate)
ORDER BY ISNULL(PAPC_ValidTo,@MaxDate) DESC
END
ELSE
BEGIN
SELECT TOP 1 @InitialPlanID = PAPC_InitialPlanID
,@SelectedMaxDate = ISNULL(PAPC_ValidTo,@MaxDate)
FROM PAPC_PlanActualsProcessingConfiguration
WHERE PAPC_PLT_ID = @PLT_ID
AND @DayDate >= PAPC_ValidFrom AND @DayDate <= ISNULL(PAPC_ValidTo,@MaxDate)
ORDER BY ISNULL(PAPC_ValidTo,@MaxDate) DESC
END

SELECT TOP 1 @PLA_ID = PD.PLD_PLA_ID
FROM dbo.PLD_PlanDays AS PD INNER JOIN dbo.PLA_Plan AS P ON PD.PLD_PLA_ID = P.PLA_ID
WHERE P.PLA_InitialPlanID = @InitialPlanID
AND ISNULL(P.PLA_Inactive,0) = 0
AND PD.PLD_DayDate = @DayDate
AND P.PLA_Version IN (2, 3)
ORDER BY P.PLA_Revision DESC
END
ELSE
BEGIN
SELECT TOP 1 @PLA_ID = PD.PLD_PLA_ID
FROM dbo.PLD_PlanDays AS PD INNER JOIN dbo.PLA_Plan AS P ON PD.PLD_PLA_ID = P.PLA_ID
WHERE P.PLA_PLT_ID = @PLT_ID
AND ISNULL(P.PLA_Inactive,0) = 0
AND PD.PLD_DayDate = @DayDate
AND P.PLA_Version IN (2, 3)
ORDER BY P.PLA_Revision DESC
END
RETURN @PLA_ID

END

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-22 : 10:48:52

Can you save, zip and upload the execution plan somewhere please.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources