Skip to Main Content
Refactoring SQL Applications
book

Refactoring SQL Applications

by Stephane Faroult, Pascal L'Hermite
August 2008
Intermediate to advanced content levelIntermediate to advanced
298 pages
9h 22m
English
O'Reilly Media, Inc.
Content preview from Refactoring SQL Applications

Chapter 2. Sanity Checks

And can you, by no drift of circumstance, Get from him why he puts on this confusion, Grating so harshly all his days of quiet With turbulent and dangerous lunacy?

—William Shakespeare (1564–1616)

Hamlet, III, 1

BEFORE ENGAGING IN SERIOUS REFACTORING WORK, YOU SHOULD CHECK SEVERAL POINTS, PARTICULARLY if there are a significant number of big, costly queries. Sometimes gross mistakes that are easy to fix have slipped in because of time pressures, ignorance, or a simple misunderstanding about how a DBMS works. In this chapter, I will discuss a number of points that you should control and, if necessary, correct, before you take reference performance measurements for a refactoring campaign. With any luck (I am lazy), refactoring may seem less urgent afterward.

One of the most critical aspects of DBMS performance with regard to unitary queries is the efficiency of the query optimizer (unfortunately, the optimizer cannot do much for poor algorithms). The optimizer bases its search for the best execution plan on a number of clues: typically, what the data dictionary says about existing indexes. But it also derives its choice of plan from what it knows about the data—that is, statistics that are stored in the data dictionary, the collection of which is usually a routine part of database administration. Or should be.

There are two things to check before anything else when you encounter performance issues on particular queries: whether statistics are reasonably up-to-date ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Modernizing Enterprise Java

Modernizing Enterprise Java

Markus Eisele, Natale Vinto
RESTful Java Web Services - Third Edition

RESTful Java Web Services - Third Edition

Balachandar Bogunuva Mohanram
Ultra-Fast ASP.NET 4.5

Ultra-Fast ASP.NET 4.5

Richard Kiessig

Publisher Resources

ISBN: 9780596514976Supplemental ContentErrata Page