This stored procedure needs to run faster

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

New to SQL Server Programming

This stored procedure needs to run faster

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-05-05 : 11:36:46

The app is getting the data via this SP via a dataservice. It works ok on the server, a bit slow but it works.
trying to access the app from outside host tho, it times out on the first customer run, and then it is ok.

USE [POS]
GO
/****** Object: StoredProcedure [dbo].[endAnalyzer] Script Date: 05/05/2010 11:33:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SpendAnalyzer]
@customername varchar(50) = NULL
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @NoDept table
(
custNumbr varchar(100)
)

-- Table to be populated with the last 24 months.
DECLARE @Months TABLE (
TheMonth DATETIME
)

DECLARE @TheMonth DATETIME = CONVERT(VARCHAR(7), GETDATE(), 126) + '-01T00:00:00'

DECLARE @I INTEGER = 0
WHILE @I < 24
BEGIN
INSERT INTO @Months VALUES (@TheMonth)
SET @TheMonth = DATEADD(MONTH, -1, @TheMonth)
SET @I += 1
END

insert into @NoDept
select 'IAD01'
insert into @NoDept
select 'IAH01'
insert into @NoDept
select 'NMMC02'
insert into @NoDept
select 'NMMC03'
insert into @NoDept
select 'NR01'
insert into @NoDept
select 'NM01'
insert into @NoDept
select 'NRH03'
insert into @NoDept
select 'M01'
insert into @NoDept
select 'BHCC02'

DECLARE @HasNoDept BIT = 0

IF EXISTS( SELECT RM00101.CUSTNMBR from RM00101 where CUSTNMBR iN (select custNumbr From @NoDept) and CUSTNAME = @customername)
BEGIN
SET @HasNoDept = 1
END

SELECT CASE

WHEN @HasNoDept <> 0 THEN @customername
ELSE SOP10106.USRDEF05
END AS Department,
SOP10200.XTNDPRCE As Expense,
SOP10200.QUANTITY as Quantity,
RM00101.CUSTNAME,
SOP10200.ITEMDESC as Description,
SOP10100.DOCDATE AS DateCreated,
IV40600.UserCatLongDescr as Category,
IV40600.USCATVAL,
dbo._FnGetBudgetAmount_Spend (SOP10100.DOCDATE, SOP10100.CUSTNMBR , IV40600.USCATVAL, SOP10106.USRDEF05) as Budget
,SOP10200.SOPNUMBE InvoiceNum ,
SOP10100.CUSTNMBR as custNum
INTO #temp
FROM dbo.SOP10200 AS SOP10200
INNER JOIN dbo.IV00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN dbo.SOP10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE
INNER JOIN dbo.RM00101 ON SOP10100.CUSTNMBR = dbo.RM00101.CUSTNMBR
INNER JOIN dbo.SOP10106 ON SOP10200.SOPTYPE = dbo.SOP10106.SOPTYPE AND SOP10200.SOPNUMBE = dbo.SOP10106.SOPNUMBE
INNER JOIN dbo.IV40600 on IV40600.USCATVAL = IV00101.ITMGEDSC

WHERE SOP10100.SOPTYPE = '1' AND
SOP10100.DOCDATE >= DATEADD (YYYY, - 2, GETDATE()) AND
SOP10100.DOCDATE <= GETDATE() AND
LTrim(RTrim(RM00101.CUSTNAME)) = @customername

SELECT Department,
Expense,
Quantity,
CUSTNAME,
Description,
DateCreated,
Category,
USCATVAL,
Budget,
InvoiceNum , custnum
FROM #temp

UNION ALL

SELECT DISTINCT
CASE
WHEN @HasNoDept <> 0 THEN @customername
ELSE Depts.Department
END AS Department,
0.00001 As Expense,
0 as Quantity,
RM00101.CUSTNAME,
'<No Items Purchased>' as Description,
MS.TheMonth,
IV40600.UserCatLongDescr as Category,
IV40600.USCATVAL,
dbo._FnGetBudgetAmount_Spend (MS.TheMonth, CB.CUSTNMBR , CB.USCATVAL, Depts.Department) as Budget,
'<No Orders Placed>' , CB.CUSTNMBR
FROM dbo.CustomerBudget CB
INNER JOIN dbo.RM00101 ON CB.CUSTNMBR = dbo.RM00101.CUSTNMBR
INNER JOIN dbo.IV40600 on IV40600.USCATVAL = CB.USCATVAL
CROSS JOIN (
SELECT DISTINCT Department
FROM #temp
) Depts
CROSS JOIN @Months MS
WHERE LTrim(RTrim(RM00101.CUSTNAME)) = @customername
AND NOT EXISTS (
SELECT *
FROM #temp T
WHERE T.Department
= Depts.Department
AND T.Category
= IV40600.UserCatLongDescr
AND DATEDIFF(MONTH, T.DateCreated, MS.TheMonth) = 0
)
AND dbo._FnGetBudgetAmount_Spend (MS.TheMonth, CB.CUSTNMBR , CB.USCATVAL, Depts.Department) > 0
ORDER BY DateCreated DESC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-05 : 14:24:33

Switch the table variable to a temp table. Table variables are notoriously slow. Add an index to the temporary table that helps with the queries.

If the first call is slow and then it speeds up after, it's because it's building the execution plan. That's just how it works. To work on this in other companies, they'll add the stored procedure call to a SQL Agent job so that the plan is in place.

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

Subscribe to my blog

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-05-05 : 15:08:20

thanks yes that makes a lot of sense.

quote:
Originally posted by tkizer

Switch the table variable to a temp table. Table variables are notoriously slow. Add an index to the temporary table that helps with the queries.

If the first call is slow and then it speeds up after, it's because it's building the execution plan. That's just how it works. To work on this in other companies, they'll add the stored procedure call to a SQL Agent job so that the plan is in place.

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

Subscribe to my blog

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-05-05 : 15:16:17

Do I just put '#' instead of the @ or is there more to this?

Kristen
Test

22859 Posts

Posted - 2010-05-05 : 15:19:42

Yeah, hat's prerty much it. the syntax is a bit different:


CREATE TABLE #TempTable
(
Col1 INT ...

DECLARE @TempTableVariable TABLE
(
Col1 INT ...

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-05-05 : 15:21:37

great, thanks!

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-05-05 : 22:31:17

I have done all of this, and still the app will time out the first time, from an outside. I changed the Stored proc to this:
first to stage the data. It can very well be the problem is in the Silverlight code somewhere.

USE [PBS]
GO
/****** Object: StoredProcedure [dbo].[SpendAnalyzer_Stage] Script Date: 05/05/2010 22:18:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[SpendAnalyzer_Stage]

AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

create TABLE #NoDept
(
custNumbr varchar(100)
)

-- Table to be populated with the last 24 months.
DECLARE @Months TABLE (
TheMonth DATETIME
)

DECLARE @TheMonth DATETIME = CONVERT(VARCHAR(7), GETDATE(), 126) + '-01T00:00:00'

DECLARE @I INTEGER = 0
WHILE @I < 24
BEGIN
INSERT INTO @Months VALUES (@TheMonth)
SET @TheMonth = DATEADD(MONTH, -1, @TheMonth)
SET @I += 1
END

insert into #NoDept
select 'IAD01'
insert into #NoDept
select 'IAH01'
insert into #NoDept
select 'NMMC02'
insert into #NoDept
select 'NMMC03'
insert into #NoDept
select 'NMR03'
insert into #NoDept
select 'NRH03'
insert into #NoDept
select 'NR01'
insert into #NoDept
select 'BHCC02'

SELECT CASE
WHEN ND.custNumbr IS NOT NULL THEN RM00101.CUSTNAME
ELSE SOP10106.USRDEF05
END AS Department,
SOP10200.XTNDPRCE As Expense,
SOP10200.QUANTITY as Quantity,
RM00101.CUSTNAME,
SOP10200.ITEMDESC as Description,
SOP10100.DOCDATE AS DateCreated,
IV40600.UserCatLongDescr as Category,
IV40600.USCATVAL,
dbo._FnGetBudgetAmount_Spend (SOP10100.DOCDATE, SOP10100.CUSTNMBR , IV40600.USCATVAL, SOP10106.USRDEF05) as Budget,
SOP10200.SOPNUMBE InvoiceNum,
SOP10100.CUSTNMBR
INTO #temp
FROM dbo.SOP10200 AS SOP10200
INNER JOIN dbo.IV00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN dbo.SOP10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE
INNER JOIN dbo.RM00101 ON SOP10100.CUSTNMBR = dbo.RM00101.CUSTNMBR
INNER JOIN dbo.SOP10106 ON SOP10200.SOPTYPE = dbo.SOP10106.SOPTYPE AND SOP10200.SOPNUMBE = dbo.SOP10106.SOPNUMBE
INNER JOIN dbo.IV40600 on IV40600.USCATVAL = IV00101.ITMGEDSC
LEFT JOIN #NoDept ND ON ND.custNumbr = RM00101.CUSTNMBR
WHERE SOP10100.SOPTYPE = '1' AND
SOP10100.DOCDATE >= DATEADD (YYYY, - 2, GETDATE()) AND
SOP10100.DOCDATE <= GETDATE()

DELETE FROM dbo.Staging_SpendAnalyzer

INSERT INTO dbo.Staging_SpendAnalyzer
SELECT RTRIM(Department) AS Department,
Expense,
Quantity,
RTRIM(CUSTNAME) AS CUSTNAME,
RTRIM(Description) AS Description,
DateCreated,
RTRIM(Category) AS Category,
RTRIM(USCATVAL) AS USCATVAL,
Budget,
RTRIM(InvoiceNum) AS InvoiceNum
FROM (
SELECT Department,
Expense,
Quantity,
CUSTNAME,
Description,
DateCreated,
Category,
USCATVAL,
Budget,
InvoiceNum
FROM #temp

UNION ALL

SELECT DISTINCT
CASE
WHEN ND.custNumbr IS NOT NULL THEN Custs.CUSTNAME
ELSE Depts.Department
END AS Department,
0 As Expense,
0 as Quantity,
RM00101.CUSTNAME,
'<No Items Purchased>' as Description,
MS.TheMonth,
IV40600.UserCatLongDescr as Category,
IV40600.USCATVAL,
dbo._FnGetBudgetAmount_Spend (MS.TheMonth, CB.CUSTNMBR , CB.USCATVAL, Depts.Department) as Budget,
'<No Orders Placed>'
FROM dbo.CustomerBudget CB
INNER JOIN dbo.RM00101 ON CB.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN dbo.IV40600 on IV40600.USCATVAL = CB.USCATVAL
CROSS JOIN (
SELECT DISTINCT Department
FROM #temp
) Depts
CROSS JOIN @Months MS
INNER JOIN (
SELECT DISTINCT CUSTNAME, CUSTNMBR
FROM #temp
) Custs
ON Custs.CUSTNMBR = RM00101.CUSTNMBR
LEFT JOIN #NoDept ND ON ND.custNumbr = Custs.CUSTNMBR
WHERE NOT EXISTS (
SELECT *
FROM #temp T
WHERE T.Department
= Depts.Department
AND T.Category
= IV40600.UserCatLongDescr
AND T.CUSTNMBR
= Custs.CUSTNMBR
AND DATEDIFF(MONTH, T.DateCreated, MS.TheMonth) = 0
)
AND EXISTS (
SELECT *
FROM #temp T
WHERE CUSTNMBR = Custs.CUSTNMBR
AND T.Department
= Depts.Department
)
AND dbo._FnGetBudgetAmount_Spend (MS.TheMonth, CB.CUSTNMBR , CB.USCATVAL, Depts.Department) > 0
) PreTrim

and then the app uses this sp:

USE [PBS]
GO
/****** Object: StoredProcedure [dbo].[SpendAnalyzer] Script Date: 05/05/2010 22:30:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- [dbo].[SpendAnalyzer] 'northern manor multicare center'
ALTER PROCEDURE [dbo].[SpendAnalyzer]
@customername varchar(50) = NULL
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT *
FROM dbo.Staging_SpendAnalyzer
WHERE CUSTNAME = @customername
ORDER BY DateCreated DESC

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-05-06 : 04:50:33

can you post the execution plan? and advise the volumes of data involves in each table/action (insert/deleteselect stage)?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-06 : 05:09:21

Also, can you post some run times. How long does it take to time out, and then how long does take to run on subsequent calls. I'd be surprised if compiling the execution plans causes that much of a difference.

If you drop and re-create the procedure, then run it from SSMS, does it take a long time to run? Does it still time out when called from the outside after that? If so, then the delay may be the dataservice spinning up.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-05-06 : 08:56:56

How can I get to the execution plan?

I had run the stage procedures last night - and these should run automatically after the Great Plains data is imported (see step 3 of the job that's on the server). Speed-wise it's still slow due to the volume of data going from server -> client - all of this data (in XML form) needs to be transferred - over a slow connection this is damning. the SQL side of things is as fast as it can get now, we're talking sub-second response (on SQL Server) for NMMC, and perhaps a few seconds for the worst-cast customer I came across - it does seem that the data transport is the bigger factor in terms of slowness, though. Co mpressing the data transferred from server -> client looks like it might result in 1/5 of the data being transferred - which would be a significant speed up. Decreasing the size of the data transferred would also help - there are some seemingly redundant columns in there - e.g. customer name, customer number - although with compression this redundancy should be dealt with automatically.

I think as well the problem is with the Data service.

sathiesh2005
Yak Posting Veteran

85 Posts

Posted - 2010-05-06 : 09:13:41

To get the execution plan, Click Show estimated execution plan icon or from the query menu. then run your procedure.

Regards,
Sathieshkumar. R

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-05-06 : 09:21:48

It says 50% cost on both I guess the function as well

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-05-06 : 09:50:52

I have this idea.
The app works by having a customer combo box and it just runs the first one there, not sure why that particular one is there but it happens to have a large amt of rows, 37,000.

If I could hard code to have it start on the smallest customer, maybe we would resolve this issue.
How can I tell the sp to start with customer A?

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-05-06 : 11:26:45

its working correctly now. I added a sort field on the customer table, called displayorder by. it has to be entered for new customers.
I am not sure what actually did the trick, the staging or the sorting. Unless it was on the hosters end but he hasn't emailed me.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-06 : 12:27:14

You should also consider removing the @Months table and instead read a table that contains the data. To do this, create a regular table and load it with all of the data that you'll ever need (perhaps 2 years back and then 10 years forward). Then in the stored procedure, you would join to it to get the 24 months that you need.

You should also add an index or maybe two indexes to the #temp table in the stored procedure. CUSTNMBR should be indexed and maybe even Department.

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

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2010-05-06 : 12:50:01

quote:
Originally posted by tkizer

Switch the table variable to a temp table. Table variables are notoriously slow.

I did not know (or have seen) that

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-06 : 13:09:47

quote:
Originally posted by X002548

quote:
Originally posted by tkizer

Switch the table variable to a temp table. Table variables are notoriously slow.

I did not know (or have seen) that

We had major performance issues with a stored procedure a while back. I worked with a Microsoft engineer (via DSE not PSS) to correct the issues. The first change we made was to get rid of the table variable and instead use a temporary table. I believe we added two indexes to it to support the queries in the stored procedure. This change made a drastic change in performance for us. We made other changes too, but this is the one with the biggest boost.

The problem with table variables is that you don't get statistics (this is the main issue).

From BOL:

quote:

Indexes cannot be created explicitly on table variables, and no statistics are kept on table variables. In some cases, performance may improve by using temporary tables instead, which support indexes and statistics.

I never use table variables in production code anymore. But you should be aware of this (from BOL):

quote:

table variables used in stored procedures cause fewer recompilations of the stored procedures than when temporary tables are used.

P.S.

DSE stands for dedicated support engineer. I am fortunate to work for a company that spends the money to get direct access to SQL Server experts at Microsoft. PSS is who you get when you submit a case under your Premier account. I use PSS for emergencies only, like Tuesday night at 11:30pm when I ran into major problems.

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

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources