November 18, 2016 at 12:32 pm
Hi experts,
Can someone please explain to me in simple English with each examples, different between Adhoc query and prepared query from sys.dm_exec_cached_plans.
Thanks.
November 18, 2016 at 12:53 pm
Tac11 (11/18/2016)
Hi experts,Can someone please explain to me in simple English with each examples, different between Adhoc query and prepared query from sys.dm_exec_cached_plans.
Thanks.
A prepared query is paramaterized and can be reused for a range of different inputs.
select * from t1 where id = @id;
An Adhoc query is hard coded or the dynamically executed, and the cached plan can only be re-used for a near identical statement.
select * from t1 where id = 123;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 18, 2016 at 1:02 pm
Ad Hoc:
SELECT * FROM Table;
If this is run from SQL Server Management Studio, Reporting Services, Integration Services, or through code from an app, it's ad hoc because it's not in any way "prepared." It's just a raw T-SQL statement.
Prepared:
CREATE PROCEDURE dbo.TableProc AS
BEGIN
SELECT * FROM Table;
END
GO;
or using sp_executesql also creates a prepared statement. Also using code you would use the SqlCommand.Prepare method. Code examples of both those at the link.
It's basically a question of how the query gets executed. Mostly prepared statements are best defined by parameters. A prepared statement goes through a parameterization process and the parameters can be sniffed, sampled, by the optimizer. Ad hoc can't have parameters, but can have local variables (prepared statement can also have local variables).
Hope that helps a little.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 18, 2016 at 1:18 pm
Thanks millions Eric and Grant.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply