SQL Query takes about 10 - 20 minutes

Question

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 the LotusNotesSQL driver. The query takes about 30 seconds to execute. The company I worked for used EXCEL to run the query and write everything to the worksheet. Since I am assuming it writes everything cell by cell, it used to take up to 30 - 40 minutes to complete.

I then used MS access. I made a replica local table on Access to store the data. My first try was

INSERT INTO COLUMNS OF LOCAL TABLE
FROM (SELECT * FROM VIEW)

note that this is pseudocode. This ran successfully, but again took up to 20 - 30 minutes. Then I used VBA to loop through the data and insert it in manually (using an INSERT statement) for each separate record. This took about 10 - 15 minutes. This has been my best case yet.

What i need to do after: After i have the data, I need to filter through it by department. The thing is if I put a where clause in the SQL query (the time jumps from 30 seconds to execute the query, to about 10 minutes + the time to write to local table/excel). I don't know why. MAYBE because the columns are all text columns?

If we change some of the columns to integer, would that make it faster in terms of the where clause?

I am looking for suggestions on how to approach this. My boss has said we could employ some Java based solution. Will this help? I am not a java person but a c#, and maybe I'll convince them to use c# as well, but I am mainly looking for suggestions on how to cut down the time. I've already cut it down from 40 minutes to 10 minutes, but the want it under 2 minutes.

Just to recap:

Query takes about 30 seconds to exceute

Query takes about 15 - 40 minutes to be used locally in Excel/Access

Need it under 2 minutes

Could use a java based solution

You may suggest other solutions instead of java.

Answer 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.

Answer 2

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?

Answer 3

Try something like this:

SELECT * INTO NewTable FROM View

Answer 4

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?

Answer 5

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