Can I 'store' querys in a MySQL DB? 2

[JustKIDn]

Can I store a query in the DB like you do with tables?

I'm using MySQL as a backend (database) and Access as the frontend (forms, reports, etc...).

I've got a query that runs on MySQL in 1.04 seconds.
The same query (with the table linked into Access) brought Access to it's knees! I let it run for over an hour before I canceled it.

Oh, did I mention that the query has over 30 JOIN's?

Can I store a query in the DB like you do with tables?
tgus

____________________________
Families can be together forever...

 

[swampBoogie]

no.

 

[JustKIDn]

O.K. Let me rephrase that.

Is there any way to run the query from the server instead of Access?

Or, is there a way to run it as two seperate querys?
So, run the first one and then the second, and append the results together.

Thanks
tgus

____________________________
Families can be together forever...

 

[sleipnir214]

The query is being run on the server -- on a database server, there is no way to get to the information without providing the server with a query it will use to repond with data. The Access engine is doing nothing more than populating GUI fields from data it receives from the ODBC connection.

Your speed problem is probably caused by the slowdown which occurs through the ODBC subsystem query interpretation, the way Mi¢ro$oft handles data interchanges through ODBC, and other factors.

I assume we're talking about a SELECT query here. If so, then you might be able to take the ODBC interchange problem out of the picture by selecting your data to a table, then linking Access to that table through ODBC for viewing. Take a look at the MySQL "INSERT ... SELECT" syntax:

http://www.mysql.com/doc/en/INSERT_SELECT.html

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[JustKIDn]

I found out that the answer is a 'pass-through' query.
The way it has been explained to me. When you create a pass-through query in Access, it causes the SQL DB to process the query and then return the results tthough the ODBC driver.

My query now runs just as fast in Access as it does in MySQL.
tgus

____________________________
Families can be together forever...

 

[sleipnir214]

Thus bypassing the Jet database engine, which is what was slowing you down.

tgus, I'm going to give you a star. I hadn't known about pass-through queries in Access until you told me about them. Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[JustKIDn]

Thanks sleipnir,

I just like to share what I've learned so the next person with similar problems can learn from it.

I hate finding posts where the person may have found the answer but didn't share it.
tgus

____________________________
Families can be together forever...