June 24, 2010 at 12:21 pm
I wanted to get some advice on a particular TSQL statement we are using to retrieve a list of relational records. One thing I noticed immediately was that there were some indexes missing, and I have added them in and performance has increased somewhat, but for only 14,000 records returned in 6 seconds on average, seems like it is taking longer than it should be. The statement is as follows:
SELECT LF.LogID, L.SentDate, L.UserName AS SentBy, L.Message, LF.[FileName], F.DisplayName AS CurrentFileName, LF.VersionID AS SentVersion,
F.VersionID AS CurrentVersion, L.ProjectNo, LF.ObjectID AS GUID, Count(LF.LogID) AS Count, L.ZipFiles AS Zipped, L.ProcessStatus, L.Type
FROM LogFiles LF
INNER JOIN Logs L ON (L.LogID = LF.LogID)
LEFT JOIN dbo.vwFiles F ON (F.ID = LF.ObjectID)
GROUP BY LF.LogID, L.SentDate, L.UserName, L.Message, LF.[FileName], F.DisplayName, L.ProjectNo, LF.ObjectID, L.ZipFiles,
LF.VersionID, F.VersionID, L.ProcessStatus, L.Type
ORDER BY LF.LogID
Although the above is currently in a SQL 2000 database, I am posting here because I'm curious about the indexed views in SQL 2005. You'll notice that I am using a view in the inner join statement, and I'm wondering if moving to SQL 2005 and having that view indexed would be of any benefit to performance?
Any advice on anything that stands out at you in the above code that may be detrimental to performance would be greatly appreciated.
June 24, 2010 at 12:58 pm
It could help, at the expense of maintaining the view as data changes. Depending on your read v write, this can be a big deal or not a big deal.
You are going to scan all records in the LogFile, regardless of indexing, because you have an outer join. Can't do anything about that.
June 24, 2010 at 3:45 pm
That's a monstrous GROUP BY, which are fairly high overhead.
Is there any way you can get the COUNT(*) from one of the original tables prior to JOINing, so that you can avoid a GROUP BY with that many columns?
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply