linq between 2 query

[zezo2021]

friends

I have 2 query
No shared field (available ) can use to link between the 2 query


I select 3 fields from each query and combine them
first name + last name + email

and link the 2 queries with this expression

the problem is

very slow

what is your suggestion?

--------------
The link is successfully working for linking but very very slow

Thank you so much

 

[GPGeorge]

friends

I have 2 query
No shared field (available ) can use to link between the 2 query


I select 3 fields from each query and combine them
first name + last name + email

and link the 2 queries with this expression

the problem is

very slow

what is your suggestion?

--------------
The link is successfully working for linking but very very slow

Thank you so much

How about the source tables on which the two intermediate queries are based? Any relationship between them?

 

[zezo2021]

No shared field between tables

 

[The_Doc_Man]

Assure that each of the three fields is indexed in both tables, even if not a unique index. That should help with the speed issue.

 

[zezo2021]

Assure that each of the three fields is indexed in both tables, even if not a unique index. That should help with the speed issue.

thank you

 

[The_Doc_Man]

Re-reading your question, it strikes me that you have a potential unique key - e-mail. Lots of game and business sites have you log in via e-mail plus a password, which means they consider e-mail as a decent identifier.

Unless you have people who share e-mail accounts, that e-mail account is usually unique. If you index the e-mail columns, you can probably do a formal INNER JOIN on that column, which should be very fast. It is worth checking whether you have anyone sharing e-mail accounts to see if you can use that fact to speed up your query. Even corporate e-mail accounts usually put the employee name or name/number to the left of the @-sign, which would mean you could already have an individual identifier for each person. Granted, that doesn't work if you have people who don't have any e-mail at all, in which case you would just have to continue with the three-field approach you described earlier. Just offering an idea that COULD be useful.

 

[Pat Hartman]

When you join tables in a query, you can have multiple join lines so you don't need to concatenate the three fields first.

Join first to first, last to last, email to email

Keep in mind that if any of the fields is null, no match will be found.

You can set the three fields as indexed, non unique to speed up the join.

 

[zezo2021]

When you join tables in a query, you can have multiple join lines so you don't need to concatenate the three fields first.

Join first to first, last to last, email to email

Keep in mind that if any of the fields is null, no match will be found.

You can set the three fields as indexed, non unique to speed up the join.

good idea

 

[zezo2021]

if I convert the query to Snapshot

Is it is a good idea to speed the query?

 

[The_Doc_Man]

The different ways of opening a recordset work differently for different back-ends. If the back-end is a linked table in an SQL database, the various modes of opening the recordset make a big difference. If it is an Access back-end, the speed is not so strongly affected.

A "Snapshot" is actually probably a bit slower because it makes a copy of the records, which takes time. "Dynaset" is probably faster than either Snapshot or Dynamic. "Forward-only" might be inappropriate for your situation. You didn't indicate whether you needed to update anything, but if there is any updating to be done, Dynaset and Table are your best bets. On the other hand, Table recordsets must be one table at a time, so if there is a JOIN or UNION query involved, it might not work so well for you.