How to increase my program speed

Question

i stored data in two different google spreadsheet.one spreadsheet have nearly 1000 entries.I used another spreadsheet to give input .My constraints is compare these two spreadsheet and if common data appear in two spreadsheet than put that data into another spreadsheet.So that i used for loop to compare these two.

My problem is that it take more time.Is there any other solution to increase my program speed.

Answer 1

If you can, at all, put the data into a database it would really help your read/write speed. Perhaps you can speed it up as is by reading in all the data from both spreadsheets at once and store them in memory then do your comparison?

Without more details on your implementation that is about all I can help you with.

Answer 2

If your spreadsheets are Excel ones, don't use Java for this, code it in VBA as an Excel macro, it will be 100 times easier... and probably also 100 times faster.

Answer 3

I guess your speed problem comes from incorrect algorithm. If you loop, as you said over two documents, you have something like this:

for i in doc1:
  for j in doc2:
    if (compare(i,j) == 0) output(i)

This is 1000000 operations for 1000 items documents, which is slow.

There's an easy improvement for that and it's based on sorting items. So you can append those 2 documents, sort them and iterate over array. This will make around 4000 operations, which is huge improvement.

It doesn't matter whether it's Java or Visual Basic.

I believe, you can also do this kind of operation fast in SQL.

Answer 4

• Create two HashSet instances and put each document's entries into one of them.
• Create the intersection of the two sets using set1.retainAll(set2)
• set1 now contains only those entries that are present in both documents
• Runs about 100 times faster than using nested loops