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

Help! INSERT Replication not working on Subscriber FOR ONE TABLE ONLY

Everything is working great except ONE table is not replicating INSERTS from
the subscriber back to the publisher. UPDATES *are* being replicating, and
INSERTS *are* being replicated for every other table! I can't figure out
what's going on.
When I first setup the subscription/publication, the table did have a "int
identity" field. This field has now been removed but INSERTS are still not
being replicated.
Can someone please help!
Thanks!
-Ryan
Ben,
what type of replication are you using. Assuming it is transactional, have a
look at the article properties for the problematic table, and the commands
tab. Check that the replace Insert command isn't set to 'NONE'.
HTH,
Paul Ibison
|||Ben,
what type of replication are you using. Assuming it is transactional, have a
look at the article properties for the problematic table, and the commands
tab. Check that the replace Insert command isn't set to 'NONE'.
HTH,
Paul Ibison
|||Paul,
I'm using merge replication. What is the "commands tab"? I can't find this
in the articles properties page.
I ended up getting this thing to work by dropping the publication and
recreating it, then reinitializing my subscriber. But I'd still like to
konw why it wasn't working before. Did it have something to do with the INT
IDENTITY field? Why didn't it work after I deleted this field?
(btw, this is Ryan, my last message was sent from a coworker's computer)
Thanks, Ryan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eVpz8BbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Ben,
> what type of replication are you using. Assuming it is transactional, have
a
> look at the article properties for the problematic table, and the commands
> tab. Check that the replace Insert command isn't set to 'NONE'.
> HTH,
> Paul Ibison
>
|||Paul,
I'm using merge replication. What is the "commands tab"? I can't find this
in the articles properties page.
I ended up getting this thing to work by dropping the publication and
recreating it, then reinitializing my subscriber. But I'd still like to
konw why it wasn't working before. Did it have something to do with the INT
IDENTITY field? Why didn't it work after I deleted this field?
(btw, this is Ryan, my last message was sent from a coworker's computer)
Thanks, Ryan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eVpz8BbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Ben,
> what type of replication are you using. Assuming it is transactional, have
a
> look at the article properties for the problematic table, and the commands
> tab. Check that the replace Insert command isn't set to 'NONE'.
> HTH,
> Paul Ibison
>
|||Ryan,
I don't know what was wrong previously. As you are using merge, I would have
checked that the merge trigger was firing and that the record in
msmerge_contents was being inserted. Then there are dummy updates and the
use of profiler to track what's happening. Anyway, if this crops up again
please report back and we can investigate it then.
Regards,
Paul Ibison
|||Ryan,
I don't know what was wrong previously. As you are using merge, I would have
checked that the merge trigger was firing and that the record in
msmerge_contents was being inserted. Then there are dummy updates and the
use of profiler to track what's happening. Anyway, if this crops up again
please report back and we can investigate it then.
Regards,
Paul Ibison