locked
Need to your Idea - about designe Database RRS feed

  • Question

  • I work with VB.Net , Sql Server 2008 , .Net Framework 3.5

    We design software that I have many Personnel records to scan and file storage to (About 50000 Folder that has average 50 number of leaf)

    The program will run on LAN network that run some other programs there are

    and The program have average is about 20 simultaneous users

    We want the best performance at run time have
    There are two choices for us
    1- Scan Pictures of folders and save to hard disk and save the address of files to Sql server Database
    2- Scan Pictures and save to sql server database

    Which option you choose and why ?
    Thanks



    Tuesday, July 27, 2010 8:14 AM

Answers

  • Well, first question. How important are the records? And how much will they grow? 

    I use the rule of thumb that if it is important, you need great security, and you want to be able to back up the database and files together, put the data in the database.

    Second best is to use filestream in 2008 (it isn't tremendously worse than just directly in the database, and is much faster to use, since you can (in a transaction) use the file like a normal file). I understand there to be devices that you can use to make this fast and super secure, but Ican't point you in the direction of one.

    Finally, if the records aren't critical, and/or security isn't as critical, then address of files is fine.  It is just a lot more spread out than in the database, and you will have to manage security in two places.

     


    Louis

    Wednesday, July 28, 2010 3:53 AM
  • Yes, I agree with Louis.

     

    Adding to Louis, by storing the Image/Files in Database column like (TEXT/ VAR-BINARY (MAX)...etc...), your select statement will be little slow as compare to storing a "File Path".

    It’s obvious as the data increase the performance will be slow. But in this case because of size of the data type, even you store only one row it will be slow.

    For example: We have faced similar kind of issue then we store data into a TEXT column. But when we changed the data type of column from TEXT to VARBINARY column then the performance is much better than TEXT column type.

    So my suggestion is to store just “File Path” and gets the performance as per the simple SQL Query.

    Also FILESTREAM is also a good suggestion but you need to know how to use this. But the simple way is “File Path”.

    Thanks,

    Sandeep

    Wednesday, July 28, 2010 5:11 AM
  • Hi Ashkan, as per your requirement I would suggest to go with following options.

    NOW

    With these assumptions, you suggest me to save the files in the HDD? and save (File path) to database ?

    Answer: Store File Path / Option 2 - File Stream (If you are clear on File Stream concepts)

    Please note that if you are going to store the “file path details” in your database you can go with "VARCHAR (8000)/ (Size of URL)” column.

    c) If you want to save the image into the database, Can you suggest the type of column ?

    Answer: VARBINARY (MAX)

    Thanks,

    Sandeep

    Monday, August 2, 2010 5:28 AM

All replies

  • Well, first question. How important are the records? And how much will they grow? 

    I use the rule of thumb that if it is important, you need great security, and you want to be able to back up the database and files together, put the data in the database.

    Second best is to use filestream in 2008 (it isn't tremendously worse than just directly in the database, and is much faster to use, since you can (in a transaction) use the file like a normal file). I understand there to be devices that you can use to make this fast and super secure, but Ican't point you in the direction of one.

    Finally, if the records aren't critical, and/or security isn't as critical, then address of files is fine.  It is just a lot more spread out than in the database, and you will have to manage security in two places.

     


    Louis

    Wednesday, July 28, 2010 3:53 AM
  • Yes, I agree with Louis.

     

    Adding to Louis, by storing the Image/Files in Database column like (TEXT/ VAR-BINARY (MAX)...etc...), your select statement will be little slow as compare to storing a "File Path".

    It’s obvious as the data increase the performance will be slow. But in this case because of size of the data type, even you store only one row it will be slow.

    For example: We have faced similar kind of issue then we store data into a TEXT column. But when we changed the data type of column from TEXT to VARBINARY column then the performance is much better than TEXT column type.

    So my suggestion is to store just “File Path” and gets the performance as per the simple SQL Query.

    Also FILESTREAM is also a good suggestion but you need to know how to use this. But the simple way is “File Path”.

    Thanks,

    Sandeep

    Wednesday, July 28, 2010 5:11 AM
  • Thank you very much

    but highlights of this project

    1-After finishing the scanning cases , Daily 10 new cases have (About 500 image per days)

    2- Cases are not very security.

    3- when the scan finished work , Speed access to file images is very important .

    4- Must take daily backup

    With these assumptions, You suggest me to save the files in the HDD ? and save (Filepath) to database ?

    thanks again


     

    Friday, July 30, 2010 5:02 PM
  • Thank you very much

    but highlights of this project

    1-After finishing the scanning cases , Daily 10 new cases have (About 500 image per days)

    2- Cases are not very security.

    3- when the scan finished work , Speed access to file images is very important .

    4- Must take daily backup

    NOW

    a) With these assumptions, You suggest me to save the files in the HDD ? and save (Filepath) to database ?

    b) If your first answer is ok, Can you suggest the type of column is (VARBINARY) ?

    c) If you want to save the image into the database, Can you suggest the type of column ?

    thanks again

    Friday, July 30, 2010 5:23 PM
  • Hi Ashkan, as per your requirement I would suggest to go with following options.

    NOW

    With these assumptions, you suggest me to save the files in the HDD? and save (File path) to database ?

    Answer: Store File Path / Option 2 - File Stream (If you are clear on File Stream concepts)

    Please note that if you are going to store the “file path details” in your database you can go with "VARCHAR (8000)/ (Size of URL)” column.

    c) If you want to save the image into the database, Can you suggest the type of column ?

    Answer: VARBINARY (MAX)

    Thanks,

    Sandeep

    Monday, August 2, 2010 5:28 AM