At a design stage, you take your outputs (mock up html pages or reports. I am sure you or anyone has a screen with 30,000 records on it, or even a 1,000) and do some data model.
Based on your analysis you should know the SQL.
If this is hard for you, then attach a html mock up of you screen one and screen 2. I will write the SQL for you based on those requirements.
So far I think it's a simple 2 way join (people write 12 way joins, or 10 page stored procs, quite possible you are concrend about a problem that does not exist. I tell you it's sub second! Also you just went from 300,000 to 30,000... maybe it's 3,000 or 3,000,000. It does not mater the records, it's a 2 way equi join. )
Recomendation to the list: Please read Celcko's "SQL for Smarties", my all time fave for designers.
.V
Jerry Jalenak wrote:
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]
-- 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]
