August 11, 2015 at 3:56 am
I have attached query which is having performance issue and its actual execution plan with is taking almost 5 to 7 minutes to execute and give 88000 records,
I added some indexes on the temp table #Reading1 but there is no gain in the performance, So can anyone suggest how to fix this
/*
When i see actual execution plan Query 15 is taking very long time relative to the batch 88 %
Below is that code
INSERT INTO #Measure1(
MeasureDate ,
Value)
SELECT DISTINCT
MeasureDate ,
STUFF((
SELECT
CHAR(9) + B.Value + CHAR(9)
FROM #Reading1 B
WHERE A.MeasureDate = B.MeasureDate
ORDER BY
Sequence , B.ParameterID
FOR
XML PATH('')) , 1 , 1 , '')AS Value
FROM #Reading1 A
ORDER BY
MeasureDate;
This Table contain more than 100 million records
CREATE TABLE [dbo].[Reading](
[ControllerID] [int] NOT NULL,
[ParameterID] [smallint] NOT NULL,
[MeasureDate] [smalldatetime] NOT NULL,
[Value] [real] NULL,
[Status] [tinyint] NULL
)
CREATE CLUSTERED INDEX [PK_Reading] ON [dbo].[Reading]
(
[ControllerID] ASC,
[ParameterID] ASC,
[MeasureDate] ASC
)
I have tried to create index on temp table but no use i dont see any
--CREATE NONCLUSTERED INDEX IX_Reading1
--ON #Reading1(ControllerID, ParameterID , MeasureDate)
*/
August 11, 2015 at 3:58 am
Don't go by the cost %. If you use STATISTICS TIME, which query in the batch is taking the longest time?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2015 at 5:37 am
Instead of MAX here, you might try TOP(1)
SELECT
@DateEnd = MAX(MeasureDate)
FROM Reading
WHERE Reading.ControllerID = @ControllerID;
As for the rest, I got lost. From what I can tell, you've got either some seriously messed up data structures or just a poor process. All the times you're inserting data into a temporary table only to be followed by deletes from that temporary table. That data should be excluded from the insert, not deleted after inserting. That's wasted processing time.
Your statistics are radically off. For example, on this query:
INSERT INTO #Reading1(
ControllerID ,
ParameterID ,
ParameterName ,
MeasureDate ,
Value ,
Sequence)
SELECT
B.ControllerID ,
B.ParameterID ,
B.ParameterName ,
A.MeasureDate ,
'-999' AS Value ,
B.Sequence
FROM(
SELECT DISTINCT
MeasureDate
FROM
Reading(NOLOCK)
INNER JOIN #Parameter1 ON Reading.ParameterID = #Parameter1.ParameterID
AND Reading.ControllerID = #Parameter1.ControllerID
WHERE Reading.MeasureDate BETWEEN @DateStart AND @DateEnd)A , #Parameter1 B
ORDER BY
A.MeasureDate;
In the execution plan, the optimizer thinks that PK_Reading is going to return 2500 rows. Instead it returns 1.5 million and does that using an index seek operation where a scan might be better. Be sure your statistics are up to date.
You can see the same stats issue on the next query which starts here:
UPDATE A
SET
It thinks it's returning 100k rows and instead returns 1 million.
The query with the highest cost estimate also suffers from a statistics issue. The optimizer thinks it's going to get one row when it's returning 2 million. That's going to result in a pretty horrific plan with very poor performance. Do you have automatic update of statistics turned off?
You're getting a lot of parallelism and I'm not sure it's justified. I'd also set the Cost Threshold for Parallelism to a higher value (I suspect it's currently on the default of 5. Change it to 50).
A couple of other issues likely to cause problems are the reliance on DISTINCT in so many of the queries. That's an aggregation function with quite a lot of potential overhead. It's usually there as a crutch for bad data or bad data structures. Fix the underlying issue rather than relying on the crutch. You're also sprinkling NOLOCK all over the place. Especially with such a large, and no doubt, long running query, you're much more likely to hit all sorts of data issues, missing or duplicate rows (possibly the reason for the need of the DISTINCT), caused by the use of NOLOCK. It is not a magic "run faster" switch.
The fundamental issue though is the complexity of this process. While a lot of what you're doing only involves a single row, a lot seems to be involving over a million rows. Manipulating that much data over and over as you're doing is just going to be costly, no matter what indexes you put in place. Figure out how to simplify this process.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply