General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Hi Everybody,
I need to speed up a query that I use because it takes around 26 seconds to pull out 32 records from a table that has 26,896 records. Below is the query that I am using:
*-- This Query takes 26 seconds for 32 records
SELECT table1.*;
FROM (tmp_name) table1 ;
inner JOIN (cAliasName) table2 ON table1.partno == table2.partno .AND. table1.Suppid == table2.Suppid;
INTO CURSOR tmp_cursor
*-- This Query takes .3 seconds for 64 records
SELECT table1.*;
FROM (tmp_name) table1 ;
inner JOIN (cAliasName) table2 ON table1.partno == table2.partno;
INTO CURSOR tmp_cursor
*-- This Query takes 1.06 seconds for 1296 records
SELECT table1.*;
FROM (tmp_name) table1 ;
inner JOIN (cAliasName) table2 ON table1.Suppid == table2.Suppid;
INTO CURSOR tmp_cursor
The structure of table1 (tmp_name) is Date D, Suppid C(10), Partno C(17), e_0199 C(10), q_0199 I, etc.
The structure of table2 (cAliasName) is Primekey I, Zipfield C(27), Suppid C(10), Partno C(17), etc.
I have an index on partno, suppid and partno + suppid on the table1 table.
Please note that table1 is in a Visual FoxPro 5.0a data structure and table2 is a free table created on the run. I have also tried creating indexs on partno, suppid and partno + suppid on the table2 table.
Thanks,
Joseph Schaff
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only