On: Stack Overflow Makes Slow Pages 100x Faster By Simple SQL Tuning

[ssmoot]

That's a non-sensical statement. Stored Procedures are always parameterized. It's the only way to pass in arguments.

What I'm talking about is a special stored-procedure called sp_executesql. It takes varargs for parameters and executes your query.

Open up Query Analyzer. Run SELECT * FROM users WHERE id = 1;

That's an ad-hoc, not parameterized query.

Now run: sp_executesql 'SELECT * FROM users WHERE id = @id', @id=1

(My MSSQL may be a bit rusty :p, so syntax issues aside)

That's a parameterized query. That's what runs when you use the classic ADO.NET .addParameter interface.

Queries executed that way have their query plans cached.

I spent a couple seconds on Google and came up with this. Looks like a decent overview: http://blog.sqlauthority.com/2007/07/02/sql-server-2005-comp...

If you can actually measure a significant difference between sp_executesql and a stored-procedure call that's news to me. Definitely not my experience, and not what the actual documentation indicates. I'd go so far as to bet that unless you came up with some kind of pathological example you're mis-remembering or misunderstanding what I'm trying to say.

To keep it simple: If you use the .AddParameter interface you should see no appreciable performance advantage to Stored Procedures. If you simply interpolate your parameters (or your O/RM does it for you), then you will pay the price for generating the execution plan each time. Probably not a significant different for 100ms queries, but it could be quite a substantial cost for a <10ms query.

[pradocchia → ssmoot]

that would be:

exec sp_executesql N'SELECT * FROM users WHERE id = @id', N'@id INT', @id=1

[ssmoot → pradocchia]

Thanks for the syntax help. It's been awhile. ;-)

For anyone curious, here's an old article on the interface I'm talking about: http://www.techrepublic.com/article/shorten-development-time... Just so you don't think you have to memorize the above syntax.

I'm not sure what's used these days, but I assume it's fairly similar if not simplified a bit. If it's not broke right?