BorlandTalk.com

Reader · Guest

Is it okay to store a ton of scanned documents (tiff files with multiple
pages) in SQL Server tables with IMAGE field types?
Will this cause performance problems?

Do you recommend that we save these files on a network drive and just
store those paths and file names in a table?

Or any other recommendations?

Thanks in advance.

SurferJoe · Guest

Depends on how much volume you are talking about. With SQL Server
performance is not an issue. A bigger problem is that your SQL backup files
will get bigger. We store AutoCAD drawing in one of our SQL db's,
performance is great, however making copies of the 3GB backup file is a
little sluggish. We created a data aware TOleContainer so users can cut and
past drawings or documents into the Image Field, its pretty slick.

On the other hand we store tons of PDF's & DOC's in their native file format
on disk and reference them in the database for retrieval. We only store the
document name and extension, the file path can be fixed, user defined,
system variable or registry key.

Once you know the file path, name and extension, you can hand it off to
ShellExec and it will magically open the file no matter what it is. Leaving
file management to the OS greatly simplifies things.

The following code snippets are my original design, I think you may find
them useful.

procedure TFormX.wwDBGrid1CalcCellColors(Sender: TObject;
Field: TField; State: TGridDrawState; Highlight: Boolean; AFont: TFont;
ABrush: TBrush);
begin
// This draws my SutoHyperLink ----------------------------------
if (Field.Name = 'wwClientDataSet1Document') then begin
Afont.color := clBlue;
Afont.Style := [fsUnderline];

end;

end;

procedure TFormX.wwDBGrid1MouseMove(Sender: TObject;
Shift: TShiftState; X, Y: Integer);
var
myGridCoord : TGridCoord;

begin

// This sets the cursor to HandPoint when the mouse is over my SutoHyperLink
myGridCoord := wwDBGrid1.MouseCoord( x, y);
// Label1.Caption := 'Col = ' + IntToStr(myGridCoord.x) + ',' + ' Row = ' +
IntToStr(myGridCoord.y);

if ((myGridCoord.x) = 14) and ((myGridCoord.y) > 0) then
Screen.Cursor := crHandPoint
else
Screen.Cursor := crDefault;

end;

procedure TFormX.wwDBGrid1DblClick(Sender: TObject);
var
f: String;

begin
// This little jewel opens the SutoHyperLink ----
// WpDocPath is a global string variable that gets set on start up.
if (Screen.Cursor = crHandPoint) then begin// Over a document Field
Screen.Cursor := crHourGlass;

f := WpDocPath + wwClientDataSet1Document.AsString;

if FileExists( f) then
ShellExecute(handle, 'open', PChar(f), '', '', SW_SHOWNORMAL)
else
MessageDlg('File ' + f + #13 + #10 +
'is not presently available or could not be found.', mtError,
[MbOk],0);

end;
Screen.Cursor := crDefault;

end;

All the best,

Greg Rowland

Copyright © 1997 PGRInternational & WaveLtd.com 1997

Doni Devito · Guest

Hi,
This is commonly asked question while storing files. We have a product that
runs on 40 customers. 5 years ago, while we are designing the product, we
put a parameters to store files to DB or to file system. We are using MS SQL
Server. Some of our customers have experiance about archiving. All of them
prefers to store DB, some old fashioned guys prefers file system. One of our
customers DB is 300 GB and working very well.
Here is my suggestions and points;(I will give examples about 300 GB
database)

1.backup is a problem in both situation. If you try to backup 300GB data, it
takes 2 hours and a big space. If you are using file system, you will also
back up file data. It takes 10 hours which is impossible to back them up
daily.

2.If you are using compressible files, compress them. User cant notice the
time. (Office files, PDF etc) If you are using tiff(multi page or single
page) try using a file format different then tiff. We are using DJVU
(www.lizardtech.com) We are only storing it in DJVU format. While using, we
are converting it to tiff. An Tiff G4 format A4 file is approximately 50K
and djvu file is around 10K. The conversion is very very fast that client
cant understand the difference. Also we are dividing multipages to single
pages and displaying them as tree with thumbnails. thus client can select a
page and open it. If you are using 30 pages tiff it means you are
transferring 1500K to see page 5. If you divide to pages and convert to
tiff, client will transfer 10K. In my opinion, the importing thing is not
storage area. The important thing is opening/saving performans over
LAN,WAN,internet.

3.Try to create something like file server. The client must always talk to
file server program. You can create it simply with indy. Or examine with
kbmMW (www.components4developers.com). They have file service, we are using
it and its very well product. In this situation, client will always speak
with file service locates on somewhere in network. Its very fast according
to opening file from network. In wide area, some networks forbid to access
network or OS checks the user rights every time. It takes long time in big
networks if there are switches, hubs etc. And the file service can cache the
files, use it. In kbmMW, you can use load balancing and clustering. Please
use it.:)

5.If you insist to store files to OS, put little amount of file to one
directory. Prepare a directory structure. For example, give unique file
number to all file. Plan the amount of file in your storage system(I planned
1 billion). Store file like this;
lets say the document number is 123.456 and you planned max number is
999.999.999. Create directory 0\0\0\1\2\3\4\5\6 and put file to that
directory. this means that every directory have max 10directories inside.
And the last directories must have 10 files max. this will reduce the access
time.

if you have further questions, ask here.
good luck!

"Reader" <Reader (AT) NoMail (DOT) com> wrote in message
news:464b38d2$1 (AT) newsgroups (DOT) borland.com...

Alain Quesnel · Guest

From MSSQL 2005's Books on line:

ntext, text, and image data types will be removed in a future version of
Microsoft SQL Server. Avoid using these data types in new development work,
and plan to modify applications that currently use them. Use nvarchar(max),
varchar(max), and varbinary(max) instead. For more information, see Using
Large-Value Data Types.

--

Alain Quesnel
alainsansspam (AT) logiquel (DOT) com

www.logiquel.com

"Reader" <Reader (AT) NoMail (DOT) com> wrote in message
news:464b38d2$1 (AT) newsgroups (DOT) borland.com...

Eduardo A. Salgado · Guest

Doni,

I just happened to be looking in.

Wonderful explanation of considerations for a storing system.

Thanks!

- Eduardo
Stop Continental Drift!
-- Anon

Eminent Domain Software
"Custom Software Development For Your Domain"

Makers of EDSSpell, EDSPrint, EDSZipCodes and
XSpell, the IDE Expert.

Doni Devito wrote: