How To Run LOOKUP a Single Time, Produce Multiple Resulting Variables, Each With A Different Value From The Record

[Mike Wilson]

Glad you got this to work. Frankly I am quite surprised the " deliminator between the fields works. I have never had anything but the ' work for me.

[Ronald Anusiewicz]

Nigel,

I’m no expert on xbasic coding but I will offer my thoughts.

From what I have read, you are having issues with speed. My thought is I do not think it would make any difference whether you used Lookup() or LookupC() as long as the filter is for a field that is indexed.
That said, using LookupC() you are manipulating the UN_Price changing it from a value to a string and then back to a value. I have no idea how long this takes but it could slow down the process by x amount of nano seconds.

So what you are looking for may be found in the help file.

PROFILER_BEGIN()

Description

Insert the PROFILER_BEGIN() () command in an Xbasic script at the point that you want to begin profiling a script. Insert the PROFILER_END()command at the point that you want to end profiling a script. When the PROFILER_END() command is encountered, Alpha Five dumps a report to the Trace window showing each line in your Xbasic script that was executed, the number of times it was executed, and the time taken to execute the command.
Profiling a script can help you determine which parts of a script might be taking a long time to execute. It can also help you debug your script because in complex scripts with lots of loops and branches, it can be difficult to see exactly which lines in your script actually get executed. The profiler report shows you what got executed.

Examples

Assume that you have created this script:

profiler_begin() t = table.open("customer") t.fetch_first() while .not. t.fetch_eof() t.change_begin() t.Company = upper(t.Company) t.change_end(.T.) t.fetch_next() end while t.close() profiler_end()

After running this script, the Trace window will show:

Script profiler Line Tot Time Cnt Source ======================================================== 1 0.014018 1 t = table.open("customer") 2 0.000228 1 t.fetch_first() 3 0.000256 1 while .not. t.fetch_eof() 4 0.008717 63 t.change_begin() 5 0.009599 63 t.Company = upper(t.Company) 6 0.240749 63 t.change_end(.T.) 7 0.007564 63 t.fetch_next() 8 0.004301 63 end while 9 0.001136 1 t.close() =======================================================

You can have two buttons, the first using Lookup() and the second using LookupC(). Put your code in and run it.

First button using Lookup()

profiler_begin()
[FONT=Times New Roman]Dim Found_Product As C
DIM filterstring as c
filterstring = "UPC = " + s_quote(Product_Lookup)
Found_Product = lookup("products",filterstring,"Product_ID + "*" + Item_Num + "*" + Dept_Id + "*" + Department + "*" + Tax + "*" + Un_Price + "*" + T_Price + "*" + Qty_Onhand")

DIM GLOBAL Invoice_Nu AS c
Invoice_Nu = :POS:invoice_nu.value

tbl = table.open("invoice_items")
tbl.enter_begin(.T.)
tbl.Invoice_Nu = INVOICE_NU
tbl.Product_id = word(Found_Product, 1, "*")
tbl.Item_num = word(Found_Product, 2, "*")
tbl.Dept_Id = word(Found_Product, 3, "*")
tbl.Department = word(Found_Product, 4, "*")
tbl.Price = Val(word(Found_Product, 6, "*"))
tbl.T_Price = Val(word(Found_Product, 7, "*"))
tbl.Qty_Before = Val(word(Found_Product, 8, "*"))

tbl.enter_end(.T.)
tbl.close()[/FONT]
profiler_end()

Second button using LookupC()

profiler_begin()
[FONT=Times New Roman]Dim TN as C
Dim Found_Product As C

TN = table.filename_get("products")

Found_Product = lookupc("F",Product_Lookup,"Product_ID + "*" + Item_Num + "*" + Dept_Id + "*" + Department + "*" + Tax + "*" + str(Un_Price,19,11) + "*" + str(T_Price,19,11) + "*" + str(Qty_Onhand,19,3)",TN,"Alu")

DIM GLOBAL Invoice_Nu AS c
Invoice_Nu = :POS:invoice_nu.value

tbl = table.open("invoice_items")
tbl.enter_begin(.T.)
tbl.Invoice_Nu = INVOICE_NU
tbl.Product_id = word(Found_Product, 1, "*")
tbl.Item_num = word(Found_Product, 2, "*")
tbl.Dept_Id = word(Found_Product, 3, "*")
tbl.Department = word(Found_Product, 4, "*")
tbl.Price = Val(word(Found_Product, 6, "*"))
tbl.T_Price = Val(word(Found_Product, 7, "*"))
tbl.Qty_Before = Val(word(Found_Product, 8, "*"))

tbl.enter_end(.T.)
tbl.close()[/FONT]
profiler_end()

[FONT=Times New Roman][/FONT]

You can run the buttons against different products and compared the times. You may see something else that’s taking a lot of time to execute.

[Ronald Anusiewicz]

I decided to experiment using some of your code snippets and "Profiler".
I do not have any of your data so I made a "Products" table with the fields in your lookup function.
I created a script to populate the table with 3000 records.

Next, I created the following script from one of your code snippets running the first using Lookup() and re-running the code a second time but with using Lookupc().

I then opened the result text file generated by the "profilier".

DIM SHARED Product_lookup as C = "124-12345-956"
DIM SHARED vInvoice_Nu as C = "Inv-123458"

profiler_begin()
Dim Found_Product As C
DIM filterstring as c
filterstring = "Product_ID = " + s_quote(Product_Lookup)
Found_Product = lookup("products",filterstring,"Product_ID + '*' + Item_Num + '*' + Dept_Id + '*' + Department + '*' + Tax + '*' + Un_Price + '*' + T_Price + '*' + Qty_Onhand")
DIM GLOBAL Invoice_Nu AS c
Invoice_Nu = vInvoice_Nu
tbl = table.open("invoice_items")
tbl.enter_begin(.T.)
tbl.Invoice_Nu = INVOICE_NU
tbl.Product_id = word(Found_Product, 1, "*")
tbl.Item_num = word(Found_Product, 2, "*")
tbl.Dept_Id = word(Found_Product, 3, "*")
tbl.Department = word(Found_Product, 4, "*")
tbl.Price = Val(word(Found_Product, 6, "*"))
tbl.T_Price = Val(word(Found_Product, 7, "*"))
tbl.Qty_Before = Val(word(Found_Product, 8, "*"))
tbl.enter_end(.T.)
tbl.close()


Dim Found_Product As C
DIM filterstring as c
filterstring = "Product_ID = " + s_quote(Product_Lookup)
Found_Product = lookupc("F",Product_Lookup,"Product_ID + '*' + Item_Num + '*' + Dept_Id + '*' + Department + '*' + Tax + '*' + str(Un_Price,19,11) + '*' + str(T_Price,19,11) + '*' + str(Qty_Onhand,19,3)","products","Alu")
DIM GLOBAL Invoice_Nu AS c
Invoice_Nu = vInvoice_Nu + "C"
tbl = table.open("invoice_items")
tbl.enter_begin(.T.)
tbl.Invoice_Nu = INVOICE_NU
tbl.Product_id = word(Found_Product, 1, "*")
tbl.Item_num = word(Found_Product, 2, "*")
tbl.Dept_Id = word(Found_Product, 3, "*")
tbl.Department = word(Found_Product, 4, "*")
tbl.Price = Val(word(Found_Product, 6, "*"))
tbl.T_Price = Val(word(Found_Product, 7, "*"))
tbl.Qty_Before = Val(word(Found_Product, 8, "*"))
tbl.enter_end(.T.)
tbl.close()
profiler_end("C:\profilerEnd.txt")

Profiler results text file.

Line Count Elapsed Time CPU Time User Time Kernel Time Source
===== ====== ============= ============= ============= =============
4 - - - - profiler_begin()
5 1 0.000025 0.000000 0.000000 0.000000 Dim Found_Product As C
6 1 0.000030 0.000000 0.000000 0.000000 DIM filterstring as c
7 1 0.000091 0.000000 0.000000 0.000000 filterstring = "Product_ID = " + s_quote(Product_Lookup)
8 1 0.001197 0.000000 0.000000 0.000000 Found_Product = lookup("products",filterstring,"Product_ID + '*' + Item_Num + '*' + Dept_Id + '*' + Department + '*' + Tax + '*' + Un_Price + '*' + T_Price + '*' + Qty_Onhand")
9 1 0.000018 0.000000 0.000000 0.000000 DIM GLOBAL Invoice_Nu AS c
10 1 0.000042 0.000000 0.000000 0.000000 Invoice_Nu = vInvoice_Nu
11 1 0.003816 0.000000 0.000000 0.000000 tbl = table.open("invoice_items")
12 1 0.000863 0.000000 0.000000 0.000000 tbl.enter_begin(.T.)
13 1 0.000115 0.000000 0.000000 0.000000 tbl.Invoice_Nu = INVOICE_NU
14 1 0.000120 0.000000 0.000000 0.000000 tbl.Product_id = word(Found_Product, 1, "*")
15 1 0.000109 0.000000 0.000000 0.000000 tbl.Item_num = word(Found_Product, 2, "*")
16 1 0.000106 0.000000 0.000000 0.000000 tbl.Dept_Id = word(Found_Product, 3, "*")
17 1 0.000105 0.000000 0.000000 0.000000 tbl.Department = word(Found_Product, 4, "*")
18 1 0.000133 0.000000 0.000000 0.000000 tbl.Price = Val(word(Found_Product, 6, "*"))
19 1 0.000136 0.000000 0.000000 0.000000 tbl.T_Price = Val(word(Found_Product, 7, "*"))
20 1 0.000127 0.000000 0.000000 0.000000 tbl.Qty_Before = Val(word(Found_Product, 8, "*"))
21 1 0.040341 0.000000 0.000000 0.000000 tbl.enter_end(.T.)
22 1 0.018063 0.000000 0.000000 0.000000 tbl.close()
23 - - - -
24 - - - -
25 1 0.000040 0.000000 0.000000 0.000000 Dim Found_Product As C
26 1 0.000014 0.000000 0.000000 0.000000 DIM filterstring as c
27 1 0.000125 0.000000 0.000000 0.000000 filterstring = "Product_ID = " + s_quote(Product_Lookup)
28 1 0.001196 0.000000 0.000000 0.000000 Found_Product = lookupc("F",Product_Lookup,"Product_ID + '*' + Item_Num + '*' + Dept_Id + '*' + Department + '*' + Tax + '*' + str(Un_Price,19,11) + '*' + str(T_Price,19,11) + '*' + str(Qty_Onhand,19,3)","products","Alu")
29 1 0.000018 0.000000 0.000000 0.000000 DIM GLOBAL Invoice_Nu AS c
30 1 0.000052 0.000000 0.000000 0.000000 Invoice_Nu = vInvoice_Nu + "C"
31 1 0.003687 0.000000 0.000000 0.000000 tbl = table.open("invoice_items")
32 1 0.000847 0.000000 0.000000 0.000000 tbl.enter_begin(.T.)
33 1 0.000116 0.000000 0.000000 0.000000 tbl.Invoice_Nu = INVOICE_NU
34 1 0.000122 0.000000 0.000000 0.000000 tbl.Product_id = word(Found_Product, 1, "*")
35 1 0.000111 0.000000 0.000000 0.000000 tbl.Item_num = word(Found_Product, 2, "*")
36 1 0.000106 0.000000 0.000000 0.000000 tbl.Dept_Id = word(Found_Product, 3, "*")
37 1 0.000103 0.000000 0.000000 0.000000 tbl.Department = word(Found_Product, 4, "*")
38 1 0.000132 0.000000 0.000000 0.000000 tbl.Price = Val(word(Found_Product, 6, "*"))
39 1 0.000130 0.000000 0.000000 0.000000 tbl.T_Price = Val(word(Found_Product, 7, "*"))
40 1 0.000126 0.000000 0.000000 0.000000 tbl.Qty_Before = Val(word(Found_Product, 8, "*"))
41 1 0.033215 0.000000 0.000000 0.000000 tbl.enter_end(.T.)
42 1 0.016995 0.000000 0.000000 0.000000 tbl.close()
43 - - - - profiler_end("C:\profilerEnd.txt")

I tried running the script numerous times. Each time there are variances even for the same line of code.
In this example, there does not appear to be any difference in times between the two different Lookup functions.
But my table has only 3000 records.
What does take a lot of time is: Enter_end(). I believe any indexes attached to the items table are probably being updated on
each save.

table.open() and tbl.close() take the next amount of time. I do not know if your app is running stand alone or with multiple users.
I'm not sure if it even matters. I might try to open and close the lookup table just once for each checkout instead of each time you
scan an item. This might speed things up quite a bit.

Just a thought. Someone else may have different thoughts on this.

Hope this helps.

[nigeldude]

Hey Ronald! I truly appreciate your efforts to assist me with this, You actually simulate it and I'm truly grateful for your time, thanks!

I did realize that the bulk of the time seemed to not be the querying/lookup part of the process, but rather the actual adding of the item to the invoice.
Unfortunately, though, I was never ever able to benefit from the profiler function, (I know that makes me seem really lost out there), but, I place profiler_begin() at the start of the script, then I place profiler_end() at the end, I run the script and check the Trace Window, I see nothing that resembles the info you have shown. I see other info that I don't understand, but nothing that resembles your results.

[Al Buchholz]

I'm still confused about the circumstances for this code and why a field lookup on a table doesn't fit.

Asking for a friend.....

[Ronald Anusiewicz]

My first post referenced an older help file. In my last post you will see the last line adds an argument to the profiler_end() function which is a text file where the results will be written.

43 - - - - profiler_end("C:\profilerEnd.txt")

I created the text file "profilerEnd.txt" and placed it on the C drive so it would be easy for me to find.

[nigeldude]

I did notice that Ronald, I tried that too, nothing gets written to the TXT file, it remains 100% blank/empty. It's like the function is not being initiated ant at all. Does this feature have to be enabled somewhere for the function to start working?

[CraigSchumacker]

Not working by me, either... I tried on a 3 year old version of 12, and the last version I have (since I have not updated lately), which is from February..

I have latest Win 10, and tried writing to the root, as well as the documents folder in my user profile. Thought it might be a permission issue, but that's off the table.

It doesn't write to the trace window, either.

It seems like it would be handy if we can figure out why it only works for you, Ronald. You've certainly sparked my interest.

[Ronald Anusiewicz]

I'm not sure what's going on.

First, I never used this myself. I just remembered that this function existed. I thought I'd give it a try. I've attached my working example and text file.
The text file has to be closed when you run the script, otherwise the function will not write to the file. I tried it on different version of AA, both running with administrator privilages
and without on my windows 10 machine without issue.

ForNigel.zip ProfilerEnd.txt

[CraigSchumacker]

You are correct. You MUST right click on the AA icon, and select 'Run As Administrator'.

[CraigSchumacker]

I didn't adjust the icon, but Ronald, maybe you at one point clicked on your AA icon and changed Properties / Compatibility to have a check next to 'Run this program as an administrator'?

Maybe that's why it worked for you?

[nigeldude]

I got it guys, I can now view the results of the Profiler. Thanks so so much.

[CraigSchumacker]

Yes, thank you Ronald. I was not aware of this function.

[CraigSchumacker]

Interestingly, not that it's an issue for me, but opening AA "As an administrator" hides all mapped drives within AA. They are still there in explorer. just not visible within AA.

[armasoft]

Interestingly, not that it's an issue for me, but opening AA "As an administrator" hides all mapped drives within AA. They are still there in explorer. just not visible within AA.

Craig, by running as Administrator you effectively login with AA as local admin. This account doesn't have access to networked resources by default (not sure if they can be granted)