Severe performance issues with SQL while storing BLOB

Hi

I am working on an application which involves 16 video camera streaming video feeds 24X7. The cameras are attached with video sensors, so whenever a motion is detected, the cameras start recording an AVI file. The file can be upto 15 mins long. Once the file is recorded, the application copies it to a folder on my hard drive.

I have created a VB.net application, which runs every 30 seconds to check if there is a new file on hard drive. If a new file is found on the hard drive, it copies it over to SQL table and deletes from hard drive.

While testing the application, I found that copying anything above 50 MB size is a nightmare. The memory utilization goes upto 2 Gigabyte for 250 MB file( I checked with Task Manager). Although I am using P4 2.4 GHz dual processor with 1 GB RAM, I am getting low virtual memory exception. The application then crashes without storing video to database.

My VB.net code browses the folder on my hard drive and searches for new file sequentially. If a new file is found, it is captured as a FileStream object. While debugging the application, I found that capturing the file in FileStrem object does not take much time or memory. It is when I try to execute the query and try to save the video to database.

Sample code looks like: (This is just sample, actual working code is quite different)
*****
Dim da As New SqlDataAdapter _
("Select * From binary_data15", con)
Dim fs1 As New FileStream _
(“C:\dsr-videp\A.avi”, FileMode.OpenOrCreate, _
FileAccess.Read)
Dim MyCB As SqlCommandBuilder = New SqlCommandBuilder(da)
Dim ds As New DataSet()
Dim fsi As FileSystemInfo
da.MissingSchemaAction = MissingSchemaAction.AddWithKey
con.Open()
da.Fill(ds, "binary_data15")

For Each fsi In dir.GetFileSystemInfos()

Dim strFileSize = fs1.Length
Dim strCameraFolder As String
Dim strDateFolder As String
Dim MyData1(fs1.Length) As Byte
fs1.Read(MyData1, 0, fs1.Length)
fs1.Close()
Dim myRow1 As DataRow
myRow1 = ds.Tables("binary_data15").NewRow()
myRow1("imgField") = MyData1
myRow1("FileName") = strFullName
myRow1("DateCreated") = strDateFolder
myRow1("CameraNo") = strCameraFolder
myRow1("FileSize") = strFileSize
myRow1("CreatedTime") = creationTime.ToString
myRow1("Description") = "This would be description text"
ds.Tables("binary_data15").Rows.Add(myRow1)
da.Update(ds, "binary_data15")

Next fsi
******

You are asking a lot of a db to store that kind of data. I strongly suggest you only store the file name, directory, size, datestamp and maybe a few other things. You didn't specify which database/version you are using, but I am willing to bet that most products are NOT going to stream the data, but will attempt to load it all into RAM and process it before actually putting it into the table. I would like to hear your reasoning for storing the data in the database in the first place. To view it you are going to have to do lots of programming to take the unstructured blob data and stream it (some databases will refuse to stream data objects and you will have to buffer the entire thing yourself), when you can much more easily just store the 'blobs' as files on the harddrive where they are right now.

Hi mitakeet

Thanks for the reply. I am sure, I am asking a lot from a DB to store this kind of data. However, before creating this application, I went through lot of articles regarding how efficiently SQL can handle large amount of data. It seems developers world over are equally divided on whether to store a complete file as BLOB or just reference to the file.

The reason for storing the data in the database is the requirements from customer. Customer already has all this information on the Hard Drive and a very efficient application, which can provide all the features of querying and retrieving the data from hard drive. But they need to store all the data to database for many reasons including to get rid of storing the data on different hard drives.

I am using SQL2000 version 8.00.194 and service pack 3

I guess you understood the problem 100% right. When I keep the task manager for the machine where the application is running, I can see the memory usage shooting up. However the CPU usage reaming below 2%. With each file uploaded to database, the memory usage goes up, till the point where I get low virtual memory error message.

Now, as per your reply it is possible to program in a way that I can stream the data while uploading it to database as well as while retrieving the data. I would certainly like to know if that is possible.

One more option, I would like to try is to release the memory after each object is updated to database. e.g. I am creating a FileStream object for each file in a directory. Once the file is updated to the database, I set the FileStream object to Nothing and flush it. But the memory is not freed. Consider this example. I have 10 files (50 MB each) in my directory which I need to update to database. When I start reading them to FileStream object one after another like:

Connection.Open
For each FileSystemInfo in DirectoryInfo
Create the FileStream
Updata data to Database
Dispose the FileStream
Close the For Loop
Close the Connection

When I debug this application, I can see that although I am disposing the FileStream object after it is used, the memory usage keeps going up each time a new FileStream object is created. The only time I get the memory freed up is when I close the complete application. Even stranger is the behavior of server where the SQL is installed. When I track memory usage of the server, I can see the memory shooting up almost in a same way the machine where the VB.net application is running. However I can free-up the memory of vb.net machined, by closing the application as I wrote above. But the SQL server memory is not release as long as I restart the SQL Server and SQL Server Agent.

Again, on the server where the SQL is installed, when I log in to Enterprise Manager and try to retrieve all the rows for the table, the memory usage shoots up to 2.09 GB. Now this is strange to me, as there is no VB code coming into picture. I am on the server trying to just view the data (no I/O operation) and SQL2000 tries to load the objects in table rows to memory.

I hope you got replies to all your question. Please see if you can guide me through this mess.

Thanks
Jignesh

I suspect that there may be a bug in some library somewhere that is not releasing memory. I wouldn't necessarily expect the memory to decrease with the release of the object as the application is likely to hold onto the memory in case you want to use it again, but I am concerned that it appears that it is not reusing the memory on the next call. I have worked with TEXT (as opposed to BLOBs) before, though the largest object I think I worked with was around 200K. I was using third-party db access libraries and there was a bug that failed to release memory and I was having the same sort of problem (I was on a machine with 32 GB of RAM, but the program's performance decreased to next to nothing when it had got to around 4 GB (I had left it running over night when that happened)). Due to anecdotal comments from the db API developers (who worked with SQL 2000 as well as Sybase (which, if you don't know, is the original kernel for MS SQLServer)), I suspect, but have no knowledge to confirm, that MS SQLServer will load the entire TEXT/BLOB into RAM before it writes it to the disk. I also am pretty sure that the database will read the entire blob into RAM before sending it across the network and I strongly suspect that when you read the object at the client side it will also attempt to buffer the entire thing in memory (meaning you won't return from the select statement until the entire object has been retrieved). You may be able to find work arounds, but I still suspect that the best 'solution' will be to give up the idea of sticking the data in a database. I am facing similar (though the scale is MUCH smaller) issues in a development project right now. A user wants to collect a bunch of images and associate them with data in a database but I don't want to store the images directly in the db. I also don't want to store hundreds, perhaps thousands of files and have to worry about maximum number of files per directory, etc. My plan is to store all the images in a single file and store the offset and length in the database. That way I can have the best of both worlds, a clean database and a single (or few, I plan on starting a new 'master' file once it reaches 600 MB) file for images. My plan is to build an 'image server' that listens on a socket and can write the image to file and the appropriate data to the database. It will also retrieve the data and since I can control both ends of the transaction, I know that I will be able to stream all the data and not have to worry about the image having to be stored in RAM (not that that will be an issue for my particular application, as I expect my images to be in the 1-100K range). In effect I am creating my own database, but since I have very little expectation of any deletions and don't expect to have a high level of transactions, I don't need anything fancy (though I expect performance to be orders of magnitude better).

BTW: What is the logic of your client that they require the files to be stored in the database? Retrieval is very inefficient as you have much more overhead than a file system, and after all, you are still store the data on a hard drive. If the concern is redundancy and they have a RAID drive for the database, why not just store the files on the same drives? I can accept logic of storing small binary data in a database, less to keep track of and backups are simpler, but objects of the size you are talking about just don't make much sense to me. The data will all still be moving back and forth across the network, the backup tapes will have to store all the data, where is the performance benefit? Storing all the files in a central location certainly makes sense but that is no reason to require that they be stored in a database. Also, think about backing up that database. Even though gigabytes of those AVIs (and why not MPEGs, by the way?) have not changed by one bit, every database backup will include every bit of those files whereas if you make a backup of a file system, you can easily backup only those files that have changed (i.e., are new) from last time (presuming you keep the past backups, of course). I don't know how much influence you have with your client, but it is a reasonable thing, in my mind, to question the requirements and suggest alternatives. SQLServer has the capability to retrieve portions of a TEXT or BLOB, but I expect your application will always want to retrieve starting at the beginning and proceeding toward the end (but how many times are they actually going to view the entire thing?).

If you are stuck with having to use the database for whatever reason, I am sorry to say that I can't provide much meaningful input. I also lack knowledge of VB.net (though I did program in VB 6 for 3 years), so I can't help you there either.

Good luck! Please let me know if you do find a resolution to your problem, particularly if it happens to be a bug in the libraries.

Just as an aside, looking at your original post, how can you be sure the file is done being copied before you attempt to start to processing it?

Mitakeet