What Gets Stored When Access Inserts a BLOB?

[Mark Olbert]

I've been wrestling with slow performance of a SqlServer 2008 database that stores PDFs in image fields.

The table is very simple, with an int primary index column and an image field, called 'data', to hold the binary data. When you
bring the table up in Access you're able to right-click on the data field and select "Insert Object". From there you can browse to a
PDF and insert it. Once you've inserted the file you can double-click the field and Acrobat will launch and display the field
contents.

However, what actually gets inserted in the data field is much, much larger than the actual file you select. For example, a 40K PDF
gets stored as a 7.5MB field. I verified this by calling DATALENGTH() on the data field. So a great deal of data above and beyond
the actual file content is being stored.

You >>can<< store just the file content by doing something like this:

INSERT INTO test(idnum, data) VALUES(1, 0x0)

UPDATE test set data = (SELECT * FROM OPENROWSET(BULK 'path to PDF', SINGLE_BLOB) AS x )
WHERE idnum=1

That set of operations stores just the file data in the field (again, I verified this via DATALENGTH()). But there's a catch: Access
no longer "knows" what's in the field, so you can't double-click it to display it.

So apparently the "extra" data that gets stored has something to do with storing the "context" or "management application" for the
field contents.

But what is the extra information? And can I configure Access to store just the file contents, and not the context?

- Mark

 

[Sylvain Lafontaine]

The use of Access' OLE Fields is crappy at best and will most often bring
down your application because they are to memory hungry. (A2007 is supposed
to be a little lighter in its use of memory for OLE Fields but I never tried
it personally.).

In your case, Access add a header to each binary image before storing it in
the database. This header is of a variable length but it's usually in the
range of 72 to 76 bytes and will usually be of the same length for the same
type of image. However, the structure of this header has never been
documented by MS so you'll have to poke in the dark. Stephan Lebans has
published a very good module for extracting these files for most types of
binary images: http://www.lebans.com:80/oletodisk.htm

I suggest that you use it to extract the PDF file directly to the local hard
drive and use a SHELL command or a WebBrowser object to launch it into
Acrobat Reader instead of relying on the functionality of Access. Also, if
you are going this way, an even better idea would to store and retrieve the
binary images yourself instead of relying on the OLE Document control of
Access at all.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Paul Shapiro]

When I was doing work with large collections of image files about 9 years
ago, the concensus at that time was you were better off storing the binary
objects in the file system, not in the database. The only reason to keep the
objects in the db was if you needed transactional integrity, which isn't
usually the case. Instead, store the path to the object, or whatever info
you need to compute the path.

If you do take this approach, performance at that time was severely reduced
if the number of files in a folder exceeded 1000 or so. I imagine file
systems have improved, but at that time displaying the image took a fraction
of a second with modest file counts per folder, and 15 minutes when someone
decided it would be fine to put 100,000 images in a folder.

If you do want to keep the objects in the db, maybe you can change the
Access form so users cannot insert objects by right-clicking. Give them a
button to select a file, and then insert that file to the db with the sql
code you already determined avoids the bloating. I seem to remember that
right-clicking an ole field also gives you the option to create a "Package"
rather than just inserting the file. That could cause bloating. Or maybe
Access converts the pdf to a bitmap, and stores that along with the object.
in Access 2007 there's an option to tell Access to just store the object,
without a bitmap. In the Access Options, Current Database, there are radio
buttons for Picture Property Storage Format. Maybe it applies to pdfs too,
and not just images?

 

[Mark Han[MSFT]]

Hi Mark,

Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.

From your description, I understand that after you create a talbe in the
access database, if you insert into a OLE object(PDF file), the data which
is inserted in to the database is much larger the acutally PDF file. and
you would like to know what get stored when access insets a BLOB. If I have
misunderstood, please let me know.

To make sure we are on the same page, I would like to explain the following
1 Access add a header to each binary image before storing it in the
database. This header is of a variable length but it's usually in the
range of 72 to 76 bytes and will usually be of the same length for the same
type of image.

2 I have tried to re-produce the issue; however after I inserted the PDF
file with(1175 KB) and save the accessdatabase, the size of the access
database is just 1400 KB ; so the issue you describe is not able to be
reproduced on my machine. Based on the my test, to better understand the
issue, please help to confirm/collect the following
a) how many OLE Object you have inserted into the database?

b) please send me a simple of the access database file. if possible, please
send me an email; I will create a FTP(workspace) for you to upload the file
requested.

Besides, about the slow performance when insert PDF file into the SQL
Server 2008. As I explained before, the issue might be related to the
scenario.
my test step
create a talbe with 2 column
insert the pdf file by the command. the pdf file is inserted into the SQL
Server database quickly. So to better understand the issue, please describe
the steps you do when re-producing the issue
INSERT INTO access(data) VALUES(0x0)

UPDATE access set data = (SELECT * FROM OPENROWSET(BULK
'E:\01_Engine_Extended Events (XEvent) Fundamentals.pdf', SINGLE_BLOB) AS x
)

If there is anything unclear, please let me know.

I look forward to hearing from you.

Best regards,
Mark Han
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx

============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[Mark Olbert]

Mark,

It's an ADP file, not an Access database, so it wouldn't work unless I uploaded the entire SqlServer database to your system...which
would take a long, long time

When you tried to duplicate the problem did you use a SqlServer database backing an ADP? The Sql table is actually pretty simple,
here's a CREATE script:

/****** Object: Table [dbo].[supporting_doc] Script Date: 03/25/2009 08:17:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[supporting_doc](
[idnum] [int] IDENTITY(1,1) NOT NULL,
[doc_data] [image] NOT NULL,
CONSTRAINT [PK_supporting_doc] PRIMARY KEY CLUSTERED
(
[idnum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

- Mark

 

[Mark Han[MSFT]]

Hi Mark,

Thank you for the reply.

To ensure we are on the same page, I would like to explain the following
1 according to your description: "bring the table up in Access you're able
to right-click on the data field and select "Insert Object"", based on my
research, the method to insert Objest is available for a access database
file not for ADP project. So I do a test based on access database file.

2 I also create a ADP project based on the SQL Server database; when I
insert a PDF file into the imag column, the size of the database will be
bigger. For example: the PDF is 1,175KB, the original size of the database
is 2 MB; after inserting the PDF file into the database, the data file of
the database will grow to 3 MB.

I understand that you use a ADP file. it might take you some time to send
the information to us. However, the information requested is very important
to make the issue clear. So, please help to collec the following
1 the backup file of the simple SQL Server database

2 the backup file of the adp .

I look forward to hearing from you.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[Mark Han[MSFT]]

Hi Mark,

I haven't heard from you for a few days.

I understand that you might have a busy schedule. if it is convenient to
you, please send me a note to tell me the status of the issue.

I look forward to your update.

Best regards,
Mark Han
Microsoft Online Community Support
Get Secure! - www.microsoft.com/security
====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
====================================================

 

[Gianluca Poggi]

hi hi hi hi

 

[Mark Olbert]

Sorry about the delay, I've been programming

Mark, when I open that table with the image field in Access 2007 Datasheet view, I can right-click on any one of the image fields
and get an "Insert Object..." context menu choice. Is this something that maybe got added in to Access 2007?

While I would appreciate knowing the answer to my most recent question -- just what does get stored when Access 2007 inserts an
object into an image field? -- I've since moved past that bottleneck.

I've switched the image field to a varbinary(max) field, which Access does not support. Instead, I manipulate the table, and the
field, through a C# application. I've verified that when I read a 50K byte file into a varbinary(max) field through my C#
application the database only grows by roughly 50K bytes. Of course, I can't double-click on the field within Access to view it, but
I wrote a little C# viewer program that extracts the data, stores it in a temporary file, and then launches the file. It isn't
pretty, but it works .

- Mark

 

[Mark Han[MSFT]]

Hi Mark,

Thank you for the update. I understand that you might have a busy schedule.

in order to address your concern, according to the current situation, I
would like to do a summary of the issue.
1 what does get stored when Access 2007 inserts an object into an image
field?
As I explained in the previous reply to you. when Access 2007 inserts an
object into an image field, Access will add a header to each binary image
before storing it in the database. This header is of a variable length but
it's usually in the range of 72 to 76 bytes and will usually be of the same
length for the same type of image.

2 According to your description: "when I open that table with the image
field in Access 2007 Datasheet view, I can right-click on any one of the
image fields
and get an "Insert Object..." context menu choice. Is this something
that maybe got added in to Access 2007?"
yes, it is newly added in the access 2007. there is an article to sharew
with you:http://office.microsoft.com/en-us/help/HA100241851033.aspx

3 I have reviewed the method you us. Yes, it does work for you. your
professional skill gives me a deep impression.

Based on the current situation, in order to better assist you with the
issue, I have done some internal consult. in order to better understand the
issue and help to resolve it, it is better for us to re-produce the issue.
So, if it is convenient to you, could you please send the following to me
1 the backup file of the adp.

2 the backup file of the simple database. in the database, we just need the
table [supporting_doc].

3 please describe the steps to reproduce the issue.

I look forward to hearing from you.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[Mark Han[MSFT]]

Hi Mark

I haven't heard from you for a few days.

I am concerned if the explanation I sent was not clear enough. If that's
the case, please tell me directly. I will go through the detail with you
and ensure it is easy for you to follow.

Besides, I understand that you might have a busy schedule and it is
convenient to you to send me a note to tell me the status of the case.

If you have any questions or concerns on the above, please email me.

I look forward to hearing from you.

Thanks.
Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[linqiao]

Hi Mark,

Thank you for the update. I understand that you might have a busy
schedule.

in order to address your concern, according to the current situation, I
would like to do a summary of the issue.
1 what does get stored when Access 2007 inserts an object into an image
field?
As I explained in the previous reply to you. when Access 2007 inserts an
object into an image field, Access will add a header to each binary image
before storing it in the database. This header is of a variable length but
it's usually in the range of 72 to 76 bytes and will usually be of the
same
length for the same type of image.

2 According to your description: "when I open that table with the image
field in Access 2007 Datasheet view, I can right-click on any one of the
image fields
and get an "Insert Object..." context menu choice. Is this something
that maybe got added in to Access 2007?"
yes, it is newly added in the access 2007. there is an article to sharew
with you:http://office.microsoft.com/en-us/help/HA100241851033.aspx

3 I have reviewed the method you us. Yes, it does work for you. your
professional skill gives me a deep impression.

Based on the current situation, in order to better assist you with the
issue, I have done some internal consult. in order to better understand
the
issue and help to resolve it, it is better for us to re-produce the issue.
So, if it is convenient to you, could you please send the following to me
1 the backup file of the adp.

2 the backup file of the simple database. in the database, we just need
the
table [supporting_doc].

3 please describe the steps to reproduce the issue.

I look forward to hearing from you.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.
=========================================================
very good

 

[linqiao]

Hi Mark,

Thank you for the update. I understand that you might have a busy
schedule.

in order to address your concern, according to the current situation, I
would like to do a summary of the issue.
1 what does get stored when Access 2007 inserts an object into an image
field?
As I explained in the previous reply to you. when Access 2007 inserts an
object into an image field, Access will add a header to each binary image
before storing it in the database. This header is of a variable length but
it's usually in the range of 72 to 76 bytes and will usually be of the
same
length for the same type of image.

2 According to your description: "when I open that table with the image
field in Access 2007 Datasheet view, I can right-click on any one of the
image fields
and get an "Insert Object..." context menu choice. Is this something
that maybe got added in to Access 2007?"
yes, it is newly added in the access 2007. there is an article to sharew
with you:http://office.microsoft.com/en-us/help/HA100241851033.aspx

3 I have reviewed the method you us. Yes, it does work for you. your
professional skill gives me a deep impression.

Based on the current situation, in order to better assist you with the
issue, I have done some internal consult. in order to better understand
the
issue and help to resolve it, it is better for us to re-produce the issue.
So, if it is convenient to you, could you please send the following to me
1 the backup file of the adp.

2 the backup file of the simple database. in the database, we just need
the
table [supporting_doc].

3 please describe the steps to reproduce the issue.

I look forward to hearing from you.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.
=========================================================

 

[Guest]

dario