SQL Query Takes About 10 - 20 Minutes

I have a select from (nothing to complex) Select * from VIEW This view has about 6000 records and about 40 columns. It comes from a Lotus Notes SQL database. So my ODBC drive is t

Solution 1:

Have you tried using a bulk query? I had this same problem earlier in the week with C#; I had to insert about 25000 records and it took around 30 minutes. Changing to a bulk insert cut it down to about 5 seconds.

Solution 2:

HAve you indexed your Access table after the records are inserted. That should make it much faster to query on.

Solution 3:

If using a bulk insert isn't supported or too much hassle, an easy solution may be to use a transaction: because most DB's are supposed to be atomically safe, every insert comes with a certain minimum overhead (this is a vast simplification, but whatever). By wrapping all the insert's into a single transaction, you can avoid the atomic-commit overhead.

However, to really improve performance, you'll need to benchmark some more. In particular, is it the inserts that are slow, or the select ... from view?

Solution 4:

Try something like this:

SELECT * INTO NewTable FROM View

Solution 5:

I'm not too familiar with Lotus Notes SQL, but the fact that you have integers in text columns sounds like a pretty bad idea for many, many reasons.

• Data integrity: One of these integers could end up as "foo". Then what do you do?
• Performance: Typically, integers are both smaller and easier to work with for applications
• Sorting: Sorting numbers you will get 9, 10, 11, 100. Sort those as text and you get 10, 100, 11, 9

Now on to your problem... I think that behind the scenes Lotus Notes SQL uses a NotesSQL database. I think that you can create indexes in this yourself. Have you tried creating an index on the column(s) which are in your WHERE clause?