server memory. CF does not touch the database. The query can not have
any dynamic variables (The SQL statement has to be the same).
Using cfqueryparam improves the caching on the db server side (as
described before). The cfquery will still need a roundtrip to the
database.
So the first option is the fastest, but least flexible.
/Hugo
-------------------------------------------------------------
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: David Fafard [mailto:[EMAIL PROTECTED]
| Sent: Monday, March 01, 2004 17:23
| To: CF-Talk
| Subject: Re: SQL Multiple Inserts
|
| Interesting.
|
| 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]
