Slow doSelect performance
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 '%3464A9D075E1%'
and I get a result in about a second.
My doSelect statement is as follows,
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!
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.