I have decided that rather than copying code, or IFDEFing code I'm going to alter the email subsystem of my recruitment app so I can use it in other projects just by tweaking a few parameters. I'm starting with testing if an emails sender is on my whitelist. Currently this is carried out by a few Locate()s - I use tables a lot more than queries. I decided to see what I could come up with and here it is


SELECT
IF(EXISTS (SELECT _fkContacts FROM Career WHERE _EMail = :eddress)
THEN (SELECT _fkContacts FROM Career WHERE _EMail = :eddress)
ELSE
IF(EXISTS (SELECT _ID FROM Contacts WHERE _HomeEMail = :eddress OR _OtherEddresses IS NOT NULL AND POS(:eddress,_OtherEddresses) > 0)
THEN (SELECT _ID FROM Contacts WHERE _HomeEMail = :eddress OR _OtherEddresses IS NOT NULL AND POS(:eddress,_OtherEddresses) > 0)
ELSE (SELECT _fkContacts FROM Career WHERE _SecEMail = :eddress)
)) AS ContactID FROM EmptyTable

EmptyTable is a 1 row, 1 column table created specifically for these types of queries


Career has a 1 to many relationship with contacts, generally only one is current but I do have cases where a person is working for several companies simultaneously.

The logic is:

test for business email address first (Career table, varchar, indexed), if that doesn't exist test for main home email address (Contacts table, varchar, indexed), if that doesn't exist see if its in the list of alternative email addresses (Contacts table, clob, unindexed) and finally if still not found check for a secretary's email address (Career table, varchar, indexed).

I'm surprised at how quick it is (testing using f/s & 56 Mb WLAN) once its prepared but if anyone has a suggestion for speeding it up I'd be interested.


Roy Lambert

Roy Lambert wrote:

> I'm surprised at how quick it is (testing using f/s & 56 Mb WLAN)
> once its prepared but if anyone has a suggestion for speeding it up
> I'd be interested.

My response is of the "you want to get to London? Well, I wouldn't
start from here..." variety.

How about you have a table of email addresses. Your contact table has a
table ID into that (or NULL). Then you look up in the single table and
it is either there or not. You could get fancy and have a list of
emails per contact, and the email table could have columns for
indicating the type (work, home, assistant, whatever). For that, the
email table would have the "owner ID" of course, and perhaps an order.

--

Matthew Jones

Matthew

>My response is of the "you want to get to London? Well, I wouldn't
>start from here..." variety.
>
>How about you have a table of email addresses. Your contact table has a
>table ID into that (or NULL). Then you look up in the single table and
>it is either there or not. You could get fancy and have a list of
>emails per contact, and the email table could have columns for
>indicating the type (work, home, assistant, whatever). For that, the
>email table would have the "owner ID" of course, and perhaps an order.

Ah yes - the approved approach

One problem I've almost always encountered in doing things like that is that it will speed up one aspect at the expense of slowing down others and increasing complexity.

Doing it that way would certainly be faster for checking if the email is on file but:

a) displaying a contacts details would be more complex requiring two extra lookups/joins per contact (one for the main home email address and one for any other email addresses) and two extra lookups/joins per company in the career history (one for the contacts and one for the secretary). They could be reduced to one by displaying the type of email address as well as the address itself but that has the consequence of making it more difficult to read. Currently there are locations on the different pages of the contact information that eyeballs can point at and know what type of email it is (ok there are labels as well)

b) deleting the contact, or wiping company contact details once the contact has moved on would involve an additional table

c) mailmerge field selection gets a little more tricky

On the positive side it would make presenting a list and saying pick which one you want to send this email to a bit easier

Sorry you started me off on a rant, and I haven't even touched on the fact that my system also checks to see if its a staff member sending the email and I maintain a separate list of whitelisted email addresses and domains to allow things like newsfeeds through

Roy Lambert

Yes, there are implications. You can use JOINs to mitigage some, like
mailmerges, or you have to inner-loop on them too.

What you win in one place, you lose in another. 8-)

Take 2: How about a full text search on the email fields? I suspect
that you may get false positives, but narrowing them down to a sub-set
will make the detailed check a lot faster.

Take 3: Carry on as you are. Well done. 8-)

--

Matthew Jones

Matthew

>Yes, there are implications. You can use JOINs to mitigage some, like
>mailmerges, or you have to inner-loop on them too.
>
>What you win in one place, you lose in another. 8-)

I know - and I hate it

>Take 2: How about a full text search on the email fields? I suspect
>that you may get false positives, but narrowing them down to a sub-set
>will make the detailed check a lot faster.

If I could come up with a way to combine all the different fields/tables to do a single full text index I think I'd go that way. Maybe at some point we'll get UDI (that's user defined indices).

>Take 3: Carry on as you are. Well done. 8-)

I started from a "this is how I'd do it in Delphi" perspective and tried the same in SQL. By comparison using joins takes between 10x & 100x longer. I just wish it was more elegant (having to use a dummy table - yuch) and faster

Roy Lambert

Roy,

<< I started from a "this is how I'd do it in Delphi" perspective and tried the same in SQL. By comparison using joins takes between 10x & 100x longer. I just wish it was more elegant (having to use a dummy table - yuch) and faster >>

It's slow because of the slow WLAN and the direct access that requires pulling all of the data across the wire to execute the SQL.  Slap an ElevateDB Server on the machine where the database resides, and use that instead.

Tim Young
Elevate Software
www.elevatesoft.com

Tim


Tried with WLAN, fixed LAN, local, with f/s & c/s

In all cases the nested IF statement was faster than JOINs. I'm not complaining, I'm actually impressed.

In 3 out of the 4 cases in the IF statement a single row is being accessed by an index. The final case is still pretty good since not that many contacts on the database have multiple email addresses so not that much checking to do.

I use WLAN for testing because I want the worst case scenario - that way things can only improve in live running <vbg>

Roy Lambert

Roy,

<< Tried with WLAN, fixed LAN, local, with f/s & c/s

In all cases the nested IF statement was faster than JOINs. I'm not complaining, I'm actually impressed. >>

Yeah, that's because the nested IF, if you're generating a sensitive result set, is going to only execute the sub-queries in the SELECT portion on-demand.  In other words, if you've got 10 rows in a grid, then it's only going to execute one or more of the sub-queries 10 times (initially).

Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Yeah, that's because the nested IF, if you're generating a sensitive result set, is going to only execute the sub-queries in the SELECT portion on-demand. In other words, if you've got 10 rows in a grid, then it's only going to execute one or more of the sub-queries 10 times (initially).

Not sure I follow what you're saying here. The final result is non-sensitive, the tables involved in the sub-queries aren't bound to any controls, the target table is a single row, single column table also not bound to any controls.

Roy Lambert

Enquiring minds have to know - but they're also a right royal pain in the bum.