Large imports from OLE DB

ODBC Connectivity, ELFs , Windows API etc.

Moderators: Phil Winkler, Graham Smith, Pete Tabord

Large imports from OLE DB

Postby lumberjackshaw » Wed May 25, 2011 8:19 pm

Good Day,

I am importing a table over a OLE DB driver from a MS SQL Server that has over 44k rows. There are 4 fields.

I am using a "for" statement and an "enter a record in" statement to deposit the data into a table in Ff.
In order to get all of the rows to populate I have to not only precede the "enter a record in" statement with a "list records" statement containing each field to cache the data but once the report loads I must go to the last record or I will only get a little over 1000 records. I will also take the chance to know that I must display the list records to a windows as printing to a pdf or printer will also only generate a little over 1000 records.

Once the report has loaded to the screen and I click the last record option I have a wait time of about 1.5 hours before it loads all of the data required to display the last page of the report. It does, however, upon exiting the report window dump the data into the table correctly and quite quickly.

What I would like to know ...
Is there a better way to do this perhaps?
Is there a setting in the workstation preferences that will help speed this up?
Can anyone tell me if this the bottle neck is in Ff (by my methods or settings) or the OLE DB driver (something I am stuck with) ?
--Josh

Associate with men of good quality if you esteem your own reputation; for it is better to be alone than in bad company.
-George Washington
lumberjackshaw
 
Posts: 207
Joined: Mon Jan 17, 2011 8:07 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Large imports from OLE DB

Postby Phil Winkler » Wed May 25, 2011 9:13 pm

Josh,

I suspect it is the Oledb driver. It would likely be faster to do an export from SQL and then import into Ffenics.

Can you do that?
Phil Winkler
PLM Consulting, Inc.
pwinkler@plmconsulting.com
Phil Winkler
 
Posts: 889
Joined: Fri Sep 07, 2007 12:45 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Large imports from OLE DB

Postby lumberjackshaw » Wed May 25, 2011 9:34 pm

Phil,

It's possible...just not my preferred option. I have several other tables that I am likely to run into this same issue on and was hoping for a little a less manual intervention from the SQL side once its setup. I was hoping that it was something I had done/set wrong. :-)

It seems to be the "list records" step that it has trouble with but I can't seem to get the complete data table without it, even on small tables.

I have read something about a "ENABLEFASTLOAD" setting on MS SQL Server that might be my issue as it seems to be specific to OLE DB drivers. I'm just haven't had a chance do any homework on that yet. I will probably test a cvs file if I come up at a loss there. I think I can use and SQL Exec to export the data from the SQL to the CVS file without too much work.

Thanks
--Josh

Associate with men of good quality if you esteem your own reputation; for it is better to be alone than in bad company.
-George Washington
lumberjackshaw
 
Posts: 207
Joined: Mon Jan 17, 2011 8:07 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Large imports from OLE DB

Postby Graham Smith » Thu May 26, 2011 11:39 am

In my experience, moving large amounts of data via ODBC and OLEDB are an exercise in frustration. You can dump the data from SQL and import it into {database of choice} far faster and with a lot less issues.

As far as ENABLEFASTLOAD, if I recall, that's used with the engine receiving the data is SQL. It may be similar to a SQL Append.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: Large imports from OLE DB

Postby lumberjackshaw » Thu May 26, 2011 2:59 pm

Good Day,

Graham is correct with the enablefastload option.

I have found a good solution, however, that has trumped a great deal of my efforts with imports so far.

I have post that solution here http://www.ffenics.com/forum/viewtopic.php?f=31&t=1307&start=0&st=0&sk=t&sd=a since I didn't think it could be encapsulated under this title.
--Josh

Associate with men of good quality if you esteem your own reputation; for it is better to be alone than in bad company.
-George Washington
lumberjackshaw
 
Posts: 207
Joined: Mon Jan 17, 2011 8:07 pm
Has thanked: 0 time
Been thanked: 0 time
 
 

Return to Advanced

Who is online

Users browsing this forum: No registered users and 1 guest