i am building a website which allows people to sell items online,
however the problem i am having is when users are uploading a photo of their item it stops the whole website from doing select statements on the sql table.
The Process: When a photo is uploaded it uses the SQL insert statement that saves the photo into a filestream, while this insert is taking place which can take up to a minute it blocks the whole table from select statements running,
If the select statements do not run then other users viewing the website can not access any items.
does anyone know how I can allow inserts and select statements to run at the same time on the same table
for example ebay allows many users at the same time to insert data and select data in parallel which is what i would like to achieve
First I would like to suggest to not save images in DB instead save them inside a defined folder and store their information in table. You can find many working samples for this on little bit googleing/binging.
Secondly, there is a way known as Table-Hints/ Dirty Read and they are - 1) WITH (NOLOCK) and 2) READ PAST
So you can write your select query as -
SELECT col1, col2,..
FROM tblName WITH (NOLOCK)
WHERE putyourconditionhere
If image size is heavy then it is going to kill your application as you are building a more hitting application.
Choosing folder option will give many benefits like you save your DB space, you have less overload in inserting heavy image into it, and easy to manage files in folder.
I would like to suggest you to not save your files with origianl name but with GUID name. You can keep original file name in a table where you are going to map the user with his uploaded files. For example if an user upload xyz.jpeg image then
1. generatte a new Guid and
2. upload your file with Guid name into folder
3. save the original file name with this Guid into table ( you will use guid to access the files and still you can show your users their name)
This save you overwriting issue when other user upload the same file name etc.
stuartbellam...
Member
1 Points
2 Posts
Inserting File to Filestream in SQL Server 2008 blocks access to whole table
Nov 12, 2012 08:50 AM|LINK
Hello,
i am building a website which allows people to sell items online,
however the problem i am having is when users are uploading a photo of their item it stops the whole website from doing select statements on the sql table.
The Process: When a photo is uploaded it uses the SQL insert statement that saves the photo into a filestream, while this insert is taking place which can take up to a minute it blocks the whole table from select statements running,
If the select statements do not run then other users viewing the website can not access any items.
does anyone know how I can allow inserts and select statements to run at the same time on the same table
for example ebay allows many users at the same time to insert data and select data in parallel which is what i would like to achieve
all the best,
Stuart
anil.india
Contributor
2613 Points
453 Posts
Re: Inserting File to Filestream in SQL Server 2008 blocks access to whole table
Nov 12, 2012 02:08 PM|LINK
First I would like to suggest to not save images in DB instead save them inside a defined folder and store their information in table. You can find many working samples for this on little bit googleing/binging.
Secondly, there is a way known as Table-Hints/ Dirty Read and they are - 1) WITH (NOLOCK) and 2) READ PAST
So you can write your select query as -
Refer- http://msdn.microsoft.com/en-us/library/ms187373.aspx
http://www.techrepublic.com/article/using-nolock-and-readpast-table-hints-in-sql-server/6185492
codepattern.net/blog ||@AnilAwadh
stuartbellam...
Member
1 Points
2 Posts
Re: Inserting File to Filestream in SQL Server 2008 blocks access to whole table
Nov 12, 2012 10:28 PM|LINK
Thanks for your reply,
i tried this but still didn't fix the problem,
i will move to saving the images in a folder, that way the insert statement should be instant,
anil.india
Contributor
2613 Points
453 Posts
Re: Inserting File to Filestream in SQL Server 2008 blocks access to whole table
Nov 13, 2012 05:30 AM|LINK
If image size is heavy then it is going to kill your application as you are building a more hitting application.
Choosing folder option will give many benefits like you save your DB space, you have less overload in inserting heavy image into it, and easy to manage files in folder.
I would like to suggest you to not save your files with origianl name but with GUID name. You can keep original file name in a table where you are going to map the user with his uploaded files. For example if an user upload xyz.jpeg image then
1. generatte a new Guid and
2. upload your file with Guid name into folder
3. save the original file name with this Guid into table ( you will use guid to access the files and still you can show your users their name)
This save you overwriting issue when other user upload the same file name etc.
codepattern.net/blog ||@AnilAwadh