I know that we should do these things (cfqueryparam and sp's) but I
haven't seen such a good/easy description in quite a long time!
/H.
-------------------------------------------------------------
Hugo Ahlenius E-Mail: [EMAIL PROTECTED]
Project Officer Phone: +46 8 230460
UNEP GRID-Arendal Fax: +46 8 230441
Stockholm Office Mobile: +46 733 467111
WWW: http://www.grida.no
-------------------------------------------------------------
| -----Original Message-----
| From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED]
| Sent: Monday, March 01, 2004 16:35
| To: CF-Talk
| Subject: RE: SQL Multiple Inserts
|
| Stored procedures are not cached queries - they are compiled
| execution plans. The thing that makes the difference is the
| databinding. SQL knows what type a variable is prior to
| execution of the query. So it does not have to "check" in the
| system tables to see what type it should be using. For
| example, if you do the following:
|
| select * from mytable where user_id = 2
|
| SQL doesn't know A) what the column names are and B) what
| datatype your "2" is. So first it looks in the system tables
| for that db and says, give me all the columns, which it uses
| for select, then it goes back to the system tables and says
| "what datatype is the column 'user_id' in the table
| 'mytable'". Once it knows it's an int (for example) it
| qualifies the "2" as an int, builds an execution plan and proceeds.
|
| Now, a stored proc looks like this:
|
| spu_mystoredproce @var1=2, @var2='marcus'
|
| SQL knows in advance in this case that @var1 and @var2 are
| int and char respectively. It doesn't need to check - it
| just tests var1 to make sure it's an int and moves on.
|
| Incidentally, this is why the use of <cfqueryparam> can
| significantly improve your performance - it binds the type to
| a variable. SQL caches execution plans on the server side.
| When you do databinding you greatly increase the likelyhood
| of a cache hit for your execution plan. When you do
| traditional CF (like the query above) you have almost NO
| chance of finding an execution plan in the cache.
###########################################
This message has been scanned by F-Secure Anti-Virus for Microsoft
Exchange.
For more information, connect to http://www.F-Secure.com/
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
