SQL taking ages to run - Possible to optimise? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL taking ages to run - Possible to optimise?

Author

Topic

Daze
Starting Member

42 Posts

Posted - 2011-02-22 : 06:18:42

Hi,

I've inherited an asset managements system web site and, well, let's just say it's not very performant (SQL Server 2005). There are two main bottle neck queries that I need to somehow improve drastically.

FYI: All of the foreign keys and obvious indexes are in place. Additionally, it looks like the previous developers has run Profiler over the database, as I can see a number of Profiler generated indexes.

Can anyone see any obvious tweaks that can be made to the following SQL to improve its performance? Any quick gains would be welcome.


SELECT		t.[Name],
			avt.[Value],
			COUNT(DISTINCT ac.[AssetId]) SearchCount
FROM		[AssetVersionTag] avt
INNER JOIN	[Tag] t ON t.[TagId] = avt.[TagId]
INNER JOIN	[AssetVersion] av ON av.[VersionId] = avt.[VersionId]
INNER JOIN	[AssetContents] ac ON ac.[VersionId] = avt.[VersionId]
WHERE		av.[IsDeleted] = 0
AND			t.[IsSearchable] = 1
AND			t.[IsFreeForm] = 0
AND			LEN(LTRIM(RTRIM(avt.[Value]))) > 0
AND			avt.[VersionId] IN
(
	SELECT	DISTINCT [VersionId] FROM [vwSystemKeySearchInfo]
)
GROUP BY	t.[Name],
			avt.[Value]
ORDER BY	t.[Name] ASC,
			avt.[Value] ASC

Would posting the XML execution plan be of any use?

FYI: There are 7,262 Assets, 572,015 AssetVersionTags, 33 Tags, 20,483 AssetVersions and AssetContents.

Thanks in advance for any help.

Lewie
Starting Member

42 Posts

Posted - 2011-02-22 : 08:18:15

You may need to post your indexes as well, but off the top of my head the piece of script LEN(LTRIM(RTRIM(avt.[Value]))) is a killer as it cannot use any indexing when doing this. It may help to add a computed field to your table that gives the result of LEN(LTRIM(RTRIM(avt.[Value]))) and then put an index on the computed field together with avt.TagId and avt.VersionId. I would play with which field is first in the index. Possibly avt.VersionId followed by TagId and then the computed field.

Daze
Starting Member

42 Posts

Posted - 2011-02-22 : 08:24:54

Thanks for your help Lewie.

I've actually changed the LEN(LTRIM(RTRIM(avt.[Value]))) > 0 statement to be LEN(avt.[Value]) > 0 (and ensured that the data is trimmed when stored), but I'm guessing that's not going to be much of an improvement?

Lewie
Starting Member

42 Posts

Posted - 2011-02-22 : 08:28:55

If you are saving the field avt.value correctly and assuming it a varchar then could you not just say where avt.value <> ''

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-22 : 08:35:08

How many entries are in table vwSystemKeySearchInfo?

No, you're never too old to Yak'n'Roll if you're too young to die.

Daze
Starting Member

42 Posts

Posted - 2011-02-22 : 08:43:39

quote:
Originally posted by webfred

How many entries are in table vwSystemKeySearchInfo?

Sorry - That's a simple view, that returns 20472 records:


CREATE VIEW [dbo].[vwSystemKeySearchInfo]
AS
SELECT		dbo.Asset.Name,
			dbo.Asset.[Description],
			dbo.Asset.Price,
			dbo.AssetVersion.Keywords,
			dbo.AssetVersion.VersionId,
			dbo.AssetType.Type
FROM		dbo.Asset
INNER JOIN	dbo.AssetContents ON dbo.Asset.AssetId = dbo.AssetContents.AssetId
INNER JOIN	dbo.AssetVersion ON dbo.AssetContents.VersionId = dbo.AssetVersion.VersionId
INNER JOIN	dbo.AssetType ON dbo.Asset.AssetTypeId = dbo.AssetType.AssetTypeId
WHERE		(dbo.Asset.IsDeleted = 0)
AND			(dbo.AssetVersion.IsDeleted = 0)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-02-22 : 09:05:16

That view effectively is the same set of tables that you are querying in your main query.
How many records are returned in the "distinct" version?
Is there a cheaper/easier SQL way to find that mini-list?

What are you trying to do in English?
also why are you looking for "COUNT(DISTINCT ac.[AssetId])" and not COUNT(*)?

Daze
Starting Member

42 Posts

Posted - 2011-02-22 : 09:16:50

I'm not sure why the previous developer is joining on to that view tbh, as he could easily add the join to the Asset table into the main SELECT. I've now done that, but I didn't see any significant gain.

Each Asset can have one or more Versions, and that is why he is counting the DISTINCT AssetId.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-02-22 : 11:33:03

Can you post the "execution plan"...be that text or XML or image formats?
and the tables DDL?

Can you also give us sample data in each of the tables? (obfuscated if needs be).

I also can't see why these latter two joins below don't have a "tag id" in them?
what type of tables are these...children of "tag" or reference/lookup tables?

INNER JOIN [Tag] t ON t.[TagId] = avt.[TagId]
INNER JOIN [AssetVersion] av ON av.[VersionId] = avt.[VersionId]
INNER JOIN [AssetContents] ac ON ac.[VersionId] = avt.[VersionId]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources