Dot Walking vs GlideRecord Query

[zschneider]

I have a requirement to copy a bunch of fields from a table selected in a reference field to the form with the reference field on submit. In the Business Rule, would it make more sense to dotwalk each of these like this:

current.value1 = current.u_reference.value1;
current.value2 = current.u_reference.value2;
current.value3 = current.u_reference.value3;
current.value4 = current.u_reference.value4;

Or would it be more efficient to actually create a GlideRecord and do the update in one query like:

var gs = new GlideRecord("u_reference_table");
gs.get(current.u_reference);
current.value1 = gs.value1;
current.value2 = gs.value2;
current.value3 = gs.value3;
current.value4 = gs.value4;

I'm curious if using Example 1 causes 4 individual queries to be made to the u_reference table where Example 2 stores all the values in the gs object only requring one query. I'm really not sure what the difference between these two examples would be on the back-end, or which would be the more effective way to pass values from one form to another.

My personal preference would have been to dot walk the entire field and not bother copying the data, but some of our users throw a fit about having to dot walk to view fields in a report and it was decided to avoid dotwalked fields altogether.

[479342]

Example 1 is the more effective way.

Because in 1st example,we are directly taken value from current record which is effective.

But in 2nd example,we are querying other table for the same value to get which can be possible from the reference table.as mentioned in example 1

[Mwatkins]

Example 1 is more efficient. Both examples require querying the sys_user table, however, example 1 only requires 1 query while example 2 requires 2 queries. I tested this with the following code in "Script - Background" while using "Enable All". I then opened a 2nd tab with myinstance.service-now.com/anything.do to see the debug output.

var current = new GlideRecord("incident");
current.addNotNullQuery("caller_id");
current.query();
current.next();

var result = {value1:null, value2:null, value3:null, value4:null};

gs.print("Start Option 1");
result.value1 = current.caller_id.first_name; // <- this triggers a query
result.value2 = current.caller_id.last_name;
result.value3 = current.caller_id.email;
result.value4 = current.caller_id.user_name;
gs.print("End Option 1, start option 2");

//Or would it be more efficient to actually create a GlideRecord and do the update in one query like:
var usr = new GlideRecord("sys_user");
usr.get(current.getValue('caller_id')); // <- this triggers a query
result.value1 = usr.value1; // <- this triggers a query
result.value2 = usr.value2;
result.value3 = usr.value3;
result.value4 = usr.value4;
gs.print("Option 2");

[Max_MS]

Thanks for this answer. I was wondering the same thing.

[Jan F]

Sorry I really can't confirm the above statement on my PDI (it may be different according to the age of this statement). 

In both situations I always see 2 queries in the debug SQL output:

2 for tasks (incident):

00:53:22.477 Time: 0:00:00.001 for: dev285801_1[glide.0] [1034613752] SELECT FROM task task0 WHERE task0.`sys_class_name` = 'incident' AND task0.`a_ref_4` IS NOT NULL

00:53:22.484 Time: 0:00:00.002 for: dev285801_1[glide.6] [896098098] SELECT 

 FROM task task0 WHERE task0.`sys_class_name` = 'incident' AND task0.`sys_id` IN ('1c741bd70b2322007518478d83673af3' , '1c832706732023002728660c4cf6a7b9' , '46b66a40a9fe198101f243dfbc79033d' , '46b9490da9fe1981003c938dab89bda3' , '46c03489a9fe19810148cd5b8cbf501e' , '46c88ac1a9fe1981014de1c831fbcf6d' , '46cebb88a9fe198101aee93734f9768b' , '46e18c0fa9fe19810066a0083f76bd56' , '46ee0924a9fe198100f1cf78c198454a' , '46ee8c2fa9fe198100623592c70d643e' , '46f1784ba9fe19810018aa27fbb23482' , '46f3ee0ea9fe198100c5c0e53d5abe0b' , '46f6ede0a9fe198100e10154c34a0c2a' , '47079b68a9fe198100a614a4c225c88d' , '470af5afa9fe198101b324dd773ef379' , '470d51a0a9fe1981006c20c825e48933' , '47138238a9fe1981016e3762d1fd26d4' , '4714a2aca9fe198100ecabacf6273482' , '4717dfe5a9fe198100450448b2404c16' , '8d6246c7c0a80164012fb063cecd4ace' , '8d641046c0a80164000bc7c0d3ed46a0' , '9c573169c611228700193229fff72400' , '9d3c1197c611228701cd1d94bc32d76d' , '9e7f9864532023004247ddeeff7b121f' , '9fffc328731823002728660c4cf6a742' , 'e329de99731423002728660c4cf6a73c' , 'e8e875b0c0a80164009dc852b4d677d5' , '0c5f3cece1b12010f877971dea0b1449' , '46e2fee9a9fe19810049b49dee0daf58' , '46e3e949a9fe19810069b824ba2c761a' , '46e482d9a9fe198101d3e3f3e2a14459' , '46e57642a9fe1981000b96a5dca501ff' , '46e8219ba9fe1981013806b6e04fed06' , '46edaa6aa9fe198101b9d14ced16619f' , '46f09e75a9fe198100f4ffd8d366d17b' , '46f4f4dfa9fe198100063e60278f76ec' , '46f67787a9fe198101e06dfcf3a78e99' , '47064b68a9fe19810186793eefffc9b7' , '4715ab62a9fe1981018c3efb96143495' , '471bfbc7a9fe198101e77a3e10e5d47f' , '471d4732a9fe198100affbf655e59172' , '471eb058a9fe198100f89592e1ea93d3' , '47204688a9fe1981011a20af100f381a' , '552c48888c033300964f4932b03eb092' , '57af7aec73d423002728660c4cf6a71c' , '78271e1347c12200e0ef563dbb9a7109' , '85071a1347c12200e0ef563dbb9a71c1' , '8d6353eac0a8016400d8a125ca14fc1f' , '965c9e5347c12200e0ef563dbb9a7156' , '9d385017c611228701d22104cc95c371' , 'a2496c05731110107418660c4cf6a711' , 'a623cdb073a023002728660c4cf6a768' , 'a83820b58f723300e7e16c7827bdeed2' , 'a9a16740c61122760004fe9095b7ddca' , 'a9e30c7dc61122760116894de7bcc7bd' , 'a9e428cac61122760075710592216c58' , 'd7158da0c0a8016700eef46c8d1f3661' , 'd7195138c0a8016700fd68449cfcd484' , 'd71b3b41c0a8016700a8ef040791e72a' , 'd71da88ac0a801670061eabfe4b28f77' , 'd71f7935c0a8016700802b64c67c11c6' , 'e8caedcbc0a80164017df472f39eaed1' , 'ed92e8d173d023002728660c4cf6a7bc' , 'ef4225a40a0a0b5700d0b8a790747812' , 'ef43c6d40a0a0b5700c77f9bf387afe3' , 'f12ca184735123002728660c4cf6a7ef' , 'ff4c21c4735123002728660c4cf6a758')

and one for sys_user:

00:53:22.492 Time: 0:00:00.001 for: dev285801_1[glide.18] [1882128680] SELECT FROM sys_user sys_user0 WHERE sys_user0.`sys_id` = '681ccaf9c0a8016400b98a06818d57c7'

Best regards