Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Celko's SQL Stumper: The Data Warehouse Problem Expand / Collapse
Author
Message
Posted Monday, September 28, 2009 9:55 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 11:30 AM
Points: 223, Visits: 336
kevriley (9/28/2009)
Mark,

when I run your code I get some CPU and more logical reads - have you tried on a clean 'server'

i.e after restarting or running dbcc dropcleanbuffers & dbcc freeproccache

[please don't run these on a live production server]


Kev


Kev:

Yes, I did. The results I posted were running from an x86 desktop running SQL Express 2008. I put the sample table/data and my SQL statement on a 2005 development instance (dedicated SQL machine with 4 instances and >= 100 active connections) and got the following:

(391 row(s) affected)
Table 'DailySalesTotals'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 46, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 7 ms.

I have not tried restarting the SQL service on the desktop so I don't have a true "clean" server reading. Out of curiosity, what were your results?

MJM
Post #794584
Posted Monday, September 28, 2009 10:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:25 AM
Points: 2,684, Visits: 2,431
Your results:

(391 row(s) affected)
Table 'DailySalesTotals'. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 33ms.



versus Joe's original :

(391 row(s) affected)
Table 'DailySalesTotals'. Scan count 921, logical reads 1853, physical reads 2, read-ahead reads 14, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 29 ms


These running on SQL 2008 SP1 Developer Edition, on a Intel Core 2 Duo (1.86GHz), 1.5 GB RAM (i.e. personal dev environment on spare PC)


I was just so surprised you didn't even see any CPU!!


Kev
Post #794596
Posted Monday, September 28, 2009 11:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 2, 2009 11:45 AM
Points: 1, Visits: 4

I've gone for a solution that will work in any version of SQL Server. It will also give more information, as in reality, the sales Dept will want to know by how much it dropped and when the previous order was. It runs at exactly the same speed as Chris's solution on my machine. In order to comply with the competition, I've used Joe's original result and commented out my 'improved' version.


SELECT
CustomersLastTwoDates.customer_ID,
'dropped purchase amount on',
lastOrderDate
/* CustomersLastTwoDates.customer_ID,
'dropped purchase amount by',
previous.order_amt-CustomersLastTwoDates.order_amt,
'between',
previous.order_date,
'and',
lastOrderDate */
FROM
(SELECT
lastone.customer_ID,
lastone.order_date AS lastOrderDate,
lastone.order_amt,
DATEADD(DAY, -MIN(DATEDIFF(DAY,d.order_Date,lastone.order_date)),
lastone.order_date)AS penultimateOrderDate
FROM DailySalesTotals d INNER JOIN
(SELECT latest.customer_id, latest.order_date, latest.order_amt
FROM DailySalesTotals latest
INNER JOIN
(SELECT customer_ID,
[order_date] = MAX(Order_Date)
FROM DailySalesTotals
GROUP BY customer_ID) lastPurchase
ON latest.customer_ID= lastPurchase.customer_ID
AND latest.order_Date=lastPurchase.order_date) lastone
ON lastone.customer_ID= d.customer_ID
AND lastone.order_Date> d.order_date
GROUP BY lastone.customer_ID, lastone.order_date,lastone.order_amt
HAVING MIN(lastone.order_amt-d.order_amt)<0
) CustomersLastTwoDates
INNER JOIN DailySalesTotals previous
ON previous.customer_ID =CustomersLastTwoDates.customer_ID
AND previous.order_Date=CustomersLastTwoDates.penultimateOrderDate
WHERE CustomersLastTwoDates.order_amt < previous. order_amt

Ed: entry copied from Simple-Talk's version of the competition

Post #794639
Posted Monday, September 28, 2009 11:48 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 2:46 AM
Points: 575, Visits: 2,500
I've put all entrants so far into a test harness on my dev. server. They all give the same results, They all run at about the same speed.
Typical results are:

16ms Chris Howarth
16ms Celko
13ms Wm Brewer
16ms Mark Marinovic
46ms GSquared
16ms Peter Brinkhaus
30ms back_of_the_fleet

Although this is too short a time to be seriously measurable, I'd say that all but GSquared's routine run at approx the same speed. The table size is too small. I'll try a re-run with a larger sample. However, I reckon Joe isn't going to be focussed solely on performance in judging the solution, certainly when the entries are so closely matched, and it seems a bit unfair to judge on a table size different to the one we supplied. However, scalablilty is going to be an interesting factor to look at!



Best wishes,

Phil Factor
Simple Talk
Post #794647
Posted Monday, September 28, 2009 12:04 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:35 PM
Points: 22,992, Visits: 31,471
Question, is this only open to those who have SQL Server 2008? Unfortunately the code assumes so and does not work as is with SQL Server 2005.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #794661
Posted Monday, September 28, 2009 12:15 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 3:33 AM
Points: 278, Visits: 1,068
.
Post #794671
Posted Monday, September 28, 2009 12:16 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 2:46 AM
Points: 575, Visits: 2,500
No, this is open to all. Hopefully, (and it was our original intention) those with only 2005 will be at no disadvantage and even if you only have 2000, you should be able to compete. (It looks as if William's, for example will run on almost anything, and eliminates the nested subqueries that Joe didn't like in his original solution.) Yeah: we messed up slightly w1th the rules, which were taken for Joe's original generic rules for the stumpers. Blush.


Best wishes,

Phil Factor
Simple Talk
Post #794672
Posted Monday, September 28, 2009 12:29 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 3:33 AM
Points: 278, Visits: 1,068
Not sure if a second entry is allowed, but here's another version that uses pivoting, this time using SQL Server's PIVOT keyword.

Again the values of the most recent two orders are transposed before being compared:

SELECT  customer_id, 
' dropped purchase amount on ',
MAX(order_date)
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_date DESC) AS RowNumber,
customer_id,
order_date,
order_amt
FROM DailySalesTotals
) AS st
PIVOT
(SUM(order_amt)
FOR RowNumber IN ([1],[2])
) AS PivotTable
GROUP BY customer_id
HAVING MAX([1]) < MAX([2])
ORDER BY customer_id

As with the other pivoted examples a single table scan is performed.

Chris
Post #794680
Posted Monday, September 28, 2009 1:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 4:29 PM
Points: 43, Visits: 302
I didn't check to see if this has already been submitted, but here goes:

WITH
SalesOrder AS
(
SELECT
Customer_id,
Order_date,
Order_amt,
ROW_NUMBER() OVER (PARTITION BY Customer_ID ORDER BY order_date DESC) AS SalesOrder
FROM DailySalesTotals
)
SELECT
S1.Customer_id,
' dropped purchase amount on ',
S1.Order_date
FROM SalesOrder AS S1
INNER JOIN SalesORder AS S2 ON S2.Customer_id = S1.Customer_id
WHERE
S1.SalesOrder = 1
AND
S2.SalesOrder = 2
AND
S1.Order_amt < S2.Order_amt



Post #794727
Posted Monday, September 28, 2009 1:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 31, 2011 6:43 AM
Points: 3, Visits: 33
I have some code to post. But first I wanted to ask - how do you test the CPU time and logical reads? Sorry for my ignorance.
Post #794740
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse