The Morning Brew

Last night I gave a presentation to the Liverpool Geekup group about NHibernate, thanks to everyone who came along – you were all a very nice audience. The slides from the presentation entitled Getting a Good Nights Sleep – ORM with NHibernate are available as a PDF, here. There are a number of links in the slides, but here is a more comprehensive list of links that people may find useful or interesting

• NHibernateDataSource: A DataSourceControl for ASP.NET 2.0
  The Article and code for the 2 way databinding library of NHibernate objects
• NHibernate Best Practices with ASP.NET, 1.2nd Ed.
  A good source of information about using NHibernate within web applications
• NHibernate Reference Documentation
  The official documentation for NHibernate
• TheServerSide: NHibernate
  Excellent Introductory article from TheServerSide.NET covering most of the basics of NHibernate
• NHibernate Query Analyser
  NHibernate query analyser allows you to work interactively with your NHibernate Mapped objects
• Ayende@Rahien – Presentations
  A collection of great NHibernate presentations, covering lots of the more advanced functionality

Comments Off on Getting a good nights sleep – ORM with NHibernate

 

Undocumented Stored Procedure 1 – sp_MSForEachDB

 There are a number of reasons that you might need to run the same (or similar) bits of SQL against a number of similar databases.  I find myself doing this when I update some part of an application that runs against a number of individual client databases on the same server.  In my case, the databases are consistently named in the format ‘client’ + ‘ApplicationName – i.e. ReflectivePerspectiveBLOG

sp_MSForEachDB is a built in SQL Server stored procedure which is undocumented in Books Online, but helps us out in this situation, allowing us to run a command on each database on the server

EXEC sp_MSForEachDB @command1 = '

use [?]

if (db_name() like '%BLOG')

BEGIN

     --Perform our operation

     update foo set bar = 1 where wibble=2

END

'

Undocumented Stored Procedure 2 – sp_MSForEachTable

 Another related stored proceedure is sp_MSForEachTable – this one allows you to do things to each table in the database:

EXEC sp_MSForEachTable @command1 = '

       select top 10 * from ?

'

SET vs SELECT to initialize variables

There are two ways of allocating values to variables in TSQL – SET and SELECT.  Many people get into the habit of using SET to initialize their variables, like so

SET @foo = 1

SET @bar = 2

SET @wibble = 3

For performance purposes this is quite in-efficient as each use of SET is the equivaent of a SELECT statement.

SELECT, on the other hand has the advantage that it is able to set multiple variables at once:

SELECT @foo = 1, @bar = 2, @wibble=3

This single select statement is the equivalent of one of the SET statements above.  The performance advantages of this increase when allocating values to variables from database tables:

SET @foo = (select foo from table where 1=1)

SET @bar = (select bar from table where 1=1)

does the same as the following statement, but only executes the query against the database once.

SELECT @foo = foo, @bar = bar from table where 1=1

Table Variables

Table variables are very useful and often forgotten about feature of SQL Server.  You can use them much as you might a temporary table, however they have the added benefit that they will clean up after them selves, so you don’t have to remember to do the drop table #foo.

You can create Table Variables like so:

declare @tableVar table (col1 int, col2 int)

and from then on you can use them much as you would any other table

insert into @tableVar (col1, col2) values(1,2)

select * from @tableVar

4 Comments »

I quite often find myself using a subselect to get some metadata key value to use as part of a where clause. Some thing like the following:
select * from product where producttype_fkey=(select producttype_key from ProductType where TypeId='TEST')
I had been under the impression that the database engine would be clever enough to work out that the value the subselect returned was constant and that it would simply work in an equivalent way to this query:
declare @prodType int;
select @prodtype=producttype_key from ProductType where TypeId='TEST';
select * from product where producttype_fkey=@prodType

Infact this is not the case. The first query ends running as though the product table and the productType table were being inner joined together, giving an execution plan like this:

The second query (using the variable) has two parts to its execution, however the join is no longer there:

The significant thing about this is that when these two types of query are run together, the first query accounts for almost 60% of the execution, where as the two stage with variable version only accounts for a little over 40%, so it is significantly better to select the value out into a variable rather than subselecting (especially where the same value is required multiple times in a stored procedure.

2 Comments »