>Our main client routinely sends us 60,000 invoices in a single day. At 15 seconds per invoice, processing would take hours if not days to complete.
It will take a little over 10 days...@ 15 seconds per invoice; 4 per minute, 240 per hour, 5760 per 24 hours. I have no idea how Gentran works but my guess is that your table has lots of data and you need to check your indexes!! Try reindexing if the indexes are fine. Hope this helps. Regards, Ajay www.edisphere.com ________________________________________ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Richard Sent: Thursday, January 24, 2008 10:28 PM To: [email protected] Subject: [EDI-L] ADO database error question Hi all, I've got another ADO related issue that I could use some tips/hints/suggestions. For the record, we're using Gentran:Windows, version 5.0, patch 9, MSSQL v2005 for the database. I have the need to store 6 months of invoice related data within Gentran database to identify possible duplicate invoices. We don't have the space to store 180 days of invoice data in active interchanges (we archive at 30 days), but we still need to do duplicate checks. So I can't use the default "duplicate document" check for the partner either as this requires that the interchanges remain active and within Gentran. To accomplish the duplicate checks, we came up with using the Gentran lookup_tb entries to store the invoice number. This table does not archive or purge with archive so the data would remain. In setting this up I discovered that Sterling has "streamlined" the "select" extended rule code so that it responds quickly during translation. However the "insert" and "update" extended rules use a different code path during translation that adds a lot of time to each translation of an incoming invoice. Sterling level 2 support confirmed this for me. The times we're talking about for a single "insert" extended rule is 15 or more seconds per invoice. Our main client routinely sends us 60,000 invoices in a single day. At 15 seconds per invoice, processing would take hours if not days to complete. Level 2 support suggested I use a user exit and an ADO object call to the database to make the insert. So I set up the following code to do this: At the $810 level, at the "on begin": obConnection = CreateObject ("ADODB.Connection"); strCnn = "Provider=MSDASQL;" + "DSN=GentranDB_TEST;"; obConnection.Open(strCnn); If obConnection.State != 1 then cerror(700,#ODBCerrMsg); Within the BIG02 (element #0076) there is: #INV_NBR_LOOKUP = "22_" + #0076; select Item, Text2 into #EPAY_INVOICE_NBR, #EPAY_DATE from DivisionLookup where Tablename = "EPAY_INV" and Item = #INV_NBR_LOOKUP; If exist(#EPAY_INVOICE_NBR) then begin cerror(440,#0076); auditlog(10,AL_PROC,0,#EPAY_INVOICE_NBR,#EPAY_DATE); end else begin result = obConnection.ExecuteSQL(INSERT INTO DivisionLookup (PartnerKEY,TableName,Item,Description,Text1,Text2,Text3,Text4) values ("SYSTEMUSR","EPAY_INV",#INV_NBR_LOOKUP,"","22",#0373,"","")); end And finally in the $810 level at the "on-end", there's this: obConnection.close(); deleteobject(obConnection); The result? Well, it's working ... sort of. It's flagging any duplicate invoices and logging the "cerror" just fine. If the invoice does not exist, it's adding the invoice data to the lookup_tb table just fine. But, it's still taking 15 seconds to run and I'm getting the following error: Other ActiveX error: Exception occurred. Method : ExecuteSQL Field Rule, field name = 0076 Sterling level 2 has 'washed their hands' of this because it's now a user exit and they don't support them, so I can't go to them. >From examining the CPU utilization during translation execution, it would appear that tx32.exe is actually loading the entire lookup_tb table into memory, doing the insert, then writing the entire table back out. But I can't confirm this.. just my observations during translation. Does anyone have: 1. Any idea what the "exception" error could be that I'm seeing during the insert command? Any way for me to trap and/or display more details for the exception? 2. Any idea why it's still taking 15 seconds per ST/SE if an insert is needed? Has anyone else confirmed that Gentran does in fact load the entire lookup table into memory every time an 'insert' or 'update' is executed either using extended rules or an ADO call? I know this has been rather lengthy, but I just wanted to give you all the info I could. My thanks to any and all suggestions how to resolve this. We would like to get this solved in the next week or two because we have another large client coming online soon and really need the duplicate invoice check in place by then. Thanks, again.. Richard Jones ... Please use the following Message Identifiers as your subject prefix: <SALES>, <JOBS>, <LIST>, <TECH>, <MISC>, <EVENT>, <OFF-TOPIC> Job postings are welcome, but for job postings or requests for work: <JOBS> IS REQUIRED in the subject line as a prefix. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/EDI-L/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/EDI-L/join (Yahoo! ID required) <*> To change settings via email: mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
