October 19, 2006 at 10:42 am
I am writing an SP that will execute a specific select statement based on the @status param that is passed to it.
Create Procedure s_Status_View
@status varchar(10)
as
IF @status = 'New'
BEGIN
Select * from v_Order where Order_Status = 88
END
IF @status = 'Old'
BEGIN
Select * from v_Order where Order_Status = 1991
END
-------------------------------------------------------------
I have approx 20 of these IF statements for each different status.
When I run this it takes forever (I end up cancelling it after 30 secs) but if I comment out all the IF statements but one it runs in 2 secs.
Any idea what is happening? TIA!
October 19, 2006 at 10:47 am
Do u have index on Order_Status column? Looks like u need one.
when u say "I comment out all the IF statements but one it runs in 2 secs" what gets run. Does it run select statement without any filters?
October 19, 2006 at 10:55 am
Try something like:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.s_Status_View
@Status VARCHAR(10)
AS
SET NOCOUNT ON
DECLARE @translate TABLE
(
Status VARCHAR(10) COLLATE DATABASE_DEFAULT NOT NULL PRIMARY KEY
,Order_Status INT NOT NULL
)
INSERT @translate
SELECT 'New', 88 UNION ALL
SELECT 'Old', 1991
-- etc
SELECT Col1, Col2...
FROM dbo.v_Order O
JOIN @translate T
ON T.Status = @status AND O.Order_Status = T.Status
RETURN @@ERROR
GO
Or, even better, have the translation table permanently in the DB.
October 19, 2006 at 12:04 pm
Sreejith,
Yes it runs a select statement with a "Where status = " filter.
Ken,
I'll have to tool around with your suggestion. I didn't 100% accurately represent my query in my previous post but it was very similar. The select statements are very simple with at least one where clause for each.
October 19, 2006 at 1:02 pm
Ken,
I was digging around a little and the view I'm calling actually has the status name in it. I actually have two parameters that I need to pass to the SP I'm writing:
@OrderStatus varchar (10)
@ShipmentStatus varchar(10)
If
@OrderStatus = '' and @ShipmentStatus <>''
select * from v_Order where ShipmentStatus = @ShipmentStatus
IF @ShipmentStatus = '' and @OrderStatus <>''
select * from v_Order where OrderStatus = @OrderStatus
Problem is this view is a monster and it seems like the procedure compiles the view for every call that is made to it in the sp.
If I run one select statement from QA:
select * from v_ORder where ShipmentStatus = 'Live'
it only takes 2 secs to run.
If it's run from the SP:
exec s_Order_Status @ShipmentStatus='Live'
it takes 22 secs to complete.
Is there a way to stop the SP from running through the other statements if one IF statement is sastisfied?
I'm really curious as to how SP's "compile" (for a lack of better understanding) the code within it. Is anyone familiar as to why this is happening? Thanks as always for the posts.
October 19, 2006 at 1:37 pm
@ShipmentStatus is varchar(10)
What is datatype of column ShipmentStatus?
_____________
Code for TallyGenerator
October 20, 2006 at 3:11 am
"Is there a way to stop the SP from running through the other statements if one IF statement is sastisfied?"
Yes there is. Use ELSE.
IF col1 < 0
BEGIN ... END
ELSE IF col2 > 100
BEGIN ... END
... and so on. But I don't think your performance problem could be caused by evaluation of the IFs. How is it with the datatypes (see Sergiy's post)?
October 20, 2006 at 5:48 am
Thanks for the posts.
Sreejith,
I missed one of your questions. I didn't have an Index on the Status Column so I added one. There was no performance difference after I added it.
Sergiy,
The datatype is Varchar(10) as specified in t_Status which is joined in the main view v_Order that is called by the SP.
Vladen,
I tried using Begin End statements when I originally had 20 IF statements in the SP. I've since rewrote the SP and it now only include two IF statements (see above post). It still drags even after I rewrote it.
October 20, 2006 at 6:08 am
AVB, there are no miracles. You are not telling something. Or you are missing something. In our case it's the same.
You definetely have mess with datatypes, because in 1st example you used integer statuses, later you used strings. Where it was right?
Can you look at the execution plans of both queries - wih SP and without SP - and tell us what's diferent?
_____________
Code for TallyGenerator
October 20, 2006 at 6:59 am
Sergiy,
As I posted to Ken before the first post wasn't an accurate portrayal of our SP. It was a generalization, however I"ve rewritten the SP to include only two IF statemenst both of which use the @ShimpentStatus and @OrderStatus parameters. Both are varchar(10).
The problem isn't with the datatypes for they are the same. It is when more than one IF Statement is in the SP.
October 20, 2006 at 7:29 am
Sergiy was asking for the datatypes, and lengths, of the ShipmentStatus and OrderStatus columns in the table/tables that lie beneath the view.
October 20, 2006 at 8:52 am
You asked "Is there a way to stop the SP from running through the other statements if one IF statement is sastisfied?"
Vladan's post answered that. (Though I have the same doubt he expressed that this will fix your execution time unless there is something in your actual conditions themselves that runs slowly.)
Vladan suggested that you use IF ... ELSE IF .... Your original posting already had BEGIN ... END blocks. The ELSE IF will "stop the SP from running through the other statements" as soon as the procedure reaches one IF statement whose condition has been satisfied.
Create Procedure s_Status_View
@Status varchar(10)
as
IF @Status = 'New'
BEGIN
Select * from v_Order where Order_Status = 88
END
ELSE IF @Status = 'Old'
BEGIN
Select * from v_Order where Order_Status = 1991
END
--Andrew
October 20, 2006 at 9:49 am
When the stored procedure is called with different parameters from the previous call, it has to re-generate it's execution plan for the different statements it's going to have to call this time. The way around it is to have separate stored procedures for each of the select statements with no conditional logic in them, and have an umbrella procedure with only conditional logic and now data access that just calls the appropriate 'child' stored procedures as necessary. That way the data access code always has valid execution plans already generated.
October 20, 2006 at 4:33 pm
I never saw a statement complex enough to make optimiser busy compiling it for more than 1 second.
It's clearly not the reason.
_____________
Code for TallyGenerator
October 23, 2006 at 1:15 am
Suck it and see.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply