Stored procedures question - SQL Server Forums

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

General SQL Server Forums

Database Design and Application Architecture

Stored procedures question

Author

Topic

jmchristy
Starting Member

8 Posts

Posted - 2008-03-19 : 10:30:37

Hey guys, I'm a system admin guy who doesn't primarily deal with SQL server. We've been having an ongoing problem with our SQL Server box with users complaining about it locking up. I recently just got involved with figuring out what the problems are and I saw that it mainly happens when my programmer is in SQL Query Analyzer. Below is a procedure he was running, which caused everything to come to a crawl on our system. If someone can just glance thru that, and see if there are ANY reasons why it might cause any blocks or locks in the system or recommend a better way to write the procedure I'd appreciate it!! If the code looks fine, I'm assuming there is a hardware bottleneck which I can address.

Thanks again for the help.

CREATE PROCEDURE PriceChange

@StartDate datetime,
@EndDate datetime,
@CustomerID nvarchar(10)

AS

CREATE TABLE #SalesPlanA (
CustomerID nvarchar(10),
CustomerName nvarchar(100),
InvoiceDate datetime,
InvoiceID nvarchar(20),
ItemID nvarchar(25),
ItemName nvarchar(100),
InvoiceQty float,
InvoiceAmt money,
BeginDatePrice money,
BeginDateBillings money,
PriceChange money,
PercentChange float,
Increases int,
Decreases int
)

--Initial population-

INSERT INTO #SalesPlanA(
CustomerID,
CustomerName,
InvoiceDate,
InvoiceID,
ItemID,
ItemName,
InvoiceQty,
InvoiceAmt
)

SELECT CUS.CUS_CustomerID AS INV_CustomerID,
CUS.CUS_BillName,
IVM_Header.IVM_InvoiceDate AS INV_InvoiceDate,
qryBillings_sub.INV_InvoiceID,
qryBillings_sub.INV_ItemID,
qryBillings_sub.IMA_ItemName,
IVI.IVI_TotalShipQty AS INV_InvoiceQty,
qryBillings_sub.INV_InvoiceAmt

FROM ((((((iERP601.dbo.IVM_Header IVM_Header WITH (NOLOCK)
INNER JOIN iERP601.dbo.CUS CUS WITH (NOLOCK) ON CUS.CUS_CustomerID = IVM_Header.IVM_CustomerID)
INNER JOIN iERP601.dbo.IVM_Shipment IVM_Shipment WITH (NOLOCK) ON IVM_Header.IVM_ID = IVM_Shipment.IVMSHIP_IVM_ID)
INNER JOIN iERP601.dbo.IVI IVI WITH (NOLOCK) ON IVM_Shipment.IVMSHIP_ID = IVI.IVI_IVMSHIP_ID)
INNER JOIN ( SELECT
IMA.IMA_ItemName,
INV.INV_ItemID,
Sum(INV.INV_InvoiceAmt) AS INV_InvoiceAmt,
INV.INV_InvoiceID,
INV.INV_CustomerID,
INV.INV_SOLineNbr,
INV.INV_SalesOrderID,
INV.INV_SalesmanName
FROM ((iERP601.dbo.SOM SOM WITH (NOLOCK)
INNER JOIN iERP601.dbo.SOI SOI WITH (NOLOCK) ON SOM.SOM_SalesOrderID = SOI.SOI_SalesOrderID)
INNER JOIN iERP601.dbo.INV INV WITH (NOLOCK) ON SOI.SOI_SalesOrderID = INV.INV_SalesOrderID
AND SOI.SOI_SOLineNbr = INV.INV_SOLineNbr
AND SOM.SOM_CustomerID = INV.INV_CustomerID
LEFT join iERP601.dbo.SOS SOS on INV_SalesOrderID = SOS_salesOrderID
and INV.INV_SOLineNbr = SOS.SOS_SoLineNbr
and INV.INV_RequiredDate = SOS.SOS_requiredDate
and SOS.SOS_InvoiceID = INV.INV_InvoiceID
LEFT JOIN iERP601.dbo.IMA IMA on INV.INV_ItemID = IMA.IMA_ItemID)
WHERE INV.INV_InvoiceDate Between @StartDate AND @EndDate
AND INV.INV_ItemID IS NOT NULL
AND (IMA.IMA_ItemTypeCode = 'Finished Good'
OR IMA.IMA_ItemTypeCode = 'Subassembly')
GROUP BY IMA.IMA_ItemName,
INV.INV_ItemID,
INV.INV_InvoiceID,
INV.INV_CustomerID,
INV.INV_InvoiceDate,
INV.INV_SOLineNbr,
INV.INV_SalesOrderID,
INV.INV_SalesmanName )
as qryBillings_sub ON IVM_Header.IVM_InvoiceID = qryBillings_sub.INV_InvoiceID
AND IVI.IVI_SOLineNbr = qryBillings_sub.INV_SOLineNbr
AND IVM_Shipment.IVMSHIP_SalesOrderID = qryBillings_sub.INV_SalesOrderID
AND IVM_Header.IVM_CustomerID = qryBillings_sub.INV_CustomerID)
LEFT JOIN iERP601.dbo.VEN VEN WITH (NOLOCK) ON IVI.IVI_Rep1ID = VEN.VEN_VendorID )
LEFT JOIN iERP601.dbo.EMP EMP WITH (NOLOCK) ON qryBillings_sub.INV_SalesmanName = EMP.EMP_EmployeeID)
WHERE IVM_Header.IVM_InvoiceDate Is Not Null
AND IVM_Header.IVM_ArchiveDate Is Null
AND IVM_Header.IVM_AuditFlag = 0
AND qryBillings_sub.INV_InvoiceAmt >= 0
AND CUS.CUS_CustomerID LIKE @CustomerID
ORDER BY qryBillings_sub.INV_InvoiceID

/*
UPDATE #SalesPlanA
SET BeginDatePrice = ISNULL((
SELECT TOP 1 PRQ.PRQ_UnitSalesPrice
FROM iERP601.dbo.PRM PRM WITH (NOLOCK)
INNER JOIN iERP601.dbo.PRQ PRQ WITH (NOLOCK) ON PRM.PRM_Counter = PRQ.PRQ_RecordID
WHERE PRM.PRM_ItemID = #SalesPlanA.ItemID
AND PRM.PRM_CustomerID = #SalesPlanA.CustomerID
AND ((@StartDate BETWEEN PRM.PRM_EffStartdate AND PRM.PRM_EffStopdate)
OR (@StartDate >= PRM.PRM_EffStartdate
AND PRM.PRM_EffStopdate IS NULL))
AND #SalesPlanA.InvoiceQty BETWEEN PRQ_BeginQty AND PRQ_EndQty
ORDER BY PRM.PRM_EffStartdate ASC
), 0)
*/

UPDATE #SalesPlanA
SET BeginDatePrice =
CASE
WHEN NOT EXISTS(
SELECT TOP 1 PRQ.PRQ_UnitSalesPrice
FROM iERP601.dbo.PRM PRM WITH (NOLOCK)
INNER JOIN iERP601.dbo.PRQ PRQ WITH (NOLOCK) ON PRM.PRM_Counter = PRQ.PRQ_RecordID
WHERE PRM.PRM_ItemID = #SalesPlanA.ItemID
AND PRM.PRM_CustomerID = #SalesPlanA.CustomerID
AND ((@StartDate BETWEEN PRM.PRM_EffStartdate AND PRM.PRM_EffStopdate)
OR (@StartDate >= PRM.PRM_EffStartdate
AND PRM.PRM_EffStopdate IS NULL))
AND #SalesPlanA.InvoiceQty BETWEEN PRQ_BeginQty AND PRQ_EndQty
ORDER BY PRM.PRM_EffStartdate)
THEN (SELECT TOP 1 PRQ.PRQ_UnitSalesPrice
FROM iERP601.dbo.PRM PRM WITH (NOLOCK)
INNER JOIN iERP601.dbo.PRQ PRQ WITH (NOLOCK) ON PRM.PRM_Counter = PRQ.PRQ_RecordID
WHERE PRM.PRM_ItemID = #SalesPlanA.ItemID
AND PRM.PRM_CustomerID = #SalesPlanA.CustomerID
AND #SalesPlanA.InvoiceQty BETWEEN PRQ_BeginQty AND PRQ_EndQty
ORDER BY PRM.PRM_EffStartdate ASC)

ELSE (SELECT TOP 1 PRQ.PRQ_UnitSalesPrice
FROM iERP601.dbo.PRM PRM WITH (NOLOCK)
INNER JOIN iERP601.dbo.PRQ PRQ WITH (NOLOCK) ON PRM.PRM_Counter = PRQ.PRQ_RecordID
WHERE PRM.PRM_ItemID = #SalesPlanA.ItemID
AND PRM.PRM_CustomerID = #SalesPlanA.CustomerID
AND ((@StartDate BETWEEN PRM.PRM_EffStartdate AND PRM.PRM_EffStopdate)
OR (@StartDate >= PRM.PRM_EffStartdate
AND PRM.PRM_EffStopdate IS NULL))
AND #SalesPlanA.InvoiceQty BETWEEN PRQ_BeginQty AND PRQ_EndQty
ORDER BY PRM.PRM_EffStartdate)

END

UPDATE #SalesPlanA
SET BeginDateBillings = BeginDatePrice * InvoiceQty

UPDATE #SalesPlanA
SET PriceChange = InvoiceAmt - BeginDateBillings

UPDATE #SalesPlanA
SET PercentChange = CASE
WHEN BeginDateBillings = 0 THEN 0
ELSE PriceChange / BeginDateBillings
END

UPDATE #SalesPlanA
SET Increases = (
SELECT COUNT(*)
FROM #SalesPlanA
WHERE BeginDateBillings < InvoiceAmt
)

UPDATE #SalesPlanA
SET Decreases = (
SELECT COUNT(*)
FROM #SalesPlanA
WHERE InvoiceAmt < BeginDateBillings
)

SELECT * FROM #SalesPlanA
WHERE ABS(PriceChange) > .02
ORDER BY CustomerID, ItemID, InvoiceID

DROP TABLE #SalesPlanA
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-20 : 01:35:56

Well it's hitting a ton of tables and doing a ton of work. There's really no way for us to help with it without a ton more detail. But due to the complexity, you'd probably need to hire a consultant to work on it as it looks like they'd need to be very familiar with the schema.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jmchristy
Starting Member

8 Posts

Posted - 2008-03-20 : 09:13:17

Thanks for the response!

What more information would you like? I can provide as much detail as I can.

You say it's pulling alot of information from many different tables, could it simply be a hardware limitation? We have quad 1.4ghz processors, 3GB of memory, and there are normally 65 or so users on the box at a time. The database which it is pulling data from is 16GB in size.

Looking at how slow the system has become, I have already recommended more powerful hardware to help speed things up.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-20 : 13:12:10

Even with all of the detailed information, it would take someone quite a bit of time to work up a solution. It's just not something that I can offer for free over the Internet as I just don't have that amount of free time.

Hardware may help a little, but the problem is with the stored procedure, database design, complexity of the system, or something else.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jmchristy
Starting Member

8 Posts

Posted - 2008-03-24 : 10:00:38

Thanks for the feedback!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources