SQL Query takes about 10 - 20 minutes
- by masfenix
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.
