Jailer is a Java-based utility for subsetting relational databases.
... until the collector arrives ...
I had a complex view that returned, among other things, a foreign key ("spId").
I then had a select statement of the form
SELECT * FROM theView WHERE spId
= ?. If I executed the query interactively, it returned the result
in a couple of seconds. But if I ran the query using sp_prepare/sp_execute,
the query took a very long time to complete. This was inconvenient since
the query was actually being executed through jTDS which, by default, does the
prepare/execute thing (although it can be disabled using the connection
After some research, I concluded that I was the victim of the SQL Server optimizer's sensitivity to the exact parameter value (aka "parameter sniffing"). I found some web pages that discussed this:
- A technique for ensuring plan stability in SQL Server 2000, on Ken Henderson's blog
- Tips, Tricks, and Advice from the SQL Server Query Optimization Team, on the SQL Server Query Optimization Team's blog
In short, if a query is compiled in the absence of specific query parameter values (such as in the context of sp_prepare), the execution plan may be radically different from that generated when values are available (such as when an ad hoc query is being compiled) . Solutions range from:
- wrapping the query in a stored procedure that gives more clues as to the parameters type and/or value (on Ken's blog, he goes so far as to set a value on a never-supplied optional proc parameter and reference that parameter in the query)
- forcing a recompile of the query immediately prior to running it -- e.g. using SQL Server 2005's OPTION(RECOMPILE)
- giving the optimizer a hint as to parameter values -- e.g. using SQL Server 2005's OPTION(OPTIMIZE FOR (@P=1))
As I was using SQL Server 2000, I could only use the first option (which worked). I didn't have to resort to setting up a dummy parameter -- it was enough to create a minimal proc wrapper:
CREATE PROCEDURE dbo.zot @P INT AS SELECT ... AND spId = @P
I tried to avoid the proc by changing my inline query to something like this:
DECLARE @P INT
SET @P = ?
SELECT ... AND spId = @P
but, surprisingly, the optimizer ignored the strong hint that @P was an INT. I also had no luck with:
SELECT ... AND spId = CAST(? AS INT)
Parameter-sniffing exploits statistics, so my problems might have been caused by stale or misleading statistics. Here are some useful diagnosis commands:
- show statistics for a column:
- show cached plans, etc:
select * from master.dbo.syscacheobjects where sql like 'dbo.projectFilter'
- ► 2012 (27)
- ► 2011 (8)
- ► 2010 (25)
- ▼ 2009 (51)
- ► 2008 (78)
- ► 2007 (94)
- ► 2006 (135)
- ► 2005 (58)
- ► 2004 (68)
- ► 1999 (10)