Re: Are Batch Sequences Possible ?
On Oct 30, 2007, at 6:13 PM, Owen McKerrow wrote: Hi Chuck, I'm looking over your FBIntegerPrimaryKeyGenerator and IntegerPrimaryKeyGenerator classes from GVCFrameworks and was just wondering if my understanding of it is correct. Basiaclly you will have to make a new instance of the FBIntegerPrimaryKeyGenerator at some point and then ask it to create a bunch of new PK's for you for a specific entity, lets say Person. These are then cached. So then when I make a new instance Person, it will be given one of these caches PK's stright away ( i.e. no extra code required ) or is there another step to request one of these PK's from the cache ? If it happens automatically will it be before the editingContext saves it changes ? Yes, that sounds correct. We have only used this when processing large imports that insert thousands of rows. The PK assignment from the cache happens during saveChanges(). IIRC, it if runs out it will cache another batch. I could see this potentially being "dangerous" ( not deadly or anything ) if you don't end up using all your primary keys ( at least with Orcale sequences ) in that you would ask for 100 new PK's, the sequence is incremented 100 times, you only use 50 of them and you would have a missing 50 values from your PK coloum. Anyways just a random though. Yes, it will create gaps in the PK sequence. That is the price to be paid for caching. I was discussing this with Mike earlier and it appears that our code may have a nasty race condition in it. SELECT UNIQUE might not be working as we had assumed. I have not gotten around to testing it yet. The solution in the plugin that Mike just posted about is (a) safe from this race condition, (b) less wasteful of key values, and (c) overall easier to use. We were too short sighted to consider doing it at the plugin level when we wrote that PK key caching code. Chuck On 30/10/2007, at 3:52 PM, Chuck Hill wrote: Short answer: yes. Mike recently added something to the FrontBase plugin(?) in Wonder to do this. I imagine that the same thing could be done for Oracle. There are primary key generators in GVCEOFExtensions that also do this (get a batch from the DB and allocate them locally). The method we used was in EODatabaseContext's delegate interface, databaseContextNewPrimaryKey(EODatabaseContext dbCtxt, Object object, EOEntity entity) I'm not sure what Mike did for his. Chuck On Oct 29, 2007, at 8:49 PM, Owen McKerrow wrote: Hi All, Im currently developing an app against a Oracle databas, which uses sequence for generating its Primary Keys. Is there a way to get WO to request a batch of sequences instead of doing it one at a time ( which is what it appears to be doing when you output the SQL ). For example I am doing 1 save to the database that is inserting 142 rows into 1 table, 142 into a second and 711 into a third. Each of the 142 row table inserts takes 2 seconds to return all of the sequence while the 711 inserts take's 8 seconds to get the sequences. Thats 12 seconds used before it even starts the INSERT statements, which take another 2, 2 and 8 respectively. Meaning the total save time is 24 seconds ( with SQL debugging turned on, its a little quicker with it turned off ). Please someone tell me Im doing something dumb, or that there is a better way. Heres a cut down version of the SQL output.. 14:22:44,154 DEBUG [WorkerThread2] (RQFBOWSelection:286 setUpPublicationsList) - Save Start. 14:22:44,415 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - === Begin Internal Transaction 14:22:44,416 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "SELECT t0.pub, t0.ABSTRACT FROM PUB_ABSTRACT t0 WHERE t0.pub = ?" withBindings: 1:8657(pubID)> Repeated 141 for 2 secconds 14:22:46,521 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - === Begin Internal Transaction 14:22:46,522 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "SELECT RQF_GRP_PSN_PUB_SEQ.NEXTVAL FROM DUAL"> Repated 142 for 2 seconds 14:22:48,045 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - === Begin Internal Transaction 14:22:48,046 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "SELECT AUDIT_LOG_SEQ.NEXTVAL FROM DUAL"> Repeated 711 for 8 seconds 14:22:56,928 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - === Begin Internal Transaction 14:22:56,929 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "SELECT RQF_PUB_SEQ.NEXTVAL FROM DUAL"> Repeated 141 for . 2 seconds 14:22:58,782 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - === Begin Internal Transaction 14:22:58,783 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "INSERT INTO RQF_PUB(NOTES,
Re: Are Batch Sequences Possible ?
Hi Chuck, I'm looking over your FBIntegerPrimaryKeyGenerator and IntegerPrimaryKeyGenerator classes from GVCFrameworks and was just wondering if my understanding of it is correct. Basiaclly you will have to make a new instance of the FBIntegerPrimaryKeyGenerator at some point and then ask it to create a bunch of new PK's for you for a specific entity, lets say Person. These are then cached. So then when I make a new instance Person, it will be given one of these caches PK's stright away ( i.e. no extra code required ) or is there another step to request one of these PK's from the cache ? If it happens automatically will it be before the editingContext saves it changes ? I could see this potentially being "dangerous" ( not deadly or anything ) if you don't end up using all your primary keys ( at least with Orcale sequences ) in that you would ask for 100 new PK's, the sequence is incremented 100 times, you only use 50 of them and you would have a missing 50 values from your PK coloum. Anyways just a random though. Owen McKerrow WebMaster, emlab Ph : +61 02 4221 5517 http://emlab.uow.edu.au - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 'The test of a first-rate intelligence is the ability to hold two opposed ideas in the mind at the same time and still be able to function.' -F.Scott Fitzgerald, On 30/10/2007, at 3:52 PM, Chuck Hill wrote: Short answer: yes. Mike recently added something to the FrontBase plugin(?) in Wonder to do this. I imagine that the same thing could be done for Oracle. There are primary key generators in GVCEOFExtensions that also do this (get a batch from the DB and allocate them locally). The method we used was in EODatabaseContext's delegate interface, databaseContextNewPrimaryKey(EODatabaseContext dbCtxt, Object object, EOEntity entity) I'm not sure what Mike did for his. Chuck On Oct 29, 2007, at 8:49 PM, Owen McKerrow wrote: Hi All, Im currently developing an app against a Oracle databas, which uses sequence for generating its Primary Keys. Is there a way to get WO to request a batch of sequences instead of doing it one at a time ( which is what it appears to be doing when you output the SQL ). For example I am doing 1 save to the database that is inserting 142 rows into 1 table, 142 into a second and 711 into a third. Each of the 142 row table inserts takes 2 seconds to return all of the sequence while the 711 inserts take's 8 seconds to get the sequences. Thats 12 seconds used before it even starts the INSERT statements, which take another 2, 2 and 8 respectively. Meaning the total save time is 24 seconds ( with SQL debugging turned on, its a little quicker with it turned off ). Please someone tell me Im doing something dumb, or that there is a better way. Heres a cut down version of the SQL output.. 14:22:44,154 DEBUG [WorkerThread2] (RQFBOWSelection:286 setUpPublicationsList) - Save Start. 14:22:44,415 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - === Begin Internal Transaction 14:22:44,416 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "SELECT t0.pub, t0.ABSTRACT FROM PUB_ABSTRACT t0 WHERE t0.pub = ?" withBindings: 1:8657(pubID)> Repeated 141 for 2 secconds 14:22:46,521 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - === Begin Internal Transaction 14:22:46,522 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "SELECT RQF_GRP_PSN_PUB_SEQ.NEXTVAL FROM DUAL"> Repated 142 for 2 seconds 14:22:48,045 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - === Begin Internal Transaction 14:22:48,046 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "SELECT AUDIT_LOG_SEQ.NEXTVAL FROM DUAL"> Repeated 711 for 8 seconds 14:22:56,928 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - === Begin Internal Transaction 14:22:56,929 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "SELECT RQF_PUB_SEQ.NEXTVAL FROM DUAL"> Repeated 141 for . 2 seconds 14:22:58,782 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - === Begin Internal Transaction 14:22:58,783 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "INSERT INTO RQF_PUB(NOTES, OUTLET_ANGLICISED_TITLE, LANGUAGE_OF_TITLE, LOCKED, TYPE_OTHER_DESCRIPTION, METRICS_IDENTIFIER, OUTPUT_IDENTIFIER, RQF_PUB, ENGLISH_TITLE, PUB, IS_REPRINT, SENSITIVITY_EXPLAINATION, AVALIABLE_YEAR, ANGLICISED_TITLE, IS_COMMERCIAL_SENSITIVE, TYPE_CODE, IS_CULTURALSENSITIVE, OUTLET_RANKING_IDENTIFIER, AVAILABILITY_FORM, OUTLET_ENGLISH_TITLE, AVAIL_STATUS_CODE, NON_REPOSITORY_JUSTIFICATION, YEAR_VARIANCE_JUSTIFICATION, RQF_YEAR) VALUES (NULL, NULL, NULL, ?, NULL, NULL, NULL, ?, NULL, ?, ?, NULL, NULL, NULL, ?, NULL, ?,
Re: Are Batch Sequences Possible ?
I'm not sure what Mike did for his. OK, I committed a fix to the FrontBasePlugin that adds batch key generation ... Right now it just does batches of 10, but it can be tweaked pretty easily. I'm using the syntax of: values (select unique from YourTable, select unique from YourTable, select unique from YourTable, ...) which is how Geert recommended that this be addressed. I suspect I could do WAY more than 10 as FB seems to just not care how big of a query you throw at it, but oh well .. 10x faster isn't bad either. ms ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to [EMAIL PROTECTED]
Re: Are Batch Sequences Possible ?
Actually, I proposed an idea to FrontBase a while back, but totally forgot to revisit this once they open sourced the plugin. The method you want to look at is in the plugin: public NSArray newPrimaryKeys(int i, EOEntity eoentity, JDBCChannel jdbcchannel) { Notice it takes an int? That int is the number of keys it needs. If you extend the plugin, you can provide a more optimal implementation than just a for loop (which is what most of them do in here). ms On Oct 30, 2007, at 12:52 AM, Chuck Hill wrote: Short answer: yes. Mike recently added something to the FrontBase plugin(?) in Wonder to do this. I imagine that the same thing could be done for Oracle. There are primary key generators in GVCEOFExtensions that also do this (get a batch from the DB and allocate them locally). The method we used was in EODatabaseContext's delegate interface, databaseContextNewPrimaryKey(EODatabaseContext dbCtxt, Object object, EOEntity entity) I'm not sure what Mike did for his. Chuck On Oct 29, 2007, at 8:49 PM, Owen McKerrow wrote: Hi All, Im currently developing an app against a Oracle databas, which uses sequence for generating its Primary Keys. Is there a way to get WO to request a batch of sequences instead of doing it one at a time ( which is what it appears to be doing when you output the SQL ). For example I am doing 1 save to the database that is inserting 142 rows into 1 table, 142 into a second and 711 into a third. Each of the 142 row table inserts takes 2 seconds to return all of the sequence while the 711 inserts take's 8 seconds to get the sequences. Thats 12 seconds used before it even starts the INSERT statements, which take another 2, 2 and 8 respectively. Meaning the total save time is 24 seconds ( with SQL debugging turned on, its a little quicker with it turned off ). Please someone tell me Im doing something dumb, or that there is a better way. Heres a cut down version of the SQL output.. 14:22:44,154 DEBUG [WorkerThread2] (RQFBOWSelection:286 setUpPublicationsList) - Save Start. 14:22:44,415 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - === Begin Internal Transaction 14:22:44,416 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "SELECT t0.pub, t0.ABSTRACT FROM PUB_ABSTRACT t0 WHERE t0.pub = ?" withBindings: 1:8657(pubID)> Repeated 141 for 2 secconds 14:22:46,521 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - === Begin Internal Transaction 14:22:46,522 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "SELECT RQF_GRP_PSN_PUB_SEQ.NEXTVAL FROM DUAL"> Repated 142 for 2 seconds 14:22:48,045 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - === Begin Internal Transaction 14:22:48,046 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "SELECT AUDIT_LOG_SEQ.NEXTVAL FROM DUAL"> Repeated 711 for 8 seconds 14:22:56,928 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - === Begin Internal Transaction 14:22:56,929 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "SELECT RQF_PUB_SEQ.NEXTVAL FROM DUAL"> Repeated 141 for . 2 seconds 14:22:58,782 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - === Begin Internal Transaction 14:22:58,783 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "INSERT INTO RQF_PUB(NOTES, OUTLET_ANGLICISED_TITLE, LANGUAGE_OF_TITLE, LOCKED, TYPE_OTHER_DESCRIPTION, METRICS_IDENTIFIER, OUTPUT_IDENTIFIER, RQF_PUB, ENGLISH_TITLE, PUB, IS_REPRINT, SENSITIVITY_EXPLAINATION, AVALIABLE_YEAR, ANGLICISED_TITLE, IS_COMMERCIAL_SENSITIVE, TYPE_CODE, IS_CULTURALSENSITIVE, OUTLET_RANKING_IDENTIFIER, AVAILABILITY_FORM, OUTLET_ENGLISH_TITLE, AVAIL_STATUS_CODE, NON_REPOSITORY_JUSTIFICATION, YEAR_VARIANCE_JUSTIFICATION, RQF_YEAR) VALUES (NULL, NULL, NULL, ?, NULL, NULL, NULL, ?, NULL, ?, ?, NULL, NULL, NULL, ?, NULL, ?, NULL, NULL, NULL, ?, NULL, NULL, ?)" withBindings: 1:0(locked), 2:2266(rqfPUBID), 3:8430(pubID), 4:0(reprint), 5:0(commercialSensative), 6:0(culturalSensative), 7:"R"(availabilityStatusCode), 8:21(rqfYearID)> Repeatd 142 for 2 seconds 14:22:59,717 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "INSERT INTO RQF_GRP_PSN_PUB(PERSON, RQF_PUB, GRP, RQF_GRP_PSN_PUB, TYPE) VALUES (?, ?, ?, ?, ?)" withBindings: 1:12545(personID), 2:2342(rqfPubID), 3:373(groupID), 4:2542(rqfGrpPersonPubID), 5:1(type)> Repeatd 141 for 2 seconds 14:23:00,368 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "INSERT INTO AUDIT_LOG(CLASSES_NAME, ATTRIBUTES_NAME, person, old_value, RIS_SESSION_ID, message, new_value, audit_log, ip_address, when, OBJECTS_PK) VALUES (?, ?, ?, NULL, ?, ?, ?, ?, ?, ?, ?)" withBindings:
Re: Are Batch Sequences Possible ?
Short answer: yes. Mike recently added something to the FrontBase plugin(?) in Wonder to do this. I imagine that the same thing could be done for Oracle. There are primary key generators in GVCEOFExtensions that also do this (get a batch from the DB and allocate them locally). The method we used was in EODatabaseContext's delegate interface, databaseContextNewPrimaryKey(EODatabaseContext dbCtxt, Object object, EOEntity entity) I'm not sure what Mike did for his. Chuck On Oct 29, 2007, at 8:49 PM, Owen McKerrow wrote: Hi All, Im currently developing an app against a Oracle databas, which uses sequence for generating its Primary Keys. Is there a way to get WO to request a batch of sequences instead of doing it one at a time ( which is what it appears to be doing when you output the SQL ). For example I am doing 1 save to the database that is inserting 142 rows into 1 table, 142 into a second and 711 into a third. Each of the 142 row table inserts takes 2 seconds to return all of the sequence while the 711 inserts take's 8 seconds to get the sequences. Thats 12 seconds used before it even starts the INSERT statements, which take another 2, 2 and 8 respectively. Meaning the total save time is 24 seconds ( with SQL debugging turned on, its a little quicker with it turned off ). Please someone tell me Im doing something dumb, or that there is a better way. Heres a cut down version of the SQL output.. 14:22:44,154 DEBUG [WorkerThread2] (RQFBOWSelection:286 setUpPublicationsList) - Save Start. 14:22:44,415 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - === Begin Internal Transaction 14:22:44,416 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "SELECT t0.pub, t0.ABSTRACT FROM PUB_ABSTRACT t0 WHERE t0.pub = ?" withBindings: 1:8657(pubID)> Repeated 141 for 2 secconds 14:22:46,521 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - === Begin Internal Transaction 14:22:46,522 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "SELECT RQF_GRP_PSN_PUB_SEQ.NEXTVAL FROM DUAL"> Repated 142 for 2 seconds 14:22:48,045 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - === Begin Internal Transaction 14:22:48,046 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "SELECT AUDIT_LOG_SEQ.NEXTVAL FROM DUAL"> Repeated 711 for 8 seconds 14:22:56,928 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - === Begin Internal Transaction 14:22:56,929 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "SELECT RQF_PUB_SEQ.NEXTVAL FROM DUAL"> Repeated 141 for . 2 seconds 14:22:58,782 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - === Begin Internal Transaction 14:22:58,783 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "INSERT INTO RQF_PUB(NOTES, OUTLET_ANGLICISED_TITLE, LANGUAGE_OF_TITLE, LOCKED, TYPE_OTHER_DESCRIPTION, METRICS_IDENTIFIER, OUTPUT_IDENTIFIER, RQF_PUB, ENGLISH_TITLE, PUB, IS_REPRINT, SENSITIVITY_EXPLAINATION, AVALIABLE_YEAR, ANGLICISED_TITLE, IS_COMMERCIAL_SENSITIVE, TYPE_CODE, IS_CULTURALSENSITIVE, OUTLET_RANKING_IDENTIFIER, AVAILABILITY_FORM, OUTLET_ENGLISH_TITLE, AVAIL_STATUS_CODE, NON_REPOSITORY_JUSTIFICATION, YEAR_VARIANCE_JUSTIFICATION, RQF_YEAR) VALUES (NULL, NULL, NULL, ?, NULL, NULL, NULL, ?, NULL, ?, ?, NULL, NULL, NULL, ?, NULL, ?, NULL, NULL, NULL, ?, NULL, NULL, ?)" withBindings: 1:0(locked), 2:2266(rqfPUBID), 3:8430 (pubID), 4:0(reprint), 5:0(commercialSensative), 6:0 (culturalSensative), 7:"R"(availabilityStatusCode), 8:21(rqfYearID)> Repeatd 142 for 2 seconds 14:22:59,717 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "INSERT INTO RQF_GRP_PSN_PUB(PERSON, RQF_PUB, GRP, RQF_GRP_PSN_PUB, TYPE) VALUES (?, ?, ?, ?, ?)" withBindings: 1:12545(personID), 2:2342(rqfPubID), 3:373(groupID), 4:2542 (rqfGrpPersonPubID), 5:1(type)> Repeatd 141 for 2 seconds 14:23:00,368 DEBUG [WorkerThread2] (Log.NSLogDebug:1546 appendln) - evaluateExpression: $OracleExpression: "INSERT INTO AUDIT_LOG(CLASSES_NAME, ATTRIBUTES_NAME, person, old_value, RIS_SESSION_ID, message, new_value, audit_log, ip_address, when, OBJECTS_PK) VALUES (?, ?, ?, NULL, ?, ?, ?, ?, ?, ?, ?)" withBindings: 1:"Publication"(classesName), 2:"rqfDetails"(attributesName), 3:3462 (person_fk1), 4:"hSSnp5XU8DNKnd0MFSCRHM"(risSessionID), 5:"RELATIONSHIP_ADDED"(message), 6:"RQFPub:({values = {notes = ; typeCode = ; languageOfTitle = ; locked = 0; metricsIdentifier = $Null>; outputIdentifier = ; titleInEnglish = ; rqfYear = ">"; publication = "[Publication (java.lang.Long)10988]>"; reprint = 0; rqfGroupPersonBOWPubs = ("-18 -19 -49 65 -71 -42 -47 95>>"); sensitivityExplaination = ; avaliabilityYear = ; anglicise