JOIN Query very slow
in [Queries]
|
From: Tally.NET on 4 Jun 2010 07:45 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
From: Marshall Barton on 4 Jun 2010 09:19 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]
From: vanderghast on 4 Jun 2010 11:18 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" <tally.net(a)itcatalystindia.com> wrote in message news:%23pgiet9ALHA.5748(a)TK2MSFTNGP04.phx.gbl... 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
From: Tally.NET on 6 Jun 2010 03:38 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" <marshbarton(a)wowway.com> wrote in message news:55vh061fsdepqukf0h08hcq6mmuvi633be(a)4ax.com... > 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]
From: John Spencer on 6 Jun 2010 16:07 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 >
|
Pages: 1 Prev: create a form that collects parameters Next: Forcing an ambiguous outer join |