July 30, 2015 at 8:29 am
What recommendations we have to look to improve below query performance
SELECT DISTINCT
logDate ,
STUFF((
SELECT
CHAR(9) + B.Value + CHAR(9)
FROM LOgdata B
WHERE A.logDate = B.logDate
ORDER BY
Sequence
FOR
XML PATH('')) , 1 , 1 , '')AS Value
FROM LOgdata A
ORDER BY
logDate;
July 30, 2015 at 8:44 am
You definately need some form of index on logDate column. Not only is that column determining the final sorted output, but it is also the key for the self referencing join. Assuming this is a logging type table, then my impression is that logDate would probably be a good candidate for the clustering key and maybe the primary key.
If the clustering key is someting other than logDate, then consider the following non-clustered index:
create index on LOgdata ( logDate ) include ( Sequence, Value );
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 30, 2015 at 8:52 am
even after creating clustered or non clustered it is not giving considerable performance gain, So is there any possible solution to rewire the same query without changes the output.
July 30, 2015 at 9:25 am
ramrajan (7/30/2015)
even after creating clustered or non clustered it is not giving considerable performance gain, So is there any possible solution to rewire the same query without changes the output.
You're using a corelated sub-query with FOR XML clause to concatenate a set of related values horizontally into another column. That itself is a performance hit, even with a beneficial index in place.
Try the following.
First, select into a temp table the (3) required columns while filtering on logDate so it contains only required rows.
select logDate, Sequence, Value
into #A
from FROM LOgdata
where logDate >= '2015/07/01';
alter table #L add primary key (logDate, Sequence, Value);
Modify your existing query so it references this temp table instead.
SELECT DISTINCT
logDate ,
STUFF((
SELECT
CHAR(9) + B.Value + CHAR(9)
FROM #A B
WHERE A.logDate = B.logDate
ORDER BY
Sequence
FOR
XML PATH('')) , 1 , 1 , '')AS Value
FROM #A A
ORDER BY logDate;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 30, 2015 at 9:45 am
Eric M Russell (7/30/2015)
You definately need some form of index on logDate column. Not only is that column determining the final sorted output, but it is also the key for the self referencing join. Assuming this is a logging type table, then my impression is that logDate would probably be a good candidate for the clustering key and maybe the primary key.If the clustering key is someting other than logDate, then consider the following non-clustered index:
create index on LOgdata ( logDate ) include ( Sequence, Value );
I would actually suggest the index with both logDate and Sequence as keys.
create index on LOgdata ( logDate, Sequence ) include ( Value );
The query is well written, but to give more solid advice we neeed more information as described in here
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 30, 2015 at 9:58 am
The DISTINCT operation is going perform aggregation. You really shouldn't need it if your data structures are correct. That's a pretty big performance hit right there.
"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
July 30, 2015 at 10:16 am
Before going any further, we'll need you to provide DDL for this LogData table and it's indexes. Also give an estimate on number of rows.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply