Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array takes 6x longer to process VLOOKUP than worksheet table does
I am running comparative performance tests and have an odd result. I can
post the code but the outcome is a bit surprising. I read 10k rows which splits records resulting in 22k rows. In that process I do a total of 100k VLOOKUPs (10k * 10 cells). When I built the 22k row output directly to the worksheet, it took 12 seconds. I inserted an array, built the output there and then moved the entire array to the worksheet at the end of the procedure. That reduced processing time to 10 seconds (20% reduction in elapsed time). I then moved the "lookup table" to an array and the processing time jumped to 60 seconds! The questions a 1) does that make sense to you? and 2) do some functions (like VLOOKUPs) become inefficient when used in arrays? My guess is that I am doing something wrong like not setting the array as a table (but I can't seem to figure out how to do that). I can post the code but I don't want to tie up a lot of board space.... any general ideas on this? Thanks, Ken |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array takes 6x longer to process VLOOKUP than worksheet table does
I don't follow what you mean by "I then moved the "lookup table" to an
array". Post your code together with clear instructions for someone who cannot see what you what you have to recreate, without guessing what is intended! Regards, Peter T "KGOldWolf" wrote in message ... I am running comparative performance tests and have an odd result. I can post the code but the outcome is a bit surprising. I read 10k rows which splits records resulting in 22k rows. In that process I do a total of 100k VLOOKUPs (10k * 10 cells). When I built the 22k row output directly to the worksheet, it took 12 seconds. I inserted an array, built the output there and then moved the entire array to the worksheet at the end of the procedure. That reduced processing time to 10 seconds (20% reduction in elapsed time). I then moved the "lookup table" to an array and the processing time jumped to 60 seconds! The questions a 1) does that make sense to you? and 2) do some functions (like VLOOKUPs) become inefficient when used in arrays? My guess is that I am doing something wrong like not setting the array as a table (but I can't seem to figure out how to do that). I can post the code but I don't want to tie up a lot of board space.... any general ideas on this? Thanks, Ken |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array takes 6x longer to process VLOOKUP than worksheet table
Following are code snippets - the top example runs 6x faster than the bottom
(10 seconds versus 60). The only change was the way the VLOOKUPs are performed. By "moving the table to an array" I simply meant that the top approach uses a table that is on the same worksheet as the rows that are used as input to the procedure. The bottom approach is intended to compare the efficiency gained by having that same information contained in an array (which I expected to be faster). I believe the problem in the bottom approach is caused because the array is not "SET" in the way the table is in the top approach. The issue is that I can't seem to find any information on how to set / reference the array data any way other than what I have coded. I hope this clarifies it for you. Thanks for your advice, Ken ++++++++++++++++++++++++++++++++++++++++++++++++++ + Array_Dates = Range("Table_Dates") €¦.. ......... Intervening code that remains the same in both examples ............ Print_Line_Array(Output_Row_Number, 8) = Application.WorksheetFunction.VLookup(Lookup_Date, Array_Dates, 2, True) ' Days In Month Print_Line_Array(Output_Row_Number, 9) = Application.WorksheetFunction.VLookup(Lookup_Date, Array_Dates, 5, True) ' Month Sequence Number Print_Line_Array(Output_Row_Number, 10) = Application.WorksheetFunction.VLookup(Lookup_Date, Array_Dates, 3, True) ' First Day In Month +++++++++++++++++++++++++++++++++++++++++++++ Set Table_Dates = Range(Cells(1, 51), Cells(61, 58)) ActiveWorkbook.Names.Add Name:="Table_Dates", RefersTo:=Range(Cells(2, 51), Cells(61, 58)) €¦.. ......... Intervening code that remains the same in both examples ............ Print_Line_Array(8) = Application.WorksheetFunction.VLookup(Lookup_Date, Table_Dates, 2, True) ' Days In Month Print_Line_Array(9) = Application.WorksheetFunction.VLookup(Lookup_Date, Table_Dates, 5, True) ' Month Sequence Number Print_Line_Array(10) = Application.WorksheetFunction.VLookup(Lookup_Date, Table_Dates, 3, True) ' First Day In Month |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array takes 6x longer to process VLOOKUP than worksheet table
OK I follow now. My test results for the array vs range were even worse than
yours, about 10x slower Using a range it's about 2x faster to pass a reference to the range eg wfn.VLookup(Lookup_Date, Range(Cells(1, 51), Cells(61, 58)), c, True) is about 2x slower than Set Table_Dates = Range(Cells(1, 51), Cells(61, 58)) x = wfn.VLookup(Lookup_Date, Table_Dates, c, True) If you really want to keep everything out of cells, following is only about 2x slower - Array_Match = Range("Table_Dates").Columns(1) m = wfn.Match(Lookup_Date, Array_Match, 0) x = Array_Dates(m, c) If your data is sorted you can probably achieve fastest results entirely with VBA, ie no use of worksheet functions (search "binary chop"). fwiw you can do - Dim wfn As WorksheetFunction Set wfn = Application.WorksheetFunction In passing, although notably, if the lookup is a date-value and the first column of the lookup array also holds dates I got incorrect results using the array method - watch out for that! Regards, Peter T "KG Old Wolf" wrote in message ... Following are code snippets - the top example runs 6x faster than the bottom (10 seconds versus 60). The only change was the way the VLOOKUPs are performed. By "moving the table to an array" I simply meant that the top approach uses a table that is on the same worksheet as the rows that are used as input to the procedure. The bottom approach is intended to compare the efficiency gained by having that same information contained in an array (which I expected to be faster). I believe the problem in the bottom approach is caused because the array is not "SET" in the way the table is in the top approach. The issue is that I can't seem to find any information on how to set / reference the array data any way other than what I have coded. I hope this clarifies it for you. Thanks for your advice, Ken ++++++++++++++++++++++++++++++++++++++++++++++++++ + Array_Dates = Range("Table_Dates") ... ........ Intervening code that remains the same in both examples ........... Print_Line_Array(Output_Row_Number, 8) = Application.WorksheetFunction.VLookup(Lookup_Date, Array_Dates, 2, True) ' Days In Month Print_Line_Array(Output_Row_Number, 9) = Application.WorksheetFunction.VLookup(Lookup_Date, Array_Dates, 5, True) ' Month Sequence Number Print_Line_Array(Output_Row_Number, 10) = Application.WorksheetFunction.VLookup(Lookup_Date, Array_Dates, 3, True) ' First Day In Month +++++++++++++++++++++++++++++++++++++++++++++ Set Table_Dates = Range(Cells(1, 51), Cells(61, 58)) ActiveWorkbook.Names.Add Name:="Table_Dates", RefersTo:=Range(Cells(2, 51), Cells(61, 58)) ... ........ Intervening code that remains the same in both examples ........... Print_Line_Array(8) = Application.WorksheetFunction.VLookup(Lookup_Date, Table_Dates, 2, True) ' Days In Month Print_Line_Array(9) = Application.WorksheetFunction.VLookup(Lookup_Date, Table_Dates, 5, True) ' Month Sequence Number Print_Line_Array(10) = Application.WorksheetFunction.VLookup(Lookup_Date, Table_Dates, 3, True) ' First Day In Month |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic Calculation in Code Takes Longer than Worksheet | Excel Programming | |||
Code takes a long time to process | Excel Programming | |||
Rerunning Macro takes longer after each consecutive use....Why | Excel Programming | |||
Double-clicking cell with =A1 no longer takes you to A1 in v2003? | Excel Discussion (Misc queries) | |||
How to use a cell value as Table Array in VLOOKUP worksheet function | Excel Discussion (Misc queries) |