June 21, 2009 at 7:51 am
Hi All,
I have a query. I am working on a multi-user desktop application inwhich a requirement is there that user needs to import a very heavy data & at the same time some other user can insert / update on the same tables which are already in use by import. But I ma facing the multi-user issues.
I am using Sql Server 2005. Is there any solution for this? Please update anybody has any robust solution.
Regards,
MG
June 21, 2009 at 7:58 am
NOLOCK hint is what i can think of. Reading can be done using nolock but beware of the consequences, You might read inconsistent data.
June 21, 2009 at 8:03 am
NOLOCk is used for reading the data in select statement. My scenerio is at one point one user is inserting the records through import from excel to database & at other place some other user is using the same tables & inserting new records through data entry screen OR updating the exisitng records.
While selecting searching there is no issue as we can manage from NOLOCK, but for adding new records OR updating existing records issues are there. Pls suggest.
June 21, 2009 at 8:06 am
How do you load your data into database? There are many ways to do this and there are many-1 ways to work slower than possible.
June 21, 2009 at 8:20 am
also how are the users updating the records? one by one or bulk updates? Do you have indexes built on the columns which are used for updation?
- proper indexes will help u update much faster than performing table scans
- locks will be placed on rows which are getting updated, there is no escape.
June 21, 2009 at 8:21 am
We have different imports for loading data in database. We are using 2 options:
1. Fetching data from front-end from excel. Making XML. Passing XML to stored procedure. Filling temp table with XML data. FInally, filling main table from temp table in one shot.
2. Using Bulk import feature of sql server 2005 form importing data.
Other user is doing one by one update to a specific record OR adding a new record although other side import is running.
Indexes for faster update is the second option but I need the solution for update at the same time. That is not happening.
June 21, 2009 at 8:37 am
First, congratulation twice the best performing solution in my opinion 😎
1. Fetching data from front-end from excel. Making XML. Passing XML to stored procedure. Filling temp table with XML data. FInally, filling main table from temp table in one shot.
As Pradeep Singh wrote, check if all needed indexes exist (also on your temp table). If you get locks try to work in batches for many rows. If you parse 100.000 (theoretic size) rows by XML into your temp table a complete INSERT/UPDATE of all values causes large locks (maybe table locks) on your destination table. Try to work in smaller batches, e.g. add an IDENTITY column to your temp table if not yet done and use it to INSERT/UPDATE in 10.000 (theoretic size) batches.
2. Using Bulk import feature of sql server 2005 form importing data.
Did you check "lock_on_bulk_load" option in sys.tables? Did you check activity monitor while bulk import, does it create a table lock?
Maybe try importing your data into a staging table and use T-SQL to INSERT data into destination table.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply