Query hangs technical problem

[knowledge76]

Hello Friends,

I have a query which search the matches of two table (Tale A has ~ 4000 record and Table B ~ 60000 reords).
Now when I first run the query after creating it, it is executed within 2 seconds but after half hour or so
the query takes two much time and hangs.
My assumption is that the problem is not linked with the no. of records and
field settings in the two tables? Could anyone tell me why it is so? How can I remove this issue?
Thanks for your proposals.

 

[MSAccessRookie]

Hello Friends,

I have a query which search the matches of two table (Tale A has ~ 4000 record and Table B ~ 60000 reords).
Now when I first run the query after creating it, it is executed within 2 seconds but after half hour or so
the query takes two much time and hangs.
My assumption is that the problem is not linked with the no. of records and
field settings in the two tables? Could anyone tell me why it is so? How can I remove this issue?
Thanks for your proposals.

One possibility is that you have a Cartesian Loop scenario here. If your tables are not properly joined, then (60,000 * 4000) or 240,000,000 records will have too be sorted through in order to determine the proper result set. As you can imagine, this could take a while.

I assume your query has a proper structure, but here as a generic structure for you to compare it to just in case:

[B][COLOR=royalblue]SELECT[/COLOR] {[COLOR=seagreen]Table1[/COLOR].Field1, [COLOR=seagreen]Table1[/COLOR].Field2, [COLOR=seagreen]Table2[/COLOR].Field3, [COLOR=seagreen]Table2[/COLOR].Field4[/B]
[B][COLOR=royalblue]FROM[/COLOR] [COLOR=seagreen]Table1[/COLOR] [/B]
[B][COLOR=royalblue]INNER JOIN[/COLOR] [COLOR=seagreen]Table2[/COLOR] [/B]
[B][COLOR=royalblue]ON[/COLOR] [COLOR=seagreen]Table1[/COLOR].{Whatever Joins the tables} = [COLOR=seagreen]Table2[/COLOR].{Whatever Joins the tables}[/B]

 

[neileg]

Could you post the SQL of your query?