Database Programming: Set-Based Update -- Hugo's Syntax Outperforms Shaun's; Ward Tweaks Hugo's Syntax Even Further

re: Database Programming: Set-Based Update -- Hugo's Syntax Outperforms Shaun's; Ward Tweaks Hugo's Syntax Even Further

Denis the SQL Menace — Thu, 21 Sep 2006 13:16:14 GMT

How about the next challenge is to return all 78498 prime numbers between 1 and 1000000?

Ward Pond's SQL Server blog : Database Programming: Denis' Prime Number Challenge

Sat, 23 Sep 2006 12:49:37 GMT

PingBack from http://blogs.technet.com/wardpond/archive/2006/09/23/458344.aspx

re: Database Programming: Set-Based Update -- Hugo's Syntax Outperforms Shaun's; Ward Tweaks Hugo's Syntax Even Further

Hugo Kornelis — Sun, 24 Sep 2006 00:34:45 GMT

Hi Ward,

What surprises me, is that the join of two 1,000-row tables outperforms the version with the single 1,000,000-row setbuilder table. I would have expected the latter to be the fastest. Have you already tried a three-way join of three 100-row tables?

(I would have tried it myself, but I can't; I'm busy writing a blog entry with my response to Denis' prime number challenge. I'll put it up shortly.)

Best, Hugo

The prime number challenge – great waste of time!

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' — Sun, 24 Sep 2006 01:56:01 GMT

No sane person would even consider using SQL Server to construct a list of prime numbers. So just to...

re: Explicit does seem to beat CTE in my experience as well

Marc Brooks — Sun, 24 Sep 2006 22:52:30 GMT

I discovered this when playing with the CTE version of date-range generation. What I ended up with is something that seems much faster and generates a very simple query plan by doing explicit date math. Check out this: http://musingmarc.blogspot.com/2006/07/need-date-range-in-sql-without-filling.html

Ward Pond's SQL Server blog : Database Programming: Prime Number Methodology

Ward Pond's SQL Server blog : Database Programming: Prime Number Methodology — Wed, 04 Oct 2006 04:36:15 GMT

PingBack from http://blogs.technet.com/wardpond/archive/2006/10/03/Database-Programming_3A00_-Prime-Number-Methodology.aspx