Guid Strategy for DB Primary Keys

Standard

I was thinking about this for a while now, some years back, I always used Integer fields as Primary key columns in my database tables, more recently I have switched to using Guid values (uuid datatype in Sql Server) for the same.  This is not a new thing for most of us, we have weighed the options and have chosen one way or the other.

In my mind, Integers work very well as a Primary Key column, it works very well as an clustered index, the most recent is naturally the largest value. If you use a regular Guid, it is random in nature and can lead to fragmented indices. But Guid being unique, we can easily  move around that data, replicate, etc. No issues like in the case of a auto number integer.

So, in short if we can get to generate a sequential Guid, we strike gold ! I am using NHibernate ORM (with Fluent NHibernate), and it can use a Comb Identifier strategy to generate sequential Guids.

It is very easy to set this up, when you setup your session factory, you can add conventions for your mappings. You can define a PrimaryKey convention like this:

    public class PrimaryKeyConvention : IIdConvention
    {
        public void Apply(IIdentityInstance instance)
        {
            instance.Column("Id");
            instance.GeneratedBy.GuidComb();
        }
    }

and you can use this convention in your configuration like this:

    var cfg = new NHibernate.Cfg.Configuration()
                    .SetProperty(NHibernate.Cfg.Environment.CommandTimeout, "100")
                    .Proxy(p => p.ProxyFactoryFactory<DefaultProxyFactoryFactory>())
                    .DataBaseIntegration(db =>
                        {
                            db.Dialect<MsSqlAzure2008Dialect>();
                            db.ConnectionString = _context.ConnectionString;
                            db.BatchSize = (short) _batchSize;
                        });

                var sessionFactory = Fluently.Configure(cfg)
                    .Mappings(m =>
                    {
                        m.FluentMappings.AddFromAssembly(_context.Assembly);
                        m.FluentMappings.Conventions.Add(typeof(EnumConvention));
                        m.FluentMappings.Conventions.Add(typeof(PrimaryKeyConvention));
                        m.FluentMappings.Conventions.Add(typeof(DefaultStringLengthConvention));
                    })
                    .BuildSessionFactory();

This should help us having a better strategy for handling Guids as our PrimaryKey values in tables.

I still have an open question in my mind. Does this scale well (as an Int or Long) when we have millions of rows in our tables??

What do you guys think?

A broad look at Web Application Performance

Standard

So does your web application perform well? Is it responsive enough so that it increases the web user experience? It is a fact that user can relate only to what they see and what they click, that is the user interface (UI) and the experience with it (UX) largely depends on the performance of the overall
application. But what is the complaint that we usually hear, “this page is slow”, “this grid takes for ever to load”, “we click on that button and then we go for a coffee break”…and UI is always blamed first. We all know that it has to do with the perception a lot, but a services guy ignores it, database developer closes his eyes too, till someone proves where is the actual bottleneck.
Performance Tips
My cose friend and ex-colleague called me the other morning asking me help to figure out why the application was timing out, users are screaming and cursing this particular search page, it started timing out suddenly last week. They did all the usual tricks, iisreset, checking logs, checking db connections, even few server restarts, no avail. So this morning, we started looked thru the logs, stack traces and we could easily spot the culprit, a stored procedure in SQL Server, has gone rogue and slow. So in this case, we need to look closely at that proc, probably data has grown so much in one of the table that now a bad join has created performance issues.

I shared that experience, to point to fact that performance and thus performance tuning has to start at the very tail end (or head depends the way your look at the layered architecture :) . So now lets take the layers, there is presentation layer, business layer, database access layer (persistence layer) and then there is the database itself.

Lets start with Database: What are the performance optimizations that you can think of, here is some starters.

  • Good logical design, helps in how db is physically stored, think thru your normalization levels depending on your usage, make sure you dont over-normalize, it will kill your joins and thus leads to bad performing queries.
  • Good key selection, have a good candidate for primary key, have proper foreign keys that are indexed (create non-clusterd index)
  • When writing queries, do not return all columns (i.e., no select * …)
  • Use SET NO COUNT ON, think about providing sql hints
  • There is 2 camps about using temp tables, some hate it, some love it, according to me temp table can be very useful when you are dealing with a large amount of tables to join. Create temp tables to divide and conquer. Always think about “number of rows” in the table when you make these decisions and potential growth of data. Check query plans, avoid table scans.
  • Use sp_executesql when you want to execute a dynamic sql
  • Reduce round trips to the server, try to do bulk operations.
  • This is just few I can think of now, Google database tuning and I am sure you can find a tons of good tuning tips from database gurus.

    Now how about Data Access Layer (DAL)? So what is DAL? DAL is nothing but a mechanism that provides us some CRUD capability along with some transactions, querying and concurrency control. In simpler systems, ADO.Net provides an database independent way to achieve this, So ADO.Net is our DAL layer. Or you might choose to absract the core features provided by ADO.Net into another library where you have tighter control and ability to switch databases at will, a DB Services Factory. If you are following a Domain Driven Design, then your OR/M plays the role of DAL.

    Lets see some ADO.Net related performance tips,

  • For connection pooling to work, always use the same connection string. By default pool size is 100, you can increase this (rarely you will need to)
  • Use db server IP rather than the server name in connection string to avoid a domain name resolution lookup. (This is a performance tip, but not a governance tip, for better governance, always use DNS name)
  • Always close the connections explicitly
  • Connect using a service account. Use windows authentication.
  • Use stored procedures, return only what you need.
  • Use some caching strategy to cache frequently used static data.
  • Again this is tip of the iceberg on what performance considerations you can use, for a more complete ADO.Net performance tuning tips, refer to “Improving .Net Application Performance and Scalability” from Microsoft patterns and practices.

    We will continue to explore performance from a OR/M layer perspective, then a Business Layer and finally UI layer in a future post.

    Until then Cheers and Happy Programming.

    Microsoft answers some data related questions…

    Standard

    These were the questions that was nagging many developers, for example future of LINQ2Sql, now that we have Entity Framework (I have heard people talking about the difference in usage scenarios), Where did ADO.Net data services go? etc….

    I found this very useful page answering these questions, here is the link -> go

    Added on 07/28/10 – Check out this article, talks about the past, present and future of data development technologies from Microsoft -> link
    Cheers!

    A Cold Case Solved !

    Standard

    We have several web user controls in one of our sites. These controls are all placed in a Controls sub directory. Many controls are created runtime and added to placeholders in the page. When we build this particular site (currently uses ASP.Net 3.5) we used to get this
    error message…
    The type or namespace name ‘Controls_XXXXX’ could not be found
    (are you missing a using directive or an assembly reference?)

    And design time Visual Studio was somehow not aware of these control’s classes!

    Today I stumbled upon this MSDN link, addresses this very issue.

    http://msdn.microsoft.com/en-us/library/c0az2h86.aspx

    Essentialy what it says is to add class name to your controls ascx file’s directive:

    <%@ Control Language="C#" AutoEventWireup="true" CodeFile="PickList.ascx.cs" Inherits="Controls_PickList" ClassName="Controls_PickList" %>
    

    And use Reference directive in your page where you use these control…

    <%@ Register src="Controls/PickList.ascx" tagname="PickList" tagprefix="uc1" %>
    

    Even if you are programmatically using these controls…

    Cold case solved!!
    Phew!!!

    Build a web site using ASP.Net MVC – Part 1

    Standard

    At last, I have decided to get my hands dirty with some real project using ASP.Net MVC. This will give me an opportunity to explore various features that are offered with ASP.Net MVC extension. There are lots
    of articles available to read about this online. I’ve been reading all these time and now its time to do something more than reading …

    How to prepare for development?

      Install .Net Framework 3.5 SP1 – If you do not have it yet – get it from here.
      Install Visual Studio 2008 Service Packs – Good to have..
      Install ASP.Net MVC 1.0 – Get it from here.

    Like I mentioned earlier in this note, there are a lot of articles out there, so I have decided to pick the best for my reading.

    1. Scott Guthrie’s Blog
    2. Asp.net MVC Official Site

    I am planning to use these 2 great resources, to build the site. Guthrie has build a tutorial and a sample site named Nerd Dinner which I think should give me a good starting point.

    This is it for now, I will posting notes as we progress…
    Lot of reading and research to do…

    Cheers!

    Using LINQ to manipulate data in DataSet/DataTable

    Standard

    Have you tried a micro ORM for your data access? Click to read more


    LINQ (Language Integrated Query) provides a very easy and fast way to manipulate data that is cached in a DataSet. In .Net applications, DataSet represents a disconnected cache of data. Your application typically needs to search, filter thru this data in order to display this data according to the need. DataView provides a very limited options
    when it comes to creating a filtered view of the data. LINQ extends these capabilities to a greater extend.

    A LINQ query opertaion consists of 3 actions (Ref:MSDN): obtain the data source, create the query and execute the query.

    Any datasource that implements the IEnumerable(T) generic interface can be queried thru LINQ. So DataTable objects are good candidates to do any LINQ query opertions, we will see using the following examples, how some common tasks can be done using LINQ queries.

    For our example, we will consider that our DataSet has one(1) table with the following schema,

    dtCustomer (CustomerID,CustomerName,Address,City,PostalCode,State,Country,PhoneNumer)
    

    A simple select:

    IEnumerable query =
        from customer in dtCustomer.AsEnumerable()
        select customer;
    

    Till this point, we have the LINQ query ready, but not executed it yet, query is executed when we actually use it.

    foreach (DataRow dr in query)
    {
        Console.WriteLine(dr.Field(&quot;CustomerName&quot;));
    }
    

    At this point, our query is executed and it prints the names of the customer.

    We can also, get the resulset as a DataView by simply doing,

    DataView view = query.AsDataView();
    

    Most times, when we are dealing with DataSet/DataTable, data we will be creating a DataView as result of our LINQ query. ToList(), ToArray() methods are also very useful when you want to get your resultset
    as a generic list or Array (Think AJAX/JSON!).

    Lambda Expressions can be used as parameters to LINQ queries.

    IEnumerable customers =
        query.Where(c =&gt; c.Field(&quot;CustomerName&quot;).Containes(&quot;John&quot;));
    
    //All names that contain &quot;John&quot;
    foreach (DataRow cust in customers)
    {
        Console.WriteLine(cust.Field(&quot;CustomerName&quot;));
    }
    

    Simple Where Clause:

    EnumerableRowCollection query
                  = from customer in dtCustomer.AsEnumerable()
                    where customer.Field(&quot;State&quot;) == &quot;NJ&quot;
                    select customer;
                DataView njview = query.AsDataView();
    

    Pretty simple, njview represents all customers that live in NJ.
    You can extend the example to add more criteria to your where clause…

    EnumerableRowCollection query
                  = from customer in dtCustomer.AsEnumerable()
                    where customer.Field(&quot;State&quot;) == &quot;NJ&quot; &amp;&amp; customer.Field(&quot;PostalCode&quot;) == &quot;08807&quot;
                    select customer;
                DataView njview = query.AsDataView();
    

    It is useful to note that when you write your where clause, you leverage the power of your C# (or VB.Net) language features to search and filter your resultset using LINQ.

    So, A SQL where clause is

    where customer.Field(&quot;State&quot;) == &quot;NJ&quot;
         where customer.Field(&quot;State&quot;) != &quot;NJ&quot;
    

    A SQL Like clause is

    where customer.Field(&quot;CustomerName&quot;).Containes(&quot;John&quot;)
    

    Skip and Take allows to get the skip the first n rows or get the top n rows as a result of the query.

    EnumerableRowCollection query
                  = (from customer in dtCustomer.AsEnumerable()
                    where customer.Field(&quot;State&quot;) == &quot;NJ&quot;
                    select customer).Skip(3);
    
    EnumerableRowCollection query
                  = (from customer in dtCusomter.AsEnumerable()
                    where customer.Field(&quot;State&quot;) == &quot;NJ&quot;
                    select customer).Take(3);
    
    

    Simple ORDER BY clause:

    EnumerableRowCollection query
                  = from customer in dtCustomer.AsEnumerable()
                    orderby customer.Field(&quot;CustomerName&quot;)
                    select customer;
    

    Above query, gets the result order by customer name (ascending is default). And if you want it by descending order,

    EnumerableRowCollection query
                  = from customer in dtCusomter.AsEnumerable()
                    orderby customer.Field(&quot;CustomerName&quot;)  descending
                    select customer;
    

    Reverse ORDER:

    EnumerableRowCollection query
                  = (from customer in dtCustomer.AsEnumerable()
                    orderby customer.Field(&quot;CustomerName&quot;)
                    select customer.Reverse();
    

    Simple JOIN Operator:

    var customers = ds.Tables[&quot;Customer&quot;].AsEnumerable();
    var orders = ds.Tables[&quot;Order&quot;].AsEnumerable();
    var query =
        		from customer in customers
        		join order in orders
    		on order.Field(&quot;CustomerID&quot;)
        		equals customer.Field(&quot;Customer&quot;)
    	into custOrders
    	select custOrders;
    

    All the examples given above is just the tip of iceberg on what you can do with LINQ and Lambda expressions. There is tons of samples and articles available on Internet on this, if you are looking for a way to simplyfy and speed up your business processing logic in your middle tier, LINQ is something you need to adopt!

    Cheers!

    Inserting/Updating Rows in Sql Database – Avoid chatty interface

    Standard

    Its a good design practise to avoid chatty interfaces. This is not only true when we design our objects and interactions among objects, but also for your database interactions. For example if you have multiple rows to be updated to your table, its highly inefficient to fire your inserts one at a time to your database. If you are using a dataset, in ADO.net 1.1, data adapters update method prcessed each datarow one at a time. In ADO.Net 2.0, there is a new property for the adapter alled “UpdateBatchSize” by which you can control how many rows are send as a batch. To read more about this click on this MSDN link

    But how about you have a list of your custom objects you want to use to do your inserts, in this case you can use the power of Xml processing in Sql Server, trick is to format your request as an Xml string and send it once to your database stored proc. This stored proc will process this Xml string and it updates/inserts your rows.

    To demonstrate this lets, say you want to insert rows into your Employee Table, represented by Employee (ID,Name)

    From your .net code, you will format an Xml request string similar to this,

    < EmployeeList >
    < Employee ID=”E001″ Name=”John Doe” / >
    < Employee ID=”E002″ Name=”Mary Smith” / >
    < Employee ID=”E003″ Name=”Luke Skywalker” / >
    </ EmployeeList >

    and here is how your stored procedure will look like,


    CREATE PROCEDURE [dbo].[sp_SaveRows]
    (
    @request ntext
    )
    AS
    Begin
    DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @request

    Declare @Id nvarchar(10)
    Declare @Name nvarchar(255

    DECLARE curs CURSOR FOR
    SELECT Id, Name
    FROM OPENXML (@idoc, '/savelist//saveitem',2)
    WITH (
    [Id] nvarchar(10) '@Id',
    [Name] nvarchar(255) '@Name',
    )
    OPEN curs
    FETCH NEXT FROM curs INTO @Id,@Name
    WHILE @@FETCH_STATUS = 0
    BEGIN

    -- Do your insert/update here!

    FETCH NEXT FROM curs INTO @Id,@Name
    END
    CLOSE curs
    DEALLOCATE curs

    End

    As you can see, we use OPENXML call to read the Xml file and use it in cursor, if you do not want to use a cursor you can always directly insert into table using the select (with openxml). Use cursor if you want to do any kind of data manipulation or rule checking, calculations, etc…

    Cheers!

    ADO.Net and Sybase ! Strange behavior !

    Standard

    This is what I see, may be there is something that I don’t see !
    I am using .Net v1.1, Sybase db ver 12.5 with Sybase ASE OleDb Provider Ver 2.70.
    When I create parameters for my command (CommandType is StoredProcedure), it expects me to create the params in the same order as specified within the stored procedure. I am creating the params using names and not by ordinal! There is no error thrown even when the params are in different order, values simply gets assigned to the wrong params !!

    Did any one of you have seen similar issue ??

    Infragistics Net Advantage 2005 Vol 3 – Tips and Tricks – Part 1

    Standard

    1) For development, always install the complete product, dont try to copy the Infragistics assemblies script files manually to set up your dev machine. A proper install will make sure that the components are added to your VS.Net IDE toolbox. If your install fails to do this, there is a command line utilty provided (available from Infragistics Program Group) with the product that will add VS.Net toolbox tab.
    If you are upgrading Infragistics from an older version to newer version, make sure to upgrade your project files using .Net Project Upgrade Utility (also found in Infragistics Program Group). Make sure that you have all the latest hot fixes applied (to avoid nasty surprises). A complete installation will also install
    help files, regsiter them propery, it also comes with a nice Samples Browser with a lot of working samples.

    2) First time when you starting your development with Infragistics, (for example UltraWebgrid), you set your references to the required assemblies, dragged the control on to your form and ran it. If you get a runtime error pointing to missing “BorderStyle” class/namespace reference, you probably need to set “CopyLocal” property for the WebUI assembly to True. To do this locate Infragistics.WebUI.Shared.v5.3, right click and Properies, set CopyLocal to true.

    3) Infragistics provides you a very rich UI experience. How does it do it? It uses a mix of client side javascripting and server side programs. Infragistics comes with a neat collection of client side javascript library for each of its controls. This provides rich client side features. It is AJAX enabled, and makes out-of-band calls to server to load data when the control’s properties are set to use that feature. When you intsall the product (example: Ver 2005 Vol 3)
    you will notice that a virtual directory named “ig_common” is created for you. This virtual directory hosts the required javascript files and other resources that are required for running Infragistics controls.

    4) Discussion of the previous point leads us to the deployment of applications that uses Infragistics, For a simple deployment follow these steps,

    • Copy the Infragistics Assemblies that are used by your application to a install folder in your server. These are signed assemblies. Add these to Global Assembly Cache (GAC).
    • Copy the directory that is pointed by ig_common virtual directory in your dev
    • Create a virtual directory in your server, point it to the folder explained in the previous step.
    • Build your application that uses Infragistics, deploy it to the server ( which ever way you choose, xcopy, copy project..)

    5) Page.SmartNavigation – Set this to False from design view. I have seen many developers complaining about this feature in v1.1. Setting this property to true is not recommended (so I was told by Infragistics Support once!)when your page has Infragistics components. If you want to preserve the scroll position on your page use some Javascript technique.

    6) UltraWebGrid – Simple Binding – Works pretty much the same way as a regular ASP.Net grid. Please remember that UltraWebgrid can have multiple bands (virtual grids within grid) and columns collection is within the Band. Another important property is DisplayLayout. Most of the UI elements are defined within this. If you are binding the grid on the fly relying on the underlying datasource for columns, Set DisplayLayout > AutoGenerateColumns to True. You can also choose to add columns design time, setting the data field names for each columns, in that case set AutoGenerateColumns to False.

    Example:

    myGrid.DataSource = myDataSet.Tables[0].DefaultView;
    myGrid.DataBind();

    Once the DataBind() is done, the grid fires “InitalizeLayout” event. This is one event you can hace code to handle some useful
    properties of the grid. This can be a good place to handle setting the column widths, making some columns hidden, setting “keys”
    for columns, turning on/off sorting for columns, handling header captions and style to name a few.


    //Do not forget to import the namespace
    using Infragistics.WebUI.UltraWebGrid;
    using Infragistics.WebUI.Misc;
    :
    :
    //Examples of setting keys for columns; you can use the key to grab handle to this column
    myGrid.Bands[0].Columns[0].Key = "Check";
    myGrid.Bands[0].Columns[1].Key = "Id";

    //Setting Align
    myGrid.Bands[0].Columns[0].CellStyle.HorizontalAlign = HorizontalAlign.Center;

    //Setting width
    myGrid.DisplayLayout.Bands[0].Columns[0].Width = Unit.Pixel(30);

    //setting click action on header
    myGrid.DisplayLayout.Bands[0].Columns[0].Header.ClickAction = HeaderClickAction.NotSet;

    //Setting Header Caption
    myGrid.DisplayLayout.Bands[0].Columns[0].Header.Caption = "My Caption";

    //set sort for all bands and columns of the grid to "No Sort"
    myGrid.DisplayLayout.HeaderClickActionDefault = HeaderClickAction.NotSet;

    More coming sooon….

    Response.Redirect not working in server?

    Standard

    [ASP.Net] Have you ever had an instance where your Response.Redirect calls are not working when you moved your code to remote server (every thing worked well in your local machine), first thing you check is your Page’s SmartNavigation settings. If you have turned on your SmartNavigation (in v1.1) this might give problems with your Response.Redirect ( Server.Transfer will work ).

    As a work around, set your SmartNavigation to false, before calling Response.Redirect

    Page.SmartNavigation = false;
    Response.Redirect("yourpage.aspx");