Help! Processing Hard Coded vs. Stored Procedure

Hi All,
I am just in the process of putting a script that updates a reporting table
into a stored procedure and have found that when run as a SP it takes soooo
much longer. Running the script in QA with the "WHERE" clauses hard coded
takes around 1 min, when I attempt to run via a SP with the "WHERE" clauses
replaced by variables (@.Title) that the user inputs it takes around 1 hour.
Below is a sample of both.
Can anyone shed any light on why? What am I doing wrong?
Thanks DC
Stored Procedure - -
CREATE PROCEDURE [dbo]. [sp_Report_Promoted_Issue_Impact_Analysi
s]
@.MarketGroup nvarchar (3),
@.Master_Title_id nvarchar (25),
@.Current_Title_Issue_Rank int
as
TRUNCATE TABLE Report_Promoted_Issue_Impact_Analysis
----
--INSERT INTO Report_Promoted_Issue_Impact_Analysis
( Retail_Type_id,
Retail_Type,
Market_Group_id,
Market_Group_Desc,
Outlet_id,
Branch_id,
EIS_Master_Title_id,
Title_id_2,
Issue_id,
Title_Issue_Rank,
Dist_Qty,
Sales_Qty
)
SELECT O.Retail_Class_id AS Retail_Type_id, MG.Retail_Class_Desc AS
Retail_Type, O.Market_Group_id, MG.Market_Group_Desc, OIH.Outlet_id,
TIB.Branch_id, T.EIS_Master_Title_id, OIH.Title_id_2,
OIH.Issue_id, TIB.Title_Issue_Rank, OIH.Dist_Qty, OIH.Sales_Qty
FROM Titles T RIGHT OUTER JOIN
Outlet_Issue_History OIH ON T.Title_id_2 =
OIH.Title_id_2 LEFT OUTER JOIN
Titles_Issues_Branch TIB ON OIH.Issue_id =
TIB.Issue_id AND OIH.Branch_id = TIB.Branch_id AND OIH.Title_id_2 =
TIB.Title_id_2 LEFT OUTER JOIN
Market_Groups MG RIGHT OUTER JOIN
Outlets O ON MG.Retail_Class_Market_Group_id =
O.Retail_Class_Market_Group_id ON OIH.Outlet_id = O.Outlet_id
WHERE (O.Market_Group_id <> @.MarketGroup) AND (T.EIS_Master_Title_id =
@.Master_Title_id) AND (TIB.Title_Issue_Rank = @.Current_Title_Issue_Rank) AND
(O.Retail_Class_id IN (N'A', N'B', N'K', N'S'))
Hard Coded Script - -
TRUNCATE TABLE Report_Promoted_Issue_Impact_Analysis
----
--
INSERT INTO Report_Promoted_Issue_Impact_Analysis
( Retail_Type_id,
Retail_Type,
Market_Group_id,
Market_Group_Desc,
Outlet_id,
Branch_id,
EIS_Master_Title_id,
Title_id_2,
Issue_id,
Title_Issue_Rank,
Dist_Qty,
Sales_Qty
)
SELECT O.Retail_Class_id AS Retail_Type_id, MG.Retail_Class_Desc AS
Retail_Type, O.Market_Group_id, MG.Market_Group_Desc, OIH.Outlet_id,
TIB.Branch_id, T.EIS_Master_Title_id, OIH.Title_id_2,
OIH.Issue_id, TIB.Title_Issue_Rank, OIH.Dist_Qty, OIH.Sales_Qty
FROM Titles T RIGHT OUTER JOIN
Outlet_Issue_History OIH ON T.Title_id_2 =
OIH.Title_id_2 LEFT OUTER JOIN
Titles_Issues_Branch TIB ON OIH.Issue_id =
TIB.Issue_id AND OIH.Branch_id = TIB.Branch_id AND OIH.Title_id_2 =
TIB.Title_id_2 LEFT OUTER JOIN
Market_Groups MG RIGHT OUTER JOIN
Outlets O ON MG.Retail_Class_Market_Group_id =
O.Retail_Class_Market_Group_id ON OIH.Outlet_id = O.Outlet_id
WHERE (O.Market_Group_id <> 'WW') AND (T.EIS_Master_Title_id = 'OK') AND
(TIB.Title_Issue_Rank = 5) AND (O.Retail_Class_id IN (N'A', N'B', N'K', N'S'
))Sounds to me like the query is using a different execution plan when it know
s
that the 'variables' have a particular value. Check the execution plans, hav
e
a look to see what indexes are being used, and see if this gives you some
clues into the situation.
Also make sure your statistics are updated, as this could cause the
optimiser to think that a particular plan might be best when it's really not
.
Rob
"David C" wrote:

> Hi All,
> I am just in the process of putting a script that updates a reporting tabl
e
> into a stored procedure and have found that when run as a SP it takes sooo
o
> much longer. Running the script in QA with the "WHERE" clauses hard coded
> takes around 1 min, when I attempt to run via a SP with the "WHERE" clause
s
> replaced by variables (@.Title) that the user inputs it takes around 1 hour
.
> Below is a sample of both.
> Can anyone shed any light on why? What am I doing wrong?
> Thanks DC
> Stored Procedure - -
> CREATE PROCEDURE [dbo]. [sp_Report_Promoted_Issue_Impact_Analysi
s]
> @.MarketGroup nvarchar (3),
> @.Master_Title_id nvarchar (25),
> @.Current_Title_Issue_Rank int
> as
> TRUNCATE TABLE Report_Promoted_Issue_Impact_Analysis
> ----
--INSERT INTO Report_Promoted_Issue_Impact_Analysis
> ( Retail_Type_id,
> Retail_Type,
> Market_Group_id,
> Market_Group_Desc,
> Outlet_id,
> Branch_id,
> EIS_Master_Title_id,
> Title_id_2,
> Issue_id,
> Title_Issue_Rank,
> Dist_Qty,
> Sales_Qty
> )
> SELECT O.Retail_Class_id AS Retail_Type_id, MG.Retail_Class_Desc AS
> Retail_Type, O.Market_Group_id, MG.Market_Group_Desc, OIH.Outlet_id,
> TIB.Branch_id, T.EIS_Master_Title_id, OIH.Title_id_2
,
> OIH.Issue_id, TIB.Title_Issue_Rank, OIH.Dist_Qty, OIH.Sales_Qty
> FROM Titles T RIGHT OUTER JOIN
> Outlet_Issue_History OIH ON T.Title_id_2 =
> OIH.Title_id_2 LEFT OUTER JOIN
> Titles_Issues_Branch TIB ON OIH.Issue_id =
> TIB.Issue_id AND OIH.Branch_id = TIB.Branch_id AND OIH.Title_id_2 =
> TIB.Title_id_2 LEFT OUTER JOIN
> Market_Groups MG RIGHT OUTER JOIN
> Outlets O ON MG.Retail_Class_Market_Group_id =
> O.Retail_Class_Market_Group_id ON OIH.Outlet_id = O.Outlet_id
> WHERE (O.Market_Group_id <> @.MarketGroup) AND (T.EIS_Master_Title_id =
> @.Master_Title_id) AND (TIB.Title_Issue_Rank = @.Current_Title_Issue_Rank) A
ND
> (O.Retail_Class_id IN (N'A', N'B', N'K', N'S'))
>
> Hard Coded Script - -
> TRUNCATE TABLE Report_Promoted_Issue_Impact_Analysis
> ----
--
> INSERT INTO Report_Promoted_Issue_Impact_Analysis
> ( Retail_Type_id,
> Retail_Type,
> Market_Group_id,
> Market_Group_Desc,
> Outlet_id,
> Branch_id,
> EIS_Master_Title_id,
> Title_id_2,
> Issue_id,
> Title_Issue_Rank,
> Dist_Qty,
> Sales_Qty
> )
> SELECT O.Retail_Class_id AS Retail_Type_id, MG.Retail_Class_Desc AS
> Retail_Type, O.Market_Group_id, MG.Market_Group_Desc, OIH.Outlet_id,
> TIB.Branch_id, T.EIS_Master_Title_id, OIH.Title_id_2
,
> OIH.Issue_id, TIB.Title_Issue_Rank, OIH.Dist_Qty, OIH.Sales_Qty
> FROM Titles T RIGHT OUTER JOIN
> Outlet_Issue_History OIH ON T.Title_id_2 =
> OIH.Title_id_2 LEFT OUTER JOIN
> Titles_Issues_Branch TIB ON OIH.Issue_id =
> TIB.Issue_id AND OIH.Branch_id = TIB.Branch_id AND OIH.Title_id_2 =
> TIB.Title_id_2 LEFT OUTER JOIN
> Market_Groups MG RIGHT OUTER JOIN
> Outlets O ON MG.Retail_Class_Market_Group_id =
> O.Retail_Class_Market_Group_id ON OIH.Outlet_id = O.Outlet_id
> WHERE (O.Market_Group_id <> 'WW') AND (T.EIS_Master_Title_id = 'OK') A
ND
> (TIB.Title_Issue_Rank = 5) AND (O.Retail_Class_id IN (N'A', N'B', N'K', N'
S'))
>|||use "with recompile" option in the stored proc.. Its just a guess.
Can you run the query and SP together and see the execution plan.
I think for the SP the execution plan is built with the
Report_Promoted_Issue_Impact_Analysis table filled. So it prepares for an
execution plan with the table full, but it gets truncated...
Hope this helps.
--
"David C" wrote:

> Hi All,
> I am just in the process of putting a script that updates a reporting tabl
e
> into a stored procedure and have found that when run as a SP it takes sooo
o
> much longer. Running the script in QA with the "WHERE" clauses hard coded
> takes around 1 min, when I attempt to run via a SP with the "WHERE" clause
s
> replaced by variables (@.Title) that the user inputs it takes around 1 hour
.
> Below is a sample of both.
> Can anyone shed any light on why? What am I doing wrong?
> Thanks DC
> Stored Procedure - -
> CREATE PROCEDURE [dbo]. [sp_Report_Promoted_Issue_Impact_Analysi
s]
> @.MarketGroup nvarchar (3),
> @.Master_Title_id nvarchar (25),
> @.Current_Title_Issue_Rank int
> as
> TRUNCATE TABLE Report_Promoted_Issue_Impact_Analysis
> ----
--INSERT INTO Report_Promoted_Issue_Impact_Analysis
> ( Retail_Type_id,
> Retail_Type,
> Market_Group_id,
> Market_Group_Desc,
> Outlet_id,
> Branch_id,
> EIS_Master_Title_id,
> Title_id_2,
> Issue_id,
> Title_Issue_Rank,
> Dist_Qty,
> Sales_Qty
> )
> SELECT O.Retail_Class_id AS Retail_Type_id, MG.Retail_Class_Desc AS
> Retail_Type, O.Market_Group_id, MG.Market_Group_Desc, OIH.Outlet_id,
> TIB.Branch_id, T.EIS_Master_Title_id, OIH.Title_id_2
,
> OIH.Issue_id, TIB.Title_Issue_Rank, OIH.Dist_Qty, OIH.Sales_Qty
> FROM Titles T RIGHT OUTER JOIN
> Outlet_Issue_History OIH ON T.Title_id_2 =
> OIH.Title_id_2 LEFT OUTER JOIN
> Titles_Issues_Branch TIB ON OIH.Issue_id =
> TIB.Issue_id AND OIH.Branch_id = TIB.Branch_id AND OIH.Title_id_2 =
> TIB.Title_id_2 LEFT OUTER JOIN
> Market_Groups MG RIGHT OUTER JOIN
> Outlets O ON MG.Retail_Class_Market_Group_id =
> O.Retail_Class_Market_Group_id ON OIH.Outlet_id = O.Outlet_id
> WHERE (O.Market_Group_id <> @.MarketGroup) AND (T.EIS_Master_Title_id =
> @.Master_Title_id) AND (TIB.Title_Issue_Rank = @.Current_Title_Issue_Rank) A
ND
> (O.Retail_Class_id IN (N'A', N'B', N'K', N'S'))
>
> Hard Coded Script - -
> TRUNCATE TABLE Report_Promoted_Issue_Impact_Analysis
> ----
--
> INSERT INTO Report_Promoted_Issue_Impact_Analysis
> ( Retail_Type_id,
> Retail_Type,
> Market_Group_id,
> Market_Group_Desc,
> Outlet_id,
> Branch_id,
> EIS_Master_Title_id,
> Title_id_2,
> Issue_id,
> Title_Issue_Rank,
> Dist_Qty,
> Sales_Qty
> )
> SELECT O.Retail_Class_id AS Retail_Type_id, MG.Retail_Class_Desc AS
> Retail_Type, O.Market_Group_id, MG.Market_Group_Desc, OIH.Outlet_id,
> TIB.Branch_id, T.EIS_Master_Title_id, OIH.Title_id_2
,
> OIH.Issue_id, TIB.Title_Issue_Rank, OIH.Dist_Qty, OIH.Sales_Qty
> FROM Titles T RIGHT OUTER JOIN
> Outlet_Issue_History OIH ON T.Title_id_2 =
> OIH.Title_id_2 LEFT OUTER JOIN
> Titles_Issues_Branch TIB ON OIH.Issue_id =
> TIB.Issue_id AND OIH.Branch_id = TIB.Branch_id AND OIH.Title_id_2 =
> TIB.Title_id_2 LEFT OUTER JOIN
> Market_Groups MG RIGHT OUTER JOIN
> Outlets O ON MG.Retail_Class_Market_Group_id =
> O.Retail_Class_Market_Group_id ON OIH.Outlet_id = O.Outlet_id
> WHERE (O.Market_Group_id <> 'WW') AND (T.EIS_Master_Title_id = 'OK') A
ND
> (TIB.Title_Issue_Rank = 5) AND (O.Retail_Class_id IN (N'A', N'B', N'K', N'
S'))
>|||Hi again, I have fixed this problem by creating and inserting into a temp
table first, the processing time is now down to 1-2 min.
The execution plans were quite different between the hard coded and the
original stored procedure.
Thanks again
DC
"David C" wrote:

> Hi All,|||This will be good if there aren't too many concurrent users.
Temporary tabl should be avoided as much as possible because it uses lot of
I/O.
I would suggest you to find the real reason and fix the proc.
if not possible try to use a table variable instead of a temporary table.
-Omnibuzz