Re: Avoiding a boat load of queries inserting multiple records - Better Way?
Ideally I would also suggest SSIS for this, but sounds like a total
re-write might be out of the question.
This is what I would probably try, maybe breaking the number of records
done during one transaction into something reasonable depending on server
performance. Believe the OUTPUT clause is supported down to sql 2005.
begin tran
INSERT INTO listTable (email, fname, lname, other)
OUTPUT #req.thisGROUPID#, inserted.listID
INTO nl_catREL (groups_id, email_id)
VALUES ('#mailREC[CurrentRow][1]#', '#mailREC[CurrentRow][2]#',
'#mailREC[CurrentRow][3]#', '#mailREC[CurrentRow][4]')
commit tran
I was on a 2005 sql server, so I'm not sure if the multiple VALUES clause
will work, but if so I would imagine this would be even more efficient.
begin tran
INSERT INTO listTable (email, fname, lname, other)
OUTPUT #req.thisGROUPID#, inserted.listID
INTO nl_catREL (groups_id, email_id)
VALUES
('#mailREC[CurrentRow][1]#', '#mailREC[CurrentRow][2]#',
'#mailREC[CurrentRow][3]#', '#mailREC[CurrentRow][4]') ,
commit tran
You'd have to re-write this to account for the extra comma of course.
Byron Mann
Lead Engineer & Architect
HostMySite
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359454
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Avoiding a boat load of queries inserting multiple records - Better Way?
On 10/10/2014 1:50 AM, Mike K wrote: > You do know that Microsoft changed the insert syntax as from SQLServer 2008 > dont you?now you can insert multiple records in one go like this: > > INSERT into Tablename (field1, field2, field3, field4 ) Values > ( value1, value2, value3, value4 ... ; > value1, value2, value3, value4 ; > value1, value2, value3, value4 > ) > > The problem is, I'm writing to TWO tables Insert into MailList (email address, name ) Insert into Categories ( just_inserted_MailList_id, other_relational_stuff...) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359450 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Avoiding a boat load of queries inserting multiple records - Better Way?
It sounds like your client needs a smarter application. When your client uploads their XLS document, you can add functionality that displays back to them the first line of the excel spreadsheet and have them identify which column is filled with which data...( email|first name|last name|company) Then you can instruct whichever method you end up using to correlate the correct column with the correct data. William -- William Seiter -Original Message- From: Les Mizzell [mailto:lesm...@bellsouth.net] Sent: Thursday, October 09, 2014 5:51 PM To: cf-talk Subject: Re: Avoiding a boat load of queries inserting multiple records - Better Way? > Not knowing much about your overall process, I'll try to stick to some > generic ideas. Let's assume you don't need this stuff dumped into the > database the very second it gets sent to you. This is actually part of a client's admin system on their website. They send out legal newsletters. LOTS of legal newsletters. And they have boats loads of various list they can send them too. Some lists go to 15,000 or more subscribers at a time. They want to be able to import as many list as they want with as many records at any time they want by just clicking the "import list" button... This app have been around for awhile. They just upgraded their server, so up to this point the import function was using the POI utility. Even when they entered a "legal" spreadsheet into the system, if it had too many records or extra crap for POI to chew through, the whole thing would time out. They KNOW that the damn spreadsheet is supposed to be formatted a very specific way for this to work, and there's a template they're supposed to use, but clients being clients, they will attempt to shove ANYTHING in there at times, and then yell when it doesn't import. Even the concept of "the email address HAS to be the first column" just gets ignored a lot. This particular application has been a huge challenge from day one, mostly because the rules get ignored half the time, so the app kicks the spreadsheet back out at them and demands it be reformatted, and they yell about it thinking that it should be able to import whatever they try to put in. Ain't never gonna happen. Really, how hard is: email|first name|last name|company? But I see sheets with hidden columns, paragraphs of extra stuff, formulas (what the heck for? It's supposed to be a list of addresses!!), email address in the 9th column when the app is specifically set to ignore anything past the first four ... it's unreal. Since the server upgrade, sheets that would time out seem to get parsed pretty quickly by cfspreadsheet functions. I'm happier with it now. But, I'm trying to improve on the efficiency of the whole thing by optimizing the queries, because I KNOW that running 20,000 queries to import 5000 email addresses isn't a good thing as far as cpu cycles go. I'm having my local SQL Server expert meet with me tomorrow and we'll get a stored procedure written to take care of all this in one fell swoop... Clients, huh?? Sheesh! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359449 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Avoiding a boat load of queries inserting multiple records - Better Way?
Mark,
That was before SQL Server 2008. Your way still works, but this was an
addition to the TSQL language.
Steve
-Original Message-
From: Gaulin, Mark [mailto:mark.gau...@ihs.com]
Sent: Friday, October 10, 2014 9:04 AM
To: cf-talk
Subject: RE: Avoiding a boat load of queries inserting multiple records -
Better Way?
My experience with MS SQL Server is that you have to do multiple inserts using
a series of UNION statements...
Insert into tablename(field1, field2, field3, field4 ...)
select value1, value2, value3, value4 ...
union all select value1, value2, value3, value4 ...
union all select value1, value2, value3, value4 ...
union all select value1, value2, value3, value4 ...
Thanks
Mark
-Original Message-
From: DURETTE, STEVEN J [mailto:sd1...@att.com]
Sent: Friday, October 10, 2014 8:19 AM
To: cf-talk
Subject: RE: Avoiding a boat load of queries inserting multiple records -
Better Way?
Actually I believe the syntax is:
Insert into tablename(field1, field2, field3, field4 ...) Values
(value1, value2, value3, value4 ...),
(value1, value2, value3, value4 ...),
(value1, value2, value3, value4 ...);
Steve
-Original Message-
From: Mike K [mailto:afpwebwo...@gmail.com]
Sent: Friday, October 10, 2014 1:51 AM
To: cf-talk
Subject: Re: Avoiding a boat load of queries inserting multiple records -
Better Way?
You do know that Microsoft changed the insert syntax as from SQLServer 2008
dont you?now you can insert multiple records in one go like this:
INSERT into Tablename (field1, field2, field3, field4 ) Values
( value1, value2, value3, value4 ... ;
value1, value2, value3, value4 ;
value1, value2, value3, value4
)
IF you uave one of those fields as an index field (some databases call them
autonumber fields) you dont have to know what the current index value of
record 1 is when you insert record 2 .Or (i havent tried this buti
reckon it ought to work) if say field1 was your index field, you could
user the value Ident_Current('Tablename ') as the value of field1 in the
subsequent records.
Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month
On Fri, Oct 10, 2014 at 11:51 AM, Les Mizzell wrote:
>
> > Not knowing much about your overall process, I'll try to stick to some
> > generic ideas. Let's assume you don't need this stuff dumped into the
> > database the very second it gets sent to you.
>
>
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359446
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Avoiding a boat load of queries inserting multiple records - Better Way?
My experience with MS SQL Server is that you have to do multiple inserts using
a series of UNION statements...
Insert into tablename(field1, field2, field3, field4 ...)
select value1, value2, value3, value4 ...
union all select value1, value2, value3, value4 ...
union all select value1, value2, value3, value4 ...
union all select value1, value2, value3, value4 ...
Thanks
Mark
-Original Message-
From: DURETTE, STEVEN J [mailto:sd1...@att.com]
Sent: Friday, October 10, 2014 8:19 AM
To: cf-talk
Subject: RE: Avoiding a boat load of queries inserting multiple records -
Better Way?
Actually I believe the syntax is:
Insert into tablename(field1, field2, field3, field4 ...) Values
(value1, value2, value3, value4 ...),
(value1, value2, value3, value4 ...),
(value1, value2, value3, value4 ...);
Steve
-Original Message-
From: Mike K [mailto:afpwebwo...@gmail.com]
Sent: Friday, October 10, 2014 1:51 AM
To: cf-talk
Subject: Re: Avoiding a boat load of queries inserting multiple records -
Better Way?
You do know that Microsoft changed the insert syntax as from SQLServer 2008
dont you?now you can insert multiple records in one go like this:
INSERT into Tablename (field1, field2, field3, field4 ) Values
( value1, value2, value3, value4 ... ;
value1, value2, value3, value4 ;
value1, value2, value3, value4
)
IF you uave one of those fields as an index field (some databases call them
autonumber fields) you dont have to know what the current index value of
record 1 is when you insert record 2 .Or (i havent tried this buti
reckon it ought to work) if say field1 was your index field, you could
user the value Ident_Current('Tablename ') as the value of field1 in the
subsequent records.
Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month
On Fri, Oct 10, 2014 at 11:51 AM, Les Mizzell wrote:
>
> > Not knowing much about your overall process, I'll try to stick to some
> > generic ideas. Let's assume you don't need this stuff dumped into the
> > database the very second it gets sent to you.
>
>
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359445
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Avoiding a boat load of queries inserting multiple records - Better Way?
Actually I believe the syntax is:
Insert into tablename(field1, field2, field3, field4 ...)
Values
(value1, value2, value3, value4 ...),
(value1, value2, value3, value4 ...),
(value1, value2, value3, value4 ...);
Steve
-Original Message-
From: Mike K [mailto:afpwebwo...@gmail.com]
Sent: Friday, October 10, 2014 1:51 AM
To: cf-talk
Subject: Re: Avoiding a boat load of queries inserting multiple records -
Better Way?
You do know that Microsoft changed the insert syntax as from SQLServer 2008
dont you?now you can insert multiple records in one go like this:
INSERT into Tablename (field1, field2, field3, field4 ) Values
( value1, value2, value3, value4 ... ;
value1, value2, value3, value4 ;
value1, value2, value3, value4
)
IF you uave one of those fields as an index field (some databases call them
autonumber fields) you dont have to know what the current index value of
record 1 is when you insert record 2 .Or (i havent tried this buti
reckon it ought to work) if say field1 was your index field, you could
user the value Ident_Current('Tablename ') as the value of field1 in the
subsequent records.
Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month
On Fri, Oct 10, 2014 at 11:51 AM, Les Mizzell wrote:
>
> > Not knowing much about your overall process, I'll try to stick to some
> > generic ideas. Let's assume you don't need this stuff dumped into the
> > database the very second it gets sent to you.
>
>
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359444
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Avoiding a boat load of queries inserting multiple records - Better Way?
You do know that Microsoft changed the insert syntax as from SQLServer 2008
dont you?now you can insert multiple records in one go like this:
INSERT into Tablename (field1, field2, field3, field4 ) Values
( value1, value2, value3, value4 ... ;
value1, value2, value3, value4 ;
value1, value2, value3, value4
)
IF you uave one of those fields as an index field (some databases call them
autonumber fields) you dont have to know what the current index value of
record 1 is when you insert record 2 .Or (i havent tried this buti
reckon it ought to work) if say field1 was your index field, you could
user the value Ident_Current('Tablename ') as the value of field1 in the
subsequent records.
Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month
On Fri, Oct 10, 2014 at 11:51 AM, Les Mizzell wrote:
>
> > Not knowing much about your overall process, I'll try to stick to some
> > generic ideas. Let's assume you don't need this stuff dumped into the
> > database the very second it gets sent to you.
>
>
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359443
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Avoiding a boat load of queries inserting multiple records - Better Way?
> Not knowing much about your overall process, I'll try to stick to some > generic ideas. Let's assume you don't need this stuff dumped into the > database the very second it gets sent to you. This is actually part of a client's admin system on their website. They send out legal newsletters. LOTS of legal newsletters. And they have boats loads of various list they can send them too. Some lists go to 15,000 or more subscribers at a time. They want to be able to import as many list as they want with as many records at any time they want by just clicking the "import list" button... This app have been around for awhile. They just upgraded their server, so up to this point the import function was using the POI utility. Even when they entered a "legal" spreadsheet into the system, if it had too many records or extra crap for POI to chew through, the whole thing would time out. They KNOW that the damn spreadsheet is supposed to be formatted a very specific way for this to work, and there's a template they're supposed to use, but clients being clients, they will attempt to shove ANYTHING in there at times, and then yell when it doesn't import. Even the concept of "the email address HAS to be the first column" just gets ignored a lot. This particular application has been a huge challenge from day one, mostly because the rules get ignored half the time, so the app kicks the spreadsheet back out at them and demands it be reformatted, and they yell about it thinking that it should be able to import whatever they try to put in. Ain't never gonna happen. Really, how hard is: email|first name|last name|company? But I see sheets with hidden columns, paragraphs of extra stuff, formulas (what the heck for? It's supposed to be a list of addresses!!), email address in the 9th column when the app is specifically set to ignore anything past the first four ... it's unreal. Since the server upgrade, sheets that would time out seem to get parsed pretty quickly by cfspreadsheet functions. I'm happier with it now. But, I'm trying to improve on the efficiency of the whole thing by optimizing the queries, because I KNOW that running 20,000 queries to import 5000 email addresses isn't a good thing as far as cpu cycles go. I'm having my local SQL Server expert meet with me tomorrow and we'll get a stored procedure written to take care of all this in one fell swoop... Clients, huh?? Sheesh! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359442 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Avoiding a boat load of queries inserting multiple records - Better Way?
Just to play devil's advocate for moment... This thread reminds me of the old adage about what happens when you hire a carpenter for just any old job, they tend to hit things with hammers. As web software developer, our first instinct is to go out and write code. Having been down this same avenue a hundred times, I can tell you, ColdFusion is a terrible way to process very large Excel files into a database. As you're beginning to feel as well I'm sure. Not knowing much about your overall process, I'll try to stick to some generic ideas. Let's assume you don't need this stuff dumped into the database the very second it gets sent to you. Also, let's assume they maybe your not getting the files from a standard web form upload. But you've got Excel files that need to be periodically read and uploaded into a SQL Server. The very best tool for doing this is Microsoft SSIS. SQL Server includes an ETL (Extract Transform Load) tool baked into a version of Visual Studio, it's free to download and use, It's learning curve is very low and it does exactly what you are describing very well and very quickly. If you instead setup an SSIS job to take the excel and load it into the database, you can either schedule it to run and check a folder on your server (thereby processing every excel file in there as a batch) or you could even fire it off as needed via some command tools in SQL Server. Here are some helpful links http://blog.sqlauthority.com/2014/02/06/sql-server-learning-ssis-where-do-i-start-notes-from-the-field-014/ http://www.microsoft.com/en-us/download/details.aspx?id=36843 -- Alan Rother ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359441 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Avoiding a boat load of queries inserting multiple records - Better Way?
I would probably use a trigger to do the second insert. On Thu, Oct 9, 2014 at 5:38 PM, Les Mizzell wrote: > I'll give this a try. It's got to be way less processor intensive than > running multiple queries for each insert. > Still, looks the stored procedure will be the "less load on the server" > way to go. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359440 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Avoiding a boat load of queries inserting multiple records - Better Way?
On 10/9/2014 5:20 PM, William Seiter wrote: > Off the top of my head... > > To insert the last inserted unique id in a transaction... > > > INSERT dbo.table(column) SELECT 1; > SELECT @newID = SCOPE_IDENTITY(); > INSERT dbo.table_2 (column) SELECT @newID; > > I'll give this a try. It's got to be way less processor intensive than running multiple queries for each insert. Still, looks the stored procedure will be the "less load on the server" way to go. Thanks!! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359439 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Avoiding a boat load of queries inserting multiple records - Better Way?
Off the top of my head... To insert the last inserted unique id in a transaction... INSERT dbo.table(column) SELECT 1; SELECT @newID = SCOPE_IDENTITY(); INSERT dbo.table_2 (column) SELECT @newID; -- William Seiter -Original Message- From: Les Mizzell [mailto:lesm...@bellsouth.net] Sent: Thursday, October 09, 2014 2:14 PM To: cf-talk Subject: Re: Avoiding a boat load of queries inserting multiple records - Better Way? On 10/9/2014 5:00 PM, Cameron Childress wrote: > > > A few years ago I stopped using numerics and auto-increment and > started using UUID for all PK/FK columns. On new stuff, this is what I'm doing as well. Unfortunitely, this is a pre-existing app and would take a good bit of rewrite to do that. Still scratching my head over writing the procedure itself. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359438 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Avoiding a boat load of queries inserting multiple records - Better Way?
On 10/9/2014 5:16 PM, William Seiter wrote: > What kind of database? MSSql? Mysql? Oracle? ... > > SQL Server 10 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359437 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Avoiding a boat load of queries inserting multiple records - Better Way?
What kind of database? MSSql? Mysql? Oracle? ... -- William Seiter -Original Message- From: Les Mizzell [mailto:lesm...@bellsouth.net] Sent: Thursday, October 09, 2014 1:29 PM To: cf-talk Subject: Re: Avoiding a boat load of queries inserting multiple records - Better Way? On 10/9/2014 1:49 PM, Cameron Childress wrote: > Most databases will let you issue multiple SQL statements in a single > request/transaction. You just have to separate them with a semicolon. If you did it this way, how would you get the ID from the first insert for use in the 2nd ... (because I'm looking at stored procedures and I'm not ashamed to admit that doing that inside SQL Server is beyond my coding capabilities - unless somebody can point me to /The Complete Idiot's Guide to Writing this Procedure with SqlServer 10/ (which I've been looking for but haven't found yet...) So something like: insert into table_1 (...) values (...); insert into table_2 (table_1_id, other_stuff) values (#Id_from_above_insert#, #other_stuff# ) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359436 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Avoiding a boat load of queries inserting multiple records - Better Way?
On 10/9/2014 5:00 PM, Cameron Childress wrote: > > > A few years ago I stopped using numerics and auto-increment and started > using UUID for all PK/FK columns. On new stuff, this is what I'm doing as well. Unfortunitely, this is a pre-existing app and would take a good bit of rewrite to do that. Still scratching my head over writing the procedure itself. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359435 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Avoiding a boat load of queries inserting multiple records - Better Way?
On Thu, Oct 9, 2014 at 4:29 PM, Les Mizzell wrote: > If you did it this way, how would you get the ID from the first insert > for use in the 2nd ... No I don't think that's going to work the way you want it to in that case. A few years ago I stopped using numerics and auto-increment and started using UUID for all PK/FK columns. There are a wide variety of benefits to this, including being able to pre-create the PK and know what it is going to be before you do the insert and without having to pull it back out of the DB. So, that's how I would get the ID from the 1st insert to use in the 2nd. However, that's probably not very helpful to you unless this is a new database/app and you have the freedom to make that change. -Cameron -- Cameron Childress -- p: 678.637.5072 im: cameroncf ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359434 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Avoiding a boat load of queries inserting multiple records - Better Way?
On 10/9/2014 1:49 PM, Cameron Childress wrote: > Most databases will let you issue multiple SQL statements in a single > request/transaction. You just have to separate them with a semicolon. If you did it this way, how would you get the ID from the first insert for use in the 2nd ... (because I'm looking at stored procedures and I'm not ashamed to admit that doing that inside SQL Server is beyond my coding capabilities - unless somebody can point me to /The Complete Idiot's Guide to Writing this Procedure with SqlServer 10/ (which I've been looking for but haven't found yet...) So something like: insert into table_1 (...) values (...); insert into table_2 (table_1_id, other_stuff) values (#Id_from_above_insert#, #other_stuff# ) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359433 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Avoiding a boat load of queries inserting multiple records - Better Way?
> Have you investigated using a Stored Procedure that does both inserts? This seems the way to go. Writing Transact-SQL is outside my area of expertise. I'm looking at example code now. Give me a bit and maybe I'll figure it out... Thanks to everybody that replied. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359432 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Avoiding a boat load of queries inserting multiple records - Better Way?
or just get all the email addresses from excel and send them over to a tored procedure as a list to process in one go. On Thu, Oct 9, 2014 at 7:22 PM, Dean Lawrence wrote: > > Les, > > I think that it would probably be more efficient if you imported all the > records into a temp table and then did an insert into the main email table > based upon a join query that only includes records from the temp table that > are not in the main email table. You could then do a similar insert into > the category relationship table, but update the query to use the new id > value that was created by the insert into the main email table. Both of > these queries could be included in a single stored procedure and executed > with a single request from ColdFusion. > > On Thu, Oct 9, 2014 at 12:30 PM, Les Mizzell > wrote: > > > > > I've got an application that imports email list from Excel sheets. > > Mostly working fine, but I've got one spot where I'd like to optimize > > things if I could. > > > > Once the data is imported I run two queries against each email address: > > 1. see if the email address is already in the group in question > > 2. see if this person has already unsubscribed from this list ---> > > > > If both of those return no records, and I've already run some other > > validation making sure entered info is valid, I add everything to an > array: > > > > > > > > > > > > > > Now the problem ... > > I need to do two inserts > >- first to enter the info into the email database > >- second to capture the ID from the first insert, and put that into a > > relational table that assigns that address to the specified group. > > I removed cfqueryparam from the below to make it more compact... > > > > So, I loop through my array and enter all the records.. > > > > > > > > SET NOCOUNT ON > > . remainder of insert code > > SET NOCOUNT OFF > > SELECT @@IDENTITY AS fromLISTID > > > > > > > >INSERT INTO nl_catREL ( groups_id, email_id ) > > VALUES ( #req.thisGROUPID#, #insertLIST.fromLISTID# ) > > > > > > > > The problem is, some of these spreadsheets and have up to 5,000 or more > > email addresses. > > That means, just for the insert, two queries for each address = 10,000 > > queries. > > If it was just one insert query, no big deal, as I could loop inside the > > query tag itself and reduce it to one call. It's the two combined that > > are giving me a headache. The application IS working the way it is, but > > shove a 15,000 email list into it, and it obviously chokes. > > > > Is there a better way to set up my two insert queries above so it's not > > making two calls for every single address? > > > > > > > > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359431 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Avoiding a boat load of queries inserting multiple records - Better Way?
Les, I think that it would probably be more efficient if you imported all the records into a temp table and then did an insert into the main email table based upon a join query that only includes records from the temp table that are not in the main email table. You could then do a similar insert into the category relationship table, but update the query to use the new id value that was created by the insert into the main email table. Both of these queries could be included in a single stored procedure and executed with a single request from ColdFusion. On Thu, Oct 9, 2014 at 12:30 PM, Les Mizzell wrote: > > I've got an application that imports email list from Excel sheets. > Mostly working fine, but I've got one spot where I'd like to optimize > things if I could. > > Once the data is imported I run two queries against each email address: > 1. see if the email address is already in the group in question > 2. see if this person has already unsubscribed from this list ---> > > If both of those return no records, and I've already run some other > validation making sure entered info is valid, I add everything to an array: > > > > > > > Now the problem ... > I need to do two inserts >- first to enter the info into the email database >- second to capture the ID from the first insert, and put that into a > relational table that assigns that address to the specified group. > I removed cfqueryparam from the below to make it more compact... > > So, I loop through my array and enter all the records.. > > > > SET NOCOUNT ON > . remainder of insert code > SET NOCOUNT OFF > SELECT @@IDENTITY AS fromLISTID > > > >INSERT INTO nl_catREL ( groups_id, email_id ) > VALUES ( #req.thisGROUPID#, #insertLIST.fromLISTID# ) > > > > The problem is, some of these spreadsheets and have up to 5,000 or more > email addresses. > That means, just for the insert, two queries for each address = 10,000 > queries. > If it was just one insert query, no big deal, as I could loop inside the > query tag itself and reduce it to one call. It's the two combined that > are giving me a headache. The application IS working the way it is, but > shove a 15,000 email list into it, and it obviously chokes. > > Is there a better way to set up my two insert queries above so it's not > making two calls for every single address? > > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359430 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Avoiding a boat load of queries inserting multiple records - Better Way?
Have you investigated using a Stored Procedure that does both inserts? -- William Seiter -Original Message- From: Les Mizzell [mailto:lesm...@bellsouth.net] Sent: Thursday, October 09, 2014 9:31 AM To: cf-talk Subject: Avoiding a boat load of queries inserting multiple records - Better Way? I've got an application that imports email list from Excel sheets. Mostly working fine, but I've got one spot where I'd like to optimize things if I could. Once the data is imported I run two queries against each email address: 1. see if the email address is already in the group in question 2. see if this person has already unsubscribed from this list ---> If both of those return no records, and I've already run some other validation making sure entered info is valid, I add everything to an array: Now the problem ... I need to do two inserts - first to enter the info into the email database - second to capture the ID from the first insert, and put that into a relational table that assigns that address to the specified group. I removed cfqueryparam from the below to make it more compact... So, I loop through my array and enter all the records.. SET NOCOUNT ON . remainder of insert code SET NOCOUNT OFF SELECT @@IDENTITY AS fromLISTID INSERT INTO nl_catREL ( groups_id, email_id ) VALUES ( #req.thisGROUPID#, #insertLIST.fromLISTID# ) The problem is, some of these spreadsheets and have up to 5,000 or more email addresses. That means, just for the insert, two queries for each address = 10,000 queries. If it was just one insert query, no big deal, as I could loop inside the query tag itself and reduce it to one call. It's the two combined that are giving me a headache. The application IS working the way it is, but shove a 15,000 email list into it, and it obviously chokes. Is there a better way to set up my two insert queries above so it's not making two calls for every single address? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359427 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Avoiding a boat load of queries inserting multiple records - Better Way?
On Thu, Oct 9, 2014 at 12:30 PM, Les Mizzell wrote: > Is there a better way to set up my two insert queries above so it's not > making two calls for every single address? Most databases will let you issue multiple SQL statements in a single request/transaction. You just have to separate them with a semicolon. I have done things before like this: INSERT INTO table (...) VALUES(...); Certain platforms may require you to turn on a switch in the JDBC driver to allow this, but you can probably get your 10,000 queries down to one single SQL call if you do it right. -Cameron ... ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359426 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
