Severe performance issues with SQL2000 while using 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!!!!!