Wednesday, February 08, 2012

5 years later... and I still hate oracle.

Tuesday, June 19, 2007

A New Beginning!

I have spent most of the past 7 years developing applications driven by SQL Server database and considered myself an expert at database development. Unfortunately, it is hard to be an database programming expert when one knows little about the #1 database on the market.

As a result I recently started a new job that uses Oracle. For the past 3 months I have been working for a large cell-phone/pager manufacturer located in Canada. I have found the experience interesting. As I have said before to co-workers 'Oracle is not SQL Server'.

The purpose of this blog is to talk about all things related to database systems. In particular I want to talk about the differences between Oracle and SQL Server. Both have their strengths and their weaknesses. I will try to be impartial but, as you will probably see, I think SQL Server is the database platform for me.

Saturday, September 09, 2006

Extending the Visual Studio Typed Dataset

Sorry for the very long delay between posts. I have been rather busy working in C# and much less in T-SQL. Since this blog came out of my annoyance with the lack of knowledge of T-SQL in the development world I have not had a whole lot to say. However, my work with C# was for a database front end application so my flow of ideas is not completely dry.

The most interesting thing that I have been working on is an extension to the default typed dataset for visual studio 2005. By extension I mean a module that expands on the functionality of the typed dataset without having any effect on the typed dataset's code or core functionality. The goal is to allow the programmer to simply add this module and, magically, the typed dataset does a whole bunch of new 'cool' things.

These are the 'cool' things that I would like to extend the typed dataset to be able to do:

1. Load all tables from database
2. Save all tables to a database
3. Undo change
4. Redo change
5. Save current changes to a file
6. Load current changes from a file

Sounds pretty simple right. Unfortunately, it is surprisingly difficult to accomplish, especially point #2.

Now at this point some braniac is going to say: 'What does all this have to do with T-SQL?' The answer is not a lot. However, if you are doing T-SQL programming you are probably using C# or Visual Basic programming so I think this is relevant.

So, for the next couple of months I am going to try and post once a week on this subject.

Monday, June 05, 2006

Interesting Performance Problem Part II

In my last post I discussed a problem with a slow running query. In this post I will explain why.

The answer to the problem can be seen if you look at the execution plan for the query. To do this from query analyzer choose the query --> Show Execution Plan from the top menu. Alternatively execute 'set showplan_Text ON'. This will cause the output to display the execution plan not the results.

Once this is done you will see the following:
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Spitfire].[dbo].[Users]))
|--Index Scan(OBJECT:([Spitfire].[dbo].[Users].[IX_Users_2]), WHERE:(Convert([Users].[IsValid])=1))

See the problem. The compiler is casting the [Users].[IsValid] to an integer to perform the comparison to the value of 1. What this means is that, for each and every row in the table the value has to be read, converted an int and then evaluated against the integer 1. The sort of the index is useless and performance will be slow. A simple change to the query will resolve this.

SELECT * from Users WHERE IsValid = CONVERT(bit,1)

This will make the compiler convert the integer value to a bit first, and then compare it to the column in the table. This means that only one conversion is executed and the index may be used. The resulting execution plan is:
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Spitfire].[dbo].[Users]))
|--Index Seek(OBJECT:([Spitfire].[dbo].[Users].[IX_Users_2]), SEEK:([Users].[IsValid]=1) ORDERED FORWARD)

This will use an index seek, not an index scan. A much faster and much more efficient plan for our purposes. Now, the same query will be virtually instantaneous about 1ms. MUCH BETTER.

Interesting Performance Problem:
I recently had an interesting problem that stemed from an implicit conversion doing something really stupid.

Here is a reproduction of the scenerio.

I have a table Users with the following columns
ID int identity
FirstName varchar(50)
MiddleName varchar(50)
LastName varchar(50)
IsValid bit

The table has around 2,000,000 records in it, three of which have the IsValid set to true. Each column has an index on it.

When I do the following query performance is poor

SELECT * from Users WHERE IsValid = 1

It takes about 200ms to execute. This is way too slow.

I will discuss why and how to fix in my next post (which I promise will be much sooner.)

Note: Tests were run using SQL Server 2000, SQL Server 2005 does much better.

Tuesday, May 16, 2006

.Net Data Access
If you are going to be using SQL Server, you are probably using it with Microsoft .net development tools. The data access components in .Net grew up from their VB roots and are powerfull, flexible and easy to use. Unfortunatly, like most other things, they can be abused as well.

Fortunatly, Microsoft and it's community have developed some seriously idiot proof tools. The Microsoft Data Access Application Block and the Enterprise Library make calling stored procedures as easy as pie. If you are writting data access routines in C# or Visual Basic and are not using these then you should really have a good look. I see to many people creating data adaptors, commands and connections in code when they are all wrapped up in these libraries. They can be found at Application Blocks.

The other tool of note is the built in dataset designer in Visual Studio. By simply dragging tables from there server explorer to a dataset it creates a nice, type-safe dataset including table adaptors. Table adaptors as generated by Visual Studio are great although they are much more geared to Client-Server 2-tier development and not proper 3-tier or web development. Having said that they can still be used for this and are quite neat.

Monday, May 01, 2006

Optional items in a Where clause

Here is a common situation. You have a query that has several possible but optional filters. For example you may filter on the first name or last name of both. Now to do this there are several possible technics. You could
1. assemble a dynamic SQL statement, you could
2. use the if statment to execute a different select statement or
3. use conditions in the where clause to handle it in one statement

Dynamic SQL
CREATE PROCEDURE Dynamic_SQL @FirstName nvarchar(100), @Lastname nvarchar(100)

AS

DECLARE @sql nvarchar(1000)

SELECT @sql = 'SELECT * FROM Users u WHERE 1=1 '

IF @FirstName IS NOT NULL

BEGIN

SELECT @sql = @sql + ' AND FirstName =''' + @Firstname + ''''

END



IF @LastName IS NOT NULL

BEGIN

SELECT @sql = @sql + ' AND LastName =''' + @Lastname + ''''

END



print @SQL

EXEC sp_executesql @sql


The above proc does what it is supposed to do but it has a couple of drawbacks. Firstly it lacks elegance. Secondly it totally defeats the whole purpose of using a stored procedure. The dynamic SQL requires a compile, there is a significant security risk and lastly it is really hard to follow. If you are going to do this you might as well just do it in the compiled VB/C# code.

Use a Where Clause
CREATE PROCEDURE IF_SQL @FirstName nvarchar(100), @Lastname nvarchar(100)

AS

IF @FirstName IS NOT NULL AND @LastName IS NOT NULL

BEGIN

SELECT * FROM Users u WHERE FirstName = @FirstName AND LastName = @LastName

END



IF @FirstName IS NULL AND @LastName IS NOT NULL

BEGIN

SELECT * FROM Users u WHERE LastName = @LastName

END



IF @FirstName IS NOT NULL AND @LastName IS NULL

BEGIN

SELECT * FROM Users u WHERE FirstName = @FirstName

END



IF @FirstName IS NULL AND @LastName IS NULL

BEGIN

SELECT * FROM Users u

END


Again this works but, as you can imagine, any more than two paramaters and it get unwieldy. Also, the if clause can force a recompile.


WHERE Clause
ALTER PROCEDURE Where_SQL @FirstName nvarchar(100), @Lastname nvarchar(100)

AS

SELECT * FROM Users u

WHERE (FirstName = @FirstName OR @FirstName IS NULL)

AND (LastName = @LastName OR @LastName IS NULL)




Again this works and is a much more elagant solution. The code is tight, easy to follow and performs well. The main problem with this is that the compiler will factor in all of the where conditions into it's execution plan. That is, if there are tables needed only for the where clause, they will be processed even if that where condition is not needed. However, IMHO this is the way to do it for 90% of cases.

Thursday, April 20, 2006

Temporary Tables vs. Table Variables

Here is an interesting behavior with SQL Server. A lot of developers use table variables and temporary tables interchangeably without understanding that they are different entities, each with slightly different behaviors. Here is one of the more interesting differences

Transaction Rollback

If you have a transaction that affects a temporary table, and roll it back, the temporary table is restored to it's original state. For example

SET NOCOUNT ON
CREATE TABLE #TempTable (TempTableColumn int)
DECLARE @TableVar TABLE (TableVarColumn int)
BEGIN TRANSACTION
INSERT INTO #TempTable SELECT 1
INSERT INTO @TableVar SELECT 1
ROLLBACK TRANSACTION
SELECT * FROM #TempTable
SELECT * FROM @TableVar


Results in

TempTableColumn
---------------
TableVarColumn
--------------
1


As you can see the temporary table was rolled back but the table variable was not.

This has several possible uses. An example I have used is, when I wanted to rollback a transaction, I copied the relevant records that were changed previously to a table variable and then rolled back the transaction. That way I had a table available with the old data after the rollback.