Database Design and Performance

While designing the database below ideas helps to gain the better performance for the SELECT query

• Compromise with Denormalization :If a significant number of your queries require joins of more than five or six tables, you should consider Denormalization.
• Use Computed column :Instead of computing columns while reading better to add a computed column to calculate the value while DML. For example ORDER table have column Qty, Price, Discount. So suppose we want to total doller amount for each order then first we have to determine the dollar amount for each product.

         SELECT "Order ID", SUM("Unit Price" * Quantity * (1.0 - Discount))


        For a large set of orders, the query can take a long time to run. The alternative is to calculate the    dollar amount of the order at the time it is placed, and then store that amount in a column within the Orders table.

• Decide Between Variable and Fixed-length Columns: Fixed length columns always take maximum space defined by the schema, even when the actual value is empty. The downside for variable length columns is that some operations are not as efficient as those on fixed length columns. For example, if a variable length column starts small and an UPDATE causes it to grow significantly, the record might have to be relocated. Additionally, frequent updates cause data pages to become more fragmented over time. Therefore, we should use fixed length columns when data lengths do not vary too much and when frequent updates are performed.

• Use Smaller Key Lengths: An index is an ordered subset of the table on which it is created. It permits fast range lookup and sort order. Smaller index keys take less space and are more effective that larger keys. It is a particularly good practice to make the primary key compact because it is frequently referenced as a foreign key in other tables. If there is no natural compact primary key, you can use an identity column implemented as an integer instead

Reference :https://technet.microsoft.com/en-us/library/ms172432(v=sql.110).aspx

Tuning Performance of Bulk INSERT / Reduce transaction log file growth by Minimal Logging

  One of my friend asked me that is there any way in SQL Server to minimise the  transaction logging for a table so that INSERT operation run faster? He is expert in ORACLE  Database and In Oracle database have feature of NOLOGGING that you can set for a table.

   Answer is NO. SQL Server don't have any direct keyword as NOLOGGING but we have alternative to reduce the logging.

Requirement : Suppose you have a big table like Fact table where every night you used to load huge data around 100GB, here transact is not much matter as its not a transnational table. Now when you load this much of  data then Transnational Log file grow very fast, also the INSERT operation will run slow.

      We can tune the performance of  the INSERT operation in several ways; here we will discuss one of the way by reducing the logging. To reduce the logging we need to change the database Recovery model to SIMPLE or BULK_LOGGED. Even it is in FULL recovery model, you can use TABLOCK table hints to minimise the logging. Finally you can use below steps to boost the INSERT performance:

1. Set the Database Recovery model to SIMPLE and use the TABLOCK.
2.  Set the Database Recovery model to BULK_LOG and use the TABLOCK.
3.  Set the Database Recovery model to FULL and use the TABLOCK.

          Here option #1 is the best way, but if your database need point of time recovery and you used to take the transaction log backup then this option will not work for it. So you can use the option #3. When you used to dump the data from heterogeneous file you can use the option #2.These will also help to reduce the transaction log file growth:

   Below are the analyses to see the performance difference and Transaction Log file size differences:

   

  DEMO:

   I have created 6 Database as below with different Recovery model :

     

            create database bulktestsimple

alter database bulktestsimple  set recovery simple

create database bulktestsimpleLock

alter database bulktestsimpleLock  set recovery simple

create database bulktestFull

alter database bulktestFull  set recovery Full

create database bulktestFullLock

alter database bulktestFullLock  set recovery Full

create database bulktestbul

alter database bulktestbul set recovery bulk_logged

create database bulktestbulLock

alter database bulktestbulLock set recovery bulk_logged

Let's See the size of Log file of all the databases, here all log file size are 63 MB.

       Now create table in every databases and load the data.

    

1.    . Database with simple recovery mode 

1. use bulktestsimple
   GO
   
   
   --Creating blank table
   select a.* into DumpTable  from AdventureWorks2008.sys.objects a
   cross join AdventureWorks2008.sys.objects b
   cross join sys.tables
   where 1=2
   
   
   
   
   --Loading Data
   
   
   insert into DumpTable
   select a.*   from AdventureWorks2008.sys.objects a
   cross join AdventureWorks2008.sys.objects b
   cross join master.sys.tables

          2.Database is in simple Recovery Mode  and load data with TABLOCK hints:

  

select a.* into DumpTable  from AdventureWorks2008.sys.objects a

cross join AdventureWorks2008.sys.objects b

cross join sys.tables

where 1=2

--Loading Data

insert into DumpTable with (tablock)

select a.*   from AdventureWorks2008.sys.objects a

cross join AdventureWorks2008.sys.objects b

cross join master.sys.tables

     I have did same for all other 4 Databases and then we find below : 

    Log file size after data loading 

    Time  taken by different databases for same set of data load :

1. Simple Recovery mode , without table hints --59 Second

2. Simple Recovery mode , with table hints -- 16  Second

3. Full Recovery mode , Without table hints --1. 4 Minutes

5. Full Recovery mode , With table hints --25 Second

   Conclusion: With the help of TABLOCK we can reduce the Logging and increase the performance, Its only helpful for the Non transaction tables.