A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

JOIN Query very slow



 
 
Thread Tools Display Modes
  #1  
Old June 4th, 2010, 12:45 PM posted to microsoft.public.access.queries
Tally.NET
external usenet poster
 
Posts: 2
Default JOIN Query very slow

Hello,

I have the following Input tables

Table1
Serial Item Date SerialandDate
123 Item1 01-04-2007 12339173
123 Item2 01-06-2010 12340330
234 Item1 01-10-2006 23438991
234 Item3 02-04-2010 23440270
234 Item2 01-01-2009 23439814


Table2
Serial Date SerialandDate
123 01-06-2010 12340330
234 02-04-2010 23440270


and i am looking at an Output as below

Serial Item BillDate
123 Item2 01-06-2010
234 Item3 02-04-2010



I am trying this SQL (MS Access 2003) however the query is taking too long to process and the system does not respond.

SELECT Table2.[Serial], Table2.[BillDate], Table1.Item
FROM Table1 INNER JOIN Table2 ON Table1.SerialandDate = Table2.SerialandDate;

There are about 25000 records in both the tables.


ITCat

  #2  
Old June 4th, 2010, 02:19 PM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default JOIN Query very slow

Tally.NET wrote:
I have the following Input tables

Table1
Serial Item Date SerialandDate
123 Item1 01-04-2007 12339173
123 Item2 01-06-2010 12340330
234 Item1 01-10-2006 23438991
234 Item3 02-04-2010 23440270
234 Item2 01-01-2009 23439814


Table2
Serial Date SerialandDate
123 01-06-2010 12340330
234 02-04-2010 23440270


and i am looking at an Output as below

Serial Item BillDate
123 Item2 01-06-2010
234 Item3 02-04-2010



I am trying this SQL (MS Access 2003) however the query is taking too long to process and the system does not respond.

SELECT Table2.[Serial], Table2.[BillDate], Table1.Item
FROM Table1 INNER JOIN Table2 ON Table1.SerialandDate = Table2.SerialandDate;

There are about 25000 records in both the tables.



The most important thing you can do is to make sure that you
have an index on the fields using in the ON clause. You do
that in the tables' design view by using the View - Indexes
menu item.

--
Marsh
MVP [MS Access]
  #3  
Old June 4th, 2010, 04:18 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default JOIN Query very slow

If that is the whole data you have and if the query is not instantaneous (human perception), I suspect something is wrong either with the file itself (try to compact the db? if the same problem, try to copy the file somewhere else on the hard disk and try on this copy? ) either the data is on another PC and the connection is very poor, either the PC itself is damaged (Registry, or hard disk, ...). Try on another PC, if possible, to see if the problem is the same. If there is no problem on that other PC, then it would be a good indication that Access itself is not directly responsible (unless its installation has been compromised).

Vanderghast, Access MVP



"Tally.NET" wrote in message ...
Hello,

I have the following Input tables

Table1
Serial Item Date SerialandDate
123 Item1 01-04-2007 12339173
123 Item2 01-06-2010 12340330
234 Item1 01-10-2006 23438991
234 Item3 02-04-2010 23440270
234 Item2 01-01-2009 23439814


Table2
Serial Date SerialandDate
123 01-06-2010 12340330
234 02-04-2010 23440270


and i am looking at an Output as below

Serial Item BillDate
123 Item2 01-06-2010
234 Item3 02-04-2010



I am trying this SQL (MS Access 2003) however the query is taking too long to process and the system does not respond.

SELECT Table2.[Serial], Table2.[BillDate], Table1.Item
FROM Table1 INNER JOIN Table2 ON Table1.SerialandDate = Table2.SerialandDate;

There are about 25000 records in both the tables.


ITCat

  #4  
Old June 6th, 2010, 08:38 AM posted to microsoft.public.access.queries
Tally.NET
external usenet poster
 
Posts: 2
Default JOIN Query very slow

Oops! i said they were tables, sorry they were Queries. I converted them to
tables and now its working fine.

I'm not sure what caused the slowdown.

Thanks for the inputs.
Praveen

"Marshall Barton" wrote in message
...
Tally.NET wrote:
I have the following Input tables

Table1
Serial Item Date SerialandDate
123 Item1 01-04-2007 12339173
123 Item2 01-06-2010 12340330
234 Item1 01-10-2006 23438991
234 Item3 02-04-2010 23440270
234 Item2 01-01-2009 23439814


Table2
Serial Date SerialandDate
123 01-06-2010 12340330
234 02-04-2010 23440270


and i am looking at an Output as below

Serial Item BillDate
123 Item2 01-06-2010
234 Item3 02-04-2010



I am trying this SQL (MS Access 2003) however the query is taking too long
to process and the system does not respond.

SELECT Table2.[Serial], Table2.[BillDate], Table1.Item
FROM Table1 INNER JOIN Table2 ON Table1.SerialandDate =
Table2.SerialandDate;

There are about 25000 records in both the tables.



The most important thing you can do is to make sure that you
have an index on the fields using in the ON clause. You do
that in the tables' design view by using the View - Indexes
menu item.

--
Marsh
MVP [MS Access]


  #5  
Old June 6th, 2010, 09:07 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default JOIN Query very slow

Assuming that SerialAndDate is a combination of the fields Serial and Date, I
would use a query like the following and ignore the SerialAndDate field.

SELECT Table1.Serial, Table1.Item, Table1.Date
FROM Table1 INNER JOIN Table2
ON Table1.Serial = Table2.Serial
AND Table1.Date = Table2.Date

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Tally.NET wrote:
Hello,

I have the following Input tables

Table1
Serial Item Date SerialandDate
123 Item1 01-04-2007 12339173
123 Item2 01-06-2010 12340330
234 Item1 01-10-2006 23438991
234 Item3 02-04-2010 23440270
234 Item2 01-01-2009 23439814


Table2
Serial Date SerialandDate
123 01-06-2010 12340330
234 02-04-2010 23440270


and i am looking at an Output as below

Serial Item BillDate
123 Item2 01-06-2010
234 Item3 02-04-2010



I am trying this SQL (MS Access 2003) however the query is taking too long to process and the system does not respond.

SELECT Table2.[Serial], Table2.[BillDate], Table1.Item
FROM Table1 INNER JOIN Table2 ON Table1.SerialandDate = Table2.SerialandDate;

There are about 25000 records in both the tables.


ITCat

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:05 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.