Tim

While messing yesterday I did a test between using an external module and using sql.

The sql was SELECT * FROM Sites WHERE REPLACE(' ','',_Switchboard) = REPLACE(' ','','01293 587369')

This took c .03 secs

The external module

function SpacelessCompare(const FldStr, CompStr: string): boolean;
begin
Result := StringReplace(FldStr, ' ', '', [rfReplaceAll]) = StringReplace(CompStr, ' ', '', [rfReplaceAll]);
end;

SELECT * FROM Sites WHERE SpacelessCompare(_Switchboard,'01293 587369')

took c.3 secs ie 10 times longer

Sites has 9327 rows and an index on _Switchboard so I'm guessing that its the index that make the difference - index scan vs row scan. Is that a good guess?

Roy Lambert

Roy,

<< took c.3 secs ie 10 times longer

Sites has 9327 rows and an index on _Switchboard so I'm guessing that its
the index that make the difference - index scan vs row scan. Is that a good
guess? >>

No, the time is most likely simply down to the shuttling of the parameters
back and forth to the external module.  There's a bit of overhead associated
with this, which is why we recommend only using external modules for
functions/procedures that aren't going to be called frequently, and using
SQL/PSM for functions that will be called frequently.

If you run both queries with an execution plan, you'll see that both use row
scans.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>If you run both queries with an execution plan, you'll see that both use row
>scans.

Should have thought of that

Roy Lambert