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

[MadMan2004]

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.

 

[Philippe]

Hi,
Which provider are you using ? IBMDA400 ?
If not, it's at least worth giving a try.

 

[MadMan2004]

I checked out the website. Unfortunately, as near as I can tell, the
problem seems to be in the communication between the AS400 and the
Vb.Net app - or in the processing of the SQL statement.

However, if I execute the same statement over and over again with the
same where clause, performance usually improves. But in any real-world
application I have written, the sql statements usually vary in their
where clause.

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

 

[Elvis]

Have you tuned your SQL on the iSeries? My experience has been that
ANY new SQL application on the iSeries must be tuned prior to releasing
it to the end users.
This is not an easy exercise and requires time, effort and basic
understanding of DB2 optimization on the iSeries. Once the developer
puts the time in, application usually rolls out just fine.
Changes that developer would make is sql syntax, creating perfect
indexes, sizing memory pools appropriately etc.

One possible problem is if the production environment is significantly
different from development environment. It doesn't sound like this is
the case in your situation.
Your comment on SQL statement running better when you run it more than
once tells me that the fact that SQL cache (job or system level) is
doing its job by preserving the access plan for your statements. If
you want to force this type of approach, you could by using SQL
packages on the iSeries. Basically you would turn extended dynamic
support for your application data source and run it through as many
variations of the SQL statements as possible, purpose being to populate
the SQL package with most commonly used SQL queries. Best use for SQL
package is if your applications uses parameter markers, as statements
would not be treated as different just due to the fact that user is
using different values for the parameters.

Hope that helps.

<vendor on>
We have a pair of tools that build on IBM's database monitor technology
and are premier tools for SQL tuning for DB2 UDB for iSeries:
insure/INDEX and insure/ANALYSIS.

http://www.centerfieldtechnology.com
<vendor off>

Elvis

 

[Jay B. Harlow [MVP - Outlook]]

Chris,
Two things to watch out for:

1. Opening new connections. Each time you create a new connection to the
AS400 its going to cost, a lot. If you can open a single connection & reuse
it. If v5r3 supports connection pooling (earlier versions did not) then use
it, if v5r3 doesn't, then I would recommend you hold onto the connection
yourself.

2. Not using "prepared" statements. If you can structure your SQL so that it
uses parameter markers & prepare the statement before executing it. your
performance should improve.


--
Hope this helps
Jay B. Harlow [MVP - Outlook]
..NET Application Architect, Enthusiast, & Evangelist
T.S. Bradley - http://www.tsbradley.net


| 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.
|