performance - Access 2007 user-defined function over join slow -

why query using user-defined function on query several joins including outer joins slow? reason function modify string sorts numerically. sorting string means 100 < 99. function reformats 99 099. so, 099 < 100. lets other non-numeric values remain unchanged.

the problem query uses function on query joins. takes 27 seconds return 100 rows. same query, function, on 1 table takes subsecond. sql-replacement query on query joins subsecond. query without function on query joins subsecond. primary table tbltests 517 rows. column function operates on text column fldpurity.

tbltests fldtestsid    autonumber fldpurity     text.  field size 50. indexed (duplicates ok). 0 length no.  

here function code. notice different inputs.

public function sortablepercent(byval pvar variant) string '------------------------------------------------------------------ ' purpose:   formats string may contain numbers or text values. '            string percent may contain % or + characters. ignore '            characters during comparison. string may start numeric '            characters, end alpha characters. compute length of resulting '            numeric characters. length 3 100, no change. prepend leading zeros length 2 '            or 1 numerics. not add prepend values starting text.  ' coded by:  2013-08-05 henry helgen ' arguments: pvar: string formatted. ' test:   debug (immediate) window: '            x = "97+%" '            ? sortablepercent(x) '            097 '            x = "98" '            ? sortablepercent(x) '            098 '            x = "99.9" '            ? sortablepercent(x) '            099.9 '            x = "100" '            print sortablepercent(x) '            100 '            x = "reagent grade" '            ? sortablepercent(x) '            reagent grade '            x = "85% & 15% h2o" '            ? sortablepercent(x) '            085 & 15 h2o '------------------------------------------------------------------ dim strhold string 'working string dim lennum  integer 'length of leading integer portion of number      ' remove whitespace, %, + characters     strhold = replace(replace(nz(trim(pvar), ""), "%", ""), "+", "")      if isnumeric(strhold) 'the entire string numeric         lennum = len(cstr(int(strhold)))         'fill leading zeros         strhold = switch(lennum = 3, strhold, lennum = 2, "0" & strhold, lennum = 1, "00" & strhold)     elseif isnumeric(left(strhold, 2)) '         strhold = "0" & strhold     elseif isnumeric(left(strhold, 1))         strhold = "00" & strhold     end if 'numeric      sortablepercent = strhold  end function 

here slow query using function on query joins (27 seconds)

select parm_testconcatreferencedatasetexposure.fldpurity,         sortablepercent([fldpurity]) temp2, count(*) rcdcount parm_testconcatreferencedatasetexposure group parm_testconcatreferencedatasetexposure.fldpurity,           sortablepercent([fldpurity]) order sortablepercent([fldpurity]); 

here fast query using function on 1 table (<1 second)

select tbltests.fldpurity,         sortablepercent([fldpurity]) temp2,         count(*) rcdcount tbltests group tbltests.fldpurity, sortablepercent([fldpurity]) order sortablepercent([fldpurity]); 

here fast query without function on query joins (<1 second)

select parm_testconcatreferencedatasetexposure.fldpurity,         count(*) rcdcount parm_testconcatreferencedatasetexposure group parm_testconcatreferencedatasetexposure.fldpurity; 

here fast query using sql partial approximation of function on 1 table (<1 second)

select tbltests.fldpurity,         iif(isnumeric(replace(replace(nz(trim([fldpurity]),""),"%",""),"+","")),cdbl(replace(replace(trim([fldpurity]),"%",""),"+","")),trim([fldpurity])) temppurity,         count(*) rcdcount tbltests group tbltests.fldpurity order iif(isnumeric(replace(replace(nz(trim([fldpurity]),""),"%",""),"+","")),cdbl(replace(replace(trim([fldpurity]),"%",""),"+","")),trim([fldpurity])); 

here query joins

select q_test.fldtestsid, q_datasettreatment.flddatasetsid,         q_datasettreatment.fldexposureeffectsid, q_test.fldvalidated,         q_test.fldpollutantid, q_test.fldpollutantname, q_test.fldpollutantcas,         q_test.fldmodeofactionid, q_test.fldmodeofaction, q_test.fldpollutanttypeid,         q_test.fldpollutanttype, q_test.fldspeciesid, q_test.fldclass, q_test.fldgenus,        q_test.fldspecies, q_test.fldcommonname, q_test.fldtesttypeid,         q_test.fldtesttype, q_test.fldtechniqueid, q_test.fldtechnique,        q_test.fldconcunits, q_test.flddescription fldconcunitdescription,        q_test.fldmrid, q_test.fldcetisid, q_test.fldhardness, q_test.fldsalinity,         q_test.fldph, q_test.fldtemperature, q_test.fldpurity, q_test.flddo,         q_test.fldacute, q_test.flduser, q_test.fldcomments,         iif([q_sumtestreference].[fldtestsid] not null,[concatref],"") combinedref,         q_datasettreatment.fldbiolvarnameid, q_datasettreatment.fldbiolvarname,         q_datasettreatment.fldlifestageid, q_datasettreatment.fldlifestage,         q_datasettreatment.flddatatypeid, q_datasettreatment.flddatatype,         q_datasettreatment.fldgenerationid, q_datasettreatment.fldgeneration,         q_datasettreatment.fldeffecttypeid, q_datasettreatment.fldeffecttype,         q_datasettreatment.flddurationdays, q_datasettreatment.fldbvunits,         q_datasettreatment.flddescription fldbvunitdescription,         q_datasettreatment.fldreportednoec, q_datasettreatment.fldreportedloec,         q_datasettreatment.fldtreatmentnum, q_datasettreatment.fldcontroltypeid,         q_datasettreatment.fldcontroltype, q_datasettreatment.fldreplicatenum,         q_datasettreatment.fldpseudoreplicatenum, q_datasettreatment.fldnumberexposed,        q_datasettreatment.fldmeasuredconcentration,         q_datasettreatment.fldnominalconcentration, q_datasettreatment.fldbiolvarvalue q_sumtestreference  right join (q_test              left join q_datasettreatment                     on q_test.fldtestsid = q_datasettreatment.fldtestsid)          on q_sumtestreference.fldtestsid = q_test.fldtestsid order q_test.fldtestsid, q_datasettreatment.flddatasetsid,          q_datasettreatment.fldtreatmentnum, q_datasettreatment.fldreplicatenum; 

i use function simpler, cleaner code. suggestions? see in this post on sql server user-defined functions evaluates row row. mean complex parsing statement in sql query 4th example correct?

@hansup. thanks, worked. here working query.

select parm_testconcatreferencedatasetexposure.fldpurity,        val(replace(replace(nz(trim([fldpurity]),""),"%",""),"+","")) temp5,         count(*) rcdcount parm_testconcatreferencedatasetexposure group parm_testconcatreferencedatasetexposure.fldpurity,           val(replace(replace(nz(trim([fldpurity]),""),"%",""),"+","")) order val(replace(replace(nz(trim([fldpurity]),""),"%",""),"+",""));