The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Importing from an Access DSN
Posted: 09 Oct 06 10:32 AM
|
fiogf49gjkf0d I've been working on rewriting a preexisting import tool that our BP wrote. It was written to take an external CSV file and import it in. It seemed to work well except there was problems with how it handled a long numeric field. Since it is a unique ID field that we are using in other places I suggested doing a database(such as Acess is) to database import rather than a CSV to database import.
Rewriting the code was a piece of cake. However i suspect there is some DNS tweeking I can do. The import "died" after 33 records. (The CPU still shows activity but the SLX client has gone to "Not Responding". There IS a counter that ticks off the number of records imported.) Any thoughts? Suggestions? The only thing I can think of is that it's something to do with the ODBC connection since the code worked fine before with a text file. |
|
|
|
Re: Importing from an Access DSN
Posted: 09 Oct 06 1:42 PM
|
fiogf49gjkf0d FYi, the import works it's just slow. I let it complete. I increased the buffer size to 32MB to see if that would help it go faster. I'm not sure if it did or not. I may just have to run this before leaving for the day. If I were to guess it took about 30-45 minutes to import 7500 records (update/insert both Accounts, Contacts (up to 10 contacts for each Account) plus addresses.). |
|
|
|
Re: Importing from an Access DSN
Posted: 09 Oct 06 3:22 PM
|
fiogf49gjkf0d Yeah, something is not right. At that speed you're barely over 2 records per second. I usually freak out if I get below 100 records per second (and expect to get around 400+ recs/sec in usual cases at a minimum).
One problem might be that this is executing in a script (I never write import code in Architect, I'd build it in .NET and launch that from wherever it is needed - but I usually go that route for about everything anyway).
Pre-create ID values, minimize trips to the database for lookups, etc. As far as SLX going into "not responding" you could call...
Application.BasicFunctions.ProcessWindowMessages
...every so many records (don't call it after each record or it will slow down the process too much). |
|
|
|
Re: Importing from an Access DSN
Posted: 09 Oct 06 3:48 PM
|
fiogf49gjkf0d A good base would be 1000 records. GroupCopy used that as it's default, a long with the new Integrity Checker in 7.
It may not be the most optimal code either. You may make multiple calls to Access tables to build one record in SLX, when you should probably build objects or something that'll keep database round-trips to a minimum. You can also run into slowness if you use ADO .Update for each record instead of .UpdateBatch after X records.
If you plan on using the tool in production on a regular basis, then you'd want to figure out why it's slow. Personally I'd just let it run overnight when necessary and tackle the bottleneck some other time when I can profile it and figure out exactly each step the tool takes. |
|
|
|
Re: Importing from an Access DSN
Posted: 10 Oct 06 12:55 PM
|
fiogf49gjkf0d thanks guys. This is something we'll run only once a quarter.
How do you all import data (regular updates) into SLX. There are remotes involved.
The data is in a flat DBF file. Contacts are in the same table as the account info.
Updates are necessary as this quarterly import is an industry database with current data on the accounts we may already have as well as new accounts that have joined the industry. |
|
|
|