Unanswered: Severe performance issues with SQL2000 while storing BLOB
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, _
Dim MyCB As SqlCommandBuilder = New SqlCommandBuilder(da)
Dim ds As New DataSet()
Dim fsi As FileSystemInfo
da.MissingSchemaAction = MissingSchemaAction.AddWithKey
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)
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"
Please see if anyone can help!!!!!
What are you trying to do? You should try to redesign it! SQL server is not file server.
I will recommend you to move all the .avi files to a specific file server and than insert into the database one record for each file that identifies its location and information(name,time,size,owner ,camera etc’). In the Data base you should save logic / textual / numeric data that can point for specific file. (the file name and path should be design with good naming convention).
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 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.
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:
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.
You have to understand that you are trying to implement something that SQL server was not planed to do , is not good in ,and you will have always ,with any workaround performance problem .
If you would like to / your requirement is to store the .avi files on the same Disk that the SQL data file locate than create on this disk new directory with the name “Fileserver” ( can be virtual folder for easier upload-copy) and store their all the files . I would like to say again that SQL server does not have any advantage vs file system ( NTFS) in storing this files . The only thing that you will get here is problem like you already have , and while roduction you will find more .