SQL query takes about 10-20 minutes

Question

I have a choice from (nothing complicated)

Select * from VIEW 

This view contains about 6,000 records and about 40 columns. It comes from a Lotus Notes SQL database. Thus, my ODBC drive is the LotusNotesSQL driver. The request takes about 30 seconds to complete. The company I worked with used EXCEL to run a query and write everything to a worksheet. Since I assume that he writes all the cells in cells, it took 30-40 minutes to complete this.

Then I used access to MS. I created a local replica table in Access to store data. My first attempt was

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

Please note that this is pseudo code. This continued successfully, but again took 20 to 30 minutes. Then I used VBA to scroll through the data and insert it manually (using the INSERT statement) for each individual record. It took about 10 to 15 minutes. This was my best example.

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

If we change some columns to integers, it will do it faster in terms of where <

I am looking for suggestions on how to approach this. My boss said that we can use some kind of Java based solution. Will this help? I am not a java person, but C #, and maybe I will run them to use C #, but I'm mostly looking for suggestions on how to reduce time. I have already reduced it from 40 minutes to 10 minutes, but I want it to be less than 2 minutes.

Just to pounce:

It takes about 30 seconds to complete the request.

The request takes about 15 - 40 minutes for local use in Excel / Access

You will need less than 2 minutes

Java solution can be used

You can offer other solutions instead of java.

Answer 1

Have you tried using bulk request? I had the same problem earlier in the week with C #; I had to insert about 25,000 records and it took about 30 minutes. Switching to the volume insert was reduced to 5 seconds.

Answer 2

HAve you indexed your Access table after making entries. This should speed up the request.

Answer 3

If using a bulk insert is not supported or there is too much trouble, a simple solution might be to use a transaction: because most databases must be atomically safe, each insert has certain minimal overhead (this is a simple simplification, but whatever). By wrapping the entire insert in one transaction, you can avoid the overhead with the atom.

However, in order to really improve performance, you will need to conduct a few more tests. In particular, is it slow insert , or select ... from view ?

Answer 4

Try something like this:

 SELECT * INTO NewTable FROM View 

Answer 5

I'm not very familiar with Lotus Notes SQL, but the fact that you have integers in text columns sounds like a pretty bad idea for many reasons.

• Data integrity. One of these integers may be "foo". Then what are you doing?
• Performance: usually integers are smaller and easier to work with applications
• Sort: Sort the numbers you get 9, 10, 11, 100. Sort them as text and you get 10, 100, 11, 9

Now about your problem ... I think behind the scenes Lotus Notes SQL uses a NotesSQL database. I think you can create indexes on this yourself. Have you tried creating an index for the columns (columns) that are in your WHERE clause?