Database

Archived Posts from this Category

The Morning Brew #446

Posted by Chris Alcock on 02 Oct 2009 | Tagged as: .NET, Database, Development, Links, Morning Brew, SysAdmin

Software

  • ASP.NET MVC 2 Preview 2 - Phil Haack announces the release of ASP.NET MVC 2 Preview 2. New additions in this preview release include jQuery Validation support for client side validation, Areas allowing you to divide up your project more easily, model validation providers and metadata providers allowing you to support validation and metadata based on something other than the default of Data Annotations. This release is for VS2008 / .NET 3.5 Sp1 only.
  • CruiseControl.NET 1.5.0 CTP Released - CruiseControl.NET - Confluence - The Cruise Control Team announce the release of the Cruise Control 1.5 CTP. This release contains a number of bug fixes, a new range of security settings to allow you to lock down control of the build server, support for a number of new source code control tools such as Git, Mercurial, etc and a number of new tasks.publishers. Looks good, but as its a CTP not recommended for production use

Information

  • New features in ASP.NET MVC 2 Preview - ‘Shaans’ explores a number of the new features of the latest preview release of ASP.NET MVC 2, including some sneak peeks at the VS2010 beta 2 version. UPDATE: This appears to be an extract from the MVC release notes available in full here in MSWord Format
  • Code Contracts Preview: PostConditions - Dino Esposito continues his series over at DotNetSlackers looking at the Code Contracts Functionality, and in this article looks at PostConditions, looking at how they are implemented and how they work, along with comparing them to the use of asserts
  • Why does char convert implicitly to ushort but not vice versa? - Eric Lippert dives back into the past to discover why it is that you can convert from a char to a ushort but not the other way round, explaining how and why such a decision was made
  • Lazy<T>: On Demand Construction in .NET 4.0 - Bill Wagner talks about one of the less publicised features of .NET 4, the ability to make object construction an on demand process by the use of Lazy<T>
  • An Engineer’s Guide to Bandwidth (Yahoo! Developer Network Blog) - Carlos Bueno, a software engineer on the Yahoo Mail project has written a good article looking at network bandwidth with a particular slant for developers. I always feel it is important to understand the process between your code and yor users, and this article seems to fill in a number of those gaps
  • Mike Chaliy: Code-generation DSL with T4 (Text Templates) - Mike Chaliy takes a look at T4 Template generation using a DSL and T4 Templates to easily build configuration section reading code. A nice short understandable example of these sometimes confusing concepts
  • Generically Constraining F# - Part III - Matthew Podwysocki continues his series of posts on Generic Constraints in F#. This part continues on from the previous looking at the remaining constraints that exists, including Constructor, Delegate and Reference constraints.
  • Perspective camera animation on a cube in WPF 3D - Razan Paul Blog - Razan Paul has a number of posts on interesting animations created in WPF with full sample code provided. This one is the spinning cube, and others include roll down, wheel and circle animations, so be sure to take a look at them too.
  • Strive for Functional Cohesion - Chris Eargle talks about Functional Cohesion, and the important decision developers are making all the time about where to place certain functionality in your object mode.

2 Comments »

The Morning Brew #405

Posted by Chris Alcock on 05 Aug 2009 | Tagged as: .NET, Database, Development, Links, Morning Brew, SysAdmin

This will be the last of the late editions for a while, my summer break is over and its back to work for me tomorrow so The Morning Brew should be returning to its more usual 8-8:30am (UK) publishing time.

Software

  • Spec# and Boogie Released on CodePlex - Matthew Podwysocki highlights the release of Spec# and Boogie on CodePlex under the Microsoft Research Shared Source License Agreement (MSR-SSLA) and Microsoft Public License (MS-PL) respectivly.

Information

Community

  • Microsoft PDC09 - Microsoft PDC 2009 to be held in LA is now accepting registrations, and until 15th September will be offering a $500 discount

No Comments »

The Morning Brew #153

Posted by Chris Alcock on 07 Aug 2008 | Tagged as: .NET, Database, Development, Morning Brew

A longer than average post today due to SQL 2008 finally being RTM’d, although it seems that Visual Studio folks should hold off untill the 11th August when the RTM of Visual Studio 2008 SP1 will be made available.

Software

Information

Community

  • Bad News, Good News - stackoverflow - Stackoverflow, the new community from Jeff Atwood and Joel Spolsky has entered beta, and there is a form you can fill in to get onto the beta linked from this post.

1 Comment »

AND/OR Searches for related records

Posted by Chris Alcock on 22 Oct 2007 | Tagged as: Database, Development, SQL

A common database problem I have to explain to people is creating search functionality which allows a user to find a master record based on it having some or all of a specified set of related records.  To illustrate this, consider a database which defines products, which can each have 0 or more features from a table of features assigned to them. 

ProductFeatureDiagram

The table Product contains the details of the product (in our case, just a name - its an example after all).  A list of features are supplied in the Feature table, and the relationship between a products and a feature is expressed by a record in the ProdFeatureLink table. 

In this example, we will have 3 products, each with a different combination of the three features:

  • Product 1 has Feature 1
  • Product 2 has features 1 and 2
  • Product 3 has features 1, 2 and 3

So, to summarise the data involved we can issue the following query:

select
    p.product_key, P.ProductName, F.FeatureName
from product p
    inner join prodfeaturelink pfl on p.product_key = pfl.product_fkey
    inner join Feature f on f.feature_key = pfl.feature_fkey

This gives the following results:

ProductFeatureSimpleSelectResult

In our first case we are interested in products which have ‘Feature 1′. To find these we can use the previous query with a filter for the feature required:

select P.* from
product p
    inner join ProdFeatureLink pfl on p.product_key = pfl.product_fkey
    inner join feature f on f.feature_key = pfl.feature_fkey
where
    f.featureNAme = 'Feature1'

This gives the following result set:

ProductFeatureOnlyFeature1

This query correctly returns Products 1,2 and 3.

In the next case, we are interested in products which have any or all of two features (Features 2 and 3). Again, this can be achieved by taking our original query and adding a where clause which filters for either of the features.  This filter can be performed using ‘featureName IN (X,Y)‘ or by ‘FeatureName = X OR FeatureNAme = Y‘.

select distinct p.*
from product p
    inner join prodfeaturelink pfl
        on pfl.product_fkey = p.product_key
    inner join feature f
        on f.feature_key = pfl.feature_fkey
where
    f.featureName in ('Feature2', 'Feature3')

And here is the result set:

ProductFeatureEitherOrBoth

In order that we only get one mention of the product record the query above uses the Distinct clause, otherwise we would get duplication of results.

The final case is to find products that have both Features 2 and 3. This isn’t as simple as it might seem - we can’t just use the AND operator in place of the or, as each individual row of our original query’s results cannot contain two feature values. In order to do this, we need some new tools - specifically Group By, Having and an aggregate function or two. 

Our previous query (Feature 2 AND/OR feature 3) provides the basis - we just need a way of finding only products which have both Feature 2 and 3. We can manage this by requiring that the count of the rows for each product is equal to the number of features we are searching for. 

select p.product_key, p.productName
from product p
    inner join ProdFeatureLink pfl on pfl.product_fkey = p.product_key
    inner join feature f on f.feature_key = pfl.feature_fkey
where featureName in ('Feature2', 'Feature3')
group by p.Product_key, p.ProductName
Having Count(product_key) = 2

giving the result set:

ProductFeatureBoth

The placement of the criteria in this query is important. We must use the where to remove any data we are not interested in - so the WHERE filters our data down to only those products which have either feature 2 and 3. We then use the GROUP BY to gather up each product, and finally the HAVING checks that each product has the required number of features.

We can actually merge both the AND and the OR cases of this query together, as the only difference is that for an AND query we must have as many features against the product as we have specified, where as the OR query must have 1 or more.

declare @type char(3)
Set @type='AND'
--Set @type='OR'
select p.product_key, p.productName
from product p
    inner join ProdFeatureLink pfl on pfl.product_fkey = p.product_key
    inner join feature f on f.feature_key = pfl.feature_fkey
where featureName in ('Feature2', 'Feature3', ..., 'Feature n')
group by p.Product_key, p.ProductName
Having Count(product_key)>= CASE WHEN @type='AND' THEN n ELSE1 END

This query does rely slightly on the structure of the data involved. We rely on the fact that each product may only be assigned one instance of a feature- if a product may be assigned more than one instance of each feature then things do become a bit more difficult - but I’m going to save that for a later date.

UPDATE: Here is a SQL script of the examples used in the article:

AND/OR Searches of related records - SQL Script containing examples in the article

No Comments »

Getting a good nights sleep - ORM with NHibernate

Posted by Chris Alcock on 28 Sep 2007 | Tagged as: .NET, Community, Database, Development, Links, SQL, Talks / Presentations

Getting a good nights sleep - ORM with NHibernate

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

No Comments »

A few SQL Tricks and Tips

Posted by Chris Alcock on 11 Jul 2007 | Tagged as: Database, Development, SQL

 

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
Technorati Tags: , , ,

4 Comments »

Careful what you subselect for

Posted by Chris Alcock on 11 Jan 2007 | Tagged as: Database, Development, SQL

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:
Execution Plan: select * from product where producttype_fkey=(select producttype_key from ProductType where TypeId='TEST')

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

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 »

Cursing Cursors

Posted by Chris Alcock on 07 Jan 2007 | Tagged as: Database, Development, SQL

A post on the MSWebDev mailing list (archive only available to subscribers - but you should join, its a really useful list) asked ‘Does anyone think that set based is always possible and cursors need never be used?’.

I’m personally of the opinion that if cursors were never needed they wouldn’t be available to us, however there is a method I have used a number of times that works in more set based way, but still gives the ability to do per row processing. The main limitation is that the table(s)/views acting as the source must have a unique key. In the example below this key is a single field, but it should still be possible with composite key.

use tempdb
--Hide rows affected lines - they just clutter the messages window
Set NOCOUNT ON

--Create a table to store some data - this would be your complex table with many columns normally
create table Product (product_key bigint identity(1,1) not null, product_name nvarchar(200))

--Now fill the table with rather trival data
declare @count int
set @count = 0
while @count <1000
begin
insert into product (product_name) values ('Product' + cast (@count as nvarchar(5)))
set @count = @count +1
end
--uncomment below to see what that trivial data looks like
--select * from product

--This is the table that will act as our cursor
--     - using a table variable as performance is slightly better
--      as there is less logging than a temp table.  Also the scopeing works well for us
declare @keybag table (theKey bigint)
--Grab the keys of all the records for the 'cursor' - obviously this can be filtered and ordered
insert into @keybag select product_key from product

--the key of the record we are currently working on
declare @currentKey bigint

--variable for us to fill in a similar way to a cursor
declare @currentProduct nvarchar(200)

--loop for all keys in the @keyBag (NB we will be removing keys as we use them
while exists(select theKey from @keybag)
begin
--Get the next Key
select top 1 @currentKey = theKey from @keybag
-- and pull out the record from the real table using that key
-- note that more than one column can be pulled into a variable here too!
select @currentProduct = product_name from product where product_key = @currentKey
--Do per row processing - we just print the name out - but you can do whatever you like!
print  @currentProduct

--finally remove the key from the keybag as we have done it - forgetting this line will loop for ever :(
delete from @keybag where theKey = @currentKey
end

--clean up the test table
drop table product

I’ve done a little performance testing on this in the past to see how well it performs against cursors, but I don’t have any numbers to hand - I’ll have to check the Wiki at work to see if I put them there. I seem to revall that it was comparable or slightly faster in my tests, but I suspect your mileage will vary, but it can be a useful method.
Also worthy of note is c# Code Format the web based formatter I used to format the code above (even if it did mess up the comments when there are keywords in the line).

Update: Dropped the syntax highlighting when importing into this new blog

No Comments »