Tuesday, July 15, 2014 - 3:20am

Thank you Itzik for this excellent walk-through!

Thursday, February 6, 2014 - 7:30am

See my set-based solution in second part of this article.
1.5 seconds and 14 000 reads.

Thursday, February 6, 2014 - 6:25am

Hi Itzik. My solution uses 14000 reads and executes in about 1.5 seconds.
My solution is a variant of Relational Division; Ordered Relational Division.

DECLARE @Items INT = (SELECT...

Thursday, February 6, 2014 - 6:23am

Hi Itzik.

The problem is just another Relational Division; Ordered Relational Division. My solution finishes in just 1.5 seconds and uses about 14000 reads.

DECLARE @Items INT = (...

Monday, November 21, 2011 - 2:57am
I use it to enforce data integrity in our data warehouse. Currently there are two business systems feeding the DW with data, and some tables (like Orders.SalesDetail) containts data from both systems...
Monday, August 22, 2011 - 2:00pm
Itzik, let me know if you got my script by email. //Peter
Friday, July 15, 2011 - 2:32am
Test done on production server. -- Celko #1 (181 row(s) affected) Table 'Worktable'. Scan count 0, logical reads 0. Table 'dimDate'. Scan count 7, logical reads 7. Table '...
Thursday, July 14, 2011 - 11:34pm
Not bad Mr Celko. Not bad at all. It's clever to build a "dual date" cte holding both yesterday's and today's dates. Add "sum(today_flg) as NumVisits," to the final...
Thursday, July 14, 2011 - 11:31pm
Not bad Mr Celko. Not bad at all. It's clever to build a "dual date" cte holding yesterday's and today's dates. Add "sum(today_flg) as NumVisits," to the final output...
Wednesday, July 13, 2011 - 11:15pm
You're welcome. See you for next challenge.
Saturday, July 9, 2011 - 7:09pm
Holy smoley! Only 1 scan count and 344 reads. ;WITH cteSource(dt, NumVisits, Added, Removed, Remained) AS ( SELECT dt, SUM(NumVisits) AS NumVisits, SUM(NumVisits) - SUM(Remained) AS Added...
Saturday, July 9, 2011 - 2:44pm
Thank you . That is what I suspected. I get similar timings on my dualcore laptop. 3108 ms duration vs 943 ms (1934 ms CPU vs 1732 ms). On my 16-core server I get this timings; 1470 ms duration vs...
Saturday, July 9, 2011 - 5:57am
AMB, can you test my #6 on your machine?
Friday, July 8, 2011 - 8:03am
This is the last one. This runs in 0.1 second on my server while the featured #3 runs in 1.4 seconds. Enjoy! -- SwePeso #6 ;WITH cte AS ( SELECT f.dt, f.NumVisits, f.Visitor, f.Added...
Friday, July 8, 2011 - 7:31am
Here is a better solution, #5. Almost the same number of reads, twice the CPU and 1/10th the duration of the featured #3 query. ;WITH cte AS ( SELECT f.dt, f.NumVisits, f.Visitor, f.Added...
Friday, July 8, 2011 - 6:55am
Here is another approach from me. It uses twice the CPU as my previous solution, 300 times more reads but finishes in almost a 1/10th of the time of my previous version. This suggestion heavily...
Thursday, July 7, 2011 - 10:01pm
Thank you Alejandro. I hope you liked the solution description. The matching section placeholders in code seem to be missing.
Wednesday, July 6, 2011 - 11:47am
Here is my stats with 100,000+ row sample data using same date interval as original (8 row(s) affected) Table 'Worktable'. Scan count 0, logical reads 0. Table 'DailyVisits'. Scan...
Wednesday, July 6, 2011 - 12:51am
Studiomoderna, give it a new try. Think about it, is there a way to minimize the number of joins? Is there another way to get rid of the distinct aggregate?
Thursday, June 30, 2011 - 5:23pm
Here is a teaser for my solution. -- Peso Table 'DailyVisits'. Scan count 1, logical reads 2
Thursday, June 30, 2011 - 5:23pm
Here is a teaser for my solution. Table 'DailyVisits'. Scan count 1, logical reads 2
Friday, March 25, 2011 - 1:12am
Where is my #3? ;-)

Tuesday, March 1, 2011 - 5:17pm
It would be a really nice feature if this could be combined with this request

http://connect.microsoft.com/SQLServer/feedback/details/470881/allow-more-than-one-resultset-to-be-stored-with-...
Monday, February 21, 2011 - 2:57am
Not much activity here...

Tuesday, February 8, 2011 - 5:58pm
Wow! A 14 second solution.
Maybe it's time to reveal "Itzik 1"? :-)




Thursday, January 20, 2011 - 8:13pm
Does set-based mean "one-query solution", or just the absence of a cursor?

I have a solution that runs in 61-63 second and uses 1,116,205 reads but have 3 queries; one insert, one merge and...
Tuesday, August 17, 2010 - 1:58pm
My bad. Just realized clustered index is over travelerid and travelstart (not milestraveled).

;WITH cteTravel(TravelerID, Destination, TotalMiles)
AS (
SELECT TravelerID,
...
Tuesday, August 17, 2010 - 10:55am
;WITH cteTravel(TravelerID, Destination, TotalMiles)
AS (
SELECT TravelerID,
Destination,
MAX(MilesTraveled) - MIN(MilesTraveled) AS TotalMiles...
Wednesday, June 2, 2010 - 12:45am
Hre we go. For the published solutions above, only "Itzik Loop", "Peso Loop" and "Itzik Recursive 2" passed before the 30 minute mark on our Data Warehouse, which has 160 tables. I used the "anchor"...
Tuesday, June 1, 2010 - 11:01pm
I'll test them all on the data warehouse (with 160+ tables) later this morning.
Earlier I only measured the duration for my and Itzik's solutions.

In short, the recursive CTE's (except...
Upcoming Conferences

Register now to get the best rate available!

Digital Magazine Archives

Browse back issues of SQL Server Pro, from January 2007 through the last issue published in April 2014. Find the back issues here.

 

From the Blogs
Two women looking a man's computer monitor
Sep 17, 2014
Article

AlwaysOn Availability Groups and SQL Server Jobs, Part 6: High-Level Options and Rationale for Handling Batch Jobs

There are a couple of options for how we could handle batch jobs when Availability Groups are thrown into the mix....More
Sep 12, 2014
Sponsored

Managing a Healthy SQL Server Database: Three Principles We Overlook

In managing a database or sometimes numerous databases, we often get caught up in the day-to-day maintenance and lose sight of the other principles we know are critical to ensuring optimal performance. Here’s a quick refresher....More
Exclamation marke on yellow road sign
Sep 9, 2014
blog

AlwaysOn Availability Groups and SQL Server Jobs, Part 5: Setting Up Failover Alerts

Alerts represent a great way to take a proactive stance on being notified or alerted when something ugly, potentially ugly, or important happens on a SQL Server. On most servers that I manage, I typically deploy a whole bevy of different alerts....More
SQL Server Pro Forums

Get answers to questions, share tips, and engage with the SQL Server community in our Forums.

Sponsored Introduction Continue on to (or wait seconds) ×