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

SQL Server Development (2000)

Help Optimizing Stored Procedure

Author

Topic

Mortifier
Starting Member

11 Posts

Posted - 2003-10-25 : 23:19:18

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

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-10-26 : 17:50:34

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30042

Please don't cross post.
You have been asked a few questions that you haven't answered, we are not a free code writing service.

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources