I really dont have any knowledge about pervasive database. We have one simple query which is running very slow (taking ages). I have created index on HiReqe (patientid,reqnum, reqdate), patient (patientid, patsex, patcob,patdob) and hitest (reqnum, testcode). Is there anything that i can do run query faster?
select a.reqnum, a.reqdate, b.patientid, b.patcob, b.patdob, b.patsex, c.testcode, c.normalresult from "HiReq" as a
inner join "Patient" as b on a.patientid = b.patientid and b.patsex = 'M'
inner join "Hitest" as c on a.reqnum = c.reqnum and c.testcode = '903'
select a.reqnum, a.reqdate, b.patientid, b.patcob, b.patdob, b.patsex, c.testcode, c.normalresult from "HiReq" as a
inner join "Patient" as b on a.patientid = b.patientid and b.patsex = 'M'
inner join "Hitest" as c on a.reqnum = c.reqnum and c.testcode = '903'