January 18, 2011 at 3:41 am
I have 10 stored procedures, all inserting data into 2 tables.
All procedures are on the db but executed from SSRS which calls all of them pretty much simultaneously, or atleast within seconds of each other.
When executed each proc inserts data into table A and then groups the table A data that its just inserted, into table B.
So for example, proceure 'myfirstproc' inserts 100 records into table A and then sums these and inserts say 20 records into table B.
there is no overlap in the records - so no procedure sums data that was generated by another procedure.
My question is this: I can see that the procedures are blocking each other.
so myproc is ready to insert data but it cant because mysecondproc has a lock on table A & B.
At least, that is what i think is happening. Its taking over an hour for the SSRS report to run. When I do an sp_who I can the procs are blocking each other, which leads me to conclude the above. After awhile the locks clear and the procedures finish.
Any ideas on what I can do to prevent the locks from happening or make it quicker. I could create 10 separate tables, one for each proc, but that doesnt seem right to me.
Many thanks
January 18, 2011 at 7:34 am
So if I understand the process, you've got a query that inserts into Table A (prosumably selecting from other sources), selects from that table using some kind of aggregate query, and then inserts into another table, and I assume selects again...
And this is for a report? I think you're process is off, right from the start. Rather than worrying about blocking, you should reassess what you're doing for this. In general, a report should pull data out of the database. If you need to aggregate data in different ways you might consider having a pre-aggregated table, maybe a materialized view. But this process sounds extremely awkward.
Do you have clustered indexes on the tables? If not, they're heaps. This means the data is getting inserted wherever there is space, which can be a very slow process. Creating a cluster on a meaningful column (or columns) that will differentiate one process from another will help to reduce the blocking you're seeing, but it won't eliminate 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
January 18, 2011 at 7:50 am
Yes, the report aggregates data into a holding table (Table A) but only at run time. It then consolidates the data into another table (table B), before selecting off table B for the report. The report is heavily parameterised so it can’t be pre-aggregated, though it is to an extent but not totally.
So there’s no way around it, other than using clustered indexes, which I already have but will revisit it.
thanks
January 18, 2011 at 8:07 am
The thing is, your process is causing the blocking. I'd seek a solution by address the process. I think you're in a "Doctor, doctor, it hurts when I do this" type of scenario. Have you tried simply selecting the data and then using the aggregation methods within SSRS? How about using temporary tables instead of shared permament tables?
"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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply