MySQL (1 Viewer)

Aoife

Registered User.
Local time
Today, 10:06
Joined
Dec 4, 2013
Messages
36
Hi

I've linked Access, using Unicode ODBC driver, to a MySQL backend that collates images using a third party app. Accessing the underlying table is very slow, as is a simple 'passthrough' query on the same table.

By removing the 'Image' (yes, I know it's a reserved word but I don't have Admin rights to edit the SQL table) from the Query it speeds up considerably - 3 records querying the Image field takes approx. 20 seconds, over 1k records based on a single text field is instantaneous so it appears the server connection is satisfactory.

The 'Image' field in the underlying table is stored as an 'OLE Object'. Can the speed of the database be improved through other means? I've read a little about a commercial component called DBPix but would prefer not to resort to that cost.

Would appreciate your thoughts
 

isladogs

MVP / VIP
Local time
Today, 10:06
Joined
Jan 14, 2017
Messages
18,186
Do not use OLE objects or attachment fields.
The best approach is to save the PATH to each image as a text field.
Then set the image control source to that path
This will reduce database size and should improve performance
 

Aoife

Registered User.
Local time
Today, 10:06
Joined
Dec 4, 2013
Messages
36
Thanks Colin

Excuse my ignorance on the MySQL front, do you know if it's feasible achieving this using MySQL? I'd like to store images on a server rather than locally
 

isladogs

MVP / VIP
Local time
Today, 10:06
Joined
Jan 14, 2017
Messages
18,186
I've not used MySQL in many years but a filepath is just a text string so it must be able to do so
 

sonic8

AWF VIP
Local time
Today, 11:06
Joined
Oct 27, 2015
Messages
998
By removing the 'Image' (yes, I know it's a reserved word but I don't have Admin rights to edit the SQL table) from the Query it speeds up considerably - 3 records querying the Image field takes approx. 20 seconds, over 1k records based on a single text field is instantaneous so it appears the server connection is satisfactory.
Of course the query is faster excluding the image column. An image can easily be 1 million times the size of short (< 250 chars) text field. So with your comparison of 1k text records to 3 images, they might still require a different magnitude of bandwidth.

DBMS in general and MySQL in particular are not performing very well at storing big binary data (e.g. images). So, Colin's advice to store the image data outside of the db may be the solution to the issue.

However, it is not always the best approach to store images outside of the DB. You need to evaluate it in the context of your application.
 

isladogs

MVP / VIP
Local time
Today, 10:06
Joined
Jan 14, 2017
Messages
18,186
However, it is not always the best approach to store images outside of the DB. You need to evaluate it in the context of your application.

Hi Phil
When in your view wouldn't it be the best solution?
 

jleach

Registered User.
Local time
Today, 06:06
Joined
Jan 4, 2012
Messages
308
Separate your queries: in your main query, don't reach to the image field/table. Only get those when you're ready to show them (use an ID from the main query as a link to get the image needed, but only when needed). You should be able to do this without needing any admin rights on the server.

An example would be a form recordsource that retrieves your desired recordset (without images), then on the Current event of the form, use the ID to run a query that returns the single image for that record to be shown.


Colin - over the weekend I wrote a quick tracking application for an in-house project. I have to share the frontend with a handful of people internally, but it's a quick and dirty app to get us through a few months of development on a larger project. I created a FileStore table in SQL Azure to store small files: screenshots, log data, other such things. This was far easier than the alternatives because I could maintain just one single backend on Azure (which is nothing to maintain), and one single Access FE.

Alternatives would haven been to store files locally (but how, when sharing with remote workers?), store files in a fileshare service such as DropBox (but then make sure everyone's set up with it and write FE config on a per-install basis to look in the correct base path), or to set up a blob storage in the cloud (S3, Azure Blob?). Sure, all of them are viable approaches, but once in a while it does make sense just to have a simple file store built directly into the db (not very often, I'll admit, but it did in this case).

Cheers,
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:06
Joined
Feb 28, 2001
Messages
26,996
If you can set the permissions correctly on the repository to prevent deletion of the files to non-admins, why would you not put the files on the same server as the MySQL executable (not saying same folder, though) and then just use a UNC file reference?
 

sonic8

AWF VIP
Local time
Today, 11:06
Joined
Oct 27, 2015
Messages
998
When in your view wouldn't it be the best solution?
In addition to jleach's scenario, here are some indicators that it might not be...

  • The image data has to be written in a transaction with related textual data in the database.
  • You need to implement complex access control logic for the image data based on related data.
  • Any access to the image data, be it write or read, has to be strictly audited.
 

isladogs

MVP / VIP
Local time
Today, 10:06
Joined
Jan 14, 2017
Messages
18,186
Thanks Jack & Phil
All of those make sense to me except I'm unclear what this might entail:
You need to implement complex access control logic for the image data based on related data.
However, please don't feel the need to answer for my sake
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:06
Joined
May 7, 2009
Messages
19,169
i don't know why some recommends not saving the image to MYSQL. it's a database manager and that it's purpose. you harness the power of the database management. its useless if you are only saving text, numbers to it. of maybe because it's not MSSQL.

i have not done this yet but there are many forums that has answers to your problem.
 

jleach

Registered User.
Local time
Today, 06:06
Joined
Jan 4, 2012
Messages
308
arnelgp - I don't think relational databases were ever really designed to store binary information, but rather to model much smaller, more structured related data. While each of the major RDBMSs have grow into the ability to store BLOB and other unstructured information, it tends to have the feel of an afterthought in the broad scheme of a relational database (though, a nicely implemented one).

NoSQL databases excel in this realm. In particular for blobs, Key/Value Stores tend to be the best flavor. If you're working with a larger enterprise system, there can be a lot of architectural advantages to keeping binary information stored in a big KVStore and recording the key as a reference in the relational database.

With that said, there does seem to be a trend of DB engines accepting less relational and more unstructured (i.e. - NoSQL-style) data. The latest version of SQL Server, for example, has GraphDBs using the Property Graph Model (which is the coolest technical undertaking I've had in many years, though we use Neo4j), and JSON and XML storage and search closely resembles DocumentDBs like MongoDB. Cosmos (an Azure DB service) aims to cover all the major DBs in one service by offering Relational, DocumentDB, KVStores and Graph all in one.

Relational data is an excellent fit for relational databases and can't easily be shoehorned into any other type of system, but on the flip side, there's a number of other databases that do a better job holding certain types of data than relational ones will. Each have their own strengths and optimizations.

Fun stuff. We've been working with NoSQL more and more over the past few years or so, and while I tend to feel that Document and KVStores are relatively simple in concept and application, working with Neo4j and GraphDBs is one of the most interesting and exciting things I've done in the tech world perhaps since I stumbled across Access itself (and it's easy!)

Relational is still at the heart of the data-driven world, and likely always will be, but it's not always the best way.

All IMHO, of course :) Cheers
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:06
Joined
May 7, 2009
Messages
19,169
Sharp and crisp explanation, tnx Jack.
 

Aoife

Registered User.
Local time
Today, 10:06
Joined
Dec 4, 2013
Messages
36
Hi
Was going to feed back to Colin and hadn't realised that there were other posts...

Now storing URL references in the table with the pictures stored separately on the server as per Colin's recommendation. I've used a Pass Through query to obtain the underlying data from the MySQL back end, performance is excellent.

To help establish form performance, I've got the pictures (approx. 60) on a continuous form and it was a little bit sluggish but to be expected - this isn't an issue as there will be no need to display pictures en masse and is more a case of testing retrieval.

More of an issue doing the same under 'Print Preview' - sometimes missing pictures, very slow to load etc. Again, this hopefully won't be a problem with the end solution as there will be limited pictures.

Thank you for your assistance Colin and all other responders
 

Users who are viewing this thread

Top Bottom