|
Before you start doing anything, remember that future versions of SQL will offer text-oriented statement i SQL. Generally speaking, buying Hardware is (and wil continue to be) a lot cheaper than producing software. A 20,000 item file is not a particularly big one. Personaly, i strongly prefer using SQL (or OPNQRYF ) rather than writing programs or adding index files. You may try anyway to restrict general search condition, allowing your user to select description of the item AND item category or some other element that correspond to a key. Sincerely Domenico Finucci Fiditalia , Milano, 02- 4301-2494 -----Messaggio originale----- Da: rob@dekko.com [mailto:rob@dekko.com] Inviato: venerd́ 30 marzo 2001 16.21 A: MIDRANGE-L@midrange.com Oggetto: Re: database search question... We have an 820-23A2 for our development machine. (It's getting a faster processor tonight.) We have a BPCS file with 29,450 records in it. I ran the following on it: select * from cliperf/iim where idesc like '%120V%' Ran in subsecond response time. Then I ran: select count(*) from cliperf/iim where idesc like '%120V%' This took about 7 seconds to return the value 1,617. Standard BPCS. No EVI's or whatnot. SQL was ran using the STRSQL interactive processor. Display System Status GDISYS 03/30/01 09:03:03 % CPU used . . . . . . . : 14.4 Auxiliary storage: Elapsed time . . . . . . : 00:00:01 System ASP . . . . . . : 161.6 G Jobs in system . . . . . : 2690 % system ASP used . . : 84.0214 % perm addresses . . . . : .050 Total . . . . . . . . : 161.6 G % temp addresses . . . . : .509 Current unprotect used : 6057 M Maximum unprotect . . : 6370 M System Pool Reserved Max -----DB----- ---Non-DB--- Pool Size (M) Size (M) Active Fault Pages Fault Pages 1 500.00 115.30 +++++ .0 .0 .0 .0 2 521.00 2.71 5000 .0 .0 .0 .0 3 15.00 .00 5 .0 .0 .0 .0 4 200.00 .00 250 .0 .0 1.8 1.8 5 300.00 .00 20 .0 .0 .0 .0 ***** Then I ran this on our main production machine. A 730-2C6E. Against a file with 28,935 records. Again, subsecond response time. The count(*) took about a second and returned a value of 99. Display System Status GDIHQ 03/30/01 09:09:19 % CPU used . . . . . . . : 6.8 Auxiliary storage: % DB capability . . . . : 1.1 System ASP . . . . . . : 203.7 G Elapsed time . . . . . . : 00:00:01 % system ASP used . . : 65.2517 Jobs in system . . . . . : 12529 Total . . . . . . . . : 203.7 G % perm addresses . . . . : .054 Current unprotect used : 7094 M % temp addresses . . . . : .451 Maximum unprotect . . : 9123 M System Pool Reserved Max -----DB----- ---Non-DB--- Pool Size (M) Size (M) Active Fault Pages Fault Pages 1 2000.00 340.11 +++++ .0 .0 .0 .0 2 2566.57 .16 17000 .0 .0 .0 .0 3 275.00 .00 75 .0 .0 .0 .0 4 1750.00 .00 3500 .0 4.4 12.5 19.6 5 1500.00 .00 100 126.2 171.8 9.8 9.8 6 100.42 .00 10 .0 .0 .0 .0 When someone tries to tell you it's normal for a 400 to run close to 100%, they are trying to justify being cheap. By the way this CPU supports about 30 copies of BPCS, payroll for about 3,000, and everyone's accounting. Currently there are 26 jobs running in QBATCH and about 408 jobs in QINTER. About 0 C/S jobs. We plan on upgrading this machine to an 8xx model by the end of the year. Rob Berendt ================== Remember the Cole! "Hatzenbeler, Tim" <thatzenbeler@clini To: "'MIDRANGE-L@midrange.com'" <MIDRANGE-L@midrange.com> tech.net> cc: Sent by: Subject: database search question... owner-midrange-l@mi drange.com 03/29/01 04:19 PM Please respond to MIDRANGE-L How does ebay do it? And can the as/400 do it? Or do I just need a super fast computer with a ton of memory... The question is this.... I have a product file... About 20,000 items... And I have end users who want to be able to preform partial word searches on the description field... Seems simple enough, but the problem is speed... If I do an rpg program, that reads every line of the product file and does a %scan function... It works, but its not very fast... I could to an SQL with the like '%fuzzy%' but even thats slow... and if they gave it a real generic lookup value, it will take forever... So my question is this, does any one have a suggestion? My 1st thought is something like this. Program a word index creator, that would run nighlty. Product catalog item#,description 1234, big red car 1235, little green car and then a lookup data base, would look like this search word, part# --------------------------- big,1234 car,1234 car,1235 green,1235 little,1235 red,1234 and then if someone wanted [little car], it would do a search on [little] and [car] and if both are positive to the same key(item#), then pull that record... I'm hoping someone could think of a better way... I'm hoping there is an os/400 database command to solve this problem, so that's why i'm posting it in this list. If not, the index creator doesn't seem too hard. Thanks, tim +--- | This is the Midrange System Mailing List! | To submit a new message, send your mail to MIDRANGE-L@midrange.com. | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com. | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com. | Questions should be directed to the list owner/operator: david@midrange.com +--- +--- | This is the Midrange System Mailing List! | To submit a new message, send your mail to MIDRANGE-L@midrange.com. | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com. | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com. | Questions should be directed to the list owner/operator: david@midrange.com +---
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com 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.
midrange.com
