You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@struts.apache.org by Jerry Jalenak <Je...@LABONE.com> on 2003/11/06 15:36:21 UTC
[OT - Design] Needing Fast Access to 300,000 Records
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.)
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
jerry.jalenak@labone.com
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: securityincidentreporting@labone.com
---------------------------------------------------------------------
To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org
Re: [OT - Design] Needing Fast Access to 300,000 Records
Posted by Vic Cekvenich <ce...@baseBeans.com>.
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
>
> jerry.jalenak@labone.com
>
>
> 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: securityincidentreporting@labone.com
--
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: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org
RE: [OT - Design] Needing Fast Access to 300,000 Records
Posted by Greg Hess <gh...@wrappedapps.com>.
To avoid having 300,000 records sitting in memory I generally have my
queries return record id's. My DAO returns an int[] or ResultObjects
collection containing record id's that I store in some scope(session) as
it takes little mem. The records are not generally all displayed at once
so I only fetch the records that will be displayed on a single page and
use a pager tag lib to provide paging functionality. The actual
recordObjects currently being viewed are put into request scope to
reduce mem consumption.
Not sure of your iBatis plug-in but maybe you could still page the
records and only pass one page of records at a time and use the strategy
above.
HTH,
Greg
> -----Original Message-----
> From: Jerry Jalenak [mailto:Jerry.Jalenak@LABONE.com]
> Sent: Thursday, November 06, 2003 9:36 AM
> To: 'struts-user@jakarta.apache.org'
> Subject: [OT - Design] Needing Fast Access to 300,000 Records
>
> 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.)
>
> 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
>
> jerry.jalenak@labone.com
>
>
> 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:
> securityincidentreporting@labone.com
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: struts-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org
Re: [OT - Design] Needing Fast Access to 300,000 Records
Posted by David Graham <gr...@yahoo.com>.
--- Jerry Jalenak <Je...@LABONE.com> 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.
No you don't (unless you're using MS Access :-). 300k records is a very
small database.
> 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.)
This is a premature optimization. Write the simplest solution possible
(ie. read from database each time) and then optimize if needed. A
properly configured database will perform table joins in less than a
second. In my experience, joining on CHAR fields is slower than joining
on INTEGER fields but if they're properly indexed the performance will be
fine.
David
>
> 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
>
> jerry.jalenak@labone.com
>
>
> 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:
> securityincidentreporting@labone.com
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: struts-user-help@jakarta.apache.org
>
__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
---------------------------------------------------------------------
To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org