SQL query execution sequence

Question

I have the following "identical" queries:

select * from myView where ID=1

and

declare @ID int
set @ID=1
select * from myView where ID=@ID

myView is a view (non-indexed view) with several internal and external connections. The unfiltered output of the view is @ 100 mil lines. For ID = 1, the output is @ 10k lines. The first request is returned in a couple of seconds, and the other request is completed within a few hours. They seem to be “identical,” but apparently behind the scenes (when viewing the execution plan) I can get the fact that the filtering in the view in the second script is applied only at the end, after requesting the entire view. Why is this happening or does anyone have a better and clearer explanation for this?

Edit later: Kevchadders suggestion was very helpful. Having looked at this article: http://blogs.msdn.com/b/turgays/archive/2013/09/10/parameter-sniffing-problem-and-workarounds.aspx I managed to use the following workaround for the second script, which " solved the problem ":

declare @ID int
set @ID=1
select * from myView where ID=@ID
option(recompile)

Answer 1

This may be due to parameterization.

The execution plan may not be identical for both, so even though you have what looks like identical code, the sql server will use a different execution route for each part of SQL.

, , , - . , OPTION (RECOMPILE).