March 17, 2008 at 7:02 am
I am working on an application (ASP.NET 2.0) that returns 2 result sets (SQL Server 2005) - filling a DataAdapter into 2 tables
The form which invokes the Fill statement has a couple of options - get all the data or limit it to one store (it is an application maintaining inventory for convenience stores)
The unrestricted selection has acceptable response but selecting one store takes several minutes. I have added indexes and foreign keys but neither seem to have brought the response to an acceptable level.
Running the stored procedure from a query window in SQL goes pretty quick - and the one store option goes faster than the all stores option.
The test database used is remote but would that alone explain the discrepancy in response time?
Any ideas?
March 17, 2008 at 1:51 pm
What do the execution plans look like?
If you can post the script of the proc and the schemas of the tables (with index definitions), we can offer suggestions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 17, 2008 at 2:14 pm
It sounds like the query is run first/more often with the unrestricted results and that the cached plan is using a table scan. So you will not get the expected results from the restricted option as it is using the cached plan for all stores.
You could use the With Recompile option which will generate a new plan each time the query is run, or you could have 2 sp's that you either call within a master sp or change your call in the ASP.NET app. Then you will get an appropriate execution plan.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 17, 2008 at 2:59 pm
thanks one and all
it's even more complicated - there are also 3 options
return all the data OR
return data with null counts OR
return data with some counts (not all NULL)
in addition to the All Stores / one store selection
I'll try the Recompile option
the sp:
ALTER PROCEDURE [dbo].[selInventory]
@Select INT = 0,
@StoreID INT = 0
AS
BEGIN
DECLARE @Inventory TABLE
(StoreName VARCHAR(50),
InventoryID INT,
Description VARCHAR(50),
SKU VARCHAR(50),
OnHand INT,
ReorderQuantity INT,
ReorderLevel INT)
DECLARE @InvUpdateLog TABLE
(InventoryID INT,
Status CHAR(1),
InvReasonCode VARCHAR(20),
FieldName VARCHAR(50),
OldValue VARCHAR(50),
NewValue VARCHAR(50),
TransactionDate DATETIME,
EmployeeID_EnteredBy VARCHAR(50))
IF @StoreID = 0
BEGIN
INSERT INTO @Inventory
(StoreName,
InventoryID,
Description,
SKU,
OnHand,
ReorderQuantity,
ReorderLevel)
SELECT
StoreName,
InventoryID,
SK.[ShortDescription] AS Description,
SK.[SKU] AS SKU,
OnHand,
ReorderQuantity,
ReorderLevel
FROM tblInventory I
INNER JOIN loyaltybase.dbo.tblStore S
ON I.StoreID = S.StoreID
INNER JOIN [tblSKUMaster] SKM
ON SKM.[SKUMasterID] = I.SKUMasterID
INNER JOIN [tblSKU] SK ON SK.[SKUID] = SKM.[SKUID]
ORDER BY InventoryID
INSERT INTO @InvUpdateLog
(InventoryID,
Status,
InvReasonCode,
FieldName,
OldValue,
NewValue,
TransactionDate,
EmployeeID_EnteredBy)
SELECT
InventoryID,
Status,
IR.Description AS InvReasonCode,
FieldName,
OldValue,
NewValue,
TransactionDate,
E.FirstName + ' ' + E.LastName AS EmployeeID_EnteredBy
FROM tblInventoryUpdateLog UL
INNER JOIN loyaltybase.dbo.tblInventoryUpdateReason AS IR
ON CAST(UL.InvReasonCode AS INTEGER) = CAST(IR.InvReasonCode AS INTEGER)
LEFT OUTER JOIN tblEmployees E
ON UL.EmployeeID_EnteredBy = E.EmployeeID
ORDER BY InventoryID
END
ELSE
BEGIN
INSERT INTO @Inventory
(StoreName,
InventoryID,
Description,
SKU,
OnHand,
ReorderQuantity,
ReorderLevel)
SELECT
StoreName,
InventoryID,
SK.[ShortDescription] AS Description,
SK.[SKU] AS SKU,
OnHand,
ReorderQuantity,
ReorderLevel
FROM tblInventory I
INNER JOIN loyaltybase.dbo.tblStore S
ON I.StoreID = S.StoreID
INNER JOIN [tblSKUMaster] SKM
ON SKM.[SKUMasterID] = I.SKUMasterID
INNER JOIN [tblSKU] SK ON SK.[SKUID] = SKM.[SKUID]
WHERE I.StoreID = @StoreID
ORDER BY InventoryID
INSERT INTO @InvUpdateLog
(InventoryID,
Status,
InvReasonCode,
FieldName,
OldValue,
NewValue,
TransactionDate,
EmployeeID_EnteredBy)
SELECT
UL.InventoryID,
Status,
IR.Description AS InvReasonCode,
FieldName,
OldValue,
NewValue,
TransactionDate,
E.FirstName + ' ' + E.LastName AS EmployeeID_EnteredBy
FROM tblInventoryUpdateLog UL
INNER JOIN tblInventory I ON UL.InventoryID = I.InventoryID
INNER JOIN loyaltybase.dbo.tblInventoryUpdateReason AS IR
ON CAST(UL.InvReasonCode AS INTEGER) = CAST(IR.InvReasonCode AS INTEGER)
LEFT OUTER JOIN tblEmployees E
ON UL.EmployeeID_EnteredBy = E.EmployeeID
WHERE I.StoreID = @StoreID
ORDER BY InventoryID
END
IF @Select = 2/* All Items */
BEGIN
SELECT * FROM @Inventory
SELECT * FROM @InvUpdateLog
END
IF @Select = 0 /* Items with some activity */
BEGIN
SELECT * FROM @Inventory
WHERE OnHand IS NOT NULL
OR ReorderQuantity IS NOT NULL
OR ReorderLevel IS NOT NULL
SELECT DB1.* FROM @InvUpdateLog DB1
JOIN @Inventory DB2 ON DB1.InventoryID = DB2.InventoryID
WHERE DB2.OnHand IS NOT NULL
OR DB2.ReorderQuantity IS NOT NULL
OR DB2.ReorderLevel IS NOT NULL
END
IF @Select = 1 /* Items with no activity */
BEGIN
SELECT * FROM @Inventory
WHERE OnHand IS NULL
AND ReorderQuantity IS NULL
AND ReorderLevel IS NULL
SELECT DB1.* FROM @InvUpdateLog DB1
JOIN @Inventory DB2 ON DB1.InventoryID = DB2.InventoryID
WHERE DB2.OnHand IS NULL
AND DB2.ReorderQuantity IS NULL
AND DB2.ReorderLevel IS NULL
END
END
March 17, 2008 at 3:09 pm
One hint I picked up somewhere, and to be honest I did not test it, is to use code like this to get a consistent query plan:
Select
...
From
table
where
id between IsNull(@id, 0) and IsNull(@id, 999999999)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 17, 2008 at 3:11 pm
This is probably using the wrong (non-optimal) query plan because your parameters are changing.
Two ways to fix this as far as I know:
1) Add WITH RECOMPILE to the stored procedure.
2) Create three new procedures, one for each of your 3 options. Cut the relevant query from the original procedure into each new procedure. Rewrite the original stored procedure to be a main procedure, calling one of the 3 new stroed procedures based on the options passed.
Method (2) is faster but is a little bit more work for you.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 17, 2008 at 11:02 pm
One the basis that you said it can take several minutes to return at times, the volume of data might make it beneficial to use temp tables instead of table variables - you would have to test to confirm either way though.
March 19, 2008 at 6:49 am
I changed the TABLE variables to temp tables and added a With RECOMPILE option - that helped a lot
thanks one and all
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply