Performance Issue - 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 2000 Forums

SQL Server Development (2000)

Performance Issue

Author

Topic

kdeconto
Starting Member

5 Posts

Posted - 2002-12-02 : 14:17:01

I have developed an online bulletin board and it is very simple so there are only 2 tables. The table in question is the one where one of the 10 fields gets the value of GETDATE() about 90% of the time.

Would it have been more efficient to create the DATETIME field, have it not accept NULLs, and have a default value of GETDATE()?

baldeep
Starting Member

18 Posts

Posted - 2002-12-02 : 15:20:47

I'm not too smart about how SQL server works so I had to try it both ways. It looks like using a default is about 5% faster. (I guess the qualitative result isn't much surprise.) Here's the script I used for my little empirical test. I've noticed there are plenty of SQL Server gurus on this board so I'd love to hear a critique of my testing methods...

And if anyone wants to get crazy I wouldn't mind seeing an analysis of how advantage changes vs. different loop sizes (I've only tested 10000 records here).


DROP TABLE DateTableDefault
GO

CREATE TABLE DateTableDefault ( --Tbl with default value.
	Id INT IDENTITY(1, 1) PRIMARY KEY,
	DateValue DATETIME DEFAULT GETDATE(),
	FakeData VARCHAR(50) NULL)
GO

DECLARE
	@ctr INT,
	@start DATETIME
SET @start = GETDATE()
SET @ctr = 10000

WHILE @ctr > 0
BEGIN
	IF @ctr % 10 = 0 --Use the modulo to get simulate "90%"
		INSERT INTO DateTableDefault (DateValue, FakeData) VALUES ('12-01-2009', CAST(RAND() AS VARCHAR(50)))
	ELSE
		INSERT INTO DateTableDefault (FakeData) VALUES (CAST(RAND() AS VARCHAR(50)))
	SET @ctr = @ctr - 1
END

PRINT DATEDIFF(ms, @start, GETDATE()) --8703 milliseconds is the result.

DROP TABLE DateTableNoDefault
GO

CREATE TABLE DateTableNoDefault ( --Tbl with no default date value.
	Id INT IDENTITY(1, 1) PRIMARY KEY,
	DateValue DATETIME NULL,
	FakeData VARCHAR(50) NULL)
GO

DECLARE
	@ctr INT,
	@start DATETIME
SET @start = GETDATE()
SET @ctr = 10000

WHILE @ctr > 0
BEGIN
	IF @ctr % 10 = 0
		INSERT INTO DateTableNoDefault (DateValue, FakeData) VALUES ('12-01-2009', CAST(RAND() AS VARCHAR(50)))
	ELSE
		INSERT INTO DateTableNoDefault (DateValue,FakeData) VALUES (GETDATE(), CAST(RAND() AS VARCHAR(50)))
	SET @ctr = @ctr - 1
END

PRINT DATEDIFF(ms, @start, GETDATE()) --9273 ms is the result

--Baldeep

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources