AS400 - Ado.Net from Vb.Net Slow Query Times

From: MadMan2004 (cvoveris2000_at_cs.com)
Date: 03/16/05 

Date: 16 Mar 2005 12:49:16 -0800

Hello all!

I'm having a problem with a project I'm working on and I'd like to ask
for anyone's input that might be helpful. I'm building a rather large
front-end application connecting to an AS400 for the back end database
and I'm experiencing slow response times when executing sql statements.
 Some select statement response times are bad. Not all, but some. And
there doesn't seem to be a consistent factor in any of the sql
statements being so slow.

Based on the application design, there's a group of components that get
loaded dynamically which all access tables on the AS400. So it's very
possible that the same sql statement will execute 40 or 50 times. My
issue here is that for one execution of a select statement, it may take
50 - 200 milliseconds to execute. Cumulatively, assuming 50 executions
of the statement, you're looking at a worse case scenario of 10 seconds
(and it's even run longer, believe me). Even some INSERTs, UPDATEs and
DELETEs are also slow.

I'm using the iSeries Access v5r3 ole-db drivers. Keep in mind that
these access times are slow even when there are no users on the box, so
CPU usage is not an issue here. Although the slowdown becomes even
more evident when the CPU is at 40% or higher.

At this point the file sizes are very small (50-200 records in a file,
and even less for lookup tables). The files are indexed according to
the fields specified in the order of the where and order by clauses.

Unfortunately I can't use the managed provider due to our legacy file
structures using char for fields. The char field definition inherently
has spaces after the end of it. It would muck up our code too much to
trim each time we need to do a field comparison or a field assignment.
The managed provider from ibm doesn't trim trailing spaces. The ole-db
provider does trim trailing spaces.

Does anyone have any idea why SQL on the AS400 is this slow for me? I
have quite a bit of experience developing SQL Server applications in
Vb.Net and I've never experienced poor response times like this.

Can anyone direct me to a faster data provider? Or is there some
tuning parameters I can use for IBM's AS400 provider?

Any help would be very much appreciated.

Thanks!
Chris Voveris
Programmer / Analyst
Penn Software and Technology Services, Inc.

Relevant Pages

  • Re: AS400 - Ado.Net from Vb.Net Slow Query Times
    ... Which provider are you using? ... > and I'm experiencing slow response times when executing sql statements. ... > The managed provider from ibm doesn't trim trailing spaces. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Website Administration Tool not working from published applica
    ... provider, whom most of them can't afford the fancy of dedicated servers - ... SQL Server 2000 instead of SQL Express ... The first mistake was using ... that the Web App Admin Tool shows when it cannot connect to the database. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Can ASP.NET 2.0 Use SQLServer 2000 For Membership Function
    ... from the IIS admin tool (setting the conn string and membership provider) ... with a sql 2000 database in the back and IE7 ... that the Web App Admin Tool shows when it cannot connect to the database. ... Create a connection string to your ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: ORA-1866 The datetime class is invalid error...the saga continues!
    ... but you could try using a different data provider. ... Use the Microsoft ADO.NET Oracle Provider ... VB.NET to my employers and not being able to query data using a date field ... SQL statements I provided earlier will execute flawlessly in any enviornment ...
    (microsoft.public.dotnet.general)
  • Re: application design problem
    ... i.e one for sql and one for oracle), easy to extend (i.e if somebody come tomorrow and say I want support for MySql, then you will say its half a day job as you will have working UI and you just need to implement MySqlDataProvider, just few methods and most of them copy and paste from your existing providers. ... It is full of provider model. ... I dont think its a good idea to put all your client spcific logic ... be in UserControl only. ...
    (microsoft.public.dotnet.languages.csharp)
Loading