Super Contributor.. Andy_HPSM Super Contributor..
Super Contributor..
230 views

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 '%3464A9D075E1%'
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-
0 Likes
1 Reply
Outstanding Contributor.. exeptt Outstanding Contributor..
Outstanding Contributor..

Re: Slow doSelect performance

@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.  

 

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.