Slow doSelect performance

Hello,

Hoping someone can give me a tip about improving the performance of a doSelect statement. What I am trying to do is get the [all] logical_name[s] from the Node table that have a certain MAC address.

My direct SQL query is pretty straightforward,
SELECT Logical_Name FROM NODEM1
where ADDLMACADDRESS IS NOT NULL and ADDLMACADDRESS LIKE '464A9D075E1%'
and I get a result in about a second.

My doSelect statement is as follows,
var mac_address="3464A9D075E1";
var nrecord = new SCFile("node");
var b=nrecord.doSelect("addlMacAddress~=NULL and addlMacAddress#\"" mac_address "\"");
if ( b == RC_SUCCESS )
{
print("Logical Name: ",nrecord.logical_name);
}
and I get a result in about 10 minutes everytime.

(note1, my mac_address won't be hardcoded and the print will be replaced by some simple business logic once I can improve performance.)
(note2, my node table has about 250,000 records, 100,000 with the addlMacAddress field populate with at least one value)
(note3, addlMacAddress is a text field)

Does anyone have an idea on how to get the performance of the doSelect to be the same/similar to directly SQL query? About 1 second compared to 10 minutes is a large difference!

Thanks for any tips!
Andy-
  • @Andy_HPSM Hi, 

    try like bellow 

    var nrecord = new SCFile('node',SCFILE_READONLY);
    nrecord.setFields(['addlMacAddress']);
    var mac_addresst  =  '3464A9D075E1';
    var sqlClause = 'SELECT logical.name FROM node WHERE addlmacaddress LIKE ';
    sqlClause = sqlClause "\"" mac_addresst "\"";   
    if (nrecord.doSelect(sqlClause)==0) print(nrecord);

    You don't need worry about null values doSelect method will return non 0 (RC_SUCCESS) value if record dosen't exist; 
    I'm not sure about performance in your environment. If it's dosen't help I'm suggesting do debugging for db queries and open case for the HP support.