Unique by email - need for speed

[rivet]

I have a large file (currently 90k records) the gets added to each week (~9k records ) On import I am checking to see if the email address is the first occurrence in the database.

Currently this is calculated via a self relationship. email = email AND recordID > recordID

This way if the relationship show any records with a low ID and the same email the uniqueness is false. The only problem is an import can take 3-5hours (run on the server)

Can anyone suggest a faster method.

[jdu98a]

Clarify. What specifically is your purpose in checking to see if the imported record is the first occurrence? If you are checking for uniqueness to avoid the creation of multiple records with the same email address, you could just use "Update matching records" in your import dialogue and then have it match up email addresses.

[rivet]

these are all entries for a contest that could win, but we want to get the stats on the number of unique entries.

[jdu98a]

Gotcha. Try this.

Create a self relationship for your entries table:

Name: [color:gray]Self_Dup_Check

Relationship: [color:gray][entry_table]::email = [entry_table]::email

Next create a number calculation field:

Name: [color:gray]EmailDups

Formula: [color:gray]Count([self_Dup_Check]::email

Then create an auto-enter number field with the following calculation:

Name: [color:gray]IsUnique

Formula: [color:gray]If(EmailDups = 1; 1; "")

(be sure to check "Do not replace existing value")

On import this auto-enter field will enter a "1" if the email that is entered is unique in the database. Otherwise it will leave the field blank.

Finally, you'll just need a summary field that will give you the total of [color:gray]IsUnique for whatever found set you have. NOTICE: I would strongly suggest not placing this summary field on a layout that you regularly use, otherwise it will try to do its summary every time the layout loads.

This shouldn't slow down your import much at all. I've run databases with calculations like this one happening during imports of up to 40k records at a time. The imports never took more than a minute or two. Hope this helps!

-----------

as an afterthought - you could actually bypass the [color:gray]EmailDups all together if you preferred. You could just have the auto-enter calc look like this:

[color:gray]If(Count([self_Dup_Check]::email = 1; 1; "")

Personally, I would keep it in it's own separate calculation field though, just in case I wanted to utilize that "count" formula somewhere else.

Edited May 5, 2010 by Guest