Hi, would you expect that there were a performance difference in running a search with a filter like this?:

new nlobjSearchFilter('internalid', 'custcol_related_record', 'anyof', ['1234','...','...'])

compared to:

new nlobjSearchFilter('custcol_related_record', null, 'anyof', ['1234','...','...'])

It is a transaction search on invoice-rows and there are over 25 million lines (including head/tax/etc) The join is to a custom record with over 15 million records. Which one would you expect to be faster?

i would normally say

new nlobjSearchFilter('custcol_related_record', null, 'anyof', ['1234','...','...'])

, the way you present it means i guess

new nlobjSearchFilter('internalid', 'custcol_related_record', 'anyof', ['1234','...','...'])

haha, I thought I was sneaky... 🙂 Yes, in this case

new nlobjSearchFilter('internalid', 'custcol_related_record', 'anyof', ['1234','...','...'])

works, while the other one times out/takes around 3-4 minutes in a scheduled script.

my guess here is that internalid is indexed, custcol_related_record is not

hmm, I see.

keep in mind that i would ignore a lot of what i say about databases

haha ok. But it does sound pretty logical. In the transaction table, the field custcol_related_record which would hold the internalid is not indexed. But the sames value is indexed in the custrecord_related_record-table.

When you look at the schema that is presented over ODBC, it suggests there’s a foreign key between the custom record ID and any referencing table. That (to me) suggests that it is indexed, as unindexed foreign keys are baaaad. Though that’s assuming that what we see over ODBC is the physical structure and not just some logical representation (which I suspect it is, due to how fine grained the permissions can be on said data)