14 Tips for optimising SQL Server 2008 Queries in ASP.Net

June 19, 2010

This article is now here.


Solution: My generated LINQ to SQL stored procedure returns an int when it should return a table!

June 2, 2010

There are various scenarios where LINQ to SQL will not recognise the results of a stored procedure, and so not generate the correct return type. If the stored procedure is a composite stored procedure that involves a lot of temporary tables, select intos, multiple resultsets based on parameters input, or control of flow statements, then it could well be that the results returned to the code generator are not consistent with the live execution of the stored procedure. 

The usual scenario is that you call a CREATE TABLE #TempTable within your stored procedure, but when the stored proc is dragged from the Server tab to the LINQ to SQL designer, it returns an int data type, and not the generated table object that you were expecting.

There are a couple of ways around this. What you could do is create a dummy SELECT that returns a representation of the data. For example, if I wanted to return a report resultset, I might type the following:


ALTER PROCEDURE myschema.mystoredproc

@MyArg uniqueidentifier

as

begin
-- Start code added temporarily
SELECT Description=cast('Test data' as varchar(100)), Month1=cast(1 as decimal(4,1)), Month2 = cast(1 as decimal(4,1)), Month3 = cast(1 as decimal(4,1));
return;
-- End code added temporarily

CREATE #TempTable

(

Description varchar(100),

Month1 decimal(4,1),

Month2 decimal(4,1),

Month3 decimal(4,1)

)

...

more content goes here

...

end

The SELECT statement and the RETURN statement are inserted temporarily so that there are valid results returned from the stored proc that will be recognised by the LINQ to SQL generator. It works, but there is a danger in that you may get the returned types wrong.

Instead of doing this, I have found that the best thing to do is to put the following statement, SET FMTONLY OFF, into my stored procedure.


ALTER PROCEDURE myschema.mystoredproc

@MyArg uniqueidentifier

as

begin
-- Start code added temporarily
SET FMTONLY OFF;
-- End code added temporarily

CREATE #TempTable

(

Description varchar(100),

Month1 decimal(4,1),

Month2 decimal(4,1),

Month3 decimal(4,1)

)

...

more content goes here

...

end

What this now does is it returns the dataset metadata by executing the actual stored procedure. It will be recognised by LINQ to SQL and will now generate the correct return table object type.

When LINQ to SQL calls the stored procedure for generation, LINQ to SQL doesn’t want to actually execute statements or cause persistent changes to the database. So to ensure that no changes of are made to the database, it executes a

SET FMTONLY ON

So this is by design.

You can override this in your code, which is what I’ve done, to return the type you want.

But this does a full execution of your stored procedure. So if you are doing more than just performing a custom SELECT out of a temp table, you should probably remove or comment out this line after use. Otherwise, every time you try to recreate the stored proc in the LINQ to SQL designer, it will execute the stored procedure and potentially update data in your database!


How to use LINQ to SQL to batch queries and return Multiple Results without Stored Procedures, by Tony Wright

July 23, 2008

I must admit I’ve been a bit disappointed with LINQ to SQL. I went looking for the concept of LINQ batches, that is, the ability to execute more than one query in the same database request. I would have thought that this feature would be highly desirable. Instead of making an SQL request, waiting for all the results, making a second request, waiting for it’s results, then a third request, waiting for it’s results…well, you get my point.

If only you could make a single request and get all the results back in one hit. This is one of the benefits of sql server stored procedures. You can put a number of select statements in the stored procedure, call it from your client, and get all the results back via a DataReader or DataSet. With a DataReader, you get the first result back and then when you’ve finished reading all the rows of that result, you call the NextResult() method and it moves on to the next result and so on. With a DataSet, you can set up a DataAdapter, set it’s select statement to call a stored proc, and then call the Fill method to populate one-to-many DataTables within the DataSet. It’s very efficient, and can return the entire batch very quickly.

I often find implementations of the domain model, where someone has implemented a whole lot of objects in .net that hide the details of the underlying sql calls. Basically, as the developer traverses the object model, extra calls are created to the database. This causes all sorts of unnecessary traffic. In some instances, I’ve seen up to a hundred calls in a Page_Load!

 With web sites, the ultimate goal is to try to only have a maximum of a single request per action. So (caching aside) a Page_Load would only call the database once to retrieve everything it needed to populate the page. After the page is populated, you modify some data and then click an Update button, and that action should perform another single request to the database to update the data and get any further results needed to ensure the state of the page is correct. I often achieve pretty close to this, so all that extra traffic is unnecessary and unwanted.

With LINQ to SQL, you shouldn’t need to call stored procedures just to batch queries – especially for simple ones. I mean, doesn’t that defeat the purpose of getting people to develop in LINQ, if as soon as it gets too hard, they say, nup, go off and use a stored procedure? Well, I’ve come up with a simple solution that does allow you to batch LINQ queries.

Here is an example of the types of queries you can execute using the batching function. Note that this performs a single query to the database, consisting of multiple select statements, and returns three enumerable collections.

  var StocktraderLevelQuery =
   from StocktraderLevel in db.StocktraderLevels
   where StocktraderLevel.ItemID==10
   select Stocktrader;

  var StocktraderFunctionQuery =
   from StocktraderFunction in db.StocktraderFunctions
   select StocktraderFunction ;

  var StocktraderLevelAccessQuery =
   from StocktraderLevelAccess in db.StocktraderLevelAccesses
   select StocktraderLevelAccess;

  var mr = db.GetMultipleResults(StocktraderLevelQuery, StocktraderFunctionQuery, StocktraderLevelAccessQuery);

  StocktraderLevels = mr.GetResult<StocktraderLevel>();
  StocktraderFunctions = mr.GetResult<StocktraderFunction>();
  StocktraderLevelAccesses = mr.GetResult<StocktraderLevelAccess>();

 
To be able to do this, open up your project and add a dbml file. You do this by right-clicking on the project, selecting Add New Item, and then selecting LINQ to SQL classes. Type in your database name (in my case I called it Stocktrader.dbml). Then you use the Server Explorer to add a database connection to that database and drag the tables onto the dbml designer.

Behind the scenes, there is designer generated code, created by the LINQ to SQL custom tool. If you can’t see this file, you need to select “Show all files” from the toolbar. If you open that file, you can see that it’s a partial class. Good, that means we can extend it. So add a new class to the project. I named mine StocktraderExt.cs. Then I added the following code:

using System.Data;
using System.Linq;
using System.Data.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data.Common;

namespace Stocktrader.DBML
{
 public partial class StocktraderDataContext
 {
  private DbParameter CloneParameter(DbParameter src)
  {
   SqlParameter source = (SqlParameter)src;
   SqlParameter destination = new SqlParameter();

   destination.Value = source.Value;
   destination.Direction = source.Direction;
   destination.Size = source.Size;
   destination.Offset = source.Offset;
   destination.SourceColumn = source.SourceColumn;
   destination.SourceVersion = source.SourceVersion;
   destination.SourceColumnNullMapping = source.SourceColumnNullMapping;
   destination.IsNullable = source.IsNullable;

   destination.CompareInfo = source.CompareInfo;
   destination.XmlSchemaCollectionDatabase = source.XmlSchemaCollectionDatabase;
   destination.XmlSchemaCollectionOwningSchema = source.XmlSchemaCollectionOwningSchema;
   destination.XmlSchemaCollectionName = source.XmlSchemaCollectionName;
   destination.UdtTypeName = source.UdtTypeName;
   destination.TypeName = source.TypeName;
   destination.ParameterName = source.ParameterName;
   destination.Precision = source.Precision;
   destination.Scale = source.Scale;

   return destination;
  }

  private SqlCommand CombineCommands(List<DbCommand> commandList)
  {
   SqlCommand batchCommand = new SqlCommand();
   SqlParameterCollection newParamList = batchCommand.Parameters;
   int commandCount = 0;
   foreach (DbCommand cmd in commandList)
   {
    string commandText = cmd.CommandText;
    DbParameterCollection paramList = cmd.Parameters;
    int paramCount = paramList.Count;
    for (int currentParam = paramCount – 1; currentParam >= 0; currentParam–)
    {
     DbParameter param = paramList[currentParam];
     DbParameter newParam = CloneParameter(param);
     string newParamName = param.ParameterName.Replace(“@”, string.Format(“@{0}_”, commandCount));
     commandText = commandText.Replace(param.ParameterName, newParamName);
     newParam.ParameterName = newParamName;
     newParamList.Add(newParam);
    }
    if (batchCommand.CommandText.Length > 0)
    {
     batchCommand.CommandText += “;”;
    }
    batchCommand.CommandText += commandText;
    commandCount++;
   }
   return batchCommand;
  }

  public IMultipleResults GetMultipleResults(params IQueryable[] queryableList)
  {
   List<DbCommand> commandList = new List<DbCommand>();
   foreach (IQueryable query in queryableList)
   {
    DbCommand cmd = this.GetCommand(query);
    commandList.Add(cmd);
   }
   SqlCommand batchCommand = CombineCommands(commandList);
   batchCommand.Connection = (SqlConnection)this.Connection;

   DbConnection cn = this.Connection;
   cn.Open();

   DbDataReader dr = batchCommand.ExecuteReader(CommandBehavior.CloseConnection);
   var mr = this.Translate(dr);
   return mr;
  }

 }
}

What I am doing here is passing a list of IQueryables into the GetMultipleResults method. The LINQ DbContext object exposes a GetCommand method. This enabled me to obtain the DbCommand object that would otherwise be used to execute the LINQ query. So I build up a list of DbCommand objects for all the queries in my parameter list.

Next, I decided to combine all the command objects into a single command (called the batch command). There are three parts to this. Firstly, the CommandText for each command can have parameters specified. In LINQ, they are always the same, starting at @p0 and working their way up, @p1, @p2, @p3 etc.

This isn’t a bad thing, because I can put in a counter that increments for every command that I am adding to the batch command, then substitute the name for a new name that includes my command counter. So @p0 becomes @0_p0 (the @p0 param for the first command). I am also using string.Replace to replace the parameters inside the CommandText, and I am building up a collection of new parameters for my new batch command. Note that this idea is based on the SqlCommandSet class that is hidden inside the SqlDataAdapter class. You could probably use this code as a basis for writing a standard batch command process for executing queries in bulk, if you wanted to.

After combining the DbCommand objects into one enormous command (potentially), I then attach to the DbContext connection, and execute the DbReader with an instruction to close the connection as soon as all data is read. No problem doing that because it’s a batch query – you shouldn’t need the connection afterwards as you’ve done all your queries in the one hit.

Finally, we need an object that has an interface of IMultipleResults. That’s done by another internal method, called Translate. Translate takes a DbDataReader as input, and returns multiple results from the reader.

Note these lines of code in the code section at the top:

  StocktraderLevels = mr.GetResult<StocktraderLevel>();
  StocktraderFunctions = mr.GetResult<StocktraderFunction>();
  StocktraderLevelAccesses = mr.GetResult<StocktraderLevelAccess>();

Basically, these retrieve the results in the order of the LINQ queries you passed to the GetMultipleResults method. And that’s it, you can now perform batch queries with LINQ to SQL.

I have provided the code here. Unfortunately wordpress only supports a few different file types, so here it is as a doc file: linqtosqlbatchqueries


LINQ to SQL, DataSets and Nullable types

June 14, 2008

I am writing an informational application at the moment. I want to code fast. I want it to be installable by just about anyone. I don’t want the user to have to configure the application, and the application involves graphics, so I decided on a WinForms app. And I want the code to be simple. Because it is informational, whoever is doing the data entry does not necessarily have all the information at the time they create the item.

So my first thought was, “I know DataSets, I know Table Adapters, I’ll use them”. And of course my next thought was how best to cause the errors to be raised at compile time and not at runtime. Earlier detection of bugs makes them cheaper to fix.  So I added some DataSets, dragged some tables onto the designer, and started to code away, using TableAdapter Fill and Update commands.

DataSets are a beautiful thing. Once populated, a DataSet can be disconnected, moved around, modified, passed back, reconnected, and all rows can be updated in a batch. If you’re writing a multi-tier application, passing data back and forth this way can be very practical. By using the designer, and dragging the tables onto the designer, you are in-effect creating typed-DataSets. And typed-DataSets are good if you want to ensure that you are using the database fields consistent with their underlying database types.

So there I was coding away, and I decided that I wanted a number of my fields to be nulls – hey, the person entering may not have the information at the time they enter it. But I also wanted to bind the data to the user interface components, like DataGridView or ComboBox. This should be as easy as possible. Create an instance of a typed-DataSet, pass it into the typed-Table Adapter Fill command, and presto, the DataSet is populated. Bind it directly to the component’s DataSource. Set the DataMember and DataValue properties, and whalla, the component is populated. If I have to do anything more than this, then I lose time doing something that should be simple infrastructure, when I could be coding business logic.

The problem I had, of course, was due to Nullable types. When Microsoft created typed DataSets, there wasn’t a concept of nullable types, and as such the MSDataSetGenerator custom tool that is used to generate typed DataSet code from the designer can’t handle nullable types. Of course, if you drill down into the underlying datarows you can, of course, check a column to find out if the column IsDBNull. But this doesn’t help if you simply want to bind a typed-DataSet to a grid or combobox. It crashes when it reaches the null value, because it can’t parse the value to be returned to the component (int and DateTime .net types can’t be set to null). Microsoft, apparently, has no intention of changing this.

So my first thought here was “someone must have had this problem before, surely someone has written their own code-generator custom tool to provide a workaround” so I went looking. I found a whole stack of references to building custom tools, and specifically related to the nullable issue. But no one had apparently written one and made it freely available, or at least not with the keywords I was using. Damn. I would have to write my own one. Or, I could try LINQ to SQL to see if it could handle nullable types. With LINQ, I do like the way queries are created in code, because if you get the structure of the query wrong in code, it will break the build at compile time, not run time. As it turns out, LINQ does allow nullable types, such as int? and DateTime?

First, I need a call to get all records from my reference table. I added a new item to my project. I selected Add New Item in Solution Explorer and selected LINQ to SQL classes. It opened up in the designer. I connected up to my SQL Server database and dragged then entire table structure into the designer. Saved it. Compiled it. Next, I went into my business layer and created a class for my object.

using System.Linq;
using MyDataLayer;
class MyRefTableClass
{
 internal static
     IQueryable<MyRefTable> SelectAll()
 {
  MyDatabaseDataContext context =
                  new MyDatabaseDataContext();
  return context.MyRefTables.AsQueryable();
 }
}

That retrieved all the MyRefTable rows. Now I could bind it to a combo box, like so:

cboMyCombo.DataSource= MyRefTableClass.SelectAll();
cboMyCombo.DisplayMember = "DisplayNameField";
cboMyCombo.DisplayValue = "ValueField";

Ok, so I did the same for my informational table, named MyInfoTable. One of the fields in MyInfo has to be populated by the key value out of a MyRefTable row, or it can be null. Then I need to update the MyInfo with the new value.

Notice above that I have declared the DataContext object within the scope of the SelectAll method. Because of this, if I want to update a MyRefTable row, information about the original state of the row will be lost, and generally, I will need to retrieve the row from the database again, modify the object, then resubmit it to the database. This is undesireable because you are requerying the database for an object that you are about to change. That’s extra unnecessary traffic.

Also, I want the ability to add a row and delete a row. Creating the objects themselves is easy. My informational class now looks like this:

class MyInfoTableClass
{
 //note the following line was moved from method
 //scope to class scope as it will then retain
 //information about changes in underlying objects.
 static MyDatabaseDataContext context =
    new MyDatabaseDataContext();
 internal static
    IQueryable<MyInfoTable> SelectAll()
 {
  return context.MyInfoTables.AsQueryable();
 }
 internal static
  IQueryable<MyInfoTable> SelectByID(int ID)
 {
  var result = (from p in context.MyInfoTables
                where p.ID == ID
                select p);
  return result;
 }
 internal static void Insert(
    MyInfoTable newInfoTable)
 {
  context.MyInfoTables.
     InsertOnSubmit(newInfoTable);
 } 
 internal static void DeleteByID(int ID)
 {
  var result = (from p in context.MyInfoTables
                where p.ID == ID
                select p);
  context.MyInfoTables.
     DeleteOnSubmit(result.Single());
 }
}

Note that LINQ operates in batches, so after doing all the CRUD work, you can send all the changes in one big hit by calling SubmitChanges() if you like. At the moment I am calling SubmitChanges straight after I modify the data.

Note that the Delete method above is really undesireable.  It will cause extra traffic to the database as it retrieves the record before deleting it. It’s best if you keep the object alive then pass that object back into the data context object’s DeleteOnSubmit method. I think it would be nice if LINQ provided an easier way to do this.

Another problem I see is in multi-tier development. It can be advantageous to completely disconnect from the database. That’s not really practical in LINQ if you want to reduce network/query traffic. If the datacontext object goes out of scope, you have to reattach the data object to a new live instance of the data context. That mean that there is a need to effectively keep the original object around to reattach it. Ok, that might be fine, but if you have a whole lot of referential contraints within your dbml file, reattaching your object to a new DataContext object will simply bomb out with “An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext.  This is not supported.” Unfortunately, the only way around this seems to be to Detach all referenced data, or don’t have referential constraints (which kind of defeats the purpose of LINQ). I’m looking for a clean way to do this at the moment. If you know one, please let me know.