[02/03] How to make tons on insert statements more effecient?

[anbliss24]

Hello I am new here and sort of new to VB.NET. I am trying to write a program that will take the national do not call list for a certain area code, which is given to me in a comma seprated file, and then to check if the number is in a database, if it isn't then to insert it. There are literaly millions of numbers. They way I have written it is so slow because I don't really know how to write database applications. Everytime I check to see if a number exists I open a connection and then run an OleDbCommand and ExecuteScalar then I close the connection. Then if it doesnt exist then I Open another connection and Insert then close. Is there a way I can just open the connection once at the beggined then do all my queries and then close it at the end? Is there something else I can do to speed this up, because the way I have it right now it will take days and days to complete.

[Shaggy Hiker]

Welcome to the forums. I have two suggestions, but they may not be ideal:

1) You can just open one connection, and leave it open for the duration. There is no need to close it, and that alone should speed up the processing. Just keep changing the command.CommandText to the new query, without ever closing the connection. I guess I'd have to see some code to make a verys specific suggestion there.

2) I'm not sure what the slowest part of the process is. You might benefit from a stored procedure handling the INSERT, but failing that, why not lump several INSERT statements together. Shoudl be able to separate them with commas. If you tested for five numbers, then lumped (up to) five INSERT statements together, that should speed things up some. Further improvements might be made along these lines, such as searching until five numbers need to be inserted, then inserting them. Etc.

[anbliss24]

Cool thanks for the ideas, Ill try them out

[wild_bill]

What kind of database?

[anbliss24]

Oracle 8

[RobDog888]

I am not familiar with Oracle but you can write some stored procedures that accept some parameters that you can pass to perform the insert. This should be the faster method.

[GaryMazzone]

Wouildn't it be quicker to load the data into a temproary table using SQL Loader then write a SP to perform the test and insert?

[anbliss24]

I don't know what SQL Loader is so I wouldn't know what would be faster. I guess I could make a stored procedure where I could pass the number and have the database check the number for me. Ill try that out.

[GaryMazzone]

SQL Loader will load the file directly into the database. It is part of the Oracle applications installed on the system. It is run from the command line. You could have you app call the command line with SQL*Loader and the required parameter file then once the load is finished (I have loaded about 2.5 million records into an Oracle 8 DB in about 2 minutes lilke this) have the app call the SP to process the records.

[RobDog888]

So its like SQL Servers bcp?

[GaryMazzone]

Yes it is. The control file tell the SQL*Loader where the data is in the line and what field it goes into.

[szlamany]

Gary has an idea worth investigating. We do something similiar in MS SQL Server.

We load the "voter" list for the state - about 4 million rows - using BULK INSERT (the MS SQL version of SQL Loader) into a staging table. This load runs relatively quickly

Then we use stored procedures to process and match this data against membership files.

[anbliss24]

How do I have my application load all the data into SQL loader. I have three fields, the area code, the phone, and the full number.

[GaryMazzone]

DO you have access to the Oracle Complete Reference Guide? If you do then there is a complete section in that refernce book that covers SQL*Loader. If not PM me and I'll have to e-mail you a PDF of the chapter as it is too large to post here.

[anbliss24]

Thanks for your help but I found out how to use it. Its a lot faster. Thanks