Well, by chance, I ran into the answer:
Original Query (9-10 seconds):
Select A.Name, Count(*) from A, B where A.ID = B.ID Group By
A.Name Order by COL2 desc
Original Query (less than 1 second):
Select A.Name, Count(*) from B, A where A.ID = B.ID Group By
A.Name Order by COL2 desc
(note the reverse order of B and A in the from clause, where
B is the small non-indexed table and A is the BIG indexed table. I noticed
that changing the order of the where clause did not seem to make a
difference)
So, if you want to drastically speed up a large SQL join, format the Select
as follows:
Select whatever from small_nonindexed_table, big_indexed_table where
...
Randy Majors - Manager, GIS & Mapping
Resource Data International 303-444-7788
<http://www.resdata.com> http://www.resdata.com -
<mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]
-----Original Message-----
From: Majors, Randy
Sent: Monday, February 22, 1999 11:40 AM
To: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
Subject: MI SQL join 150 times slower than Visual Fox
Pro
Here's my dilemma:
I have two tables with two columns each: Table "A" has
75000 records and Table "B" has 150 records. The ID fields from both tables
are indexed.
Here's the join syntax:
Select A.Name, Count(*) from A, B where A.ID = B.ID Group By
A.Name Order by COL2 desc
The join takes 9- 10 seconds to produce the results table,
which typically consists of 2 - 6 records. (I have tried saving both tables
in native MI format, as well as DBF, and the time is the same)
HERE'S THE KICKER: I tried performing the exact same join
on the exact same DBF tables in Visual Fox Pro and it took only 0.06
seconds! That means that MI SQL is at least 150 times slower that Visual
Fox Pro!
BTW, 9 to 10 seconds would normally be liveable, but it is
WAY too slow because this join is ran by a MapBasic application
"on-the-fly" to produce an array for a dialog list box.
What can I do to dramatically speed up the performance of my
MI join?
Randy
Randy Majors - Manager, GIS & Mapping
Resource Data International 303-444-7788
<http://www.resdata.com> http://www.resdata.com -
<mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]
----------------------------------------------------------------------
To unsubscribe from this list, send e-mail to [EMAIL PROTECTED] and put
"unsubscribe MAPINFO-L" in the message body, or contact [EMAIL PROTECTED]
