SQL Optimization Query
Below MSSQL2005 query is very slow. I feel like they should be a way to speed it up, but I don't know how. Note that I edited the inner join to use select statements to make it more obvious (to people reading this question) what is happening, although this does not affect speed (the Execution plan is probably the same anyway). Interestingly, I've never used the valuegroups keyword for anything more than a counter, but I'm not sure if there is any way to benefit from this.
select top 1 cde.processPath as 'keywordValue', count(*) as 'total'
from dbo.ClientDefinitionEntry AS cde INNER JOIN dbo.KeywordValueGroups AS kvg
ON cde.keywordGroupId = kvg.keywordValueGrpId
where kvg.[name] = @definitionName
group by cde.processPath
order by total desc
Edit: Apparently people keep complaining about my use of subqueries. It doesn't really matter. I added them right before posting this question to make it easier to see what's going on. But they only made things more confusing, so I changed them to not use them.
Edit: the indices used:
ClientDefinitionEntry:
IX_ClientDefinitionEntry |nonclustered located on PRIMARY|clientId, keywordGroupId
KeyWordValueGroups
IX_KeywordValueGroups |nonclustered located on PRIMARY|keywordValueGrpId
IX_KeywordValueGroups_2 |nonclustered located on PRIMARY|version
IX_KeywordValueGroups_Name |nonclustered located on PRIMARY|name
a source to share
I would make sure you have the following indices.
Identifier in keyword groups.
Name in keyword groups.
Identifier in ClientDefinitionEntry with INCLUDE for processPath.
CREATE INDEX [IX_ClientDefinitionEntry_Id_ProcessPath] ON [dbo].[ClientDefinitionEntry] ( [keywordGroupId] ASC ) INCLUDE ( [processPath]) ON [PRIMARY]
CREATE INDEX [IX_KeywordValueGroups_Id] ON [dbo].[KeywordValueGroups] ( [keywordValueGrpId] ASC )
CREATE INDEX [IX_KeywordValueGroups_Name] ON [dbo].[KeywordValueGroups] ( [name] ASC )
I would also change the query to the following.
select top 1
cde.processPath as 'keywordValue',
count(*) as 'total'
from
dbo.ClientDefinitionEntry AS cde
INNER JOIN
dbo.KeywordValueGroups AS kvg
ON
cde.keywordGroupId = kvg.keywordValueGrpId
where
kvg.[name] = @definitionName
group by
processPath
order by
total desc
a source to share
What does the execution plan look like? By looking at this, you can find out which part of the request is taking more time / resources.
Do you have indexes on columns where you are filtering? Do you have indexes on the columns you are using to join? Do you have indexes on the columns you are using to sort?
once you take a look at this and the query is still slow, you can take a look at how your database / table is fragmented (dbcc showcontig) and see if the indexes need to be rebuilt. It can be helpful to have a maintenance plan that updates your indexes regularly.
a source to share
Run the request with this option:
SET SHOWPLAN_TEXT ON
And add the result to the question.
Also check if your statistics are up to date:
SELECT
object_name = Object_Name(ind.object_id),
IndexName = ind.name,
StatisticsDate = STATS_DATE(ind.object_id, ind.index_id)
FROM SYS.INDEXES ind
order by STATS_DATE(ind.object_id, ind.index_id) desc
And information about indexes, table definitions and foreign keys would be helpful.
a source to share
In fact, there is not enough information to know for sure. If there are performance issues in this query, then the tables must have a non-trivial amount of data and you do not need to specify important indexes.
Which indexes will definitely help depends a lot on how large the tables are and how little the KeywordGroupId and KeywordValueGrpId values are distributed.
Without any other information, I would say that you want to make sure it is dbo.KeywordValueGroups.[name]
indexed as well dbo.ClientDefinitionEntry.[keywordGroupId]
.
Due to the way the query is written, an index alone dbo.KeywordValueGroups.[keywordValueGrpId]
cannot help, but a composite index [name], [keywordValueGrpId]
probably won't. Once you have this index, you don't need a dedicated index on [name]
.
Based on gut feeling alone, I might fear that the index [name]
is not required and that cde.keywordGroupId is likely to be important. Whether the linked index is [name], [keywordValueGrpId]
helpful depends on how many records exist with the same [name].
The only way to know for sure is to add indexes and see what happens.
You also need to think about how often this query is executed (as well as how important it is to do it quickly) and how often the underlying data changes. Depending on your specific circumstances, the increase in speed may not justify the added value of maintaining indices.
a source to share
Not sure how many records we are talking about, but this: order descending is in the calculated column meaning that every calculation on each row must be done before the order can be placed. This is probably one of the things slowing it down, but I see no way out of this particular problem. Not a problem if you only have a few entries after merging, but maybe if there are many.
I would focus on indexing first. We often forget that when we create foriegn keys, they are not automatically indexed. Check if both parts of the connection are indexed.
Since you are passing the value in a parameter, you may also have a problem with the sniffing parameter. Google it for a techie to fix this.
a source to share