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.

[Mwatkins]

@Jan F  Yes, there are two queries against the task table, but you should discount those because they come from the lines before Option 1 starts, for example:

current.query();

 That line is not part of Option 1. That line is just to set the context of the background script so that it matches the context of a Business Rule - which gets "current" for free.

My previous experiment was a little unclear. Allow me to expand. This time I will turn on "Session Debug > Debug Log" and "Session Debug > Debug SQL (Detailed)". This should result in a popup window that will display all the logging and sql output from all my transactions. I've added some additional logging after each line execution to make it clear which SQL statement is coming from which line.

Code:

var current = new GlideRecord("incident");
current.addNotNullQuery("caller_id");
gs.info("1");
current.query();
gs.info("2");
current.next();

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

gs.print("Start Option 1");
gs.info("3");
result.value1 = current.caller_id.first_name; // <- this triggers a query
gs.info("4");
result.value2 = current.caller_id.last_name;
gs.info("5");
result.value3 = current.caller_id.email;
gs.info("6");
result.value4 = current.caller_id.user_name;
gs.info("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");
gs.info("7");
usr.get(current.getValue('caller_id')); // <- this triggers a query
gs.info("8");
result.value1 = usr.value1;
gs.info("9");
result.value2 = usr.value2;
gs.info("10");
result.value3 = usr.value3;
gs.info("11");
result.value4 = usr.value4;
gs.info("End Option 2");

Interestingly, the results are different than when I ran this experiment 5 years ago. Now I only get two queries against sys_user, one during the dot-walk between 3 and 4, and one during the GlideRecord.getValue() operation between 7 and 8. If you do this experiment, hover over the ellipses between "SELECT ... FROM" to see what columns are returned in the queries. In my experiment, in both cases, all columns are returned. It seems like ServiceNow has made GlideRecord.getValue() more efficient these days and it no longer runs as a two-pass query method (i.e. one query to get the sys_id and one query to get all the other columns). Now GlideRecord.getValue() just returns all the columns on the first go.

So, in conclusion, the options now seem to be identical in terms of database efficiency.

Results (note that we're only interested in queries that occur between "Start Option 1" "End Option 2"

11:37:56.918 Start Option 1
11:37:56.919 3
11:37:56.921 Time: 0:00:00.001 for: XX[glide.15] [1882128680] SELECT ... FROM sys_user sys_user0 WHERE sys_user0.`sys_id` = '6a826bf03710200044e0bfc8bcbe5dec'  
11:37:56.926 4
11:37:56.927 5
11:37:56.927 6
11:37:56.928 End Option 1, start option 2
11:37:56.928 7
11:37:56.930 Time: 0:00:00.000 for: XX[glide.25] [1882128680] SELECT ... FROM sys_user sys_user0 WHERE sys_user0.`sys_id` = '6a826bf03710200044e0bfc8bcbe5dec'  
11:37:56.932 8
11:37:56.932 9
11:37:56.933 10
11:37:56.934 11
11:37:56.934 End Option 2