asked on

Access AS/400 db from .NET too slow

I have written an HTML/asp and a .NET/C# application that access the AS/400. The asp version runs at least three times faster and I must figure out why.
Here are snippets of the C# code (it works fine, just too slow):

In web.config:
<appSettings>
<add key="OrderInq.Data.Mapics" value="DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=AS400.BLOUNT.COM;TRANSLATE=1;XDYNAMIC=1;CONNTYPE=2;BLOCKFETCH=1;BLOCKSIZE=128;DBQ=LIBRARY;UID=user;password=pswd" />
</appSettings>

public class Mapics
{
public Mapics()
{
}
private static string GetConnectionString()
{
      const string cnnString = "OrderInq.Data.Mapics";
      return ConfigurationSettings.AppSettings[cnnString];
}
private static Microsoft.Data.Odbc.OdbcConnection GetConnection()
{
      return new Microsoft.Data.Odbc.OdbcConnection(GetConnectionString());
}
public static DataSet ExecuteDataset(string commandText)
{
Microsoft.Data.Odbc.OdbcCommand cmd = new Microsoft.Data.Odbc.OdbcCommand();
      cmd.CommandText = commandText;
      cmd.Connection = GetConnection();
      if (cmd.Connection.State.ToString() == "Closed")
            cmd.Connection.Open();
Microsoft.Data.Odbc.OdbcDataAdapter da = new
Microsoft.Data.Odbc.OdbcDataAdapter(cmd);
      DataSet ds = new DataSet();
      da.Fill(ds);
      return ds;
}
public static DataTable ExecuteDataTable(string commandText)
{
Microsoft.Data.Odbc.OdbcCommand cmd = new Microsoft.Data.Odbc.OdbcCommand();
      cmd.CommandText = commandText;
      cmd.Connection = GetConnection();
Microsoft.Data.Odbc.OdbcDataAdapter dat = new
Microsoft.Data.Odbc.OdbcDataAdapter(cmd);
      DataTable dt = new DataTable();
      dat.Fill(dt);
      return dt;
}
}

Then:

DataSet ds = DataTier.Mapics.ExecuteDataset(GetOrderCommandTextSql());
DataView dv =
new DataView(ds.Tables[0],"",sColumn,DataViewRowState.CurrentRows);
System.Collections.IEnumerator myEnumerator;
if (dv.Count > 0)
{
myEnumerator = dv.GetEnumerator();
while ( myEnumerator.MoveNext() )
{
shuttle(myOrder, dr); }}

private String GetOrderCommandTextSql()
{                                                return "SELECT C6CBTX AS PO,C6ABAL AS SHIPTO,C6ACDT AS ORDERDATE,C6FNST AS STATUS,C6CVNB AS ORDERNUMBER,C6B9CD AS SHIPTONO,C6DCCD AS HTYPE,C6AENB AS CO FROM AMFLIBA.MBC6RES3 AS MBC6RES3 WHERE MBC6RES3.C6DCCD<>'2' AND " + strWhere;
}

Snippet of shuttle to show how moving into myOrder:
private void shuttle(Order myOrder, DataRowView dr)
{
try
{
myOrder.OrderNumber = dr["ORDERNUMBER"].ToString();

Then the databind() to dg.

daveslater

Hi
run the SQL on the as/400 direct. STRSQL
If it is slow then
start debug. STRDBG
run the SQL again.
Look at the job log - and it will suggest some access paths.

Dave

dcass

ASKER

I'm an As400 programmer - I've got the access paths I want - taking advantage of logical file keys.
What I need is some help on the either finding a newer Microsoft.Data.ODBC.dll than 1.0 or the connect statement or fixing the code above.
The eight files I access are all in classes - the example above is just one of them. It basically does the code above eight times and it takes forever.
Does it make a new connection each time? Is there someway without re-writing the whole program to open a connection one time and use it in each of the classes instead of each of the eight classes doing it's own connection?

_TAD_

dcass,
I may be able to help you out a bit.

1) Are you using Visual Studio .Net, if so which version?
2) What version of .Net are you using (1.0 or 1.1)?
3) Finally, do you *have* to use static connections (they do tend to be slower and take up more bandwidth)?

Just for giggles we are going to try a few different scenarios (you should be able to cut and paste most of my code).

<coding... brb>

_TAD_

Actually... before i begin, I noticed your use of the Microsoft.Data.ODBC namespace....

This means that you are using Visual Studio 2002 with a .net version of 1.0.

I think if it is possible for you to upgrade to Visual Studio 2003 (.net version 1.1) it may solve most of your problems

_TAD_

Also note:

using fully qualified namespaces degrades performance because these licraries are then added at runtime. If you use the the "using" syntax you can add these libraries at compile time and then you won't have to load them "on-the-fly" as it were.

So...... without actually changing any of your code (I'm just moving things around):

using System;
using System.Data;
using Microsoft.Data.Odbc;

public class Mapics
{
public Mapics()
{
}
private static string GetConnectionString()
{
const string cnnString = "OrderInq.Data.Mapics";
return ConfigurationSettings.AppSettings[cnnString];
}
private static OdbcConnection GetConnection()
{
return new OdbcConnection(GetConnectionString());
}
public static DataSet ExecuteDataset(string commandText)
{
OdbcCommand cmd = new OdbcCommand();
cmd.CommandText = commandText;
cmd.Connection = GetConnection();
if (cmd.Connection.State.ToString() == "Closed")
cmd.Connection.Open();
OdbcDataAdapter da = new OdbcDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
public static DataTable ExecuteDataTable(string commandText)
{
OdbcCommand cmd = new OdbcCommand();
cmd.CommandText = commandText;
cmd.Connection = GetConnection();
OdbcDataAdapter dat = new OdbcDataAdapter(cmd);
DataTable dt = new DataTable();
dat.Fill(dt);
return dt;
}
}

Then:
using System;
using System.Data;
using System.Collections;

DataSet ds = DataTier.Mapics.ExecuteDataset(GetOrderCommandTextSql());
DataView dv =
new DataView(ds.Tables[0],"",sColumn,DataViewRowState.CurrentRows);
IEnumerator myEnumerator;
if (dv.Count > 0)
{
myEnumerator = dv.GetEnumerator();
while ( myEnumerator.MoveNext() )
{
shuttle(myOrder, dr); }}

_TAD_

If I were to be coding this class here is what my code would look like:

(actually I would remove all "static" key words and make this an instance class, but I wanted to keep the same 'flavor' of coding that you were using)

using System;
using System.Data;
using Microsoft.Data.Odbc;

public class Mapics
{
      private static OdbcCommand cmd;
      private static OdbcDataAdapter da;
      private const string cnnString = "OrderInq.Data.Mapics";

      public Mapics()
      {
// This constructor can be deleted if all of the elements are static
      }

      private static string GetConnectionString()
      {
            return ConfigurationSettings.AppSettings[cnnString];
      }

      private static OdbcConnection GetConnection()
      {
            return new OdbcConnection(GetConnectionString());
      }

      private static void InitCmdObj(string commandText)
      {
            cmd = new OdbcCommand();
            cmd.CommandText = commandText;
            cmd.Connection = GetConnection();
            if (cmd.Connection.State == ConnectionState.Closed)
                  cmd.Connection.Open();
      }

      public static DataSet ExecuteDataset(string commandText)
      {
            InitCmdObj(commandText);
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
      }

      public static DataTable ExecuteDataTable(string commandText)
      {
            InitCmdObj(commandText);
            da.SelectCommand = cmd;
            DataTable dt = new DataTable();
            dat.Fill(dt);
            return dt;
      }
}

dcass

ASKER

I'd have never guessed it would have made THIS much difference - it's huge.
Thanks

ASKER CERTIFIED SOLUTION

_TAD_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

daveslater

Hi
Nothing to do with this question but I you may have the answer to another C question in this area.
I tried to look at the C manual but its all curly brackets and semi colons :-)

TAD would it be best to re-direct these type of qurstions to the C area?

Daveslater
page editor.

_TAD_

Dave,
That would probably be a good idea. When it comes right down to it, working on an AS/400 machine using C/C++/C#/Java/etc. is like writing code to process data on a database or another server.

Just find out which language they are using the four languages mentioned above look very similar, but have enough differences in sytax to cause headaches.

:-)