Database Programming: Game, Set, Match -- Why Writing Code In Transact-SQL Is Different Than Writing In A Compiled Language

re: Database Programming: Game, Set, Match -- Why Writing Code In Transact-SQL Is Different Than Writing In A Compiled Language

Hugo Kornelis — Wed, 20 Sep 2006 10:50:55 GMT

Hi Ward,

Yep, I think I can do faster.

Five subsequent executions of your code on my sandbox, all with clean cache and buffers, took 890, 906, 940, 1030, and 906 ms respectively. (I discarded the very first execution; that one took about 3 seconds, but I think that the database files had to grow).

I then tested the code posted below and got 813, 796, 810, 1000, and 846 ms on 5 subsequent executions. I can't really explain the one with 1000 ms - maybe CPU or I/O conflicts with one of the other processes running on my box?

Anyway, here's the code. Note that I used the same "surrounding" code to measure performance of both your and my versions. Also note that I tested on an empty database; if yoou have a database with at least 1,000 rows in sysobjects, you'll only need two cross-joins of said table.

drop table SomeTable;
go
checkpoint;
dbcc freeproccache;
dbcc dropcleanbuffers;
go
declare @start datetime;
set @start = getdate();
select top (1000000) newid() as uuid
into SomeTable
from sysobjects a, sysobjects b, sysobjects c, sysobjects d;
select datediff(ms, @start, getdate());
go
select count(*) from SomeTable;

Best regards,

Hugo Kornelis

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

Thu, 21 Sep 2006 10:06:56 GMT

PingBack from http://blogs.technet.com/wardpond/archive/2006/09/20/457856.aspx

The prime number challenge – great waste of time!

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

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

Denis Offers A Challenge

Ward Pond's SQL Server blog — Thu, 19 Jul 2007 00:17:54 GMT

" Denis the SQL Menace " is at it again. When we last heard from Denis on a technical matter in this