How does the @instance affects performance ?

Hello peps, i got in hands some relatively big app that runs in a windows .net context and works agains several ids instances. The solution of the developer was to connect to 1 ids in particular and put in every query: "select some_cod, some_des from some_db{0}:some_table..." and then in run-time replace the {0} for the corresponding @ids1 or @ids2 etc... Of course the db and table structures are the same acorss the several ids, this makes every query to the db use the @ids reference. My doubt is there is a performance penalty in this logic ? been @ids the same server of the connectio or one external. The alternative would be to have a connection to each ids and send the query on the correct connection to not use @ids but i dont know if it worth the work of change this. Thanks for your help. Enrique

There is a penalty for funneling queries through to a different instance, yes. You are sending the query to the local instance which must parse it to discover that it is not intended for itself but for a different instance. Then the local (connected) instance will open a connection to the remote instance and pass the query on and await results which it must then cache and send on to the original client. When the query is complete and the cursor closed the local instance will drop the remote connection. Therefore the next query to that same remote instance will have to experience the overhead of a connection. Much better to open multiple connections, one to each instance, then just switch instances with SET CONNECTION TO ... If you create each connection with the optional WITH CONCURRENT TRANSACTIONS clause and create each cursor with the WITH HOLD clause, you can even switch from connection to connection freely while fetching data from all of them with cursors and even having open transactions on one or more connection. Just remember that only one of the concurrently open connections can use shared memory if more than one instance is running on the same machine. The other connections have to be either stream pipe or a networked protocol. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Nov 19, 2012 at 8:50 AM, eferreyra <eferreyra@gmail.com> wrote: > Hello peps, i got in hands some relatively big app that runs in a windows > .net context and works agains several ids instances. > > The solution of the developer was to connect to 1 ids in particular and > put in every query: "select some_cod, some_des from > some_db{0}:some_table..." and then in run-time replace the {0} for the > corresponding @ids1 or @ids2 etc... > > Of course the db and table structures are the same acorss the several ids, > this makes every query to the db use the @ids reference. > > My doubt is there is a performance penalty in this logic ? been @ids the > same server of the connectio or one external. > > The alternative would be to have a connection to each ids and send the > query on the correct connection to not use @ids but i dont know if it worth > the work of change this. > > Thanks for your help. > Enrique > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Thanks Art, the first part i understand well, helps me a lot. The second, i think you refering to some I4GL kinda app, i dont have "SET CONNECTION TO" as far i know, im in a .Net app in a Windows server using IBM.Data.Informix .Net provider, what i can do is to have several connection (one for each ids) with the help of the .Net Framework connection pool. Then i send the query on the correct connection, its like having different applications-clients. Anyway i find interesting this: "Just remember that only one of the concurrently open connections can use shared memory if more than one instance is running on the same machine. The other connections have to be either stream pipe or a networked protocol." Wich kind of apps this applies ? Have some link to more info ? while this one is a .Net server/client app we have a lot of Informix-4GL apps too. Thanks

No problem, the .NET library is handling switching connections for you. Here is from 2-128 (the section on the CONNECT statement) of the Guide to SQL Syntax PDF manual (v11.70.xC6): On UNIX, the only restriction on establishing multiple connections to the same database environment is that an application can establish only one connection to each local server that uses the shared-memory connection mechanism. To find out whether a local server uses the shared-memory connection mechanism or the local-loopback connection mechanism, examine the $INFORMIXDIR/etc/sqlhosts file. For more information on the sqlhosts file, refer to your IBM Informix Administrator's Guide. On Windows, the local connection mechanism is named pipes. Multiple connections to the local server from one client can exist. Only one connection is current at any time; other connections are dormant. The application cannot interact with a database through a dormant connection. When an application establishes a new connection, that connection becomes current, and the previous current connection becomes dormant. You can make a dormant connection current with the SET CONNECTION statement. See also “SET CONNECTION statement” on page 2-707. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Nov 19, 2012 at 10:19 AM, eferreyra <eferreyra@gmail.com> wrote: > Thanks Art, the first part i understand well, helps me a lot. > > The second, i think you refering to some I4GL kinda app, i dont have "SET > CONNECTION TO" as far i know, im in a .Net app in a Windows server using > IBM.Data.Informix .Net provider, what i can do is to have several > connection (one for each ids) with the help of the .Net Framework > connection pool. > > Then i send the query on the correct connection, its like having different > applications-clients. > > Anyway i find interesting this: > "Just remember that only one of the concurrently open connections can use > shared memory if more than one instance is running on the same machine. > The other connections have to be either stream pipe or a networked > protocol." > Wich kind of apps this applies ? Have some link to more info ? while this > one is a .Net server/client app we have a lot of Informix-4GL apps too. > > Thanks > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

eferreyra <eferreyra@gmail.com> wrote: > The solution of the developer was to connect to 1 ids in particular and put in every query: > "select some_cod, some_des from some_db{0}:some_table..." and then in run-time replace the > {0} for the corresponding @ids1 or @ids2 etc... [...] > My doubt is there is a performance penalty in this logic ? once I had to solve an issue with ~10 times performance drop. the reason for this was a synonym created for one table that pointed to remote instance. the table had very good index on char(x) field, data was almost unique and select statement run extremaly fast. with synonym it was also quick, fractions of second but compare following: 0.0001s query time + 0.001s extra overhead for remoteness = big impact 0.1s query time + 0.001s extra overhead for remoteness = minor impact