Why don't you just drop the order by, and sort the data once you have it in
your localSQL Server database, that should be much quicker (in theory
anyway). Give it a try and see what happens!

Not sure if this is a web related question though, might be better on the
midrange list.


-----Original Message-----
From: Varnadoe, Chris [mailto:cvarnadoe@xxxxxxxxxx] 
Sent: 05 June 2003 13:51
To: web400@xxxxxxxxxxxx
Subject: [WEB400] DB2 & Visual Basic

Hi! I'm new to this list and fairly new to the iSeries. My company is a
small, specialized insurance company and we use a policy management system
(POINT) from Computer Sciences Corporation (CSC, formerly PMSC). We are an
outsourcing customer and access their iSeries system via a T1 line. Their
operating system is V5R1 and here we are using Client Access Express V5R1M0
with the latest service pack installed.

Here is my problem: I have written a Visual Basic 6.0 program that goes
through ODBC to pull certain data out of the database and then store that
data into a local SQL Server database. The query on one of the tables is
excruciatingly slow. In working with Access and SQL Server my first thought
was to add an index, but evidently the DB2 database uses "logical" files as
indexes. In talking with the systems people at CSC, they felt like it was
the "order by" clause on my SQL query that was causing the delay. This
logical file has 13 fields as the key and I only have 6 of them in my "order
by" clause. I am used to the indexes being formed from the fields being used
in the "where" clause. Below I have listed the SQL query that is taking so
long (the questions marks just indicate where VB will put data). I am
thinking about asking them to create a new logical file with the fields from
my "where" clause, but since that costs me money I thought I would see if
anyone here could give ! me some insight first.

select * from ASBYCPL1 
where BYASTX = ? and BYADNB = ? and BYAGTX = ? and BYBRNB = ? and BYEGNB = ?



Chris Varnadoe
Information Systems Manager
Southern Mutual Church Insurance Co. 

This is the Web Enabling the AS400 / iSeries (WEB400) mailing list To post a
message email: WEB400@xxxxxxxxxxxx To subscribe, unsubscribe, or change list
or email: WEB400-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives

For the latest on Bertram Books products and services and for up-to-the-minute 
book news visit

This e-mail and any attachments may contain information that is confidential 
and privileged and is solely for the use of the intended recipient. If you are 
not the intended recipient please notify the sender and delete this e-mail and 
any attachments immediately

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.