We had a similar problem using ODBC drivers on an HP3000 once. It had something to do with how the HP was trying to process the SELECT. The setting that we had to play with was client side versus server side cursors. If you are using a client side cursor then the mainframe will try and send all of the data to the client before applying the WHERE clause of the SELECT statement. If you use server side cursors then the WHERE clause will be executed on the mainframe rather than the client.
Steve Miller G. A. Sullivan -----Original Message----- From: dotnet discussion [mailto:[EMAIL PROTECTED]] On Behalf Of Christopher Castelot Sent: Friday, May 03, 2002 1:24 PM To: [EMAIL PROTECTED] Subject: Re: [DOTNET] Accessing AS400 data through ado.net Ok. This is the deal. Using a single connection and the DataReader I brought the entire production data base to its knees simply by doing a select statement to see if an item already exists on the database. I had to abort the process after an hour when management screamed because of performance. (And this is only a subset of the necessary processing.) Making new connections doesn't seem to bring the 400 to its knees, just takes for ever and a day. The kicker here is that an old, rather poorly written VB6 application using odbc drivers is out performing the .Net version. -----Original Message----- From: Tom Archer [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 02, 2002 7:04 PM To: [EMAIL PROTECTED] Subject: Re: [DOTNET] Accessing AS400 data through ado.net On start, yes, but then you no longer need to reopen the connection. Personally, I prefer the lower-level coding of APPC because I like to have that level of control. You'll also note that the faster ODBC drivers (such as Showcase) are implemented using APPC for this reason. -----Original Message----- From: dotnet discussion [mailto:[EMAIL PROTECTED]]On Behalf Of Dave Heizer Sent: Thursday, May 02, 2002 6:57 PM To: [EMAIL PROTECTED] Subject: Re: [DOTNET] Accessing AS400 data through ado.net APPC incurs the same overhead on start up as a opening a connection do to the need to start a new job on the 400 to service the requests. My preference has bee to just hold an open connection over writing the routines to handle the APPC communication. -----Original Message----- From: dotnet discussion [mailto:[EMAIL PROTECTED]] On Behalf Of Tom Archer Sent: Thursday, May 02, 2002 5:37 PM To: [EMAIL PROTECTED] Subject: Re: [DOTNET] Accessing AS400 data through ado.net >>> 1) I assume you are not creating a new connection each time you access the 400. 400 connections are very costly to create. If for some reason you must create a new connection all the time, verify that 400 is configured to reuse the prestart jobs handling the connection. <<< This would be one of the main benefits of writing your own APPC conversations - to have this level of control. -----Original Message----- From: dotnet discussion [mailto:[EMAIL PROTECTED]]On Behalf Of Dave Heizer Sent: Thursday, May 02, 2002 6:26 PM To: [EMAIL PROTECTED] Subject: Re: [DOTNET] Accessing AS400 data through ado.net Some thoughts after many years of squeezing what ever I could 1) I assume you are not creating a new connection each time you access the 400. 400 connections are very costly to create. If for some reason you must create a new connection all the time, verify that 400 is configured to reuse the prestart jobs handling the connection. 2) Use programs written in one of the ILE languages on the 400 and call them just as you would a SQL stored procedure. 3) Inserts are always slow, if you only will be using the 400 for this application, use the SQL extensions for blocking the inserts. Good luck -----Original Message----- From: dotnet discussion [mailto:[EMAIL PROTECTED]] On Behalf Of Christopher Castelot Sent: Thursday, May 02, 2002 3:30 PM To: [EMAIL PROTECTED] Subject: [DOTNET] Accessing AS400 data through ado.net Hi all, I work in an environment which has most inventory and distribution data on an AS400. The company also has an Oracle 8i database for client server apps and web based business. For all intents and purposes the Oracle database is a mirror of the 400. However, all data is entered on to the 400 and oracle is kept in sync through a series of updates. My project is to start loading product data for new sources directly to the AS400 (what has been a primarily manual process). I started out writing the application in .Net and would like to finish it that way. Here's the problem: 1) Using Client Access 5.1, Ado.Net, and the oledb provider IBMDA400 directly hitting the 400 takes nearly 3 hours to process 28,000 records. Unacceptably long. This same scenario in terms of processing except hitting the Oracle database takes 14 minutes to process the same 28,000 records. (MSDAORA as the provider.) If I use an Oracle Gateway (MSDAORA as the provider) the same processing takes 30 minutes. I would think that going through a gateway would be slower than going direct. The question is how can I optimize performance going directly to the 400? Although it is interesting that Oracle takes significantly less time it is also not an option. The gateway is only for selects, not inserts. And, most importantly, this project is dead on arrival if I can't get that processing time down to a reasonable time. Here's what I am doing? connectionString "Provider=IBMDA400.DataSource.1;Data Source=prod;User ID=*****;Password=******" cmdText "SELECT title AS result FROM tdmsdta01.dmm005 WHERE itnbr = '009335021X '" try { conn = NewConnection(); OleDbCommand cmd = new OleDbCommand( sSql, conn); conn.Open(); dr = cmd.ExecuteReader(); if (dr.Read()) { objReturn = dr.GetValue(0); } } catch Any hints would be greatly appreciated! Chris You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com. You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com. You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com. You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com. You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com. You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com. You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com.
