performance hit?

picture this: 1 informix server (IFX 10) with a onsoctcp in sqlhosts, NO onipcshm at all. 50 databases (1 database for each business client) 300 workstations connecting to the server To avoid having to configure a DSN for each database on each 300 workstations, when a new business client comes (or goes), the workstation application always connects to global_db@biz_prod and each and every SQL uses a fully qualified database and table names eg. select foo from bar_db@biz_prod:footable where foo='baz'; Does this cause a performance hit? Is this something we shouldn't do? If a workstation is connected to global_db@biz_prod and it reads (or writes) into another database using a fully qualified db@instance:table format, what type of connection is actually made to the referenced db@instance:table? another socket connection? Any good publication to read that would explain this in detail? If all of this is a big nono... should shared memory connection be considered eg. create biz_prod_shm in sqlhosts as onipcshm and let the workstation application continue to connect to global_db@biz_prod, but alter it's logic to address all fully qualified databases thru the newly created shared memory connection as 'select foo from bar_db@biz_prod_shm:footable where 1=1;' thanks, --IAM

Connections from one database to another within the same server are made internal to the engine and do not require or use a TCP connection. No worries on that front. I wrote an entire middleware server suite that depended on that same behavior at BB 15 years ago and it's successor is still in use today using the identical logic. It works fine and performance is fine. But don't take my word for it. It's easy enough to test. Just grab a few representative queries and run them manually once connected to global_db and once connected to <client>_db and see if there's any performance difference. That said, if the client applications are using ODBC connections (I'm assuming that because you mention DSN's) the clients can all use the same DSN but just define the DSN for each client to point to a different database! Art On Wed, Jan 28, 2009 at 12:53 PM, ILKKA MUSTONEN <i.will.sue.you@gmail.com>wrote: > picture this: > 1 informix server (IFX 10) with a onsoctcp in sqlhosts, NO onipcshm at all. > 50 databases (1 database for each business client) > 300 workstations connecting to the server > > To avoid having to configure a DSN for each database on each 300 > workstations, > when a new business > client comes (or goes), the workstation application always connects to > global_db@biz_prod > and each and every SQL uses a fully qualified database and table names > eg. select foo from bar_db@biz_prod:footable where foo='baz'; > > Does this cause a performance hit? Is this something we shouldn't do? > > If a workstation is connected to global_db@biz_prod and it reads (or > writes) > into > another database using a fully qualified db@instance:table format, what > type > of > connection is actually made to the referenced db@instance:table? another > socket connection? > > Any good publication to read that would explain this in detail? > > If all of this is a big nono... should shared memory connection be > considered > eg. create biz_prod_shm in sqlhosts as onipcshm and let the workstation > application > continue to connect to global_db@biz_prod, but alter it's logic to address > all > fully qualified databases thru the newly created shared memory connection > as 'select foo from bar_db@biz_prod_shm:footable where 1=1;' > > thanks, > --IAM > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. --001636c594e21550e3046190463c