Select Query vs union query 1

[Xzibit7]

I have a passthru query that gets data from a table with millions of records. However I am only looking for those records that match my database's stocknumbers(only in the hundreds). Right now I am using a select query with access that matches these two tables up(This easily takes 20 minutes). My question is, is a union query with SQL much better or am I just going to have to accept that this is going to take some time? I've thought about updating at night with xp's scheduled tasks program but this brings on some other problems that I am unsure of.

 

[Ed2020]

I think you may have misunderstood what a union query does (or else I have misunderstood your question!).

A union query does not match two tables of data and look for commonality between the two. A union query takes two or more tables of data containing the same number of fields, with the same data types, and combines it into one table. The resulting dataset would therefore have the same table structure but the number of records would equal the sum of the two (or more) tables you have queried.

20 mins still sounds like quite a while for this to run. Are you sure all your table indexes are set up correctly?

Try running the passthrough query with hard coded criteria to return just one record. How long does that take? If it's quick then I'd try writing a VB routine to loop through your local data table, change the WHERE clause of your SQL statement in the passthrough query, and execute it one record at a time. You can then copy the results into a local table in your database.

Ed Metcalfe.

Please do not feed the trolls.....

 

[Xzibit7]

yeah the passthru query returns the data very fast, its when I try to match my partnumbers with the ones in it that takes forever. Also I am only using select, and from in my passthru query. Would it be possible to incorporate a where that compares my table and matches the data all at once?

 

[Xzibit7]

BTW this other table that the passthru query gets its data from is in anODBC database somewhere else on the server .

 

[lespaul]

what's the SQL of the query?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Xzibit7]

SELECT AMC, AMSC,PLT,Price_to_DLA, Price_to_Customer, NIIN
FROM DSS_USER.V_Portal_ZDOR_ITM

 

[Xzibit7]

I'm sorry, im getting off work so dont be offended if I don't respond. Thanks for all the help so far everyone.

 

[lespaul]

Is this the query that takes long to run? I only see one table....where are you doing the comparison that you mention in the OP?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Xzibit7]

No I have another select query in Access that just matches the NIIN in the passthru query to the NIIN in one of my tables. This is what takes so long.

 

[lespaul]

ok, can we see THAT query?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[dhookom]

Depending on what you want to do with the result of your "final" query that combines the P-T and the Access table, you might be able to write code to change the SQL property of the P-T. For instance:

Dim db as DAO.Database
Dim qd as DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs("qsptMyPT")
qd.SQL = "SELECT * FROM tblRemote " & _
  "WHERE Color IN ('Red','Green','Blue')"

To improve speed, I have pulled a limited number of the P-T records and placed them in a temporary Access table. I found this allowed users to run reports in just 5 seconds that had taken 20-30 minutes.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Xzibit7]

okay lespaul the other query isn't in sql it just makes a table by matching(using relationships) the NIIN in my supervisor table to the NIIN in the passthru query.


dhookum that def. seems to be the way to go....I got lost in the last part though, I'm pretty new to sql. This is the part i got confused on what is happening

qd.SQL = "SELECT * FROM tblRemote " & _
"WHERE Color IN ('Red','Green','Blue' )

 

[dhookom]

You would need to write code that loops through your Access table to build the "IN (....)" string. The code would open a recordset and loop while concatenating the "matching" field values.

There is a generic concatenate function at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

If you need specific assistance, come back with specific table and field names.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]