Again.. because one can NOT use cached queries with cfqueryparam, which is more beneficial,
cached queries or cfqueryparam ?
I have always been confused with this distinction.
Is there any good "rules of thumb" when deciding on this?
Dave
----- Original Message -----
From: Mark A. Kruger - CFG
To: CF-Talk
Sent: Monday, March 01, 2004 10:34 AM
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.
-Mark
-----Original Message-----
From: brobborb [mailto:[EMAIL PROTECTED]
Sent: Monday, March 01, 2004 7:47 AM
To: CF-Talk
Subject: Re: SQL Multiple Inserts
But will it help in terms of performance? My understanding is that Stored Procedures are cached queries. But if the
query you use has different variable values everytime, then it's just like using a new query. no performance benefit.
But if this is not the case, the I would totally use a stored procedure for this part!!
----- Original Message -----
From: Robertson-Ravo, Neil (RX)
To: CF-Talk
Sent: Monday, March 01, 2004 7:34 AM
Subject: RE: SQL Multiple Inserts
No, thats the whole point of a stored procedure...you pass it in variables
and it does the processing....
_____
From: brobborb [mailto:[EMAIL PROTECTED]
Sent: 01 March 2004 13:32
To: CF-Talk
Subject: Re: SQL Multiple Inserts
Here's what i thought about stored procedure. I thought they are only
useful when the SQL query isn't dynamic, like SELECT * from tbl_images. But
if it's something like SELECT * from tbl_images where project_id = 30, then
a stored procedure is not beneficial, because it's essentially a different
query every time.
Is this correct? My queries are like the latter.
----- Original Message -----
From: Robertson-Ravo, Neil (RX)
To: CF-Talk
Sent: Monday, March 01, 2004 6:14 AM
Subject: RE: SQL Multiple Inserts
Stored Procedure : BULK INSERT.
_____
From: brobborb [mailto:[EMAIL PROTECTED]
Sent: 01 March 2004 12:16
To: CF-Talk
Subject: SOT: SQL Multiple Inserts
Hey guys, let's say I have to insert like 5,000 rows of data with the
click
of a button in my app. Would it be alot faster to insert all 5,000 in one
CFQuery with multiple inserts? Right now it is just one query looped
5,000
times.
Also are there any drawbacks with using multplie inserts?
Thanks
_____
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
