SQL Server: Top (variable) vs TOP n

For past week or so, we have been struggling with performance issues in one of our applications using SQL Server. Some of the queries are quite complex and SQL Query Optimizer spends almost 1500-2000 ms in Parse and Compile whereas execution takes about 500ms. We tried our best to reduce compile time but we couldn't so we left it to a later date.

Now, the biggest problem remaining was that the SQL Query Plan wasn't getting re-used. In the application, users can use pagination and on every page change, query was getting compiled repeatedly. What I found was a bit strange. The way our code called was something like:

SELECT TOP 10 * FROM vwCustomerList

SELECT TOP 20 * FROM vwCustomerList

...

So, we were a bit stumped. Just our of curiosity, I changed the queries to:

SELECT TOP (@Top) FROM vwCustomerList

And passed @Top as the parameter. Now, suddenly the query plans were reused and application was back to good speed.

The good thing was almost all the queries from our application are executed through the same set of libraries and hence I was able to modify code at just one place and all the queries benefit from this performance boost!