Import error "Out of internal resources" with wide table

Toad Data Point
1

I am attempting to transfer a table's data from Oracle to SQL Server using an Import Template. The table is very wide with close to 500 fields but it has less than 1000 rows. I get the following error if I go over about 200 fields. Is there a column number limit?

"The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
Do you want to cancel the import? Click no to ignore the current error(s) and any additional errors and continue importing. Toad will not prompt you again if another error occurs."

2

Wow, never seen that particular message before, but yeah, you're likely running into memory ceilings here. I'm not aware of any hard limits on the number of columns within the TDP app itself. So your workstation memory/disk are probably the constraint here, and yes, there is a known issue on Data Compare (and I suspect on imports also) as shown below in latest release notes.

Having said that, a few questions...

  • What's the config of your workstation? (RAM, disk, bitness)?
    Amount of RAM and the bitness of your machine are perhaps the most constraining factors here. 64-bit workstations are better for TDP to work with large volumes of data, and the more RAM, the greater capacity for TDP to handle larger data sets.
  • Using latest version of TDP?
  • What's the average length of these columns... OR, are some of the column lengths really long? LOBs, CLOBS?

... and maybe some work-arounds? ...

  • Possible to import records a hundred at a time?
  • Possible to import all records, but maybe a subset of the columns on each import?
    e.g. first 100 columns on import 1, next 100 columns on import 2, etc.
    .

image
image768×181 8.57 KB

3

Thanks so much for your reply. Since I was adding columns gradually, I was in the "column" mindset, I guess. Moving a limited number of rows at time works for me.