Query Speed - 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 2005 Forums

Transact-SQL (2005)

Query Speed

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-06-22 : 04:52:41

Hi Guys

I have the following query which returns details on contacts held within our database:

DECLARE
@StartDate DATETIME
,@EndDate DATETIME

SET @StartDate = '2009-05-01'
SET @EndDate = '2009-05-08'

IF OBJECT_ID('tempdb..#MatchingCustomers') IS NOT NULL
dROP TABLE #MatchingCustomers

IF OBJECT_ID('tempdb..#OrdersAfterContact') IS NOT NULL
DROP TABLE #OrdersAfterContact

SELECT DISTINCT
c.CustomerID
,c.DateEntered
,c.FirstName
,c.LastName
,c.Email
,bic.Contact_point_data
,bic.CreatedDate
,(SELECT COUNT(ReceiptID) from dbo.Receipt WITH (NOLOCK) where dateentered > min(bic.CreatedDate) and CustomerID=c.CustomerID) AS 'TransactionCount'
INTO #MatchingCustomers
FROM dbo.Customer AS c WITH (NOLOCK)
JOIN dbo.BI_CustData AS bic
ON c.Email = bic.[Contact_point_data]
AND bic.CreatedDate BETWEEN @StartDate AND DATEADD(s,-1,DATEADD(d,1,@EndDate))
GROUP BY
c.CustomerID
,c.DateEntered
,c.FirstName
,c.LastName
,c.Email
,bic.Contact_point_data
,bic.CreatedDate

CREATE INDEX IDX_MatchingCustomers_CustomerID
ON #MatchingCustomers (CustomerID)

;WITH r_set
AS
(

SELECT
mc.CustomerID
,CONVERT(VARCHAR(10),mc.CreatedDate,103) AS 'CreatedDate'
,mc.FirstName
,mc.LastName
,mc.Email
,mc.DateEntered AS 'RegisteredDate'

,DATEDIFF(wk,mc.DateEntered,mc.CreatedDate) AS 'NumberOfWeeksSinceRegistration'

,r.ReceiptID
,r.DateEntered AS 'ReceiptDate'
,DATEDIFF(d,mc.CreatedDate,r.DateEntered) AS 'DaysFromContactToNextOrder'
,SUM(ri.PriceIncTax * ri.Quantity) AS 'NextOrderValue'
,mc.TransactionCount
,ROW_NUMBER() OVER(PARTITION BY mc.CustomerID, mc.CreatedDate ORDER BY r.DateEntered ASC) AS rn
FROM
#MatchingCustomers as mc

LEFT JOIN dbo.Receipt as r
ON mc.CustomerID = r.CustomerID
AND r.DateEntered > mc.CreatedDate

LEFT JOIN dbo.ReceiptItem AS ri
ON r.ReceiptID = ri.ReceiptID

GROUP BY
mc.CustomerID
,CONVERT(VARCHAR(10),mc.CreatedDate,103)
,mc.FirstName
,mc.LastName
,mc.Email
,mc.DateEntered
,r.ReceiptID
,r.DateEntered
,mc.CreatedDate
,mc.TransactionCount

)
SELECT
r_set.CustomerID
,CreatedDate
,UPPER(FirstName) AS 'FirstName'
,UPPER(LastName) AS 'LastName'
,Email
,RegisteredDate
,NumberOfWeeksSinceRegistration
,r_set.ReceiptID
,ReceiptDate
,DaysFromContactToNextOrder
,NextOrderValue
,TransactionCount

FROM
r_set

WHERE
rn = 1

GROUP BY
r_set.CustomerID
,CreatedDate
,UPPER(FirstName)
,UPPER(LastName)
,Email
,RegisteredDate
,NumberOfWeeksSinceRegistration
,r_set.ReceiptID
,ReceiptDate
,DaysFromContactToNextOrder
,NextOrderValue
,TransactionCount

ORDER BY
r_set.CustomerID
,CreatedDate
GO

The problem that I am having is that I have a sub query within #MatchingCustomers temp table creation, which is causing the the query to run slowly (on occasions over 5mins).

Does anyone have any ideas how i could speed things up?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 05:01:59

~~AND bic.CreatedDate BETWEEN @StartDate AND DATEADD(s,-1,DATEADD(d,1,@EndDate))~~

AND bic.CreatedDate => @StartDate
AND bic.CreatedDate < DATEADD(DAY, 1, EndDate)

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 05:05:10

The bold part in your query above creates a "triangular join", which is very costly.
Depending on your business rules, I doublt if can be done faster.

One thing though to speed things up is to remove the LEFT JOIN Receipt from the CTE to the first staging query.
That way you do not need to reference Receipt table twice.

E 12°55'05.63"
N 56°04'39.26"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-22 : 05:07:25

quote:
Originally posted by Peso

~~AND bic.CreatedDate BETWEEN @StartDate AND DATEADD(s,-1,DATEADD(d,1,@EndDate))~~

AND bic.CreatedDate => @StartDate
AND bic.CreatedDate < DATEADD(DAY, 1, EndDate)

E 12°55'05.63"
N 56°04'39.26"

Hi Peso!
Does it mean: Don't use BETWEEN?

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 05:16:25

[code]SELECT c.CustomerID,
c.DateEntered,
c.FirstName,
c.LastName,
c.Email,
bic.Contact_point_data,
bic.CreatedDate,
r.ReceiptID,
r.DateEntered,
COUNT(r.CustomerID) OVER (PARTITION BY c.CustomerID) AS TransactionCount
INTO #MatchingCustomers
FROM dbo.Customer AS c WITH (NOLOCK)
INNER JOIN dbo.BI_CustData AS bic ON bic.[Contact_point_data] = c.Email
LEFT JOIN dbo.Receipt AS r ON r.CustomerID = c.CustomerID
AND r.DateEntered > c.CreatedDate
WHERE bic.CreatedDate >= @StartDate
AND bic.CreatedDate < DATEADD(DAY, 1, @EndDate)
GROUP BY c.CustomerID,
c.DateEntered,
c.FirstName,
c.LastName,
c.Email,
bic.Contact_point_data,
bic.CreatedDate,
r.ReceiptID,
r.DateEntered

CREATE INDEX IDX_MatchingCustomers_CustomerID
ON #MatchingCustomers
(
CustomerID
)

;WITH r_set
AS (
SELECT mc.CustomerID,
CONVERT(VARCHAR(10),mc.CreatedDate,103) AS CreatedDate,
mc.FirstName,
mc.LastName,
mc.Email,
mc.DateEntered AS RegisteredDate,
DATEDIFF(wk,mc.DateEntered,mc.CreatedDate) AS NumberOfWeeksSinceRegistration,
mc.ReceiptID,
mc.DateEntered AS ReceiptDate,
DATEDIFF(d, mc.CreatedDate, mc.DateEntered) AS DaysFromContactToNextOrder,
SUM(ri.PriceIncTax * ri.Quantity) AS NextOrderValue,
mc.TransactionCount,
ROW_NUMBER() OVER(PARTITION BY mc.CustomerID, mc.CreatedDate ORDER BY mc.DateEntered ASC) AS rn
FROM #MatchingCustomers as mc
LEFT JOIN dbo.ReceiptItem AS ri ON ri.ReceiptID = mc.ReceiptID
GROUP BY mc.CustomerID,
CONVERT(VARCHAR(10), mc.CreatedDate, 103),
mc.FirstName,
mc.LastName,
mc.Email,
mc.DateEntered,
mc.ReceiptID,
mc.DateEntered,
mc.CreatedDate,
mc.TransactionCount
)

SELECT CustomerID,
CreatedDate,
UPPER(FirstName) AS FirstName,
UPPER(LastName) AS LastName,
Email,
RegisteredDate,
NumberOfWeeksSinceRegistration,
ReceiptID,
ReceiptDate,
DaysFromContactToNextOrder,
NextOrderValue,
TransactionCount
FROM r_set
WHERE rn = 1
ORDER BY CustomerID,
CreatedDate[/code]

E 12°55'05.63"
N 56°04'39.26"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-06-22 : 05:39:24

Hey thats GREAT!!!

Thanks!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 05:55:57

Does the query return the results you want?
Please post back the speed difference.

There are more things to do here for optimizing further.

E 12°55'05.63"
N 56°04'39.26"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-06-22 : 06:05:06

The results seem to be fine from what I can see so far - but still checking in more detail.

The speed was reduced to about 30 secs.

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-06-22 : 06:40:55

Hi

Just to follow on from my last post.

The TransactionCount field doesnt seem to be displaying the correct figure. It is meant to be displaying the number of transactions made since the created date.

Any ideas on what I can do?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 06:47:53

The Receipt table is LEFT JOINed on CustomerID and also filtered by DateEntered > DateCreated.
So when COUNTing the matching records in Receipt table, all NULL records are counted as zero, and all non-null records are counted as one each.

E 12°55'05.63"
N 56°04'39.26"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-06-22 : 07:06:50

Do u know what I could do to resolve this?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 07:10:38

I think you will have to post some sample data and expected output for us to debug with.

E 12°55'05.63"
N 56°04'39.26"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-06-22 : 08:25:40

CustomerID: 658
Count Given: 3
Contacted: 02/05/2009, 05/05/2009, 07/05/2009
Orders in May: 06/05/2009
Expected: 02/05/2009 = 1 05/05/2009 = 1 07/05/2009 =0

CustomerID: 398825
Contacted: 01/05/2009, 03/05/2009, 16/05/2009
Orders in May: 01/05/2009, 02/05/2009, 15/05/2009
Expected: 01/05/2009 = 3 03/05/2009 = 1, 16/05/2009 = 0

I hope this makes a little more sense.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 08:51:35

The windowed function for TransactionCount should have an additional column as this

COUNT(r.CustomerID) OVER (PARTITION BY c.CustomerID, c.DateEntered) AS TransactionCount

See this sample data

DECLARE	@Customers TABLE
	(
		CustomerID INT,
		DateCreated DATETIME
	)

INSERT	@Customers
SELECT	398825, '20090501' UNION ALL
SELECT	398825, '20090503' UNION ALL
SELECT	398825, '20090516'

DECLARE	@Orders TABLE
	(
		CustomerID INT,
		DateEntered DATETIME
	)

INSERT	@Orders
SELECT	398825, '20090501' UNION ALL
SELECT	398825, '20090502' UNION ALL
SELECT	398825, '20090515'

SELECT		c.*,
		COUNT(o.CustomerID) OVER (PARTITION BY c.CustomerID) AS oldPeso,
		COUNT(o.CustomerID) OVER (PARTITION BY c.CustomerID, c.DateCreated) AS newPeso,
		o.*
FROM		@Customers AS c
LEFT JOIN	@Orders AS o ON o.CustomerID = c.CustomerID
			AND o.DateEntered >= c.DateCreated