Observing the NoSQL hype through the eyes of an SQL performance consultant is an interesting experience. It is, however, very hard to write about NoSQL because there are so many forms of it. After all, NoSQL is nothing more than a marketing term. A marketing term that works pretty well because it goes to the heart of many developers that struggle with SQL every day.
Posts Tagged ‘sql’
Finding the Best Match With a Top-N Query
In Performance on 2010-09-29 at 11:16There was an interesting index related performance problem on Stack Overflow recently. The problem was to check an input string against a table that holds about 2000 prefix patterns (e.g., LIKE 'xyz%'). A fast select is needed that returns one row if any pattern matches the input string, or no row otherwise.
I believe my solution is worth a few extra words to explain it in more detail. Even though it’s a perfect fit for Use The Index, Luke it’s a little early to put it as an exercise there. It is, however, a very good complement to my previous article Analytic Top-N queries—so I put it here.
Although the problem was raised for a MySQL database, my solution applies to all databases that can properly optimize Top-N queries.
Analytic Top-N Queries
In Maintainability, Performance on 2010-07-30 at 10:55One of the more advanced tricks I like to exploit are analytic Top-N queries. Although I am using them for quite a while, I recently discovered a “limitation” that I was not aware of. Actually—to be honest—it’s not a limitation; it is a missing optimization in a rarely used feature that can easily worked around. I must admit that I ask for quite a lot in that case.
The article starts with a general introduction into Top-N queries, applies that technique to analytic queries and explains the case where I miss an optimization. But is is really worth all that efforts? The article concludes with my answer to that question.
Clustering Factor: Row Migration’s Victim
In Performance on 2010-03-09 at 10:15This article describes the effects of a high row migration rate on the clustering factor and the optimizer’s ability to select the best execution plan.
In my previous article—Row Migration and Row Movement—I have demonstrated that the “insert empty, update everything” anti-pattern can lead to 100% row migration. This article continues the research on row migration and unveils surprising effects on the clustering factor. To be precise, the clustering factor can become completely bogus in presence of a very high row migration rate. Once the clustering factor is “wrong”, it’s just a finger exercise to construct an optimizer trap and proof that row migration can affect the query plan.
Oracle JDBC PreFetch Portability
In Portability on 2010-01-29 at 14:23In a previous article about (network) latencies, I have presented the OracleStatement.setRowPrefetch() method to reduce round trips for SELECT statements. I must admit that I was a little bit wrong. Not in the essence; increasing the PreFetch size is still a great (read: simple) way to reduce latencies. However, there is a better approach to set the PreFetch size. Read the rest of this entry »
Latency: Security vs. Performance
In Performance on 2009-12-22 at 08:01I have witnessed a very short talk between a network engineer and a top-level manager at a client’s Christmas party. The network guy explained that the firewall adds about 0.2 milliseconds latency to each round trip between the application server and the database, which adds up to some hours in one particular case. So the manager asked what could be done and the network guy provided two solutions:
- Change the application to make less round trips
- Accept the security risk and don’t put a firewall in between those two tiers
Funny enough the network guy explained that the second option needs the managers signature because it bypasses the corporate security guidelines and somebody must take the responsibility for that.
Consider you are the manager, would you sign that a paper?
To Trace or Not to Trace
In Maintainability on 2009-11-24 at 17:49I have always been a fan of the oracle SQL trace facility. It’s a very powerful method to analyze performance problems, especially if you don’t know the application very well. If you don’t know oracle SQL tracing yet, you will find some info at orafaq and some more details at orable-base. Obviously, Oracle has also some documenation about it.
In short, the Oracle SQL tracing facility causes the database server to write all executed SQL statements to a file for later analysis. One of the main issues with SQL tracing is the huge size of the trace files; every statement execution is logged, potentially with additional data about the execution plan, wait events, and bind values.
This article introduces the new possibilities of Oracle 10g to enable tracing on a very fine level. With a properly instrumented application, tracing can be enabled for specific users or program modules only. With such a fine control over the tracing, the generated data volume can be kept small and SQL tracing becomes a much more useful tool.
