General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Speed up matching of tables using SQL
hi!
any help on how i can speed up my "matching procedure"? it usually takes 30 mins to finish the procedure. here's my SQL:
SELECT * FROM xa a WHERE EXISTS (SELECT checkno FROM xm b WHERE a.checkno=b.checkno .AND. b.agycode=a.agycode .AND. b.brcode=a.brcode .AND. a.amount=b.amount) INTO TABLE M
SELECT * FROM xa a WHERE EXISTS (SELECT checkno FROM xm b WHERE a.checkno=b.checkno .AND. b.agycode=a.agycode .AND. b.brcode=a.brcode .AND. a.amount!=b.amount) INTO TABLE Q
for each table i have at least 20,000 records. and the execution of that SQL takes the least of 30 mins. you bet it is really slow!
i need to have both the matched record in table XA and XM into table M. meaning if the query found a match, it will copy the matched record from XA and XM to M. both tables have the same structure so if i execute advancepack's SQL, it will give me fields like checkno_a, checkno_b.. etc. all in one record.
i have indexes for both tables.
thanks
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