Vic - This is still in the design stage, so I don't have a SQL statement to provide. I'm looking for the best approach to handling what is essentially a table lookup where I need two keys (BILLING_ACCOUNT and ACCOUNT_CODE)....
The scenario is this: When one of my clients logs on to the webapp, the app determines the BILLING_ACCOUNTs the client has assigned, then explodes the BILLING_ACCOUNTs into the composite ACCOUNT_CODES. As an example, I've got one client with 54 BILLING_ACCOUNTs; these explode to over 30,000 ACCOUNT_CODES. I am trying to eliminate 30,000 db calls to get the account information - that's why I'm looking to pre-load the table(s) in a plug-in and access everything in memory. Make sense? Jerry Jalenak Development Manager, Web Publishing LabOne, Inc. 10101 Renner Blvd. Lenexa, KS 66219 (913) 577-1496 [EMAIL PROTECTED] > -----Original Message----- > From: Vic Cekvenich [mailto:[EMAIL PROTECTED] > Sent: Thursday, November 06, 2003 8:46 AM > To: [EMAIL PROTECTED] > Subject: Re: [OT - Design] Needing Fast Access to 300,000 Records > > > > Jerry Jalenak wrote: > > Hi All - > > > > I've been trying to figure out a good way of handling > something, and just > > can't quite seem to get a grip on the best approach. > Here's what I've got: > > > > Two database tables - Table 1 has a BILLING_CODE and an > ACCOUNT_CODE. Table > > 2 has the ACCOUNT_CODE and other account information (name, > address, etc.) > > The tables are linked by ACCOUNT_CODE. > > > > Table 1: > > Table 2: > > BILLING_CODE ACCOUNT_CODE > > ACCOUNT_CODE NAME ADDRESS ... > > 1234 ABC1 > > ABC1 blah blah > > 1234 ABC2 > > ABC2 blah blah > > 1234 ABC3 > > ABC3 blah blah > > 5678 DEF1 > > DEF1 blah blah > > 5678 DEF2 > > DEF2 blah blah > > > > I need to be able to rapidly access the information in > table 2 either > > through the BILLING_CODE, or directly through the > ACCOUNT_CODE. I can > > create a POJO containing the BILLING_CODE and a List object > to hold a second > > POJO for the table 2 info. Or I can use a Map. Either way > doesn't give me > > a good method of accessing the table 2 information based on > ACCOUNT_CODE. > > > > Two other bits of info - the combined number of records > exceeds 300,000, so > > I have a scaling issue. Second, I'd like to load > everything in a plug-in > > using iBatis dbLayer and store it in application scope (to > eliminate db > > calls as the webapp is used.) > > That is ridiculous!! > Why stop there, why not just write your own SQL engine in Java? > Say 300,000 records times 500 bytes for each record in > memory... I can't > begin to ... > > You can easily get sub second response scelable, that is a > very, very, > small database for a SQL engine. Post the the SQL command > that is giving > you performance problem. What DB are you using? > iBatis is nicely going to cache duplicate requests and flush, that's > all, its a DAO. > .V > > > > > Does anyone have any experience in handling something like > this? How did > > you do it? > > > > Thanks... > > > > Jerry Jalenak > > Development Manager, Web Publishing > > > > > > LabOne, Inc. > > 10101 Renner Blvd. > > Lenexa, KS 66219 > > (913) 577-1496 > > > > [EMAIL PROTECTED] > > > > > > This transmission (and any information attached to it) may > be confidential and > > is intended solely for the use of the individual or entity > to which it is > > addressed. If you are not the intended recipient or the > person responsible for > > delivering the transmission to the intended recipient, be > advised that you > > have received this transmission in error and that any use, > dissemination, > > forwarding, printing, or copying of this information is > strictly prohibited. > > If you have received this transmission in error, please > immediately notify > > LabOne at the following email address: > [EMAIL PROTECTED] > > -- > Victor Cekvenich, > Struts Instructor > (215) 321-9146 > > Advanced Struts Training > <http://basebeans.com/do/cmsPg?content=TRAINING> Server Side Java > training with Rich UI, mentoring, designs, samples and > project recovery > in North East. > Simple best practice basic Portal, a Struts CMS, Membership, Forums, > Shopping and Credit processing, <http://basicportal.com> > software, ready > to develop/customize; requires a db to run. > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > This transmission (and any information attached to it) may be confidential and is intended solely for the use of the individual or entity to which it is addressed. If you are not the intended recipient or the person responsible for delivering the transmission to the intended recipient, be advised that you have received this transmission in error and that any use, dissemination, forwarding, printing, or copying of this information is strictly prohibited. If you have received this transmission in error, please immediately notify LabOne at the following email address: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
