Help Optimizing Stored Procedure

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)

Help Optimizing Stored Procedure

Author

Topic

Mortifier
Starting Member

11 Posts

Posted - 2003-10-23 : 22:29:06

Hi, I have the stored procedure below that is the backbone of my application for determining what is available to each specific visitor. The problem is that it usually takes about 30 seconds to execute.

This is for purchasing space locations/booths at trade shows. There are 2 prices for each booth, and regular exhibitors get a discounted price (PricePermanent). One time exhibitors are PriceOneTime. There are booths which are unreserved which anyone can purchase. The EventCode is a specific code depending on the event location.

It goes through and adds what is available to the visitor to a temporary table. Then it checks which spaces are specific to that user and adds them with the permanent price (PricePermanent). After the merge is all complete, I eliminate the crossover data and keep the lower priced ones.

I was wondering what can be done or how it can be rewritten to speed it up, and maybe I could even create a specific index to speed this up.

Thanks,
Kevin

The procedure is below:
===========================================================================================================================
ALTER PROCEDURE dbo.spSpacesAvailableToVendor @CustomerID int, @Name varchar(20), @ProductID varchar(10), @EventCode varchar(10)
AS

set nocount on

-- Future One Time Spaces
DECLARE @Table TABLE (Name varchar(50), EventCode varchar(10), EventCodeFuture varchar(10))

INSERT INTO @Table
SELECT dbo.Products.Name, dbo.vEventCodes.EventCode, SUBSTRING(dbo.vEventCodes.EventCode,1,LEN(dbo.vEventCodes.EventCode)-3) + CONVERT(varchar(10), CONVERT(int,SUBSTRING(dbo.vEventCodes.EventCode,LEN(dbo.vEventCodes.EventCode)-2,3)) +1 ) AS EventCodeFuture
--SELECT Name, EventCode, SUBSTRING(EventCode,1,LEN(EventCode)-3) + CONVERT(varchar(10), CONVERT(int,SUBSTRING(EventCode,LEN(EventCode)-2,3)) +1 ) AS EventCodeFuture
FROM dbo.Products INNER JOIN
dbo.vEventCodes ON dbo.Products.CategoryID = dbo.vEventCodes.CategoryID RIGHT OUTER JOIN
dbo.Orders INNER JOIN
dbo.Order_Details ON dbo.Orders.OrderID = dbo.Order_Details.OrderID ON dbo.Products.ProductID = dbo.Order_Details.ProductID
--FROM vOrderListing
WHERE dbo.Orders.CustomerID = @CustomerID AND dbo.vEventCodes.EventDate > CONVERT(char(10), GETDATE(), 101) AND Name Like @Name AND dbo.vEventCodes.EventCode Like @EventCode
ORDER BY dbo.Products.Name

--UNION
INSERT INTO @Table
SELECT Name, EventCode, SUBSTRING(EventCode,1,LEN(EventCode)-3) + CONVERT(varchar(10), CONVERT(int,SUBSTRING(EventCode,LEN(EventCode)-2,3)) +1 ) AS EventCodeFuture
FROM vBasket
WHERE CustomerID = @CustomerID AND EventDate > CONVERT(char(10), GETDATE(), 101) AND Name Like @Name AND EventCode Like @EventCode
--ORDER BY Name

DECLARE @SpaceTable TABLE(Name varchar(50), EventCode varchar(10), EventDate smalldatetime, Details varchar(100), PriceOneTime money, PricePermanent money, Quantity int, QuantitySold int, TicketQuantityAvailable int, TicketUnreserved bit, TicketsAvailable bit, ProductID int)

INSERT INTO @SpaceTable

--SELECT dbo.vSpacesAvailableAll.*
SELECT Name, EventCode, EventDate, Details, Price AS PriceOneTime, NULL AS PricePermanent, Quantity, QuantitySold, TicketQuantityAvailable, TicketUnreserved, TicketsAvailable, ProductID
FROM dbo.vSpacesAvailableAll
WHERE (Name IN (SELECT Name FROM @Table) AND EventCode IN (SELECT EventCodeFuture FROM @TABLE) AND ProductID Like @ProductID)

-- Day of Event
UNION ALL
SELECT Name, EventCode, EventDate, Details, Price AS PriceOneTime, NULL AS PricePermanent, Quantity, QuantitySold, TicketQuantityAvailable, TicketUnreserved, TicketsAvailable, ProductID
FROM dbo.vSpacesAvailableAll
WHERE (EventCode IN
(SELECT TOP 1 dbo.Categories.EventCode
FROM dbo.Categories
WHERE EventDate = CONVERT(char(10), GETDATE(), 101)) AND Name Like @Name AND ProductID Like @ProductID AND EventCode Like @EventCode)

-- All Unreserved Tickets
--INSERT INTO @SpaceTable
UNION ALL
SELECT Name, EventCode, EventDate, Details, Price AS PriceOneTime, NULL AS PricePermanent, Quantity, QuantitySold, TicketQuantityAvailable, TicketUnreserved, TicketsAvailable, ProductID
FROM dbo.vSpacesAvailableAll
WHERE (TicketUnreserved = 1 AND Name Like @Name AND ProductID Like @ProductID AND EventCode Like @EventCode)

-- Permanent Vendor Spaces
--INSERT INTO @SpaceTable
UNION ALL
SELECT Name, EventCode, EventDate, Details, NULL AS PriceOneTime, PricePermanent, Quantity, QuantitySold, TicketQuantityAvailable, TicketUnreserved, TicketsAvailable, ProductID
FROM dbo.vSpacesAvailableAll INNER JOIN
dbo.SpacesPermanent ON dbo.vSpacesAvailableAll.Name = dbo.SpacesPermanent.TicketNumber
WHERE (dbo.SpacesPermanent.CustomerID = @CustomerID AND dbo.vSpacesAvailableAll.EventCode LIKE (dbo.SpacesPermanent.EventName + '%') AND Name Like @Name AND ProductID Like @ProductID

AND EventCode Like @EventCode)

-- Rose Bowl next 2 events (should make 45 days in advance)
IF @EventCode LIKE 'RB%' OR @EventCode = '%'
BEGIN
INSERT INTO @SpaceTable
--UNION ALL
SELECT Name, EventCode, EventDate, Details, Price AS PriceOneTime, NULL AS PricePermanent, Quantity, QuantitySold, TicketQuantityAvailable, TicketUnreserved, TicketsAvailable, ProductID
FROM dbo.vSpacesAvailableAll
WHERE (EventCode IN
(SELECT TOP 2 dbo.Categories.EventCode
FROM dbo.Categories
WHERE CategoryID > 100 AND EventDate > CONVERT(char(10), GETDATE(), 101) AND EventCode LIKE 'RB%') AND Name Like @Name AND ProductID Like @ProductID AND EventCode Like @EventCode)
END

-- Ventura next 1 event
IF @EventCode LIKE 'V%' OR @EventCode = '%'
BEGIN
INSERT INTO @SpaceTable
--UNION ALL
SELECT Name, EventCode, EventDate, Details, Price AS PriceOneTime, NULL AS PricePermanent, Quantity, QuantitySold, TicketQuantityAvailable, TicketUnreserved, TicketsAvailable, ProductID
FROM dbo.vSpacesAvailableAll
WHERE (EventCode IN
(SELECT TOP 1 dbo.Categories.EventCode
FROM dbo.Categories
WHERE CategoryID > 100 AND EventDate > CONVERT(char(10), GETDATE(), 101) AND EventCode LIKE 'V%') AND Name Like @Name AND ProductID Like @ProductID AND EventCode Like @EventCode)
END

-- TroubleShooter next 1 event
IF @EventCode LIKE 'TS%' OR @EventCode = '%'
BEGIN
INSERT INTO @SpaceTable
--UNION ALL
SELECT Name, EventCode, EventDate, Details, Price AS PriceOneTime, NULL AS PricePermanent, Quantity, QuantitySold, TicketQuantityAvailable, TicketUnreserved, TicketsAvailable, ProductID
FROM dbo.vSpacesAvailableAll
WHERE (EventCode IN
(SELECT TOP 1 dbo.Categories.EventCode
FROM dbo.Categories
WHERE CategoryID > 100 AND EventDate > CONVERT(char(10), GETDATE(), 101) AND EventCode LIKE 'TS%') AND Name Like @Name AND ProductID Like @ProductID AND EventCode Like @EventCode)
END

-- Select Temp Table
SELECT Name, EventCode, EventDate, Details, COALESCE(MIN(PricePermanent),MIN(PriceOneTime)) AS Price, Quantity, QuantitySold, TicketQuantityAvailable, TicketUnreserved, TicketsAvailable, ProductID
FROM @SpaceTable
GROUP BY Name, EventCode, EventDate, Details, Quantity, QuantitySold, TicketQuantityAvailable, TicketUnreserved, TicketsAvailable, ProductID

set nocount off

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-10-24 : 10:49:26

One approach is to look at the execution plan....Press ctrl-k in QA....and look to remove the poor performance elements...table scans, etc...by applying indices.

Also you seem to be doing extra work by hard-coding the upcoming events....there should be a quick way of doing all that work in one go...ie the RB, Ventura, etc, stuff...

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-10-26 : 09:59:41

/*
You may consider temp tables (#) vs table variables (@)

Table variable have some drawbacks as compared to temporary tables:

Non-clustered indexes cannot be created on table variables,
other than the system indexes that are created for a PRIMARY or UNIQUE constraint.
That can influence the query performance when compared to a temporary table with non-clustered indexes.

Table variables do not maintain statistics like temporary tables can. Statistics cannot be created on table variables
through automatic creation or by using the CREATE STATISTICS statement. Therefore, for complex queries on large tables,
the lack of statistics may deter the optimizer to determine the best plan for a query, thus affecting the performance of that query.

For more info visit: http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q305977&
*/

Mortifier
Starting Member

11 Posts

Posted - 2003-10-27 : 17:52:17

I went through and added indexes to any of the fields the stored procedure references. Now when I run QA, there are 3 querys, with costs of 4.26%, 0.33%, and 95.41%. Looking at the last query, there is a sort cost of 27%, and there an index seek of 13% on a Categories index for the EventCode. Is this normal? Everything else has a highest hit of 3-4%.

When you talk about not hard coding all the upcoming events, what do you suggest doing?

Thanks,
Kevin

quote:
Originally posted by AndrewMurphy

One approach is to look at the execution plan....Press ctrl-k in QA....and look to remove the poor performance elements...table scans, etc...by applying indices.

Also you seem to be doing extra work by hard-coding the upcoming events....there should be a quick way of doing all that work in one go...ie the RB, Ventura, etc, stuff...

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-10-27 : 18:28:05

In the Execution plan, move your mouse over the icon on the far left. You should see the "Sub tree cost" of each query. Can you post those?

Also, you could see how many IO/s those queries are using by doing this

SET STATISTICS IO ON

--Your queries here

SET STATISICS IO OFF

That will tell you how many pages it had to read to get your results. Lower is better.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

homam
Starting Member

31 Posts

Posted - 2003-10-27 : 18:40:16

Dude, why don't you alias your table names instead of writing the FQNs?

The first thing I would do if I were you is to run each query on its own and see how it performs. Once you do that you can optimize each one individually. The next step is to see if you can refactor your queries so that you can utilize the same/similar joins that appear in two or more queries and convert those queries into a single one.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-10-28 : 09:37:45

re "When you talk about not hard coding all the upcoming events, what do you suggest doing?"

Why not put the upcoming events into a 'upcoming events' table...and do a join to that table to include them in the result?

By doing that you remove the need for the stored-procedure to be updated each time a new event comes along.

The structure for this 'upcoming events' table is open for you to determine...but a 'enddate' column might be useful to eliminate 'upcoming events' that have passed!

Mortifier
Starting Member

11 Posts

Posted - 2003-10-29 : 16:26:24

Well the Categories table does have all the events listed in it, which is why it selects the top x of the EventCode from Categories and EventDate > today. But not all listings for the future apply to all people.

How would that remove the need for the stored procedure?

quote:
Originally posted by AndrewMurphy

re "When you talk about not hard coding all the upcoming events, what do you suggest doing?"

Why not put the upcoming events into a 'upcoming events' table...and do a join to that table to include them in the result?

By doing that you remove the need for the stored-procedure to be updated each time a new event comes along.

The structure for this 'upcoming events' table is open for you to determine...but a 'enddate' column might be useful to eliminate 'upcoming events' that have passed!

Mortifier
Starting Member

11 Posts

Posted - 2003-10-29 : 16:29:28

Why would aliasing the table name provide any benefit to the execution speed?

I was thinking about separating the 3 parts of the stored procedure and maybe I can optimize them a bit. I just don't see how I can combine the queries into one because they are somewhat complex.

quote:
Originally posted by homam

Dude, why don't you alias your table names instead of writing the FQNs?

The first thing I would do if I were you is to run each query on its own and see how it performs. Once you do that you can optimize each one individually. The next step is to see if you can refactor your queries so that you can utilize the same/similar joins that appear in two or more queries and convert those queries into a single one.

Mortifier
Starting Member

11 Posts

Posted - 2003-10-29 : 16:31:21

What about the speed? Which would be faster? I guess if I did use a temp table I could then index by the Name, or possibly something else.

quote:
Originally posted by ehorn

/*
You may consider temp tables (#) vs table variables (@)

Table variable have some drawbacks as compared to temporary tables:

Non-clustered indexes cannot be created on table variables,
other than the system indexes that are created for a PRIMARY or UNIQUE constraint.
That can influence the query performance when compared to a temporary table with non-clustered indexes.

Table variables do not maintain statistics like temporary tables can. Statistics cannot be created on table variables
through automatic creation or by using the CREATE STATISTICS statement. Therefore, for complex queries on large tables,
the lack of statistics may deter the optimizer to determine the best plan for a query, thus affecting the performance of that query.

For more info visit: http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q305977&
*/

homam
Starting Member

31 Posts

Posted - 2003-10-29 : 16:55:12

It just makes it a whole lot more readable.

quote:
Originally posted by Mortifier

Why would aliasing the table name provide any benefit to the execution speed?

I was thinking about separating the 3 parts of the stored procedure and maybe I can optimize them a bit. I just don't see how I can combine the queries into one because they are somewhat complex.

quote:
Originally posted by homam

Dude, why don't you alias your table names instead of writing the FQNs?

The first thing I would do if I were you is to run each query on its own and see how it performs. Once you do that you can optimize each one individually. The next step is to see if you can refactor your queries so that you can utilize the same/similar joins that appear in two or more queries and convert those queries into a single one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources