We have many small SQL queries in our asp.net application. Also we have some stored procedures in database.
We can not create stored procedures for each of those SQL statements.
How we can make those SQL queries precompiled, so that it neglects compilation time each time query executes? Give us small example for that.
The only way to have queries pre-compiled is using stored procedure and parametric queries. Whenever you run a query, SQL server keeps the compiled execution plan in cache so if a query is used often in your application, SQL server keeps it for longer time.
Note that this will not happens if you use ad-hoc queries. If query1 is even 1 character different than query2 SQL assumes they are 2 different queries.
So use parametric queries as much as you can. This also helps you to prevent SQL-Injection.
For having more structured db programming it's better to use stored procedure. SP's act like parametric queries and actually prevent you from using ad-hoc queries.
You can find many articles in web if you search about "SQL Sever Execution Plan Cache"
If you need deep understanding of these stuff, read this book:
Microsoft SQL Server 2012 Internals
This link gives you deeper information.