Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
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
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement