November 8, 2010 at 8:14 pm
Hi All,
I need help with a performance issue. I have an update statement that is part of a larger stored procedure. The stored procedure is part of a large, ugly, SSIS package. When I run the update alone, or run the stored procedure alone, it runs in less than a minute. When run in the context of the SSIS package, sometimes it runs quickly (about 30 seconds), other times it may take hours to complete.
UPDATE dbo.eyecare_customer_call_summary
SET dbo.eyecare_customer_call_summary.required_calls = eyecare_customer_call_summary_1.required_calls
FROM dbo.eyecare_customer_call_summary
JOIN dbo.eyecare_customer_call_summary AS eyecare_customer_call_summary_1
ON dbo.eyecare_customer_call_summary.customer_id = eyecare_customer_call_summary_1.customer_id
AND dbo.eyecare_customer_call_summary.period_date = eyecare_customer_call_summary_1.period_date
AND dbo.eyecare_customer_call_summary.product_id = eyecare_customer_call_summary_1.product_id
WHERE (dbo.eyecare_customer_call_summary.period_type_id = 3)
AND (eyecare_customer_call_summary_1.period_type_id = 1)
CREATE TABLE [dbo].[eyecare_customer_call_summary](
[customer_id] [nvarchar](50) NOT NULL,
[period_date] [datetime] NOT NULL,
[period_type_id] [int] NULL,
[product_id] [nvarchar](20) NOT NULL,
[required_calls] [int] NULL,
[actual_calls] [int] NULL,
[called_appropriately] [int] NULL
)
CREATE UNIQUE CLUSTERED INDEX [idx_EyecareCustomerCallSummary_Customerid_Product_Period_PeriodType] ON [dbo].eyecare_customer_call_summary]
(
[customer_id] ASC,
[period_date] ASC,
[product_id] ASC,
[period_type_id] ASC
)
The table contains less than a million rows. With or without the index does not seem to affect the performance one way or another (I just added it this week to see what would happen. There has not been an index on this table in the 2+ years that this procedure has been running).
The symptoms that I see when this statement is behaving poorly, monitoring sys.dm_exec_requests, are that reads stay constant over time, logical_reads rise slowly, and writes rise even slower. What should I be looking at?
November 8, 2010 at 9:51 pm
A couple of ideas.
1) Look to see if its waiting on locks. Specifically when it runs long, see if it's waiting for an exclusive table lock. It might have to do with selectivity and the internal locking mechanisms.
2) Run a server side trace and grab the actual xml execution plans for both short and long term runs. Load them up as .sqlplans and see if ther's significant differences.
3) Get a rowcount of that table with and without the where clause. See what your selectivity percentage is. If it's 0.3% or lower, that index should affect it. If it's between 0.3% and 0.5%, it *might*. Over 1%... yeah, that's probably DOA as an index.
4) Try not using the self join if you can get one small subset going. Extract the data on the smaller side out to a #tmp or @Tmp, and then update with the larger set. You might get away with some optimization for higher selectivity that way.
I'd need to be on the system at the time of the blockage to really help you further than that. Poke around in sysprocesses, see if you can expose the blockers. You really need to determine what it's waiting for.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 8, 2010 at 9:51 pm
Just a little more information is needed. Some sample data (as a series of INSERT INTO statements), and the expected results based on the sample data. About 5 to 10 rows of data would be nice.
November 9, 2010 at 12:30 am
UPDATE dbo.eyecare_customer_call_summary
SET dbo.eyecare_customer_call_summary.required_calls = eyecare_customer_call_summary_1.required_calls
FROM dbo.eyecare_customer_call_summary
JOIN dbo.eyecare_customer_call_summary AS eyecare_customer_call_summary_1
ON dbo.eyecare_customer_call_summary.customer_id = eyecare_customer_call_summary_1.customer_id
AND dbo.eyecare_customer_call_summary.period_date = eyecare_customer_call_summary_1.period_date
AND dbo.eyecare_customer_call_summary.product_id = eyecare_customer_call_summary_1.product_id
WHERE (dbo.eyecare_customer_call_summary.period_type_id = 3)
AND (eyecare_customer_call_summary_1.period_type_id = 1)
The previous reply was right on the 4th point do not use self joins rather try to select a portion of data into a temp table and join against the temp table.
Select * into #temp from eyecare_customer_call_summary
where period_type_id = 1
SElect @variable=min(customer_id) from
eyecare_customer_call_summary
where period_type_id = 3
UPDATE dbo.eyecare_customer_call_summary
SET dbo.eyecare_customer_call_summary.required_calls = eyecare_customer_call_summary_1.required_calls
FROM dbo.eyecare_customer_call_summary
JOIN #temp as eyecare_customer_call_summary_1 ON dbo.eyecare_customer_call_summary.customer_id = eyecare_customer_call_summary_1.customer_id
AND dbo.eyecare_customer_call_summary.period_date = eyecare_customer_call_summary_1.period_date
AND dbo.eyecare_customer_call_summary.product_id = eyecare_customer_call_summary_1.product_id
WHERE
dbo.eyecare_customer_call_summary.customer_id>=@variable
and (dbo.eyecare_customer_call_summary.period_type_id = 3)
If customer_id>= is making things slow then drop it and trying play with subsets.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply