• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 47
  • Last Modified:

need help with access query

I have two tables:
one table is a system table, huge, let say customer database, let's call  SystemInfo
and another one is small, let's call it Input

the structure of SystemInfo:
Rec#
FName
Lname
code1
code2
code3
the structure of Input
Fullname
phone

i need to connect two tables thru Phone field.
1&code1&code2&code3 from SystemInfo and phone from Input
0
rfedorov
Asked:
rfedorov
3 Solutions
 
Fabrice LambertFabrice LambertCommented:
Maybe the following:
SELECT *
FROM SystemInfo, Input
WHERE (SystemInfo.code1 & SystemInfo.code2 & SystemInfo.code3) = Input.phone;

Open in new window

0
 
rfedorovAuthor Commented:
thank you, it takes soooooooooo much time to run!!! so the tables not linked by fields, i mean there is no inner join between tables?
0
 
PatHartmanCommented:
Modify the small table to break the phone into three fields.  Then you can use a join rather than a Cartesian Product.  You might need to create an index on the three fields in each table to speed up the join even more.
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can use an INNER JOIN but it won't be because of that, that will run faster:
SELECT *
FROM SystemInfo
    INNER JOIN Input ON (SystemInfo.code1 & SystemInfo.code2 & SystemInfo.code3) = Input.phone;

Open in new window

The problem is the engine is loosing time concatenating 3 fields and then comparing it one by one with the Phone field. And it will do it for each of the records from the SystemInfo table that as you said, is a very big table.
0
 
PatHartmanCommented:
That is why I said to actually MODIFY the small table to separate the columns and surely an index on the large table would help the query engine to optimize the join.
0
 
John TsioumprisSoftware & Systems EngineerCommented:
You can always concatenate code1,code2,code3 and create a temp table and then join this temp table with the input table...the time of creating the temp table and then joining should be faster...
0
 
PatHartmanCommented:
Why would you modify the large table rather than the small one?
0
 
rfedorovAuthor Commented:
thank you all
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now