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

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.

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

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?

Try something like this:

SELECT * INTO NewTable FROM View

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?