I'm curious if there are any bottle-neck issues I need to be aware of with
an application that stores scanned images into SQL. The app uses a standard
SQL Image field for the storage, and TIFF images are being stored in it.
Currently my client is adding about 500 records (about 100 megs) per day.
The current size of the image table is just over 4 gigs. In year they will
have a 35 - 40 gig table.
Other than running out of disk space, are there any issues, design or
otherwise I should know about?
TIA,
-Steve-Lookup TEXTIMAGE_ON in Books Online under CREATE TABLE for details on how to
use separate data files to store large data.
ML|||We developed something which had a similar situation, instead of
storing images in SQL, we stored them as images on a hard disk. We had
a column in a table which had a filereference to the image on disk.
We felt that managing large SQL databases could cause a problem and
hence adopted this approach which worked out well for us.
Joshi
Steve Zimmelman wrote:
> I'm curious if there are any bottle-neck issues I need to be aware of with
> an application that stores scanned images into SQL. The app uses a standa
rd
> SQL Image field for the storage, and TIFF images are being stored in it.
> Currently my client is adding about 500 records (about 100 megs) per day.
> The current size of the image table is just over 4 gigs. In year they wil
l
> have a 35 - 40 gig table.
> Other than running out of disk space, are there any issues, design or
> otherwise I should know about?
> TIA,
> -Steve-|||Thanks ML.
Are you referring to "FileGroups" ? The documentation isn't that clear to
me.
I'd appreciate any additional pointers.
Here are the Scripts used to create the image table. Given what I've stated
about the application, what would you change?
CREATE TABLE [dbo].[Images] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FamilyNo] [varchar] (15) NOT NULL ,
[PersonNo] [varchar] (2) NOT NULL ,
[Folder_ID] [int] NOT NULL ,
[Descr] [varchar] (100) NULL ,
[ImageData] [image] NULL ,
[ImageSize] [int] NULL ,
[ImageCount] [smallint] NULL ,
[DataType] [varchar] (1) NULL ,
[CreatedDate] [datetime] NULL ,
[CreatedUser] [varchar] (6) NULL ,
[LastUpdate] [datetime] NULL ,
[LastUser] [varchar] (6) NULL ,
[TimeToPost] [smallint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[Images] WITH NOCHECK ADD
CONSTRAINT [PK_Images] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
ALTER TABLE [dbo].[Images] ADD
CONSTRAINT [DF_Images_DataType] DEFAULT ('I') FOR [DataType],
CONSTRAINT [DF_Images_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate],
CONSTRAINT [DF_Images_TimeToPost] DEFAULT (0) FOR [TimeToPost]
CREATE INDEX [IX_ImagesFamNoPerNo] ON [dbo].[Images]([FamilyNo],
[PersonNo]) ON [PRIMARY]
CREATE INDEX [IX_Images_FolderID] ON [dbo].[Images]([Folder_ID]) ON
[PRIMARY]
"ML" <ML@.discussions.microsoft.com> wrote in message
news:87B0E552-EC1B-484B-8B1C-DC28C1079345@.microsoft.com...
> Lookup TEXTIMAGE_ON in Books Online under CREATE TABLE for details on how
> to
> use separate data files to store large data.
>
> ML|||Hi Joshi,
We looked at that option, but IT decided they didn't want to manage the
backups/restores. The other problem is the shear number of images. The app
has only been in production for a few months and there are over 30,000
records with more than 500 being added daily. We also have quite a few
remote sites across the country using a VPN for data access. It would
require a bit more tweaking for those remote sites to send physical image
files to the server. Right now they just send it using a SQL stored
procedure, and SQL does the rest. But if maintaining a large database is
not something that SQL can handle very well, then we'll have to rethink our
options. My clients' objective is to become "paperless" and store all
patient documentation electronically.
Thanks,
-Steve-
<joshidm@.gmail.com> wrote in message
news:1129132566.523707.107460@.g49g2000cwa.googlegroups.com...
> We developed something which had a similar situation, instead of
> storing images in SQL, we stored them as images on a hard disk. We had
> a column in a table which had a filereference to the image on disk.
> We felt that managing large SQL databases could cause a problem and
> hence adopted this approach which worked out well for us.
> Joshi
>|||Steve,
Yeah...from the BOL as ML suggested:
TEXTIMAGE_ON
Are keywords indicating that the text, ntext, and image columns are stored
on the specified filegroup. TEXTIMAGE ON is not allowed if there are no
text, ntext, or image columns in the table. If TEXTIMAGE_ON is not
specified, the text, ntext, and image columns are stored in the same
filegroup as the table.
HTH
Jerry
"Steve Zimmelman" <skz@.charter.nospam.net> wrote in message
news:OeqlQe0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Thanks ML.
> Are you referring to "FileGroups" ? The documentation isn't that clear to
> me.
> I'd appreciate any additional pointers.
> Here are the Scripts used to create the image table. Given what I've
> stated about the application, what would you change?
> CREATE TABLE [dbo].[Images] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [FamilyNo] [varchar] (15) NOT NULL ,
> [PersonNo] [varchar] (2) NOT NULL ,
> [Folder_ID] [int] NOT NULL ,
> [Descr] [varchar] (100) NULL ,
> [ImageData] [image] NULL ,
> [ImageSize] [int] NULL ,
> [ImageCount] [smallint] NULL ,
> [DataType] [varchar] (1) NULL ,
> [CreatedDate] [datetime] NULL ,
> [CreatedUser] [varchar] (6) NULL ,
> [LastUpdate] [datetime] NULL ,
> [LastUser] [varchar] (6) NULL ,
> [TimeToPost] [smallint] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> ALTER TABLE [dbo].[Images] WITH NOCHECK ADD
> CONSTRAINT [PK_Images] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[Images] ADD
> CONSTRAINT [DF_Images_DataType] DEFAULT ('I') FOR [DataType],
> CONSTRAINT [DF_Images_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate],
> CONSTRAINT [DF_Images_TimeToPost] DEFAULT (0) FOR [TimeToPost]
> CREATE INDEX [IX_ImagesFamNoPerNo] ON [dbo].[Images]([FamilyNo],
> [PersonNo]) ON [PRIMARY]
> CREATE INDEX [IX_Images_FolderID] ON [dbo].[Images]([Folder_ID]) ON
> [PRIMARY]
>
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:87B0E552-EC1B-484B-8B1C-DC28C1079345@.microsoft.com...
>|||Steve,
Change nothing in your table.
Create a folder called IMAGES somewhere on your server. When someone
stores an image basically place the image into the IMAGES folder with
the name as ID.gif or whatever the extension is.
Joshi
Steve Zimmelman wrote:
> Hi Joshi,
> We looked at that option, but IT decided they didn't want to manage the
> backups/restores. The other problem is the shear number of images. The a
pp
> has only been in production for a few months and there are over 30,000
> records with more than 500 being added daily. We also have quite a few
> remote sites across the country using a VPN for data access. It would
> require a bit more tweaking for those remote sites to send physical image
> files to the server. Right now they just send it using a SQL stored
> procedure, and SQL does the rest. But if maintaining a large database is
> not something that SQL can handle very well, then we'll have to rethink ou
r
> options. My clients' objective is to become "paperless" and store all
> patient documentation electronically.
> Thanks,
> -Steve-
> <joshidm@.gmail.com> wrote in message
> news:1129132566.523707.107460@.g49g2000cwa.googlegroups.com...|||Thanks Joshi,
I understand the concept. But it would require quite a bit of code changing
to accomodate this. Not only the table structure, but also the application
in the way it loads, saves, and displays the images.
Thanks,
-Steve-
<joshidm@.gmail.com> wrote in message
news:1129134627.264554.161450@.o13g2000cwo.googlegroups.com...
> Steve,
> Change nothing in your table.
> Create a folder called IMAGES somewhere on your server. When someone
> stores an image basically place the image into the IMAGES folder with
> the name as ID.gif or whatever the extension is.
> Joshi|||How far back do you need to save these images in the database?
Can you archive them after a certain period?
Of course, the client will eventually want to access an archived image but
you have a while to figure that out.
"Steve Zimmelman" <skz@.charter.nospam.net> wrote in message
news:%23p05FH1zFHA.268@.TK2MSFTNGP09.phx.gbl...
> Thanks Joshi,
> I understand the concept. But it would require quite a bit of code
> changing to accomodate this. Not only the table structure, but also the
> application in the way it loads, saves, and displays the images.
> Thanks,
> -Steve-
> <joshidm@.gmail.com> wrote in message
> news:1129134627.264554.161450@.o13g2000cwo.googlegroups.com...
>|||Storing large data (text, ntext and/or image) on a separate filegroup
improves performance of queries that do not retrieve or test any large data.
You also say you expect a large quantity of images - when issuing a typical
search query the server only has to search the proportionally smaller
filegroup, containing descriptive data, and only needs to access the rather
large text/image filegroup to retrieve the few images that correspond to the
search criteria.
You should also consider designing an archive table (maybe even in a
separate database, but that's not mandatory). Of course the criteria which
the archiving process should be based upon cannot simply be the age of an
image (the amount of time elapsed since the image was inserted). Only *least
accessed* images should be archived. To accommodate this, you'd have to keep
a record of table access, and use a scheduled job to move less accessed
images to the archive table. Or something like that - in order to keep the
production table as small as necesairy.
But I really don't want to over-complicate this answer right now. :)
ML
