Hi,
Through VB, in MS Access, I am running a series of 6 queries against two large unnormaizled tables (one 100 000 rows, other 200 000 rows). The goal is to create a new table with data that match between the two queried tables.
It works perfectly with the DoCmd.RunSQL statement. However the queries take around 25 minutes to complete (with indexed searchcolumns). I believe this is largely do to a query that has the following in its WHERE clause:
"a.AA Like 'XX*' " & _
"And b.BB Like 'XX*' " & _
"And (right(b.BB,len(B.BB)-4)=right(a.AA,len(a.AA)-4) " & _
"Or right(b.BB,len(b.BB)-4)=right(a.AA,len(a.AA)-5))"
Instead of the DoCmd.RunSQL method, I have also tried executing the queries in this manner:
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
cnn.Execute "...SQL query..."
To my joy, this cut the processing time from 25 to 5 minutes. However I found out later that the queries did not run correctly. Where the DoCmd.RunSQL method (correctly) made a table of 75000 matched records, the other method made one of 74000 matched records.
The part of records that were easily noticed to be missing were comming from the query described above.
So in short, I have two questions.
Why are the two different methods returning different results?
And how can I cut the processing time?
Please advise.
Thanks.
Through VB, in MS Access, I am running a series of 6 queries against two large unnormaizled tables (one 100 000 rows, other 200 000 rows). The goal is to create a new table with data that match between the two queried tables.
It works perfectly with the DoCmd.RunSQL statement. However the queries take around 25 minutes to complete (with indexed searchcolumns). I believe this is largely do to a query that has the following in its WHERE clause:
"a.AA Like 'XX*' " & _
"And b.BB Like 'XX*' " & _
"And (right(b.BB,len(B.BB)-4)=right(a.AA,len(a.AA)-4) " & _
"Or right(b.BB,len(b.BB)-4)=right(a.AA,len(a.AA)-5))"
Instead of the DoCmd.RunSQL method, I have also tried executing the queries in this manner:
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
cnn.Execute "...SQL query..."
To my joy, this cut the processing time from 25 to 5 minutes. However I found out later that the queries did not run correctly. Where the DoCmd.RunSQL method (correctly) made a table of 75000 matched records, the other method made one of 74000 matched records.
The part of records that were easily noticed to be missing were comming from the query described above.
So in short, I have two questions.
Why are the two different methods returning different results?
And how can I cut the processing time?
Please advise.
Thanks.