I actually thought about doing a round-robin DSN by saying
<cfset application.dsn_counter = 1>
<cfset this_dsn= application.dsn_counter mod 4>
<CFQUERY name="yeah right" datsource="#Evaluate("dsn#this_dsn#")#">
...
<cfset application.dsn_counter = dsn_counter +1>
After thinking about the round-robin DSNing, though, I thought it more
efficient to just declare a specific DSN for each major table in the app.
Either way could work, though.
I was testing this multi-conn idea today with 2 connections, and it seemed
to work fine, however, I didn't have enough of a load to see a difference.
Alan McCollough
Web Programmer
Allaire Certified ColdFusion Developer
Alaska Native Medical Center
> -----Original Message-----
> From: Shannon Hicks [SMTP:[EMAIL PROTECTED]]
> Sent: Friday, September 01, 2000 11:30 AM
> To: [EMAIL PROTECTED]
> Subject: RE: Theory: Multiple ODBC conns?
>
> *slaps forehead*
>
> That depends on if the bottleneck is ODBC or the Database server.
>
> If ODBC is the bottleneck, then why not take it a step further...
>
> If your application uses #application.dsn#, then in the app_globals file,
> just specify something like
>
> <cfset application.dsn = "datasource#RandRange(1,4)#">
>
> (haven't looked up the random code in a while, so this may not be
> perfect.)
>
> Shan
>
> -----Original Message-----
> From: McCollough, Alan [mailto:[EMAIL PROTECTED]]
> Sent: Friday, September 01, 2000 11:18 AM
> To: '[EMAIL PROTECTED]'
> Subject: Therory: Multiple ODBC conns?
>
>
> This isn't purely a fusebox question, but I think y'all will appreciate
> it...
>
> I was thinkin' to myself (stop laffin'), on a high-traffic site, with a
> lot
> of DB hits, could you speed the CF>SQL transaction process up by having
> multiple ODBC connections?
>
> Most of us (what an assumption!) set up an ODBC for a given app, and
> that's
> it. We refer to it as "#application.dsn#" or some such variable.
>
> But... What if we set up #application.dsn1#, #application.dsn2# and so on,
> lets say 4 similar ODBC connections, which would of course, also be
> present
> on the CFAS, all 4 pointing to the same datasource.
>
> My theory is, if you know that your app has, say, 4 common SQL
> queries/actions that aren't cacheable, you assign each SQL query its own
> DSN, so it travels on a separate ODBC connection, even though its hitting
> the same database
>
> EXAMPLE
> You have an app, and it calls upon a SQL db "Northwind", and this db has
> four tables you are using, "Employees" ,"Customers", "Orders", "Products".
>
> You create four ODBC DSN's at the CFAS:
> Northwind_Employees
> Northwind_Customers
> Northwind_Orders
> Northwind_Products
>
> In your CF templates, whenever you do a CFQUERY that calls upon one of the
> aforementioned tables, you use the associated DSN. If a query involves
> multiple tables, you use the DSN of the table that bears the weight of the
> processing burden (you can use Query Analyzer to find this out).
>
> I haven't tested this out for speed or load-bearing ability, but I have
> set
> up multiple DSN's to the same db. Has anybody experimented with this, or
> debunked this idea? I'm curious and don't wanna re-invent the wheel if its
> a
> flat.
>
> Alan McCollough
> Web Programmer
> Allaire Certified ColdFusion Developer
> Alaska Native Medical Center
>
> --------------------------------------------------------------------------
> --
> --
> To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/fusebox or
> send a message to [EMAIL PROTECTED] with 'unsubscribe' in
> the body.
>
> --------------------------------------------------------------------------
> ----
> To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/fusebox or
> send a message to [EMAIL PROTECTED] with 'unsubscribe' in
> the body.
------------------------------------------------------------------------------
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/fusebox or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
