Tips to improve SQL Server database design and performance

Best performance is the main concern to develop a successful application. Like a coin database is the tail side (back-end) of an application. A good database design provides best performance during data manipulation which results into the best performance of an application.

During database designing and data manipulation we should consider the following key points:

1. Choose Appropriate Data Type

   Choose appropriate SQL Data Type to store your data since it also helps in to improve the query performance. Example: To store strings use varchar in place of text data type since varchar performs better than text. Use text data type, whenever you required storing of large text data (more than 8000 characters). Up to 8000 characters data you can store in varchar.

2. Avoid nchar and nvarchar

   Practice to avoid nchar and nvarchar data type since both the data types takes just double memory as char and varchar. Use nchar and nvarchar when you required to store Unicode (16-bit characters) data like as Hindi, Chinese characters etc.

3. Avoid NULL in fixed-length field

   Practice to avoid the insertion of NULL values in the fixed-length (char) field. Since, NULL takes the same space as desired input value for that field. In case of requirement of NULL, use variable-length (varchar) field that takes less space for NULL.

4. Avoid * in SELECT statement

   Practice to avoid * in Select statement since SQL Server converts the * to columns name before query execution. One more thing, instead of querying all columns by using * in select statement, give the name of columns which you required.

   
    -- Avoid
   SELECT * FROM tblName
   --Best practice 
   SELECT col1,col2,col3 FROM tblName 
   
   

5. Use EXISTS instead of IN

   Practice to use EXISTS to check existence instead of IN since EXISTS is faster than IN.

   
    -- Avoid 
   SELECT Name,Price FROM tblProduct 
   where ProductID IN (Select distinct ProductID from tblOrder)
   --Best practice 
   SELECT Name,Price FROM tblProduct 
   where ProductID EXISTS (Select distinct ProductID from tblOrder)
   
   

6. Avoid Having Clause

   Practice to avoid Having Clause since it acts as filter over selected rows. Having clause is required if you further wish to filter the result of an aggregations. Don't use HAVING clause for any other purpose.

7. Create Clustered and Non-Clustered Indexes

   Practice to create clustered and non clustered index since indexes helps in to access data fastly. But be careful, more indexes on a tables will slow the INSERT,UPDATE,DELETE operations. Hence try to keep small no of indexes on a table.

8. Keep clustered index small

   Practice to keep clustered index as much as possible since the fields used in clustered index may also used in nonclustered index and data in the database is also stored in the order of clustered index. Hence a large clustered index on a table with a large number of rows increase the size significantly. Please refer the article Effective Clustered Indexes

9. Avoid Cursors

   Practice to avoid cursor since cursor are very slow in performance. Always try to use SQL Server cursor alternative. Please refer the article Cursor Alternative.

10. Use Table variable inplace of Temp table

    Practice to use Table varible in place of Temp table since Temp table resides in the TempDb database. Hence use of Temp tables required interaction with TempDb database that is a little bit time taking task.

11. Use UNION ALL inplace of UNION

    Practice to use UNION ALL in place of UNION since it is faster than UNION as it doesn't sort the result set for distinguished values.

12. Use Schema name before SQL objects name

    Practice to use schema name before SQL object name followed by "." since it helps the SQL Server for finding that object in a specific schema. As a result performance is best.

    
     --Here dbo is schema name
    SELECT col1,col2 from dbo.tblName
    -- Avoid
    SELECT col1,col2 from tblName 
    
    

13. Keep Transaction small

    Practice to keep transaction as small as possible since transaction lock the processing tables data during its life. Some times long transaction may results into deadlocks. Please refer the article SQL Server Transactions Management

14. SET NOCOUNT ON

    Practice to set NOCOUNT ON since SQL Server returns number of rows effected by SELECT,INSERT,UPDATE and DELETE statement. We can stop this by setting NOCOUNT ON like as:

    
     CREATE PROCEDURE dbo.MyTestProc
    AS
    SET NOCOUNT ON
    BEGIN
    .
    .
    END 
    
    

15. Use TRY-Catch

    Practice to use TRY-CATCH for handling errors in T-SQL statements. Sometimes an error in a running transaction may cause deadlock if you have no handle error by using TRY-CATCH. Please refer the article Exception Handling by TRY…CATCH

16. Use Stored Procedure for frequently used data and more complex queries

    Practice to create stored procedure for quaery that is required to access data frequently. We also created stored procedure for resolving more complex task.

17. Avoid prefix "sp_" with user defined stored procedure name

    Practice to avoid prefix "sp_" with user defined stored procedure name since system defined stored procedure name starts with prefix "sp_". Hence SQL server first search the user defined procedure in the master database and after that in the current session database. This is time consuming and may give unexcepted result if system defined stored procedure have the same name as your defined procedure.

How to have a Select All Multi-Value Cascading Parameter

I’ve seen several cases that involved Multi-Value Cascading Parameters and how the behavior of those work.  Let me start with the default (Out of the box) behavior.  The example report I’ll be using will make use of the AdventureWorksDW sample database.  I will also be using SQL 2008 R2 November Release, but the same will apply to SQL 2008 Reporting Services as well.

In my report, I have three multi-value parameters.  Each one dependent on the parameter before it (cascading parameters).  The parameters are Category, Subcategory and Product, in that order.

As you can see above, I have Bikes selected for the category which will select all by default as that is my first selection.  But what happens if I now add in another category?

Only the first three are selected after adding Components to the category list.  We have seen where people feel this is a bug, but that was actually intended.  We will only maintain what your current selection is.  On the first selection, we didn’t have anything, we actually didn’t have a dataset yet because I didn’t have it loading by default.  But that initial selection will be Select all.  After that, it will be based on what you have selected.  Removing entries will make it seem that we still select all, but that is because the items left were still selected previously, so nothing really changes.  But adding items to the mix will cause them to not be selected by default.  Only the original selection will remain.

So, then the question comes in, what if i want them to all be selected whenever it gets refreshed?  We currently do not provide for that ability out of the box, but I have a way you can add the functionality into your report. 

I will add a disclaimer here.  The code that you will see in this blog has not been fully tested and should not be used in production.  Please be sure to review it first and determine how it will affect performance within your report.

Subcategory

Lets start with the Subcategory parameter as there is nothing we need to do with Category.  To start, you will want to add the following into the code section of the report.  This can be found by going to Report on the Menu bar and selected Report Properties.

Function EncodeParamValue(ByVal ParentParam As Object(), ByVal ChildParam As Integer) As String

    Dim i As Integer
    Dim EncodedChildParam As String = ""

    'Concatenate the ParentParam as the prefix
    For i = 0 To ParentParam.Length - 1
        If i = 0 Then
            EncodedChildParam = CStr(ParentParam(i))
        Else
            EncodedChildParam = EncodedChildParam + "-" + CStr(ParentParam(i))
        End If
    Next i

    'Add the ChildParam at the end
    EncodedChildParam = EncodedChildParam + "_" + CStr(ChildParam)

    Return EncodedChildParam

End Function

Function DecodeSubcatParamValue(ByVal SubCategoryIDs As Object()) As Object()

    Dim i As Integer
    Dim SubCategoryID As String

    'Remove the prefix on the parameter values to restore the original value
    For i = 0 To SubCategoryIDs.Length - 1
        SubCategoryID = SubCategoryIDs(i)
        SubCategoryIDs(i) = SubCategoryID.Substring(SubCategoryID.IndexOf("_") + 1)
    Next i

    Return SubCategoryIDs

End Function

These two functions will be what we use to alter the parameter values.  Essentially saying that the value includes everything.  But we strip it out when we actually want to use it.

The next step is to modify the dataset for Subcategory to make use of the EncodeParamValue function.  We will add a Calculated Field.  You can do this by going to the properties of the DataSet, selecting the Fields section and click on Add, select Calculated Field.

We will call this field ModifiedSubcategoryKey.  This name is based on what the value field was we were using for the Parameter itself which was the ProductSubcategoryKey field.  So, we are just modifying that field a bit.  The expression for that field will be the following:

=Code.EncodeParamValue(Parameters!Category.Value,Fields!ProductSubcategoryKey.Value)

We are using the EncodeParamValue to take in the Category (1st Parameter) list and tack it onto the SubCategoryKey. We then need to adjust the Available Values and Default Values to use the ModifiedSubcategoryKey instead of ProductSubcategoryKey.  This is done in the Parameter Properties for Subcategory.  You will want to change the Value Field to be ModifiedSubcategoryKey.

The last thing we will do for Subcategory is to change the refresh value to “Always Refresh”.  This is done in the Parameter Properties for Subcategory under the Advanced section.

That is all you need to get the Subcategory parameter drop down to work correctly.

Product

Now we need to make sure Product works with the SubcategoryKey, as it expects that as a parameter for its dataset.  It expects an Integer and right now we are giving it bit string. Without changing anything, you will see the following error:

We need to make use of our Decode function as the Parameter Value expression for the Product Dataset.  This is done in the Product Dataset properties under the Parameters section.  The expression we will use is the following:

=Code.DecodeSubcatParamValue(Parameters!Subcategory.Value)

This will take in the big string we created, and break it up to just give us the value we really want.  This allows the Product Drop down to show values, but in order for the Select all behavior to work, we need to use the Encode method as well.  So, we will add a Calculated Field to the Product Dataset called ModifiedProductKey.  The expression for this field will be the following:

=Code.EncodeParamValue(Parameters!Subcategory.Value,Fields!ProductKey.Value)

We then want to change the Available Values and Default Values for the Product Parameter.  This is done in the Parameter Properties for Product.  We want to change the Value Field to ModifiedProductKey.  This will get the Product Drop down to have the Select All behavior we are looking for.

This will introduce two levels of encoding on the product value, so I created a second Decode method which will strip it twice for use in the Report Dataset.

Function DecodeProductParamValue(ByVal ProductIds As Object()) As Object()

    Dim i As Integer
    Dim ProductId As String

    'Remove the prefix on the parameter values to restore the original value
    For i = 0 To ProductIds.Length - 1
        ProductId = ProductIds(i)
        'The first one removes the Category encoding
        ProductId = ProductId.Substring(ProductId.IndexOf("_") + 1)
        'The second one removes the SubCategory encoding
        ProductIds(i) = ProductId.Substring(ProductId.IndexOf("_") + 1)
    Next i

    Return ProductIds

End Function

Report Dataset

The last thing to take into account is the Dataset for the report itself.  You will need to use the Decode methods for the parameters within the Report Dataset like we did on the Product Dataset for the parameter listing. You will need to do this for both the SubCategory Parameter value as well as the Product Parameter Value usingDecodeSubcatParamValue and DecodeProductParamValue respectively.

Hopefully this will help you to get the desired behavior you are interested in.

Adam W. Saxton | Microsoft SQL Server Escalation Services

Reporting Services, 2008, Adam, Report Design, 2008 R2