I have some files that I would like to attach to my sde features. Files are small in size ( <40 KB) and have 1:1 relation to features.
I wonder which method is superior, specially in terms of performance?
1) to add a URL field to my features and store my files on the file server
2) To let arcsde do it for me on the database (attachment table)
What are the advantages and disadvantages of storage of small files within the SDE geodatabase?
Best Answer
Well, no one is gonna answering. Therefor based on my own tests, I guess I can provide some useful information in case anyone interested in sde attachments.
Storage of any attachment in file server (IIS in my case) provides unbeatable export speeds, especially in case of facing concurrent requests. However, the performance issue in using URL method here is that the users need to fetch the URLs before starting to download the files. I realized that the actual bottleneck of the system is not the storage tier (DB or File-Server) but it is the ArcGIS server itself, which should provide the users with some attribute data, i.e the attachment URLs or attachment information.
In brief, my performance results show that:
(1) In both methods, arcgis server is the main source of delays that users experience. For example SDE's default configuration could export each file in 0.140-0.218 seconds on my system and IIS in 0.0198 sec. However, the total process of acquiring URLs from GIS server and downloading the files takes about 0.839 sec. This value is very close to 0.814 sec for SDE method which consists of fetching the "attachment info" through the REST interface and downloading the files.
(2) When focusing on batch exports, for example migration or backup scenarios, we can expect less drawback on behalf of DB for larger datasets. I realized that performing queries on large tables can provide faster export speeds in comparison with url requests to IIS. It means that DBs are more able to reduce overheads "per feature" when facing batch export scenarios. Nevertheless, I should emphasis here that we used the term "batch export", which by that we mean the BLOB data is not turned into real files in this process. Conversion of BLOBs to files, which is necessary for the "end-users", definitely requires client-side or server-side process and perhaps will end in results similar to (1).
(3) A research article about the break-even point of using BLOBs and FS was interesting for me, since it used a fully controlled environment in testing the performance of DBs and files system storages.To blob or not to blob
At last my most important point, which is not performance-related, is: SDE attachment method provides very good consistency. In case any errors happen, SDE just rolls back. However, when relying on file servers you need to develop an especial process yourself to maintain the consistency of your attachments. You need to make sure that URLs are pointing to a valid address. Whenever an incomplete attachment upload, a change in feature attachment or a feature/attachment delete occurs you need a process to make sure that everything is fine. SDE on the other hand, takes advantage of transactions and even provides you with versioning.
So I believe that if you have your features in SDE, having your attachments in SDE too is "easier" and "not slower" than having them in File-Server.