Trying to speed up query without sacrificing inner join

[johnmerlino]

Hey all,

I had a query taking over half hour to run. It was too long. So I cleaned up data so I could use this, which only takes less than 20 seconds to run:

SELECT possibles.fullname, uniSelectedContacts.addresses, uniSelectedContacts.cities, uniSelectedContacts.us_states_and_canada, uniSelectedContacts.zip_codes INTO PrepareForDuplicateCheckFROM uniSelectedContacts INNER JOIN possibles ON uniSelectedContacts.TheName = possibles.fullname;

There's a problem with this query. There are numerous cases where possibles.fullname will not have a middle initial but uniSelectedContacts.TheName will. Because of that, those specific records are not being pulled. Without extracting the middle_initial into its own column in uniSelectedContacts, which will be a tedious task, is there a way to increase accuracy of returned data while still preserving time efficiency?

Thanks for response.

 

[vbaInet]

I don't follow what the problem is. Could you please explain differently, maybe with some examples?

 

[Banana]

Given that it's just a simple inner join query, I'm thinking that you probably need to add index to uniSelectedContacts.TheName and possibles.fullname (I'm assuming those two columns belong to a table, and not to a query. If either or both are actually a query, then we'd need to see the SQL for those since this may be actually the one making trouble.

Also, what are those actually? The fact that you have a "fullname" that contains composite data (first, middle and last name) isn't normalized so that also impacts the performance but maybe you're just importing and sanitizing the data?

Finally, do you actually need to do a SELECT...INTO? Typically it's preferable to do a INSERT INTO into a pre-existing table with the same structure and if you need to, clear it out before running this.

HTH.

 

[johnmerlino]

Thanks for responses. For example:
Heller,Gary (this had a middle initial in uniSelectedContacts but not in possibles)
Auerbach,Allen (this had a middle initial of sanders in uniSelectedContacts but not in possibles)
PRICE,LISA (had a middle initial of butler in uniSelectedContacts but didn't have it in possibles)

Hence, none of these names were returned. I'm trying to find a way to be able to capture these names when they have a middle initial of sanders in uniSelectedContacts but only have a middle initial of s or no middle initial at all in possibles.

Thanks for response.

 

[Brianwarnock]

John I think that you need to bite the bullet and parse the names into 3 columns Lastname, Firstname, Therest (ie middle initial(s) or name(s)). If you don't do it in the Tables you are going to have to do it on the fly in the queries.

Matching on names can be a long tedious multi pass job, when I had to combine dbS some 7 years ago I ened up with a long list to visually check.
Remember that your computer is a high speed pedantic moron, it wont spot extra spaces or spelling mistakes.

Does everybody have a first name, or do some have just a first initial? I ask since there is lack of consistency for the middle "initial".

Is there a secondary check such as the first line of Address?

Brian

 

[johnmerlino]

first name and last name will always be present. Middle initial may be in one but not in another or it may be spelled out in one (e.g. Joan) and initial only given in another (e.g. J).

 

[Brianwarnock]

John you seem reluctant to answer all of the questions asked.
I believe that you are going to have to parse on the fly using
eg
firstname: Left([txtname],InStr([txtname],",")-1)
lastname: Mid([txtname],InStrRev([txtname],",")+1)

where txtname is the name of your names fields


Brian

 

[johnmerlino]

Those appear to be visual basic functions. So I presume I can't incorporate them into the query itself. And if not, would I have to iterate through the records in vba and then do something with what you posted there?

Thanks for response.

 

[Galaxiom]

Sometime searching for plausible matches in names can benefit from looking for loose similarities rather than exact matches.
Using something like Soundex might even be worthwhile.

http://allenbrowne.com/vba-Soundex.html

 

[John Big Booty]

Those appear to be visual basic functions. So I presume I can't incorporate them into the query itself. And if not, would I have to iterate through the records in vba and then do something with what you posted there?

Thanks for response.

The code posted by Brianwarnock can be used directly in your query, you can copy and paste it directly into the Field row of a blank column in your query design grid. Making the changes noted by Brianwarnock.

 

[johnmerlino]

Thanks for responses. I added BrianWornock's suggestion to two empty fields in Design View and for Criteria I put the two functions. One thing is though that there's two names I am comparing: TheName of uniSelectedContacts and fullname of possibles. So basically I just want to check if middle initials exist in either and if do then compare to see if the first character of the middle names match. The other thing is when I run the suggested query, I get "Enter Parameter Value possibles.firstname" message.

This is what is in sql:

SELECT possibles.fullname, uniSelectedContacts.addresses, uniSelectedContacts.cities, uniSelectedContacts.us_states_and_canada, uniSelectedContacts.zip_codes, possibles.[firstname] AS Expr1, possibles.[lastname] AS Expr2 INTO PrepareForDuplicateCheck
FROM uniSelectedContacts INNER JOIN possibles ON uniSelectedContacts.TheName = possibles.fullname
WHERE (((possibles.firstname)=Left([fullname],InStr([fullname],",")-1)) And ((possibles.lastname)=Mid([fullname],InStrRev([fullname],",")+1)));

Maybe a better solution is to remove middle name altogether from both on the fly? Whatever solution, I want to preserve INNER JOIN because I don't want to have to wait 45 minutes going through 800,000 records.

Thanks for response.

 

[Brianwarnock]

Obviously you need to parse both fields so that it becomes

Where Left([fullname],InStr([fullname],",")-1)= Left([TheName],InStr([fullname],",")-1) and   Mid([fullname],InStrRev([fullname],",")+1)) = Mid([TheName],InStrRev([fullname],",")+1))

You do not need to Select these alaises uncheck the show.

So basically I just want to check if middle initials exist in either and if do then compare to see if the first character of the middle names match

I don't recall you asking for this before, but you can use nested Instr and Mid functions to find this.

Brian