Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

Report loads slowly with attachment Access 2007

I have an Access 2007 db that is split and the back-end is installed on a server.  A report (sales receipt) includes an image (attachment) in the select query and on the report and takes at least a minute to load.  When the back-end is on the local computer, the report functions quickly.  When the back-end is on the server and I remove the attachment field from the query and report, the report functions quickly.  Is there anyway to fix this issue so that I can keep the back-end on the server and still have the image on the sales receipt?
0
rdani07
Asked:
rdani07
  • 7
  • 7
2 Solutions
 
danishaniCommented:
Check out this performance FAQ, to improve your speed;
http://www.granite.ab.ca/access/performancefaq.htm

HTH,
Daniel
0
 
danishaniCommented:
To add on improving your speed, see below links as well;
http://msdn.microsoft.com/en-us/library/dd942824%28v=office.12%29.aspx

HTH,
Daniel
0
 
rdani07Author Commented:
I have already performed all of the suggested items to improve speed.  Every other report and form functions in a timely manner.  It is just when the attachment field that holds a .jpg file is added to the query that the issue occurs (even if the field is empty).  When I remove that field from the query and report, the report functions in a timely manner.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
danishaniCommented:
What is the size of this Image?

The image size can be causing dramaticly low performance when loading a Report.
Best way to resize it to smaller / still good quality , which fits in your report.

HTH,
Daniel
0
 
rdani07Author Commented:
The size of the jpg varies from 96kb to 3mb, however even if there is no jpg loaded for this item in the table, the report still times for at least a minute before loading.  We are resizing all images smaller, however that can't be the only issue.
0
 
danishaniCommented:
Hmm no, not only, but can help to reduce loading time.

What is your back-end? How is the network speed?

HTH,
Daniel
0
 
rdani07Author Commented:
The current back-end is a full version of the database, but I've tried using the back-end file that is created using the database splitter as well.  The issue also occurs when using the full version on the server.  I've been assured by the IT contractor that network speed is not the issue.  

Thanks for brainstorming with me.
Bob
0
 
rdani07Author Commented:
Daniel,

I just realized the query for the report is grouped by "Expression" for the image field, to you think removing that will speed it up?  I'm not in the office to test it myself.

Thanks!
0
 
danishaniCommented:
Hmmm normaly a split database performance much faster, so thats something you need to have in place.
I am not sure if you are using the Full version of the Split version?

What is the speed of the Network, what kind of Network do you have in place?

Query speed also depend on how the Tables are Indexed.
I am not sure what you mean with Expression, is that a calculated field in your Query?

HTH,
Daniel
0
 
rdani07Author Commented:
Daniel,

I have tried both a split version and the full version.  Both have the same issue with this report.

I'll have to check on the specs of the network tomorrow.

I worded the Expression wrong.  In the Total row for the Image field, where the default is usually Group By, the total defaults to Expression when the attachment/image field is added to the query.  Should I remove the totals row from the query?

Thanks
0
 
danishaniCommented:
Ok, both ways have issues, but is the Split Version faster then the Full Version?

Is the Query a Totals Query? Sure, if there is nothing to Group it will run little faster.

But I guess the problem is not so much the Group By, more likely the network in relation with the Image size.

Did you make us of an ImageControl at your report to display the images?

HTH,
Daniel

0
 
rdani07Author Commented:
Neither version runs any faster than the other, split or full.

The totals are built into the report, not the query.  So I will eliminate the Totals in the query tomorrow.

Once again, the problem exists even if there is no image attached to the field.  Initially I thought that the attachment size was the problem too, but when there is no image attached the same issue occurs.

Not sure about ImageControl.  How do I do that? I just added the field to the report like i would any other field.

Thanks!
0
 
danishaniCommented:
Neither version runs any faster than the other, split or full.
Thats odd, that there is no difference, but guess thats not the issue then.

The totals are built into the report, not the query.  So I will eliminate the Totals in the query tomorrow.

How did you build the totals by using aggregate functions like DSum(), DCount() ?
Those function can be very slow, especially going through lots of data.

Not sure about ImageControl.  How do I do that? I just added the field to the report like i would any other field.
Yes, just curiouse which control you use to display the image on your report.

HTH,
Daniel


HTH,
Daniel
0
 
Jeffrey CoachmanCommented:
rdani07

Try dumping the attachment fields and simply "link" to the image on the server.
I use something like this (sample DB attached) and have never had any issues...

Also note that it takes Access *Time* to load (render) any images, whether they are linked or embedded, or attached.
So going to an external server to get the image data then bring it back to the DB, then render it make take some time.

In other words, depending on the size and resolution of the image, the speed of the network, and the number of images to be displayed, ... this may never be what you might call "Fast"...

JeffCoachman


Access-BasicLinkOpenImageDialogB.mdb
0
 
rdani07Author Commented:
Daniel,

Removing the Totals from the query was the fix.  The report worked immediately after I took totals out.

Thanks for your help!

Jeff,

I like that idea to keep the database file size low.  Thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now