General information
Title:
Performance troubles
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")
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only