RE: DB2 & Visual Basic -- WEB400

Chris,

As other have said, you might have more luck on Midrange-l, but since it's
here:

Without knowing the fields and indexes available it's a little hard, but the
where clause is probably going to be a killer. I'll bet there is not index
(logical) keyed on the 5 fields (or even a subset) in your where clause. Can
you get a list of the indexes that are available and maybe tweak your query?
Remember, if the index is keyed by A, B, C, D, E then "where a=? And b=?" is
ok, "where A=? And b=? And c=?" is also good, but "where b=? And c=?" is bad
because the index is useless.

We worked with one client where there was an index keyed by Company,
Division, Customer, Order and customer number is unique across company and
division. The query was "where customer=? And order=?" and ran forever. By
changing it to "where company=? And divison=? And Customer=? And order=?"
the run time droped to sub-second because there was an index to use. Lesson
learned, sometimes adding things to your where clause can increase
performance.

-Walden

------------
Walden H Leverich III
President
Tech Software
(516) 627-3800 x11
(208) 692-3308 eFax
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com 

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)
 

-----Original Message-----
From: Varnadoe, Chris [mailto:cvarnadoe@xxxxxxxxxx] 
Sent: Thursday, June 05, 2003 9:51 AM
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 = ?

order by BYAACD, BYABCD, BYARTX, BYASTX, BYADNB, BYAOTX

Thanks,

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 options,
visit: http://lists.midrange.com/mailman/listinfo/web400
or email: WEB400-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/web400.