Friday, February 13, 2015

The Effect of Incorrect SQL Data Types

On taking over an existing data transfer and comparison system I was intrigued to notice that a seemingly simple SSIS process was taking over four hours to run - on a daily basis.  Something didn’t seem right, and on investigation it turned out there were lots of improvements to be made.  This blog is covering only one of them: the importance of choosing the correct SQL data types.


Three hours of this process was taken up by transferring data across the network between two servers.  There were only 8 million rows of simple text and numbers and the time it was taking was clearly excessive.  As the number of rows was increasing daily, this was something that needed attention as the running time was only going to increase.

The Problem

It was simple, the data was stored in the source and destination tables as nvarchar(max).  Each column, no matter whether it held an 8 digit date or a 3 character code, was being stored in nvarchar(max).

Some might say what difference does that make?  This data type will hold string data of any length, it’s a variable length data type, SQL will only use as much storage as it needs.  True, but SQL also compresses data where it can and unfortunately nvarchar(max) cannot be compressed.  And even if space is not an issue for you, once you start to transfer the data you will definitely feel the effects.

The Solution

As the source table was not under my control I did not want to affect any other possible processes that wrote to it, so I created a new table in the source database using the most appropriate data types: int, varchar(20), char(3), etc.  As part of the daily process I copied the data into my new table and then kicked off the transfer to the destination table which also now had the correct structure. 

The Result

55 seconds.  I did not believe it at first and had to run the process a number of times to satisfy myself that it was doing everything it needed to.   It was true.  The data transfer time was reduced from three hours to 55 seconds!

Whether this was an oversight by a developer, lack of time to go back and review, or simply due to inexperience, it was costly.  The network was being tied up by this data transfer on a daily basis.  In fact on fixing the problem the whole process needed to be rescheduled as it was then finishing too quickly and other parts weren’t ready for the next steps to start running.

Sometimes going back to the very beginning can be extremely beneficial in terms of productivity and efficiency. 

The fact that the same unchanged data was being transferred every day is not being addressed here.  That deserves its own blog entirely.

10 comments:

  1. Very nice example of how incorrect data types can screw things up!

    ReplyDelete
  2. Very nice example of how incorrect data types can screw things up!

    ReplyDelete
  3. Thank you for the great article. This is something I am definitely going to share!

    ReplyDelete
  4. Thanks for sharing informative article on sas. It helped me to understand the career prospects in
    SAS Training in Chennai

    ReplyDelete
  5. Really nice information you had posted. Its very informative and definitely it will be useful for many people

    Email Marketing Chennai

    ReplyDelete
  6. Thanks you for sharing the unique content. you have done a great job. thank you for sharing such a unique content.
    Email Marketing Chennai

    ReplyDelete
  7. Superb. I really enjoyed very much with this article here. Really it is an amazing article I had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article.thank you for sharing such a great blog with us. expecting for your.
    Digital Marketing Company in India
    seo Company in India

    ReplyDelete
  8. It's interesting that many of the bloggers to helped clarify a few things for me as well as giving.Most of ideas can be nice content.The people to give them a good shake to get your point and across the command.

    web designing training in chennai
    java training in chennai

    ReplyDelete
  9. Its a wonderful post and very helpful, thanks for all this information. You are including better information regarding this topic in an effective way.Thank you so much

    Installment loans
    Payday loans
    Title loans
    Cash Advances

    ReplyDelete