I have a selection (nothing from complex)
Select from VIEW This view contains approximately 6000 records and approximately 40 columns. It comes from a Lotus Note SQL database, so my ODBC Drive has LotusNotesSQL driver. The query takes about 30 seconds to execute. To run the query and to write everything in the worksheet, I've worked for the Excel used because I'm assuming that it all writes the cell by cell, in order to complete it It took 30 to 40 minutes.
Then I used MS Access I created a replica local table on access to store data. My first try was
INSERT (SELECT * FROM VIEW) in the local table column Note that this is pseudocode, it ran successfully, but then It took 20 to 30 minutes. Then I used VBA to loop through the data and manually enter it for each separate record (using the INSERT statement). It took about 10 to 15 minutes. This is my best case yet.
What do I have to do next: After having the data, I'll have to filter it by department. This thing is that if I put a clause in the SQL query (30 seconds to execute the query, then about 10 minutes + time to write in local table / Excel) I do not know why because MB Are Columns All Text Columns?
If we change some columns to an integer, then can say where?
I am looking for tips on how to reach it. My boss has said that we can employ some Java based solution, will it help? I'm not a Java person but a C #, and maybe I'll refuse to use them as C #, but I mainly suggest how to reduce the time. I have already cut it from 40 minutes to 10 minutes, but want to do it less than 2 minutes.
To simply reset:
The query takes about 30 seconds to remove
query to be used locally in Excel / Access 15 It takes 40 minutes
Required within 2 minutes
Using a Java-based solution
You can recommend other solutions instead of Java .
Have you tried using a bulk query? I had this problem with C # in the first week; I had to include nearly 25,000 records and it took about 30 minutes. It can be cut for about 5 seconds to bulk.
Comments
Post a Comment