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: Writing an Apache 404 Handler in CF
Youve likely got a rewrite rule passes everything that is not a document to CF
for handling. Im guessing you have a friendly URL setup that routes everything
through index.cfm, which is why the redirect is taking place. Something like
this is probably the culprit:
#These are handled by Apache and not by CF
RewriteCond %{REQUEST_URI} \.(bmp|gif|jpe?g|png|css|js|txt|xls|ico|swf)$
RewriteRule ^(.*)$ - [NC,L]
#Everything else is handled by Coldfusion
RewriteRule ^$ index.cfm [QSA,NS]
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ index.cfm/%{REQUEST_URI} [QSA,L,NS]
If you change the rule for static files to include .aspx, then Apache will
handle that 404 as usual:
RewriteCond %{REQUEST_URI} \.(aspx|bmp|gif|jpe?g|png|css|js|txt|xls|ico|swf)$
HTH,
Jon
On Oct 10, 2014, at 4:34 PM, Russ Michaels wrote:
>
> Wouldn't url rewriting be better for this?
>
>
> On Fri, Oct 10, 2014 at 20:46 PM, Robert Glover wrote:
>
>
> This is as much an Apache question as it is a CF question; I hope this is
> acceptable. I posted it to Serverfault several days ago and haven't
> gotten any response, and it's become a rather critical issue.
>
> -
>
> I've written a custom 404 handler for Apache. The handler looks at the
> incoming URL (cgi.request_url), queries a database table, and responds by
> redirecting the visitor to the new URL. What should happen:
>
> www.mysite.co.uk gets redirected to www.mysite.com
>
> That much works fine. But when we get to more complex urls:
>
> www.mysite.co.uk/contact.aspx should go to www.mysite.com/contact/
>
> Something (Apache?) is stripping out the contact.aspx portion of it so I
> can't tell where to send the user. In fact, what's happening instead is
> contact.aspx is replaced with index.cfm (the name of the 404 handler). No
> other variables returned in the CGI scope contain the contact.aspx part of
> the url.
>
> Of course, we have the domain to be redirected pointed at this server via
> DNS, and configured in Apache, since the simple versions of the URL work
> fine.
>
> Is there a configuration setting or something to get it to stop stripping
> out the rest of the url? Thanks!
>
> Rob
>
>
>
>
~|
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:359453
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Writing an Apache 404 Handler in CF
Wouldn't url rewriting be better for this? On Fri, Oct 10, 2014 at 20:46 PM, Robert Glover wrote: This is as much an Apache question as it is a CF question; I hope this is acceptable. I posted it to Serverfault several days ago and haven't gotten any response, and it's become a rather critical issue. - I've written a custom 404 handler for Apache. The handler looks at the incoming URL (cgi.request_url), queries a database table, and responds by redirecting the visitor to the new URL. What should happen: www.mysite.co.uk gets redirected to www.mysite.com That much works fine. But when we get to more complex urls: www.mysite.co.uk/contact.aspx should go to www.mysite.com/contact/ Something (Apache?) is stripping out the contact.aspx portion of it so I can't tell where to send the user. In fact, what's happening instead is contact.aspx is replaced with index.cfm (the name of the 404 handler). No other variables returned in the CGI scope contain the contact.aspx part of the url. Of course, we have the domain to be redirected pointed at this server via DNS, and configured in Apache, since the simple versions of the URL work fine. Is there a configuration setting or something to get it to stop stripping out the rest of the url? Thanks! Rob ~| 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:359452 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Writing an Apache 404 Handler in CF
This is as much an Apache question as it is a CF question; I hope this is acceptable. I posted it to Serverfault several days ago and haven't gotten any response, and it's become a rather critical issue. - I've written a custom 404 handler for Apache. The handler looks at the incoming URL (cgi.request_url), queries a database table, and responds by redirecting the visitor to the new URL. What should happen: www.mysite.co.uk gets redirected to www.mysite.com That much works fine. But when we get to more complex urls: www.mysite.co.uk/contact.aspx should go to www.mysite.com/contact/ Something (Apache?) is stripping out the contact.aspx portion of it so I can't tell where to send the user. In fact, what's happening instead is contact.aspx is replaced with index.cfm (the name of the 404 handler). No other variables returned in the CGI scope contain the contact.aspx part of the url. Of course, we have the domain to be redirected pointed at this server via DNS, and configured in Apache, since the simple versions of the URL work fine. Is there a configuration setting or something to get it to stop stripping out the rest of the url? Thanks! Rob ~| 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:359451 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: Using caching and threading to load a page quickly
> Another approach would have the feed fetching functionality in a > separate template which is run as a scheduled task every 2 minutes. That > task can shove the parsed (and potentially formatted) feed into the > application scope. (With a lock.) And then your main page can output > that variable (with a lock). Then your home page would load as fast as > it would as if the feed were local. Based on all the information exchanged so far, this is the approach I'd recommend. Just fetch it periodically and store it locally. Don't wait for a user to request it first (as you're doing in another post). Dave Watts, CTO, Fig Leaf Software 1-202-527-9569 http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| 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:359448 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Using caching for a RSS feed
> ... > > >properties="info" timeout = "180"> > > ... > > For Application Server Caching, we have Cache Template in Request and Save > Class Files enabled. Since lot of files are in ColdFusion, > Trusted Cache is disabled. > > I added a basic cache so that the server running ColdFusion 9 reaches out to > the server hosting Wordpress site every 2 minutes > > 1. If 10(or 1000) people visit the webpage in an hour, where the above code > exists, will it connect to the server hosting Wordpress site every 2 minutes > for the RSS feed? Not necessarily. Assuming it starts with nothing in the cache, the first request will cause CF to fetch the RSS feed and store it for two minutes. Any subsequent requests in those two minutes will read from the cache. After those two minutes, the next request will again cause CF to fetch the RSS feed and store it for two more minutes. > 2. Or, will it be sooner if the RSS feed on WordPress changes between the 2 > minute interval? No, it will not be sooner. CF has no way of knowing whether the RSS feed changed without fetching it, which would defeat the point of caching. Dave Watts, CTO, Fig Leaf Software 1-202-527-9569 http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| 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:359447 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
