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]
Re: SQL Multiple Inserts
I've often wonder at what point Bulk Insert becomes an advantage. I imagine the drawback with bulk insert is that it has to read an actual file versus read from ram. It seems that for a small number of records a regular insert would be faster. Has anyone done any comparisons between bulk insert versus regular insert? Steve Nelson Robertson-Ravo, Neil (RX) wrote: 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]
RE: SQL Multiple Inserts
From: brobborb 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? I've not tested with JDBC, but with ODBC there is a limit to how much you can pass to the SQL engine in one go But in answer to your question - it's a LOT quicker to do it in less CFQUERY tags as you're passing the load to the database and letting it do it's job rather than saying Here is one item, here is another, and another Where are the inserts coming from? If they're from another table, then you should look at letting the database handle the inserts the best way it can You've also not said which DB Engine [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL Multiple Inserts
From: Robertson-Ravo, Neil (RX) Stored Procedure : BULK INSERT. This really depends - if you're inserting data from another table, then it's a lot easier to do a INSERT INTO SELECT FROM Also, if he's running Access *spit* then doing SPs is impossible [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
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]
Re: SQL Multiple Inserts
I have just tested this myself and I am very happy! 308 milliseconds vs. 1590 milliseconds (the numbers were something like that) This is alot of time saved since the actual application will do 10 times the amount of row inserts. - Original Message - From: Steve Nelson To: CF-Talk Sent: Monday, March 01, 2004 7:00 AM Subject: Re: SQL Multiple Inserts I've often wonder at what point Bulk Insert becomes an advantage. I imagine the drawback with bulk insert is that it has to read an actual file versus read from ram. It seems that for a small number of records a regular insert would be faster. Has anyone done any comparisons between bulk insert versus regular insert? Steve Nelson Robertson-Ravo, Neil (RX) wrote: 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]
RE: SQL Multiple Inserts
True, true.also Bulk Inserts are not entered into the Transaction Logif on MSSQL _ From: Philip Arnold [mailto:[EMAIL PROTECTED] Sent: 01 March 2004 13:28 To: CF-Talk Subject: RE: SQL Multiple Inserts From: Robertson-Ravo, Neil (RX) Stored Procedure : BULK INSERT. This really depends - if you're inserting data from another table, then it's a lot easier to do a INSERT INTO SELECT FROM Also, if he's running Access *spit* then doing SPs is impossible _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
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]
Re: SQL Multiple Inserts
Really?There's a limit?What is it?hmmm.Me sets loop to 50,000 :D it took it!16203 milliseconds! however, at 100,000 inserts, it took 57333 milliseconds.Maybe cos mys erver is crapping out.It seems like doing 2x 50,000 inserts is alot faster than 1 100,000 inserts! 50,000 should be more than enuff! - Original Message - From: Philip Arnold To: CF-Talk Sent: Monday, March 01, 2004 7:12 AM Subject: RE: SQL Multiple Inserts From: brobborb 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? I've not tested with JDBC, but with ODBC there is a limit to how much you can pass to the SQL engine in one go But in answer to your question - it's a LOT quicker to do it in less CFQUERY tags as you're passing the load to the database and letting it do it's job rather than saying Here is one item, here is another, and another Where are the inserts coming from? If they're from another table, then you should look at letting the database handle the inserts the best way it can You've also not said which DB Engine [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SQL Multiple Inserts
Just out of pure curiosity, can you try reducing the number of records until the processing time is about equivalent? Maybe we can figure out a very simple rule of thumb, like If there are more than 100 records, use bulk insert, otherwise use a regular insert. Hell, it might be if there are more than 2 records... :-) Steve Nelson brobborb wrote: I have just tested this myself and I am very happy! 308 milliseconds vs. 1590 milliseconds (the numbers were something like that) This is alot of time saved since the actual application will do 10 times the amount of row inserts. - Original Message - From: Steve Nelson To: CF-Talk Sent: Monday, March 01, 2004 7:00 AM Subject: Re: SQL Multiple Inserts I've often wonder at what point Bulk Insert becomes an advantage. I imagine the drawback with bulk insert is that it has to read an actual file versus read from ram. It seems that for a small number of records a regular insert would be faster. Has anyone done any comparisons between bulk insert versus regular insert? Steve Nelson Robertson-Ravo, Neil (RX) wrote: 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]
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]
Re: SQL Multiple Inserts
I did 2x 50,000 records insert:one using multiple inserts, the other by looping the query 50,000 times. 16 seconds vs 61 seconds, respectively. Forget the conditionals!Multiple Inserts yay! - Original Message - From: Steve Nelson To: CF-Talk Sent: Monday, March 01, 2004 7:43 AM Subject: Re: SQL Multiple Inserts Just out of pure curiosity, can you try reducing the number of records until the processing time is about equivalent? Maybe we can figure out a very simple rule of thumb, like If there are more than 100 records, use bulk insert, otherwise use a regular insert. Hell, it might be if there are more than 2 records... :-) Steve Nelson brobborb wrote: I have just tested this myself and I am very happy! 308 milliseconds vs. 1590 milliseconds (the numbers were something like that) This is alot of time saved since the actual application will do 10 times the amount of row inserts. - Original Message - From: Steve Nelson To: CF-Talk Sent: Monday, March 01, 2004 7:00 AM Subject: Re: SQL Multiple Inserts I've often wonder at what point Bulk Insert becomes an advantage. I imagine the drawback with bulk insert is that it has to read an actual file versus read from ram. It seems that for a small number of records a regular insert would be faster. Has anyone done any comparisons between bulk insert versus regular insert? Steve Nelson Robertson-Ravo, Neil (RX) wrote: 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]
RE: SQL Multiple Inserts
In theory an SP should be faster no matter... _ From: brobborb [mailto:[EMAIL PROTECTED] Sent: 01 March 2004 13:47 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]
Re: SQL Multiple Inserts
Hey question.When you do an INSERT statement, does SQL still return a resultset?Or does that only happen with SELECT statements? - Original Message - From: Philip Arnold To: CF-Talk Sent: Monday, March 01, 2004 7:28 AM Subject: RE: SQL Multiple Inserts From: Robertson-Ravo, Neil (RX) Stored Procedure : BULK INSERT. This really depends - if you're inserting data from another table, then it's a lot easier to do a INSERT INTO SELECT FROM Also, if he's running Access *spit* then doing SPs is impossible [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SQL Multiple Inserts
Did you add the 50,000 cffile writes? Or loop 50,000 times to create the data in one variable, then do a single cffile write? Don't you have to write to a file first, then perform the bulk insert on the file? I imagine that would make a pretty major difference. Steve Nelson brobborb wrote: I did 2x 50,000 records insert:one using multiple inserts, the other by looping the query 50,000 times. 16 seconds vs 61 seconds, respectively. Forget the conditionals!Multiple Inserts yay! - Original Message - From: Steve Nelson To: CF-Talk Sent: Monday, March 01, 2004 7:43 AM Subject: Re: SQL Multiple Inserts Just out of pure curiosity, can you try reducing the number of records until the processing time is about equivalent? Maybe we can figure out a very simple rule of thumb, like If there are more than 100 records, use bulk insert, otherwise use a regular insert. Hell, it might be if there are more than 2 records... :-) Steve Nelson brobborb wrote: I have just tested this myself and I am very happy! 308 milliseconds vs. 1590 milliseconds (the numbers were something like that) This is alot of time saved since the actual application will do 10 times the amount of row inserts. - Original Message - From: Steve Nelson To: CF-Talk Sent: Monday, March 01, 2004 7:00 AM Subject: Re: SQL Multiple Inserts I've often wonder at what point Bulk Insert becomes an advantage. I imagine the drawback with bulk insert is that it has to read an actual file versus read from ram. It seems that for a small number of records a regular insert would be faster. Has anyone done any comparisons between bulk insert versus regular insert? Steve Nelson Robertson-Ravo, Neil (RX) wrote: 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]
Re: SQL Multiple Inserts
the way it works is it gets the name of all the images in a diretory/and all subdirectories, and inserts them into the database.I'm not writing to a file or anything.Or are you talking about something else? - Original Message - From: Steve Nelson To: CF-Talk Sent: Monday, March 01, 2004 8:02 AM Subject: Re: SQL Multiple Inserts Did you add the 50,000 cffile writes? Or loop 50,000 times to create the data in one variable, then do a single cffile write? Don't you have to write to a file first, then perform the bulk insert on the file? I imagine that would make a pretty major difference. Steve Nelson brobborb wrote: I did 2x 50,000 records insert:one using multiple inserts, the other by looping the query 50,000 times. 16 seconds vs 61 seconds, respectively. Forget the conditionals!Multiple Inserts yay! - Original Message - From: Steve Nelson To: CF-Talk Sent: Monday, March 01, 2004 7:43 AM Subject: Re: SQL Multiple Inserts Just out of pure curiosity, can you try reducing the number of records until the processing time is about equivalent? Maybe we can figure out a very simple rule of thumb, like If there are more than 100 records, use bulk insert, otherwise use a regular insert. Hell, it might be if there are more than 2 records... :-) Steve Nelson brobborb wrote: I have just tested this myself and I am very happy! 308 milliseconds vs. 1590 milliseconds (the numbers were something like that) This is alot of time saved since the actual application will do 10 times the amount of row inserts. - Original Message - From: Steve Nelson To: CF-Talk Sent: Monday, March 01, 2004 7:00 AM Subject: Re: SQL Multiple Inserts I've often wonder at what point Bulk Insert becomes an advantage. I imagine the drawback with bulk insert is that it has to read an actual file versus read from ram. It seems that for a small number of records a regular insert would be faster. Has anyone done any comparisons between bulk insert versus regular insert? Steve Nelson Robertson-Ravo, Neil (RX) wrote: 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]
Re: SQL Multiple Inserts (LIMIT)
I have found the limit.It is 456.Even though i did 50,000 INSERTS in one query, SQL Server only took 456.Bummer.On well.Doing Round((50,000/456)) queries is alot faster than doing 50,000 queries ehehe - Original Message - From: brobborb To: CF-Talk Sent: Monday, March 01, 2004 7:43 AM Subject: Re: SQL Multiple Inserts Really?There's a limit?What is it?hmmm.Me sets loop to 50,000 :D it took it!16203 milliseconds! however, at 100,000 inserts, it took 57333 milliseconds.Maybe cos mys erver is crapping out.It seems like doing 2x 50,000 inserts is alot faster than 1 100,000 inserts! 50,000 should be more than enuff! - Original Message - From: Philip Arnold To: CF-Talk Sent: Monday, March 01, 2004 7:12 AM Subject: RE: SQL Multiple Inserts From: brobborb 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? I've not tested with JDBC, but with ODBC there is a limit to how much you can pass to the SQL engine in one go But in answer to your question - it's a LOT quicker to do it in less CFQUERY tags as you're passing the load to the database and letting it do it's job rather than saying Here is one item, here is another, and another Where are the inserts coming from? If they're from another table, then you should look at letting the database handle the inserts the best way it can You've also not said which DB Engine [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SQL Multiple Inserts
Hmmm, maybe. I haven't used bulk insert in a couple years. This is the syntax I've used before: BULK INSERT SomeTable FROM 'c:\temp\filetoinsert.txt' WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '~') So I figured you'd have to write the 50,000 records to c:\temp\filetoinsert.txt (i.e. any file) What syntax are you using? Steve Nelson brobborb wrote: the way it works is it gets the name of all the images in a diretory/and all subdirectories, and inserts them into the database. I'm not writing to a file or anything.Or are you talking about something else? - Original Message - From: Steve Nelson To: CF-Talk Sent: Monday, March 01, 2004 8:02 AM Subject: Re: SQL Multiple Inserts Did you add the 50,000 cffile writes? Or loop 50,000 times to create the data in one variable, then do a single cffile write? Don't you have to write to a file first, then perform the bulk insert on the file? I imagine that would make a pretty major difference. Steve Nelson brobborb wrote: I did 2x 50,000 records insert:one using multiple inserts, the other by looping the query 50,000 times. 16 seconds vs 61 seconds, respectively. Forget the conditionals!Multiple Inserts yay! - Original Message - From: Steve Nelson To: CF-Talk Sent: Monday, March 01, 2004 7:43 AM Subject: Re: SQL Multiple Inserts Just out of pure curiosity, can you try reducing the number of records until the processing time is about equivalent? Maybe we can figure out a very simple rule of thumb, like If there are more than 100 records, use bulk insert, otherwise use a regular insert. Hell, it might be if there are more than 2 records... :-) Steve Nelson brobborb wrote: I have just tested this myself and I am very happy! 308 milliseconds vs. 1590 milliseconds (the numbers were something like that) This is alot of time saved since the actual application will do 10 times the amount of row inserts. - Original Message - From: Steve Nelson To: CF-Talk Sent: Monday, March 01, 2004 7:00 AM Subject: Re: SQL Multiple Inserts I've often wonder at what point Bulk Insert becomes an advantage. I imagine the drawback with bulk insert is that it has to read an actual file versus read from ram. It seems that for a small number of records a regular insert would be faster. Has anyone done any comparisons between bulk insert versus regular insert? Steve Nelson Robertson-Ravo, Neil (RX) wrote: 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]
Re: SQL Multiple Inserts
Something like
cfquery datasource=#dsn# name=name
cfloop index=index query=cfdirectory_query
INSERT INTO testy (name, a, b, c, d, e, f, g) VALUES ('#name#', 'other stuff', 'a', 'a', 'a', 'a', 'a', 'a')
/cfloop
/cfquery
The problem is that even though the query goes through and the page gets processed, SQL Server only actually accepts 456 rows!So I will try using your syntax :)
- Original Message -
From: Steve Nelson
To: CF-Talk
Sent: Monday, March 01, 2004 8:15 AM
Subject: Re: SQL Multiple Inserts
Hmmm, maybe. I haven't used bulk insert in a couple years. This is the
syntax I've used before:
BULK INSERT SomeTable
FROM 'c:\temp\filetoinsert.txt'
WITH
(FIELDTERMINATOR = '|',
ROWTERMINATOR = '~')
So I figured you'd have to write the 50,000 records to
c:\temp\filetoinsert.txt (i.e. any file)
What syntax are you using?
Steve Nelson
brobborb wrote:
the way it works is it gets the name of all the images in a
diretory/and all subdirectories, and inserts them into the database.
I'm not writing to a file or anything.Or are you talking about
something else?
- Original Message -
From: Steve Nelson
To: CF-Talk
Sent: Monday, March 01, 2004 8:02 AM
Subject: Re: SQL Multiple Inserts
Did you add the 50,000 cffile writes? Or loop 50,000 times to create
the
data in one variable, then do a single cffile write? Don't you have
to
write to a file first, then perform the bulk insert on the file? I
imagine that would make a pretty major difference.
Steve Nelson
brobborb wrote:
I did 2x 50,000 records insert:one using multiple inserts, the
other
by looping the query 50,000 times.
16 seconds vs 61 seconds, respectively.
Forget the conditionals!Multiple Inserts yay!
- Original Message -
From: Steve Nelson
To: CF-Talk
Sent: Monday, March 01, 2004 7:43 AM
Subject: Re: SQL Multiple Inserts
Just out of pure curiosity, can you try reducing the number of
records
until the processing time is about equivalent? Maybe we can
figure
out a
very simple rule of thumb, like If there are more than 100
records,
use
bulk insert, otherwise use a regular insert. Hell, it might be
if
there are more than 2 records... :-)
Steve Nelson
brobborb wrote:
I have just tested this myself and I am very happy!
308 milliseconds vs. 1590 milliseconds (the numbers were
something
like that)
This is alot of time saved since the actual application will
do 10
times the amount of row inserts.
- Original Message -
From: Steve Nelson
To: CF-Talk
Sent: Monday, March 01, 2004 7:00 AM
Subject: Re: SQL Multiple Inserts
I've often wonder at what point Bulk Insert becomes an
advantage. I
imagine the drawback with bulk insert is that it has to read
an
actual
file versus read from ram. It seems that for a small number
of
records a
regular insert would be faster.
Has anyone done any comparisons between bulk insert versus
regular
insert?
Steve Nelson
Robertson-Ravo, Neil (RX) wrote:
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]
RE: SQL Multiple Inserts
From: brobborb
Something like
cfquery datasource=#dsn# name=name
cfloop index=index query=cfdirectory_query
INSERT INTO testy (name, a, b, c, d, e, f, g) VALUES
('#name#', 'other stuff', 'a', 'a', 'a', 'a', 'a', 'a')
/cfloop
/cfquery
The problem is that even though the query goes through and
the page gets processed, SQL Server only actually accepts 456
rows!So I will try using your syntax :)
Can you try the #name# in a CFQUERYPARAM - it should give you more
space as it's a parameter and not part of the SQL
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
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]
RE: SQL Multiple Inserts
Thanks, Mark - great description! 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 AhleniusE-Mail: [EMAIL PROTECTED] Project Officer Phone:+46 8 230460 UNEP GRID-ArendalFax:+46 8 230441 Stockholm OfficeMobile:+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]
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]
RE: SQL Multiple Inserts
Cached queries means storing the whole output query variable in the cf 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 AhleniusE-Mail: [EMAIL PROTECTED] Project Officer Phone:+46 8 230460 UNEP GRID-ArendalFax:+46 8 230441 Stockholm OfficeMobile:+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]
Re: SQL Multiple Inserts
David Fafard wrote: Again.. because one can NOT use cached queries with cfqueryparam http://www.macromedia.com/go/wish/ which is more beneficial, cached queries or cfqueryparam ? Depends on the situation. I have always been confused with this distinction. Is there any good rules of thumb when deciding on this? Always use cfqueryparam for the security features. Caache a query if it is executed often and / or has a long runtime. Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL Multiple Inserts
From: Hugo Ahlenius Cached queries means storing the whole output query variable in the cf 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. The cached query is only useful if you're only getting one set of data, and it never changes, then the cached query is right If you've got loads of variation on the data, for example getting different records from the database, the CFQUERYPARAM is the way to go Of course, using SPs might be even faster than using CFQUERYPARAM, as long as your queries are never going to change [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SQL Multiple Inserts
So of I use: select x,y,z from tbl where id=1 select x,y,z from tbl where id=2 select x,y,z from tbl where id=3 where id would be a dynamic variable, I would not get any benefit in using cached query but would see a benefit from cfqueryparam ? Thanks, Dave - Original Message - From: Philip Arnold To: CF-Talk Sent: Monday, March 01, 2004 12:25 PM Subject: RE: SQL Multiple Inserts From: Hugo Ahlenius Cached queries means storing the whole output query variable in the cf 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. The cached query is only useful if you're only getting one set of data, and it never changes, then the cached query is right If you've got loads of variation on the data, for example getting different records from the database, the CFQUERYPARAM is the way to go Of course, using SPs might be even faster than using CFQUERYPARAM, as long as your queries are never going to change [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL Multiple Inserts
From: David Fafard So of I use: select x,y,z from tbl where id=1 select x,y,z from tbl where id=2 select x,y,z from tbl where id=3 where id would be a dynamic variable, I would not get any benefit in using cached query but would see a benefit from cfqueryparam ? How many variations on ID? How much data is coming back? In this instance, I'd use a CFQUERYPARAM as you know that the ID is changing BUT - if you only have 3 variations on ID, then it might be worth caching the query Next you get into the question of HOW to cache the query If you use CACHEDWITHIN or CACHEDAFTER then you can't use CFQUERYPARAM But if you store the results into another scope, like Application, then you can use CFQUERYPARAM to your heart's content :) [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SQL Multiple Inserts
Actually, you'd get a major advantage using a cached query, that is, if the select statement is taking more than about 30ms. In your example below, the select statement would need to be a little more complex, or the tbl table would need to have hundreds of thousands or millions of records to make it worthwhile. Cached queries are incredibly fast, but if you want the data to always be up to date, you have to manually refresh the cache when you insert, update or delete data. This is done by setting the cachedwithin timespan to 0. It's rather difficult to keep track of when to refresh the queries, but if you are anal enough to figure it out, you can dramatically speed up your application. By doing this, you only hit the database on the first query or after data has changed. To give you a comparison of times Regular cfquery that averages 200milliseconds 1st time: 200 milliseconds 2nd time: 200 milliseconds 3rd time: 200 milliseconds update data: 10 milliseconds 4th time: 200 milliseconds etc Same cfquery using cachedwithin 1st time: 200 milliseconds 2nd time: ~1 milliseconds 3rd time: ~1 milliseconds update data: 10 milliseconds 4th time: 200 milliseconds manual refresh 5th time: ~1 milliseconds etc Does that make sense? One thing that i think is in BlueDragon but not in MM CFML is a way to delete the cache as opposed to refreshing it. The difference being that when you do a cachedwithin=#createtimespan(0,0,0,0)# to refresh a query, you're effectively rerunning it. In BD you can just delete the query from memory when you change data. This is a more elegant way of handling it because when data changes in the db, you may not actually need to run the query at the point in time. So you don't force the user to have to wait for the query(ies) to run. Steve David Fafard wrote: So of I use: select x,y,z from tbl where id=1 select x,y,z from tbl where id=2 select x,y,z from tbl where id=3 where id would be a dynamic variable, I would not get any benefit in using cached query but would see a benefit from cfqueryparam ? Thanks, Dave - Original Message - From: Philip Arnold To: CF-Talk Sent: Monday, March 01, 2004 12:25 PM Subject: RE: SQL Multiple Inserts From: Hugo Ahlenius Cached queries means storing the whole output query variable in the cf 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. The cached query is only useful if you're only getting one set of data, and it never changes, then the cached query is right If you've got loads of variation on the data, for example getting different records from the database, the CFQUERYPARAM is the way to go Of course, using SPs might be even faster than using CFQUERYPARAM, as long as your queries are never going to change [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
