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.

72 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. 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
  6. Excellent post!!!. The strategy you have posted on this technology helped me to get into the next level and had lot of information in it.
    python training in chennai
    python training in chennai
    python training in bangalore

    ReplyDelete
  7. In the beginning, I would like to thank you much about this great post. Its very useful and helpful for anyone looking for tips to help him learn and master in Angularjs. I like your writing style and I hope you will keep doing this good working.
    Angularjs Training in Bangalore
    Angularjs Training Institute In Bangalore
    Angularjs Course in Bangalore
    ccna Coaching Centres in Bangalore
    ccna Certification Course in Bangalore

    ReplyDelete
  8. Thank you for taking the time to provide us with your valuable information. We strive to provide our candidates with excellent care and we take your comments to heart.As always, we appreciate your confidence and trust in us

    Java training in Chennai

    Java training in Bangalore

    ReplyDelete
  9. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
    Devops Training in Bangalore
    Best Devops Training in pune

    ReplyDelete
  10. You’ve written a really great article here. Your writing style makes this material easy to understand.. I agree with some of the many points you have made. Thank you for this is real thought-provoking content
    Data science course in bangalore

    ReplyDelete

  11. Nice posting..thanks.
    Ijin..info investasi lahan keluarga di San Diego Hills memorial Park bebas biaya perawatan dan kebersihan selamanya klik
    travel trekking tips
    see the link Tent Camping 101 Exploring Smithriver

    ReplyDelete
  12. 3. if you want girls mobile numbers then this website is best for you . you can visit on this website and get their information and you also can meet with thrm and go for a date . click here to use our website --- online dating website

    ReplyDelete
  13. 1. many peoples want to join random whatsapp groups . as per your demand we are ready to serve you whatsapp group links . On this website you can join unlimited groups . click and get unlimited whatsapp group links

    ReplyDelete
  14. Thanks for sharing valuable information.It will help everyone.keep Post.
    Goa State Lottery

    ReplyDelete
  15. amazing post written ... It shows your effort and dedication. Thanks for share such a nice post. Please check whatsapp status in hindi

    ReplyDelete
  16. Superb Post. Your simple and impressive way of writing this make this post magical. Thanks for sharing this and please checkout this best wifi names

    ReplyDelete
  17. Really i appreciate the effort you made to share the knowledge. The topic here i found was really effective...

    Start your journey with SAP S4 HANA Simple Logistics Training in Bangalore and get hands-on Experience with 100% Placement assistance from experts Trainers @Softgen Infotech Located in BTM Layout Bangalore. Expert Trainers with 8+ Years of experience, Free Demo Classes Conducted.

    ReplyDelete
  18. Really i appreciate the effort you made to share the knowledge. The topic here i found was really effective...

    Really i appreciate the effort you made to share the knowledge. The topic here i found was really effective...

    ReplyDelete
  19. Thanks for one marvelous posting! I enjoyed reading it; you are a great author. I will make sure to bookmark your blog and may come back someday. I want to encourage that you continue your great posts.Prathima Infotech training center bangalore

    ReplyDelete
  20. Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site.Prathima Infotech training center bangalore


    ReplyDelete
  21. Thanks for sharing this blog. This very important and informative blog.Become a Trainer

    ReplyDelete
  22. Learned a lot of new things from your post! Good creation and HATS OFF to the creativity of your mind.Student Reviews

    ReplyDelete
  23. Thanks for sharing this blog. This very important and informative blog. content






    ReplyDelete
  24. Blockchain Course in Bangalore with 100% placement. We are the Best Blockchain Course Institute in Bangalore. Our Agile testing course and Certification courses are taught by working professionals who are experts in Blockchain.

    Blockchain Training in Bangalore

    Blockchain course in bangalore

    Blockchain in bangalore

    Blockchain classes in bangalore

    Blockchain course institute in bangalore

    Agile testing course and Certification course syllabus

    best Blockchain course

    Blockchain course centers

    ReplyDelete
  25. http://eohmicrosoft.blogspot.com/2015/02/the-effect-of-incorrect-sql-data-types.html

    http://damodapk.com/
    http://infotodaypk.com/

    ReplyDelete
  26. Thanks for sharing such a wonderful blog with us. It makes the business develop more and more.
    DevOps Training In Hyderabad

    ReplyDelete
  27. An impressive share! I have just forwarded this onto a coworker who had been conducting a little homework on this. And he in fact bought me dinner due to the fact that I found it for him... lol. So allow me to reword this.... Thank YOU for the meal!! But yeah, thanx for spending time to discuss this topic here on your website. Also Check out My Website and Blog

    ReplyDelete
  28. A great piece of writing! You have an efficient flair for high quality writing. I am highly impressed by your way of writing this content beyond words.
    SAP training in Kolkata
    Best SAP training in Kolkata
    SAP training institute in Kolkata

    ReplyDelete
  29. this blog is very useful for who wants to learn DevOps course if you want to take DevOps training once go through thid blog.

    ReplyDelete
  30. Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot.

    SAP Online Training

    SAP Classes Online

    SAP Training Online

    Online SAP Course

    SAP Course Online

    ReplyDelete
  31. I concur with a ton of the focuses you made in this article. I value the work you have placed into this and expectation you keep composing regarding this matter.
    Data Science training in Mumbai
    Data Science course in Mumbai
    SAP training in Mumbai

    ReplyDelete
  32. Thanks for sharing information awesome blog post Online Education Quiz website For Exam Follow this website Gk in Hindi

    ReplyDelete
  33. If I had to give a good example of top quality content, I would surely take this one. It's a well-written explanation that holds your interest.
    SAP training in Mumbai
    SAP course in Mumbai

    ReplyDelete
  34. You have presented such an informative article with great quality content and well laid out points. I have gone through this article. I also possess the same opinion with you on many of your views.
    SAP training in Kolkata
    SAP course in kolkata

    ReplyDelete
  35. 이 웹 사이트는 내가이 주제에 대해 원했던 모든 정보를 가지고 있고 누구에게 물어봐야할지 몰랐습니다.메이저놀이터
    공유해 주셔서 감사합니다!

    ReplyDelete
  36. Login Your TriumphFX Account. Read In Depth TriumphFX Review.

    ReplyDelete
  37. Aximtrade Mt4 Download Is A Investment Company And One Of The Largest Forex Brokers. With Clients Based Around The Globe, We Provide Global Financial Solutions For Private And Corporate Customers Across All Major Asset Classes Including Equities, Fixed Income, Etfs, Cfds And Commodities.

    ReplyDelete
  38. That Is Very Interesting, You Are An Excessively Skilled Blogger. Stay In Control Of Your Online Trades With AximTrade Review Login, A Cloud-based Online Trading Platform.

    ReplyDelete
  39. If You Are Looking For A Reliable Fx Broker, Don't Rush And Read This XM REVIEW Review First. This Is A Serious Warning Against The Broker's Illegal Activities.

    ReplyDelete
  40. AVATRADE REVIEW Review - Find Out Everything About This Forex Broker. Read Our Detailed Fx Choice Review And Make Sure If This Broker Is For You. We Scrutinized The Broker And The Trading Conditions Thoroughly.

    ReplyDelete
  41. Good to visit your weblog again, it has been months for me. Nicely this article that i've been waiting for so long. I will need this post to total my assignment in the college, and it has exact same topic together with your write-up. Thanks, good share.
    data science training institute in hyderabad

    ReplyDelete
  42. Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue posting. A debt of gratitude is in order for sharing.360digitmg-data science data analytics course in surat

    ReplyDelete
  43. Are you Worried to Know AMD Ryzen 7 5700X 8-Core 16 Thread Unlocked Desktop Processor Price in ? let’s Discuss the Price of the AMD Ryzen 7 5700X 8-Core 16 Thread Unlocked Desktop Processor[Read more]

    ReplyDelete
  44. edius 9,edius 9 crack,edius,edius pro 9,edius 8 crack,edius crack,edius pro 9 crack,edius 8,how to fix edius pro 9 installation error,edius 9 free download .Edius 9 Lifetime Crack

    ReplyDelete
  45. Thank you for sharing this valuable information with us.
    Girvi Software
    Girvi Software

    ReplyDelete
  46. Language enter its follow. Chair girl man piece which.education

    ReplyDelete