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_CODEACCOUNT_CODE ACCOUNT_CODENAMEADDRESS ... 1234ABC1 ABC1blahblah 1234ABC2 ABC2blahblah 1234ABC3 ABC3blahblah 5678DEF1 DEF1blahblah 5678DEF2 DEF2blahblah 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]
Re: [OT - Design] Needing Fast Access to 300,000 Records
--- Jerry Jalenak [EMAIL PROTECTED] 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_CODEACCOUNT_CODE ACCOUNT_CODE NAMEADDRESS ... 1234ABC1 ABC1 blahblah 1234ABC2 ABC2 blahblah 1234ABC3 ABC3 blahblah 5678DEF1 DEF1 blahblah 5678DEF2 DEF2 blahblah 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 [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] __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [OT - Design] Needing Fast Access to 300,000 Records
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_CODEACCOUNT_CODE ACCOUNT_CODENAMEADDRESS ... 1234ABC1 ABC1blahblah 1234ABC2 ABC2blahblah 1234ABC3 ABC3blahblah 5678DEF1 DEF1blahblah 5678DEF2 DEF2blahblah 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
RE: [OT - Design] Needing Fast Access to 300,000 Records
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:[EMAIL PROTECTED] Sent: Thursday, November 06, 2003 9:36 AM To: '[EMAIL PROTECTED]' 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_CODEACCOUNT_CODE ACCOUNT_CODE NAMEADDRESS ... 1234ABC1 ABC1 blahblah 1234ABC2 ABC2 blahblah 1234ABC3 ABC3 blahblah 5678DEF1 DEF1 blahblah 5678DEF2 DEF2 blahblah 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 [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] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [OT - Design] Needing Fast Access to 300,000 Records
Hi, But I think over here you are missing one important point. Out of those 3 records, the client will want to browse over only a couple of (may be a couple of hundreds). So as and when he requests, geting a few of them(stanadard pageful records at a time)wont be slow.Just retrieving all the records because just in case the client may need them does not look very efficient approach to me..I am assuming your functionality is something similar... any ideas guys? -Original Message- From: Jerry Jalenak [mailto:[EMAIL PROTECTED] Sent: Thursday, November 06, 2003 3:56 PM To: 'Struts Users Mailing List' Subject: RE: [OT - Design] Needing Fast Access to 300,000 Records 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_CODEACCOUNT_CODE ACCOUNT_CODENAMEADDRESS ... 1234ABC1 ABC1blahblah 1234ABC2 ABC2blahblah 1234ABC3 ABC3blahblah 5678DEF1 DEF1blahblah 5678DEF2 DEF2blahblah 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
Re: [OT - Design] Needing Fast Access to 300,000 Records
This should all be done is SQL. 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_CODEACCOUNT_CODE ACCOUNT_CODENAMEADDRESS ... 1234ABC1 ABC1blahblah 1234ABC2 ABC2blahblah 1234ABC3 ABC3blahblah 5678DEF1 DEF1blahblah 5678DEF2 DEF2blahblah 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
Re: [OT - Design] Needing Fast Access to 300,000 Records
[EMAIL PROTECTED] wrote: retrieving all the records because just in case the client may need them does not look very efficient approach to me. One never designs for all the exceptions of what might be, that would be a bad design. One designs for the likely case only, at least good designers do. The old 80/20 rule. (Unlikely excetiopns you deal with by doing exception procesing) But you design for the rule, else you are fighting your design. .V - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [OT - Design] Needing Fast Access to 300,000 Records
All, Thanks for the comments. It does appear that I am attempting to solve an easy the most difficult way possible. I'm going to go away now and spend some time with my handy dandy SQL book Thanks again! 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 9:32 AM To: [EMAIL PROTECTED] Subject: Re: [OT - Design] Needing Fast Access to 300,000 Records [EMAIL PROTECTED] wrote: retrieving all the records because just in case the client may need them does not look very efficient approach to me. One never designs for all the exceptions of what might be, that would be a bad design. One designs for the likely case only, at least good designers do. The old 80/20 rule. (Unlikely excetiopns you deal with by doing exception procesing) But you design for the rule, else you are fighting your design. .V - 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]
