editing pass through queries? possible?

[yepwingtim]

thanks you!
________
FERRARI 166 INTER SPECIFICATIONS

 

[pbaldy]

If you mean editing the data returned, to the best of my knowledge, it can not be edited.

 

[yepwingtim]

Ah I have a performance issue with mysql backend access front-end

It is laging greatly =(

Any ideas on how can it be run more smoothly?
________
Honda XRM history

 

[Banana]

We'll need more information than that to be able to help you.

But for starters, have you went to support.microsoft.com and looked for two articles; 'Optimizing Client/Server Performance' and 'Jet/ODBC Connectivity Whitepaper'? They are essential reading, and even though they're old, they're still relevant and does apply to MySQL as well.

 

[yepwingtim]

Thanks for the links~

the search is really slow...

mysql only takes yyyy-mm-dd
in access we use mm/dd/yy

when inputting a date in access, i think it mysql converts to yyyy-mm-dd format which slows us ALOT down.

if i use yyyy-mm-dd in access, the insert query goes alot faster, but im afraid the data entry people won't get use to this
________
Herbal vaporizers

 

[Banana]

Hm, I'm not following you because you're talking about two things:

1) Search and 2) Insert query.

In my experience with Access -> MySQL, inserting new dates, even in Access's format is barely noticeable at all. I, however, didn't do any bulk inserts.

As for searching, it may not be the formatting but rather that the column is not indexed. Are you sure you're only searching on indexed columns? If this is indeed the case, one way you can do is to alter the data before it is sent to MySQL so it does not have to be re-formatted in MySQL. This can be done be writing a function to convert the dates into ISO format used by MySQL.

HTH.

 

[yepwingtim]

Hi Banana thanks for the reply - I will find those great articles.
I'm using InnoDB with indexed fields of ID, Last Name, First Name.
What's werid right now is I can only do "Full Matches" to get decent fast matches
(2-3 sec). "Partly Matched" ones are pretty slow (10 seconds?)
I have around 2000 records max.
I guess what really slows me down is I am actually connected to a server to mysql - not using localhost

I tried using navicat which was amazely fast. Maybe I should switch to navicat instead?
The problem is most basic users wouldn't know how to use navicat. I don't know what to do T_T

Thanks for the input

-Danielle
________
Essential vaaapp vaporizer

 

[Banana]

To be clear, are the search against last name and first name or is it against dates? If you're doing search against dates, then dates needs to be indexed, most likely.

As for differences between Access and Navicat, I would say there is something wrong because in my experience, Access queries has performed pretty good. It is of course expected that Navicat would be faster because it is connecting via native libraries, rather than ODBC, but even the overhead of additional layers shouldn't slow Access to a 10 second query for what takes 2-3 seconds.

Are you using dynamic SQL (e.g. you're writing out random SQLs in a pass through query or in VBA? If so, avoid that and use stored procedures or prepared statements; this will give you better performance. Rarely, if ever, should you need dynamic SQL.

Navicat, BTW, is a great tool, but it's primarily for developers not for end users so I wouldn't give it out to my users. But if you have cash to burn, do get it. Quite useful in conjunction with free tools MySQL give out.


PS Wait, I just remembered something. See if this helps.