August 1, 2008 at 2:26 am
Hello,
I have a table with 2.5 million records. I will insert bulk of records in this table using BULK INSERT every day(more than 4 times). While Bulk insert I could not select the records from this table. Its taking too long time to execute the query in SQL Query Analyzer. In my web application I have a report showing from this table, it thrown an error "Timeout Expired". After insertion finished, I can select the records from the table and can see the report in my application without any problem.
Please tell me why its happening like this. Is the table locked? (I think so.) If yes, how to know whether this table is locked or not?. Because I will show the friendly message to the user who are seeing that important report in my web application. How to avoid this locking?
Additional Information
* Table has 2.5 million rows with 30 columns and has a Primary Key field.
* Table has a Indexed View with 25 columns
* Indexed View has a Unique Clustered Index and has non-clustered index of remaining 24 column (to speed up my reports.[querying all the fields thats why])
Please give your valuable suggestions.
Thanks in advance.
Ganesh.
August 1, 2008 at 5:48 am
Once you pass a certain number of rows, the engine has to lock the table to be effecient. There's no getting around that fact. So, instead, you have to look at different mechanisms for doing the load.
Assuming you're adding to the table, not replacing it every time, one thing you could try is using a partitioned table. This mechanism would allow you to load the data to a different physical storage location that is marshalled together with other physical storage to make a single table. There's a whole bunch of information here at SSC and in the BOL.
You could try using A/B switch mechanism if you have to replace the table every time. This would entail having two copies of the table. Have the users access one while you load the other. Once the load is complete, rename the tables to swap them out. That will lock the table temporarily, but should pretty radically reduce the time that users can't access it to a number of milliseconds instead of however long your data load is. That might be problematic with a materialized view though. You'll need to test it out to be sure it will work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 1, 2008 at 7:29 am
Great! Thanks for your valuable suggestion.
Ganesh.
August 1, 2008 at 7:32 am
But now I noticed that, the whole database gets locked. I could not query any other tables.:(.
Is this locking related to Remote Query.? Bcz I will query as Remote Query using linked server.
Please suggest me.
Ganesh.
August 1, 2008 at 8:10 am
Hrmmm...
Unless this is the only table in the database, I'd be really shocked to see a database lock based solely on inserts to a single table. I'm guessing there's a lot more complexity to this import than you've mentioned so far. What kind of isolation level does this database have? Are you changing options on the DB during the load?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 1, 2008 at 10:01 am
Ganesh Babu (8/1/2008)
But now I noticed that, the whole database gets locked. I could not query any other tables.:(.
Off-hand, it sounds like it is having to expand the database. Make sure that your database is pre-extended for this data & work and do not shrink it in-between.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 1, 2008 at 10:31 am
Ooh. I hadn't thought of that.
It wouldn't show up as a database lock though, would it?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 1, 2008 at 11:42 am
Afraid that I do not know, Grant. Maybe someone else does?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 1, 2008 at 11:51 am
Now that I'm thinking about it (and I'm also asking around) unless the database is only a single filegroup, it's unlikely that it would show up as an actual locked db because the individual file or file group would be expanding and the rest of the database would be accessed as normal. I'm still checking around to see if someone has a more definitive answer.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 5, 2008 at 7:54 am
Have you tried disabling the indexes prior to the insert; then enabling them once inserted? This should provide a significant boost.
September 8, 2008 at 12:14 am
You should check what type of locking procedure you are using; and by the time you want to view the data on your table; try to make use of NOLOCK so that you can freely select the data.
by the way i suggest that you use a data warehousing for your database; now that you mentioned you had a record with 2million rows and you use to insert bulk records. data warehousing will solve your problem. if you use .net on your web or SQL2005 try to implement the cube. you can reply on that.
_____________________________________________
[font="Verdana"]ToM™[/font] (Possibility will come out of nothing)
September 8, 2008 at 5:49 am
rbarryyoung (8/1/2008)
Ganesh Babu (8/1/2008)
But now I noticed that, the whole database gets locked. I could not query any other tables.:(.Off-hand, it sounds like it is having to expand the database. Make sure that your database is pre-extended for this data & work and do not shrink it in-between.
Surely though, if that were the issue, then the lock would only be there while the expansion was taking place and not throughout the load?
September 19, 2008 at 3:43 am
Frnds....
Can we use Horizontal Partitioning on table ? this may reduce the processing time..
September 19, 2008 at 8:50 am
Ganesh - verify that when you try to query another table that your process is actually blocked (check sp_who2 for a blocked process). If it is not being blocked, it is likely that the process is SUSPENDED because it is waiting for the database to expand or simply waiting for your disk drives to catch up.
Avoid ToM's suggestion of using NOLOCK - especially during a bulk insert. It is very likely during a bulk insert of a significant size to cause data to be read multiple times and data to be skipped because of data being moved around in your indexes. This is a pretty common mis-use of the NOLOCK query hint.
September 19, 2008 at 7:22 pm
Grant Fritchey (8/1/2008)
Ooh. I hadn't thought of that.It wouldn't show up as a database lock though, would it?
No... in fact, it wouldn't show up as anything because you simply can't do anything while the database is growing. I think they fixed this in Katmai, but I'm pretty sure that growth periods are exclusive periods in 2k5.
The other thing is that importing directly into a table that is also being used for OLTP is a form of death by SQL. A staging table should be used so that you can verify the data without the excessive time it takes for constraints and a bunch of other stuff to happen.
Also, check the Clustered index... if it doesn't make it so the data is inserted at the logical end of the table, then you're going to get a bunch of page splits... doesn't take very many for SQL Server to say "Ok be that way... I'm locking the whole table".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply