Tag: sql

Effective Tree Structures In Relational Databases

Joe Celko explains the Nested Set Model. It is an alternative way of storing tree-structured data in relational databases. The main benefit is that it allows you to make queries that would be hard or impossible if you follow the classic approach i.e. storing the parent id in each node. One example: with the nested set model it is easy to select all children recursive of a given node.

Fog Creek Software uses this approach in FogBugz.

How to benchmark queries on Sql Server

Recently I had to optimize some SQL-queries on Sql Server 2005. Therefore I used  Sql Server Management Studio.
I run into the following two problems:

  1. The resolution of the timer is one second. Thats too inaccurate.
  2. The cache in Sql Server optimized subsequent runs of the same query. So i couldn’t tell if I improved the query or made it worse.

If found the following solution for both problems on http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic13664.aspx. Just insert the query you want to tune below the comment and you can run your query always under the same preconditions.

CHECKPOINT
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
go
DECLARE @Started datetime, @Ended datetime
SET @Started = CURRENT_TIMESTAMP

— insert query to tune here

SET @Ended = CURRENT_TIMESTAMP
SELECT DATEDIFF(ms, @Started, @Ended)
go