Greetings, John -

is the app running on the user's home PC/laptop? Citrix? Remote Desktop? Where does the file being imported reside during the import? Are users accessing the company network via a VPN? VPN's are typically slow, so if the data being imported is having to make its way to the DBMS over the VPN, or down to the laptop and then back to the DBMS, that's a potential bottleneck.

The data import is part of an application, I assume. Does it have to be? Since stored procedures are evil/taboo, could the import process be encapsulated in a small, stand-alone PB application that just does the import and gets scheduled and run on an in-house server on an automatic schedule?

I'd also look at using Truncate Table to clear the table instead of inline deletes, if that is workable for your scenario. MUCH faster. If you are absolutely sure there will be no primary key violations in the imported data, I'd maybe also look into temporarily disabling PK & FK indexes, constraints, triggers, etc., import/insert the data, then restore/enable everything. Rebuild the table indexes once at the end instead of 1000's of times.

Reduce unnecessary overhead in the DBMS, eliminate the VPN as much as possible.

Food for thought.

Regards, John

Thanks for your response. Much appreciated.

To answer your question, we are using a VPN connection to the network. The exe file resides on a server, but of course it is run on the user's client machine. The data file resides on a shared drive. I will need to run a test to see how long it takes just to load the datawindow from the file. I hadn't considered that copying the file to be local might speed things up.

I did consider trying to get an app running on a server to do the file load, so the path would be server-to-server of both the file and DW connection. However, I didn't want to attempt that now, working remotely. There will be hurdles with security groups, server DBAs, share drive owners, and other mean people. After all, the current solution works. I can try when we are back together. However, I do think users would delighted to have one less thing to do and have the file loaded automatically at early am.

I hadn't considered the DB overhead of the indexes, etc. So I could create a new duplicate table with no keys. DW Import into that table (after truncation, no need for rollback) and then once loaded, just do a table to table copy.

Thanks for the help.

Hi John

I don't think the overhead is related to DW verification. You can check this by enabling database tracing. I think your problem is related to the network response. If thousands of inserts has to be executed and, as we know, DW sends them one by one, then the delay will be really huge because for each insert, PB wait's for it's response (Success or error code) in order to send the next one.

Since you are not allowed to use Oracle stored procedures / packages, my advice would be to build anonymous PL/SQL blocks that will insert some hundreds of rows, using just one call.

An anonymous PL/SQL block can be like this:

Begin
  INSERT INTO TBL_OWNER.MY_TABLE(V1, V2, V2)
  VALUES('1', '2', '3');

  INSERT INTO TBL_OWNER.MY_TABLE(V1, V2, V2)
  VALUES('1', '2', '3');

  INSERT INTO TBL_OWNER.MY_TABLE(V1, V2, V2)
  VALUES('1', '2', '3');
End;

 

The obvious disadvantage is that you have to build this string manually. Also keep in mind that anonymous PL/SQL blocks have a size limit (about 64kb I think), so you have to do something like the following script:

string sPLSQL

for r = 1 to dw_1.rowcount()
if mod(r, 500) = 0 then
if len(sPLSQL) > 0 then
sPLSQL += "End";

execute immediate :sPLSQL;
end if;

sPLSQL = "Begin~r~n";
end if;

sPLSQL += "INSERT INTO TBL_OWNER.MY_TABLE(V1) VALUES('" + dw_1.getItemString(r, "COL1") + "')~r~n";
next

if mod(r -1, 500) > 0 then
sPLSQL += "End";

execute immediate :sPLSQL;
end if;

You will be surprised but speed improvement of this approach....! 

Hi John;

  I did a big project about a decade ago that was importing many CSV files from various Telco companies that supply telecommunications services to the Canadian government. Many of these CSV files were the same or larger size to yours. Importing the large datum like your encouraging was a long tedious process and a degrading process as more Telco companies were supplying services.

  The target of this system was to import all the Telco data ASAP and then reconcile this information about every phone call the Telco's billing information to what the Canadian records were tracking.

  To solve this intensive problem, I designed the systems around the Oracle's External Table Feature. The ETF feature allows you to directly attach a CSV file to an Oracle DBMS as a table definition. The PB applications were changed to:

• FTP the CSV files to the Oracle Server.
• Execute DDL commands to create & modify the Oracle ESF table(s) to match the FTP'ed CSV files.
• Execute DML commands to reconcile the CSV data via standard SQL on the ETF tables.
• Produce reconciliation reports & work queues of phone records to be Revit by the Telco companies.
• Recieve updated billing adjustments CSV files to process.

The above design reduced the processing of these CSV files from about a week to hours.

FYI: External Tables

HTH ... Food for thought.  ;-)

Regards... Chris

Just wondering: could an async db connection be helpful in this case?

In addition to all great advice I'm seeing here already: the way transaction logs and space for tempdb are configured might also be improvable to speed up things. (I'm not a DBA, but I know it matters).