Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-07-16 : 07:21:06
|
| Brian writes "Hello,I read somewhere that SQL Server 2000 was "smarter" than older version of SQL Server. I believe that I read if you were to pass two SQL statements that were identical except for the value you were querying on, SQL Server 2000 would be smart enough to realize they were almost the same, so it would only compile once and use the same copilation for each making the query results much faster.For example:SELECT NAME FROM CONTACTS WHERE LASTNAME = 'JOHNSON'andSELECT NAME FROM CONTACTS WHERE LASTNAME = 'SMITH'Running these two queries would only compile on the first one making the results of the second query considerably faster. Am I correct regarding this? Is my only other option for producing these sorts of results to write a stored procedure?Thanks,Brian" |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-07-16 : 09:55:52
|
| Yes, SQL 2K does cache query plans and recognize when a new query is a parameterized version of a already-compiled plan. Bear in mind that the cost of executing the plan may significantly surpass the cost of compiling it.You are always better off creating stored procedures to execute DML.Jonathan{0} |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-07-16 : 19:18:33
|
quote:
I read somewhere that SQL Server 2000 was "smarter" than older version of SQL Server. I believe that I read if you were to pass two SQL statements that were identical except for the value you were querying on, SQL Server 2000 would be smart enough to realize they were almost the same, so it would only compile once and use the same copilation for each making the query results much faster.
SQL 7.0 and 6.5 were also smart enough to do this as well, certainly for the example you provided. There have been gradual improvements though that allow the optimizer to recognize a broader range of shortcuts that can improve performance. You can find out specifics on what's been improved in the What's New? section of Books Online. |
|
|
|
|
|
|
sql compilation