set based iterations – section 2, chapter 4

Hugo Kornelis starts off by telling us in this section that some of the assumptions we may have learned early in our career may be incorrect.  I recall being told early on in my career that if I could get a hook into set based theory, I would make it far in the database world of IT.  At about the same point in my career, I learned that cursors were the bane of databases and should be avoided.

I like how Hugo simply lets us know that both of these impressions have their place, and that something between the two could provide a good solution.  Instead of simply declaring it to be so, he actually gives us some concrete examples of when to use iterative vs declarative vs a combination of both.

After stepping through a couple samples of each type of solution, it is comforting to see that the solution is to test your possible solutions yourself.  Don’t simply depend on what others have told you, but put your ideas to the test, and find the best way to solve the problem.  It may surprise you to find that one or the other will perform well, but a combination actually becomes the fastest in the majority of situations.

The minutia in which he dissects the examples and the SQL solutions makes you want to reproduce his steps and prove it for yourself, or at least see it all in action. The details of adding an order by or removing it, are priceless.
While the examples given may not actually fit in with your needs, it’s a healthy endeavor to go thru the logic and code, and see how our favorite SQL engine performs what was requested of it.

In the summary of the chapter, he states it plainly that there is no easy answer or matrix that you can use to find the best solution.  I like that he even goes out on a limb to denounce his favorite solution (set-based iteration), saying that this solution may not perform as well as expected. It’s even a breath of fresh air to hear that in many cases, this  solution has incurred a performance decrease.  Keeping this new solution as a possible tool in your toolbox is invaluable for your future needs.  Use his examples, try them out, attempt to understand why it works the way it does, and apply this knowledge and experience to your future needs.  But always remember, there is never a panacea for all performance problems.  You need to test and test and test and remember how your data will grow and impact your solution.  It may be good now, but not in the future. 

Well done explanation of a difficult explanation, and great code based examples with detail to help us all learn.  My thanks to Hugo for writing this well versed section.  I can’t wait to try each of the solutions myself over and over, to remind myself how it all works. 

TJay Belt is a seasoned IT professional with over a decade of technical experience. Currently serving as a Senior Database Engineer. He has worked at several software companies, working at internal customer sites in the United States and abroad. He has always gravitated to the database side of each job and excelled. TJay studied Computer Science at Brigham Young University and is a member and active volunteer of PASS.
When TJay is not at work, or on call, he enjoys spending time with his family; playing racquetball, riding dirtbikes, waterskiing/boating or simply spending time together.


0 Responses to “set based iterations – section 2, chapter 4”

  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 11 other followers