Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Jacksonville, FL

    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 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 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
    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)
    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"
    da.Update(ds, "binary_data15")

    Next fsi
    Please see if anyone can help!!!!!

  2. #2
    Join Date
    Jul 2002


    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).

  3. #3
    Join Date
    Jul 2003
    Jacksonville, FL
    Hi eschapir

    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 application is running. However I can free-up the memory of 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.

  4. #4
    Join Date
    Jul 2002


    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 .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts