Table of Contents: Everything developers need to know about SQL performance

MarkusWinand · 7 days

I know. I guess I'm one of the very few people not just writing something, but actually maintaining it. For almost two years now....

MarkusWinand · 7 days

Hi!

Seems it is available at my amazon.de store now. Shipping is done by amazon.de (Versand durch Amazon). So, you get the usual free delivery and all of that.

MarkusWinand · 7 days

Hi!

It is currently sold as printed book in German language: http://sql-performance-explained.de/ An English edition is on it's way (also printed), but there is no release date yet.

ebook is likely to appear as PDF soon.

Just follow my RSS feed or twitter account for the updates.

MarkusWinand · 7 days

Seems that I didn't make clear that I mean the insert itself--when you already know where to insert. Just the pointer magic.

The next page ("The B-Tree") is devoted to the search part--there you'll find the O(log(n)) stuff.

Edit: Well this sentence is not essential at this place--so I just removed it. There is a whole chapter about Insert, Delete and Update anyway.

Edit2: found out it IS essential, because the reason for the doubly-linked list is to avoid moving large amounts of data when inserting. So, I changed that: Yet it is possible to insert new entries without moving large amounts of data—it just needs to change some pointers.

That is what I originally wanted to say :)

better now?

MarkusWinand · 7 days

To make a long story short: because the postal service is a little bit slow. My first shipment to amazon.de is on its way. Should be available in a few days. But that's out of my control :/

But you can place a direct order here (no shipping fees).

note: that is the German edition--the English edition is not yet available.

MarkusWinand · 7 days

I'm actually working with "proof scripts". That is; Appendix C, "Example Schema" has the scripts to run the examples and see the result.

MarkusWinand · 3 months

It seems that this method relies on the fact that IDs must increment monotonically with time

The ID was just taken to make it unique. It is not required to increment with time.

So, if the functional requirement just says “by date, latest first” the row sequence is not specified for sales on the same date. So it may be any order--with respect to the functional requirement. Technically, you need a deterministic order, to make paging work correctly. Hence, you can add any unique column to the order by clause. The order shown to the client might change because of the additional order by column, but it is still ordered “by date, latest first” (as long as you keep SALE_DATE at first in the order by clause).

It all depends on the actual requirement. If you need it in "reverse chronological order" you need to consider the time as well. My example, however, uses the date only (from functional perspective).

MarkusWinand · 3 months

Hi!

That's the variant that was introduced with SQL:2003. That will also be covered on my site very soon. It is, in the best case however, working like the offset method.

MarkusWinand · 3 months

Just read your form submission ;)

Thanks, seems that I was so busy working with pipelined/indexed order by all the time that I didn't think what the statement actually said--and what happens without having a corresponding index.

Already fixed. And thanks for the compliments :)

MarkusWinand · 3 months

Practicing. Years.

I hope the book gives you an easier way to learn all that.

MarkusWinand · 3 months

Well, the chart was not meant to say "it pays off after 20 pages only." It's just that--given the right index--both methods are fast, but the seek method is still faster. When you are running hundreds of them at the same time, it might also pay off from page 2 onwards.

Performance is, however, not everything and I agree that the offset method is the best choice in many cases. But the seek method is also rather easy to handle when using row values--sigh.

And yes, I'm also in favor to avoid the classical paging approach, that's why I also mentioned the infinite scrolling at the end :)

MarkusWinand · 5 months

Because of many factors, but to make a long story short:

When you don't index it, the database has more freedom to access the data in an efficient way -- needing less IO operations. That may, or may not, bring more performance than sorting costs.

Have a look at the chapter intro for Sorting and Grouping and also in Chapter 1 for Anatomy: The Leaf Nodes and Slow Indexes for more details.

__Joker · 6 months

Hi,

Regarding "forwarding pointer": Oracle has them as well. The same problem exists (extra reads).

I wrote two articles about that, from Oracle perspective:

I thought about it, when I wrote that the data doesn't move in heap tables. I concluded, however, that this detail doesn't help to understand clustering indexes (IOT's). But I took a note to see if I can mention that in Chapter 9 (Insert, Delete and Update).

MarkusWinand · 8 months

Thanks. Hope you'll find the time. It's not as much as it looks on the first sight.

MarkusWinand · 8 months

Time will come. But not too soon, I guess.

When I think about the advice SQL Server Management Studio gives, it can be counter productive as well. Once a tool gives advice, people follow it blindly. I am not aware of any tool that would have earned this trust. Current tools are not even close to it. Instead, they give a good feeling of false safety...

MarkusWinand

TROPHY CASE

you'll need to login or register to do that

create a new account

login