So the issue is that say the communications table is very large (which it is on a lot of production systems) and even though it is indexed using findrecord is slow as its does a select *
when i really only need a few fields.
I want to use FindRecord as it applies the security SQL so using CreateQueryObj is not a runner.
Is there some hidden way to do this (i cant see anything in the documentation)
AND/OR
specify the top x rows
OR
request only a specific set ...eg rows 11-20
To provide some context this query
select comm_communicationid
from vCommunication
where cmli_comm_companyid=846 and comm_private is null
order by comm_datetime
takes around 15 seconds in sql querymgr and returns 4583 rows with all the data
but
select comm_communicationid, comm_subject, Comm_Email, Comm_Note
from vCommunication
where cmli_comm_companyid=846 and comm_private is null
order by comm_datetime
takes about a second.