Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-13-2018, 12:33 PM   #1
Aoife
Newly Registered User
 
Join Date: Dec 2013
Posts: 36
Thanks: 2
Thanked 0 Times in 0 Posts
Aoife is on a distinguished road
MySQL

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

Aoife is offline   Reply With Quote
Old 06-13-2018, 12:59 PM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,367
Thanks: 92
Thanked 1,812 Times in 1,687 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: MySQL

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-13-2018, 01:21 PM   #3
Aoife
Newly Registered User
 
Join Date: Dec 2013
Posts: 36
Thanks: 2
Thanked 0 Times in 0 Posts
Aoife is on a distinguished road
Re: MySQL

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

Aoife is offline   Reply With Quote
Old 06-13-2018, 01:26 PM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,367
Thanks: 92
Thanked 1,812 Times in 1,687 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: MySQL

I've not used MySQL in many years but a filepath is just a text string so it must be able to do so
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-13-2018, 02:05 PM   #5
Aoife
Newly Registered User
 
Join Date: Dec 2013
Posts: 36
Thanks: 2
Thanked 0 Times in 0 Posts
Aoife is on a distinguished road
Re: MySQL

Thanks Colin, sounds promising
Aoife is offline   Reply With Quote
Old 06-13-2018, 02:09 PM   #6
sonic8
Newly Registered User
 
Join Date: Oct 2015
Posts: 124
Thanks: 23
Thanked 37 Times in 36 Posts
sonic8 is on a distinguished road
Re: MySQL

Quote:
Originally Posted by Aoife View Post
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.
__________________
New Video:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
, recorded at the AEK conference, Nuremberg, Oct. 2018.
sonic8 is offline   Reply With Quote
Old 06-13-2018, 02:20 PM   #7
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,367
Thanks: 92
Thanked 1,812 Times in 1,687 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: MySQL

Quote:
Originally Posted by sonic8 View Post
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?

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-13-2018, 04:40 PM   #8
jleach
Newly Registered User
 
jleach's Avatar
 
Join Date: Jan 2012
Location: New York, NY
Posts: 307
Thanks: 16
Thanked 69 Times in 69 Posts
jleach will become famous soon enough
Re: MySQL

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,
__________________
- Jack D. Leach

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Microsoft MVP 2012-2016
jleach is offline   Reply With Quote
Old 06-13-2018, 06:18 PM   #9
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,568
Thanks: 62
Thanked 1,196 Times in 1,096 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: MySQL

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?
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-13-2018, 10:11 PM   #10
sonic8
Newly Registered User
 
Join Date: Oct 2015
Posts: 124
Thanks: 23
Thanked 37 Times in 36 Posts
sonic8 is on a distinguished road
Re: MySQL

Quote:
Originally Posted by ridders View Post
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.
__________________
New Video:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
, recorded at the AEK conference, Nuremberg, Oct. 2018.
sonic8 is offline   Reply With Quote
Old 06-14-2018, 04:16 AM   #11
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,367
Thanks: 92
Thanked 1,812 Times in 1,687 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: MySQL

Thanks Jack & Phil
All of those make sense to me except I'm unclear what this might entail:
Quote:
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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-14-2018, 05:06 AM   #12
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,839
Thanks: 55
Thanked 2,183 Times in 2,094 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: MySQL

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 06-15-2018, 02:38 AM   #13
jleach
Newly Registered User
 
jleach's Avatar
 
Join Date: Jan 2012
Location: New York, NY
Posts: 307
Thanks: 16
Thanked 69 Times in 69 Posts
jleach will become famous soon enough
Re: MySQL

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
__________________
- Jack D. Leach

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Microsoft MVP 2012-2016
jleach is offline   Reply With Quote
Old 06-15-2018, 02:51 AM   #14
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,839
Thanks: 55
Thanked 2,183 Times in 2,094 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: MySQL

Sharp and crisp explanation, tnx Jack.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 06-27-2018, 06:53 AM   #15
Aoife
Newly Registered User
 
Join Date: Dec 2013
Posts: 36
Thanks: 2
Thanked 0 Times in 0 Posts
Aoife is on a distinguished road
Re: MySQL

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

Aoife is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL Server gone - Relink MySQL Tables cdlpatrickusa Modules & VBA 0 07-18-2013 12:09 PM
mysql help Bonzay0 VB.NET 0 05-19-2009 02:14 AM
New to MySQL bonekrusher SQL Server 0 06-09-2006 06:32 AM
MySQL KenHigg Other Software 1 01-06-2006 12:13 PM
Looking into MySql DanG General 12 12-28-2005 10:42 AM




All times are GMT -8. The time now is 08:47 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World