Silicon remarks

AMD Fusion - a response to your dreams?

2008-09-19T16:30:00.001+02:00

I've found today a very nice video. If you're AMD fan, enjoy this video.

How to manipulate data through time.

2008-03-06T21:33:00.012+01:00

Plenty of us are using in our databases Bit column as information if record is valid or not. But when you're selecting only valid records, you must filter table with Del=0 or something. So we have plenty of selects, which are using full scan thank to "Del" column low selectivity. Yes, we can delete records and all associated records in child tables if we want, but there two defects can arise.

First of them is absence of history related to our data collections. Second one is higher CPU and I/O load.
So I decided to choose two columns in my tables to localize records in time. First of them is column with GETDATE() default value (you know it probably :)), "CreateDate" name and nonclustered index on it, in descend order.
A several week ago I spoked with one software company owner about NULL values in columns. "Here is no reason to use delegated values instead of NULLs", he said. "When database engine comes with new version, here can be plenty of difficulties with those values.Why you have choosed this kind of mechanism?"
I thought, that seeking some values in datetime column would be faster then seeking columns which has NULL instead of value. Scanning NULLs is slower then scanning values as I noticed in my last article. I answered him that database engine always use index scan and never use index seek. "You're wrong", he said. "Server always use index scan."
I talked about it with my boss and he said: "Compare those two mechanisms".
So, I decided to write testing script. I create table with integer data type primary key with identity incrementation and two columns with date time data type. I insert about 1 million records into it and then I select all records with delegate values, records with null values and then I seek records in some random interval. Interval is random for every iteration for those two tests. With selecting range over NULL enabled column is more slower, because SARG went for a walk :).
Programmer, I have written about him in second paragraph was right and I was wrong with my decision. But when you select limited ranges of data, using delegate values will be always slower.

Note: You can make some steps to history, when you select data like this:

SELECT *
FROM dbo.Table
WHERE CreateDate <= @ParameterDate AND DeleteDate >= @ParameterDate

to select records which were valid on @ParameterDate.

Here you can download testing script.

To be with NULL or not to be with NULL

2008-01-21T14:57:00.029+01:00

with help from ISNULL and COALESCE functions.

From time to time you probably catch an idea about making better performance on your SQL Server. So, I mean SQL Server 2005 or "Yukon", as you wish.

I have read plenty of articles on the internet about performance related questions. Some authors say, that ISNULL is better and other say that COALESCE is speed like CASE vs. IF-THEN, for instance.

So I created a small SQL script to measure which method can be faster.

I ran this script on my machine, so results are better for ISNULL function. IMHO it's because COALESCE must hold datatype for each tested value.

Please also notice differences between selecting values from NULL-enabled and NON-NULL enabled columns with the same data types. I think, that NON-NULL columns will be always faster.

Function	Data type	NullValues Presence	MinCPUTime	MaxCPUTime	AvgCPUTime	Min Timelength [ms]	Max Timelength [ms]	Avg Timelength [ms]
COALESCE	int	AllColumnsNull	13.33	14.66	14.00	388.95	597.64	493.30
COALESCE	int	AllThreeNotNull	14.02	15.77	14.90	455.90	534.09	495.00
COALESCE	int	FirstColumnNull	15.06	17.93	16.50	509.25	624.54	566.90
COALESCE	int	LastColumnNotNull	13.16	15.23	14.20	457.96	568.63	513.30
COALESCE	varchar	AllColumnsNull	27.82	30.97	29.40	854.74	1287.25	1071.00
COALESCE	varchar	AllThreeNotNull	29.65	32.54	31.10	972.10	1301.89	1137.00
COALESCE	varchar	FirstColumnNull	33.50	39.09	36.30	1075.53	1567.66	1321.60
COALESCE	varchar	LastColumnNotNull	31.36	34.03	32.70	1029.25	1169.94	1099.60
ISNULL	int	AllColumnsNull	11.16	12.43	11.80	350.55	494.64	422.60
ISNULL	int	AllThreeNotNull	9.53	10.66	10.10	245.90	526.09	386.00
ISNULL	int	FirstColumnNull	11.16	12.63	11.90	367.54	419.65	393.60
ISNULL	int	LastColumnNotNull	11.28	13.11	12.20	367.91	463.48	415.70
ISNULL	varchar	AllColumnsNull	24.79	26.20	25.50	825.06	1103.33	964.20
ISNULL	varchar	AllThreeNotNull	19.64	21.75	20.70	585.75	965.64	775.70
ISNULL	varchar	FirstColumnNull	25.36	26.83	26.10	808.47	984.52	896.50
ISNULL	varchar	LastColumnNotNull	28.47	30.12	29.30	929.79	1095.00	1012.40