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.
As an Amazon Associate we earn from qualifying purchases.
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.