SQLServerFast

Bin packing part 5: Set-based iteration

Hugo Kornelis
2 Comments
One of the most common techniques authors use to keep their readers interested is to leave them with a cliff-hanger. It’s what I did when I finished part 4 of my series on the bin packing problem – never intending to leave you all hanging over a cliff for almost three years, though that is exactly what happened. My apologies to everyone who has been checking my blog on a daily basis all that time, in the idle hope of finally learning that faster method I promised. For those of you had have forgotten what I wrote in the previous…
Read More

Bin packing part 4: The set-based disaster

Hugo Kornelis
12 Comments
Almost a year ago now, I started a series of blog post on the bin packing problem. But after the first three posts, various reasons caused the research I still had to do for the fourth part to be massively delayed. It’s only now that I have finally found the time to finish my research and write up the fourth installment. After a nine-month delay, I can hardly expect you to remember what I covered in the first posts, so you may want to follow these links to re-read that: ·        The first post includes an explanation of the bin-packing…
Read More

Speaking at PASS

Hugo Kornelis
2 Comments
Are you planning to attend this year’s PASS Community Summit? There’s only three weeks left before the pre-conference seminars kick off, so if you’re not registered yet, now is the time to act! The session schedule shows two days with seven full-day pre-conference seminars each, followed by three days packed full of high-quality sessions – with four time slots per day, over three days, and twelve sessions in parallel at each time slot, you problem will not be finding sessions that are of interest to you, but rather choosing between them. And you will be able to see and hear…
Read More

Data modeling: art or science?

Hugo Kornelis
10 Comments
When I started blogging here on sqlblog.com, I intended to write about stuff like T-SQL, performance, and such; but also about data modeling and database design. In reality, the latter has hardly happened so far – but I will try to change that in the future. Starting off with this post, in which I will pose (and attempt to answer) the rather philosophical question of the title: is data modeling an art or a science? Before I can answer the question, or at least tell you how I think about the subject, we need to get the terms straight. So…
Read More

[OT] Alive and kicking

Hugo Kornelis
No Comments
Wow! Would you believe that it’s almost five months since my last blog post? How time flies. No, I have not forgotten about you. I know you’ve all been faithfully checking the site (or your feed) each day, maybe even each hour, to get my next post. What can I say? I’m sorry for keeping you waiting so long. The truth is, that I have been very, very busy. Lots of time has been spent on work (for without work, there will be no salary and hence no way to feed the family) and family (for why bother working hard…
Read More

Let’s deprecate UPDATE FROM!

Hugo Kornelis
59 Comments
I guess that many people using UPDATE … FROM on a daily basis do so without being aware that they are violating all SQL standards. All versions of the ANSI SQL standard that I checked agree that an UPDATE statement has three clauses – the UPDATE clause, naming the table to be updated; the SET clause, specifying the columns to change and their new values; and the optional WHERE clause to filter the rows to be updated. No FROM or JOIN – if you need data from a different table, use a subquery in the SET clause. The optional FROM…
Read More

Want a Service Pack? Ask for it!

Hugo Kornelis
32 Comments
Service pack 2 for SQL Server 2005 is already 11 months old. And there is still no sign of service pack 3 on the horizon. Why is that? Has Microsoft managed to release a perfect, completely bug-free product? No, of course not – with the size and complexity of a product such as SQL Server is, that will simply never happen. There have, in fact, enormous numbers of bugs been uncovered and fixed since SP2 was released. And roughly once every two months, a so-called “cumulative update package” gets released. The last one is officially called “Cumulative update package 5…
Read More

Bin packing part 3: Need for speed

Hugo Kornelis
2 Comments
In the first post of this series, I explained the bin-packing problem and established a baseline solution. The second post investigated ways to increase the packing efficiency. In none of these posts did I pay particular attention to performance – and frankly, it shows. Performance of all solutions presented thus far sucks eggs. Time to see what can be done about that. If you look in detail at the most efficient solution so far (dbo.OrderDesc, as described in the second post of the series), you’ll see that all 40,000 registrations are processed one by one, with a cursor. No surprise…
Read More

Bin packing part 2: Packing it tighter

Hugo Kornelis
No Comments
In my previous post, I explained the bin packing problem, explained an example scenario, and established a baseline for both speed and efficiency of bin packing algorithms by writing a rather crude cursor-based procedure. In this part, I will look at some small modifications that can be made to this code to make it better at packing bins as full as possible, so that less bins will be needed. Reordering the cursor The Baseline procedure didn’t process the registrations in any particular order. Granted, there is an ORDER BY Year, Quarter in the cursor declaration, but that is only needed…
Read More

Bin packing part 1: Setting a baseline

Hugo Kornelis
No Comments
Some problems can only be solved by brute-forcing every possible combination. The problem with such an approach, is that execution time grows exponentially as the amount of input data grows – so that even on the best possible hardware, you will get inacceptable performance once the input data goes beyond the size of a small test set. These problems are called “NP-complete” or “NP-hard”, and the most viable way to deal with them is to use an algorithm that finds a solution that, while not perfect, is at least good enough – with a performance that, while not perfect, is…
Read More

Hugo Kornelis

Married, father of 2 children.

Awarded 13x MVP (2006-2016; 2019-now), in the areas SQL Server and Data Platform.

Subscribe via Email

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

Archives

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close