SQL Query takes about 10 - 20 minutes

Posted by masfenix on Stack Overflow See other posts from Stack Overflow or by masfenix
Published on 2010-06-18T13:05:43Z Indexed on 2010/06/18 13:13 UTC
Read the original article Hit count: 194

Filed under:
|

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 seperate 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 dont 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/acess

Need it under 2 minutes

Could use java based solution

You may suggest other solutions instead of java.

© Stack Overflow or respective owner

Related posts about sql

Related posts about time