To add to what others have already pointed out: I use Gateway with pre-set formats only for small data sets, as I am going to guess it does a lot of checks and it is slow.
To import data from an external database, this is what I do: Connect to the external database using a DSNless connection. Attach the table you want to import; use aliases as needed to avoid conflict with existing columns names/definitions and/or links. Determine which records you need to import. PROJECT the desired records from the attached table to a temporary table. Detach the table and disconnect from external database. Now you have the desired data in native R:Base format and you can Insertt, Append or otherwise manipulate the data any way you see fit into your permanent table. Javier, Javier Valencia, PE O: 913-829-0888 H: 913-397-9605 C: 913-915-3137 From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Tuesday, August 21, 2012 11:08 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Slow Import by Gateway File Gateway will be slow for that size data. Create a system dsn for the access db. In RBase under utilities click Connect Sql Data Source then attach SQL data tables. Attach the table as temporary, rename the attached table, then do what you need to do. Avoids Gateway entirely and much faster. When loading a new table from external data, make sure the new table have no indexes assigned until AFTER the data is loaded. Indexes will slow down data loading significantly. Hope this helps. Bill Eyring -------- Original Message -------- Subject: [RBASE-L] - Re: Slow Import by Gateway From: Gary Wendike <[email protected]> Date: Tue, August 21, 2012 6:44 am To: [email protected] (RBASE-L Mailing List) I have found it slow to use Gateway on such large files. Here are some things I do, they may not be all right but they seem to work... I. Make sure you have no indexes on the table being loaded. It may seem strange but is quicker to drop the indexes, load the data and rebuild the indexes than load with indexes on large tables. 2. If you are going to load a file from MS Access, I use a DSN-less connection. In general, I... 1. Connect to the Access database 2. Attach the table 3. Insert the data from MS table into Rbase table by using the INSERT command. 3. Make sure no one else in the database. I use this method daily, as this is how I receive my data from our customer. Going thru Gateway with its overhead will not be the most efficient way to go. You could use the LOAD as ASCII. This is the method I use for doing a backup. I have tables with 14million records and total backup takes about 20-30 minutes. I do not have any tables with 60+ fields. I primarily use the DSN-less method and have had no problems what-so-ever. My guess with 1.3 million rows it may take a sometime but not 30 hours. Gary _____ From: Richard <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Mon, August 20, 2012 10:33:30 PM Subject: [RBASE-L] - Slow Import by Gateway Recent efforts to load some large tables from MS Access via File Gateway have required extremely long times. One table of about 1,300,000 records with 60+ fields took 30 hours to complete. Is this just to be expected or might there be underlying inefficiency or setting that can be overcome the long load time? I also tried loading the table from a text file with about the same (lack of) speed. Loading the same text file into Access takes minutes. Whazzup with File Gateway?
