>We had low performance with a sql-statement. The sql took 5 seconds to get one record (about 10 tables). When we replaced the '=' with > .. < (between) performance increased to 0.5 second!!!!!!!!!!!!!
>Does anybody has a logic explanation?!?!?!?!?!?!?!?!????
>
>Here is the slow SQL (5 seconds):
>
>var = sqlexec(nhandle, ;
>"SELECT B_org.ORGID, M_internet.INTOMS AS email,"+;
>" M_adres.ADRSTRPB AS straat, M_adres.ADRHSPBNR AS huisnr,"+;
>" M_adres.ADRHSPBPC AS postcode, M_adres_a.ADRSTRPB AS postbus,"+;
>" M_adres_a.ADRHSPBNR AS postbusnr, M_adres_a.ADRHSPBPC AS postcode_pb,"+;
>" M_telefonie.TELOMS AS telefoon, M_internet_a.INTOMS AS site,"+;
>" M_internet_b.INTOMS AS ftp, M_telefonie_a.TELOMS AS telefax,"+;
>" B_org.ORGZOEKNM AS zoeknaam, B_org.ORGNAAM AS naam,"+;
>" B_org.ORGSBIHFDCD AS sbi_hoofd, B_org.ORGSBINEVCD AS sbi_neven,"+;
>" B_org.ORGMEMO AS memo, M_adres_a.ADRLANID AS lanid_pb,"+;
>" M_adres.ADRLANID AS lanid, M_adres_b.ADRSTRPB AS strpb_fac,"+;
>" M_adres_b.ADRHSPBNR AS hspbnr_fac, M_adres_b.ADRHSPBPC AS hspbpc_fac,"+;
>" M_adres_b.ADRLANID AS landid_fac"+;
>" FROM dbo.B_ORG B_org, dbo.M_ADRES M_adres,"+;
>" dbo.M_TELEFONIE M_telefonie, dbo.M_INTERNET M_internet,"+;
>" dbo.M_INTERNET M_internet_a, dbo.M_INTERNET M_internet_b,"+;
>" dbo.M_ADRES M_adres_a, dbo.M_TELEFONIE M_telefonie_a,"+;
>" dbo.S_TAAL S_taal, dbo.M_ADRES M_adres_b, dbo.S_VORM S_vorm"+;
>" WHERE B_org.ORGADRIDB = M_adres.ADRID"+;
>" AND B_org.ORGTELIDT = M_telefonie.TELID"+;
>" AND B_org.ORGINTIDE = M_internet.INTID"+;
>" AND B_org.ORGINTIDU = M_internet_a.INTID"+;
>" AND B_org.ORGINTIDF = M_internet_b.INTID"+;
>" AND B_org.ORGADRIDP = M_adres_a.ADRID"+;
>" AND B_org.ORGTELIDF = M_telefonie_a.TELID"+;
>" AND B_org.ORGTALID = S_taal.TALID"+;
>" AND B_org.ORGADRIDF = M_adres_b.ADRID"+;
>" AND B_org.ORGVRMID = S_vorm.VRMID"+;
>" AND B_org.ORGID = 1"+;
>" ORDER BY B_org.ORGID",;
>"crsOrg")
>
>
>
>Here is the fast SQL (0.5 seconds):
>
>var = sqlexec(nhandle, ;
>"SELECT B_org.ORGID, M_internet.INTOMS AS email,"+;
>" M_adres.ADRSTRPB AS straat, M_adres.ADRHSPBNR AS huisnr,"+;
>" M_adres.ADRHSPBPC AS postcode, M_adres_a.ADRSTRPB AS postbus,"+;
>" M_adres_a.ADRHSPBNR AS postbusnr, M_adres_a.ADRHSPBPC AS postcode_pb,"+;
>" M_telefonie.TELOMS AS telefoon, M_internet_a.INTOMS AS site,"+;
>" M_internet_b.INTOMS AS ftp, M_telefonie_a.TELOMS AS telefax,"+;
>" B_org.ORGZOEKNM AS zoeknaam, B_org.ORGNAAM AS naam,"+;
>" B_org.ORGSBIHFDCD AS sbi_hoofd, B_org.ORGSBINEVCD AS sbi_neven,"+;
>" B_org.ORGMEMO AS memo, M_adres_a.ADRLANID AS lanid_pb,"+;
>" M_adres.ADRLANID AS lanid, M_adres_b.ADRSTRPB AS strpb_fac,"+;
>" M_adres_b.ADRHSPBNR AS hspbnr_fac, M_adres_b.ADRHSPBPC AS hspbpc_fac,"+;
>" M_adres_b.ADRLANID AS landid_fac"+;
>" FROM dbo.B_ORG B_org, dbo.M_ADRES M_adres,"+;
>" dbo.M_TELEFONIE M_telefonie, dbo.M_INTERNET M_internet,"+;
>" dbo.M_INTERNET M_internet_a, dbo.M_INTERNET M_internet_b,"+;
>" dbo.M_ADRES M_adres_a, dbo.M_TELEFONIE M_telefonie_a,"+;
>" dbo.S_TAAL S_taal, dbo.M_ADRES M_adres_b, dbo.S_VORM S_vorm"+;
>" WHERE ( B_org.ORGADRIDB < M_adres.ADRID + 1 AND B_org.ORGADRIDB > M_adres.ADRID - 1 )"+;
>" AND (B_org.ORGTELIDT < M_telefonie.TELID + 1 AND B_org.ORGTELIDT > M_telefonie.TELID - 1 )"+;
>" AND (B_org.ORGINTIDE < M_internet.INTID + 1 AND B_org.ORGINTIDE > M_internet.INTID - 1 )"+;
>" AND (B_org.ORGINTIDU < M_internet_a.INTID + 1 AND B_org.ORGINTIDU > M_internet_a.INTID - 1 )"+;
>" AND (B_org.ORGINTIDF < M_internet_b.INTID + 1 AND B_org.ORGINTIDF > M_internet_b.INTID - 1 )"+;
>" AND (B_org.ORGADRIDP < M_adres_a.ADRID + 1 AND B_org.ORGADRIDP > M_adres_a.ADRID - 1 )"+;
>" AND (B_org.ORGTELIDF < M_telefonie_a.TELID + 1 AND B_org.ORGTELIDF > M_telefonie_a.TELID - 1 )"+;
>" AND (B_org.ORGTALID < S_taal.TALID + 1 AND B_org.ORGTALID > S_taal.TALID - 1 )"+;
>" AND (B_org.ORGADRIDF < M_adres_b.ADRID + 1 AND B_org.ORGADRIDF > M_adres_b.ADRID - 1 )"+;
>" AND (B_org.ORGVRMID < S_vorm.VRMID + 1 AND B_org.ORGVRMID > S_vorm.VRMID - 1 )"+;
>" AND ( B_org.ORGID > 0 AND B_org.ORGID < 2 )"+;
>" ORDER BY B_org.ORGID",;
>"crsOrg")

I am not sure, but do you have indices on all fields? (Maybe when you changed the code style you didn´t allow the SQL engine to optimize...) Did you try using the new JOIN style? Which data server are you using?