Badly written SP need help - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Badly written SP need help

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2015-03-24 : 08:27:51

Hi,

IU got this SP that returns the amount of customers that registered that month and did not have a sucessfull order (status=8)
the Changedate is filed that had the registration date.

ALTER PROCEDURE [dbo].[SP_STATS_GET_TOTAL_new_customer_with_NO_orders]
@totals as nvarchar(100) output,
@site as varchar(50),
@month as CHAR(2),
@year as CHAR(4)
AS

DECLARE @ADate DATETIME
DECLARE @amountdaysinmonth int

SET @ADate = @year +'-' + @month +'-01'

set @amountdaysinmonth=DAY(EOMONTH(@ADate))

SELECT @totals=COUNT(distinct T_Order_Main.CustomerID)
FROM dbo.T_Customer INNER JOIN
dbo.T_Order_Main ON dbo.T_Customer.CustomerID = dbo.T_Order_Main.CustomerID
WHERE (dbo.T_Order_Main.Orderstatus <> 8) and
dbo.T_Customer.changedate BETWEEN cast(@year as varchar) + '-' + cast(@month as varchar) + '-01 00:00:00' AND cast(@year as varchar) + '-' + cast(@month as varchar) +'-'+ CAST(@amountdaysinmonth AS varchar) +' 23:59:59'

AND (dbo.T_Customer.site = @site)
AND NOT T_Order_Main.CustomerID IN
(SELECT distinct T_Order_Main.CustomerID
FROM dbo.T_Customer INNER JOIN
dbo.T_Order_Main ON dbo.T_Customer.CustomerID = dbo.T_Order_Main.CustomerID
WHERE (dbo.T_Order_Main.Orderstatus = 8) AND
dbo.T_Customer.changedate BETWEEN cast(@year as varchar) + '-' + cast(@month as varchar) + '-01 00:00:00' AND cast(@year as varchar) + '-' + cast(@month as varchar) +'-'+ CAST(@amountdaysinmonth AS varchar) + ' 23:59:59'

AND (dbo.T_Customer.site = @site))

Thanks a lot

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-24 : 08:52:19

No idea what you want to do here. Does the proc work? If so, what's the problem? If not, how does it fail?

Kristen
Test

22859 Posts

Posted - 2015-03-24 : 10:43:27

[code]
dbo.T_Customer.changedate
BETWEEN cast(@year as varchar) + '-' + cast(@month as varchar) + '-01 00:00:00'
AND cast(@year as varchar) + '-' + cast(@month as varchar) +'-'+ CAST(@amountdaysinmonth AS varchar) +' 23:59:59'
[/code]
Will have lousy performance, and there is probably one millisecond at the end of the month that will escape this test

Change it to
[code]
dbo.T_Customer.changedate >= @StartDate AND dbo.T_Customer.changedate < @EndDate
[/code]
Precalculate both @StartDate and @EndDate because if you leave a complex formula in the WHERE clause there is a high chance that SQL will not be able to use any suitable index.

Don't fiddle about with string dates with punctuation like "-" because if the server locale setting changes, or the user connects with a different LANGUAGE setting, or a host of other such things your assumptions about date will fail. The only valid string date to use which is guaranteed UNambiguous is 'yyyymmdd' or ISO format 'yyyy-mm-ddThh:mm:ss'
[code]
SELECT @StartDate = CONVERT(datetime, @year + @month + '01'),
@EndDate = DATEADD(Month, 1, @StartDate)
[/code]

mike13
Posting Yak Master

219 Posts

Posted - 2015-03-24 : 10:43:51

Sorry it performs real slow!

Kristen
Test

22859 Posts

Posted - 2015-03-24 : 10:46:25

quote:
Originally posted by mike13

Sorry it performs real slow!

My suggestion, above, on Date Range test may help.

If not: what indexes do you have on the tables?

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-03-24 : 13:20:30

[code]
ALTER PROCEDURE [dbo].[SP_STATS_GET_TOTAL_new_customer_with_NO_orders]
@totals as nvarchar(100) output,
@site as varchar(50),
@month as CHAR(2),
@year as CHAR(4)
AS
SET NOCOUNT ON;

SELECT @totals = COUNT(*)
FROM dbo.T_Order_Main
WHERE CustomerID IN (
SELECT CustomerID
FROM dbo.T_Customer
WHERE
changedate >= @year + RIGHT('0' + @month, 2) + '01' AND
changedate < DATEADD(MONTH, 1, CAST(@year + RIGHT('0' + @month, 2) + '01' AS date)) AND
site = @site
)
GROUP BY CustomerID
HAVING MAX(CASE WHEN Orderstatus = 8 THEN 1 ELSE 0 END) = 0 --verify no successful order
AND MAX(CASE WHEN Orderstatus <> 8 THEN 1 ELSE 0 END) = 1 --Edit: verify unsuccessful order

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources