I'm have made a series of tables and views to create some reports. I have included boiled-down versions of them below. The report query that I want to run (at the bottom) takes a long time to execute and sometimes times out. Creating a rank within the query is doing the most damage, but it can still take a long time to execute without it. The usage table has almost 70k records and there are over 10k users. Does anyone have suggestions for making these views/queries more efficient
==========================
TABLE TGroupAudit
AuditID, intOldGroupID, intNewGroupID, strUserID, dtJoinDate
==========================
TABLE people
strUserID, name
==========================
TABLE usage
UsageID, strUserID, cost, seconds, gmttime, accesstype
==========================
CREATE VIEW VUsageSubtotals AS
SELECT TOP 100 PERCENT VUserGroupDates.strUserID, people.name, VUserGroupDates.intGroupID, usage.accesstype, SUM(usage.cost) AS subtotal, MONTH(usage.gmttime) AS [month], VUsageTotals.total
FROM VUserGroupDates INNER JOIN
people ON VUserGroupDates.strUserID = people.strUserID INNER JOIN
usage ON people.strUserID = usage.strUserID INNER JOIN
VUsageTotals ON VUserGroupDates.strUserID = VUsageTotals.strUserID AND
VUserGroupDates.intGroupID = VUsageTotals.intGroupID AND MONTH(usage.gmttime) = VUsageTotals.[month]
WHERE (usage.gmttime BETWEEN VUserGroupDates.dtJoinDate AND VUserGroupDates.dtLeaveDate)
GROUP BY VUserGroupDates.strUserID, VUserGroupDates.intGroupID, usage.accesstype, MONTH(usage.gmttime), VUsageTotals.total
==========================
CREATE VIEW VUsageTotals AS
SELECT VUserGroupDates.strUserID, people.name, VUserGroupDates.intGroupID, SUM(usage.seconds) AS totaltime, SUM(usage.cost) AS totalcost, MONTH(usage.gmttime) AS [month]
FROM VUserGroupDates INNER JOIN
people ON VUserGroupDates.strUserID = people.strUserID INNER JOIN
usage ON people.strUserID = usage.userid
WHERE (usage.gmttime BETWEEN VUserGroupDates.dtJoinDate AND VUserGroupDates.dtLeaveDate)
GROUP BY VUserGroupDates.strUserID, people.name, VUserGroupDates.intGroupID, MONTH(usage.gmttime)
==========================
CREATE VIEW VUserGroupDates AS
SELECT strUserID, intNewGroupID AS intGroupID, dtJoinDate,
ISNULL ((SELECT MAX(TGA2.dtJoinDate) FROM TGroupAudit TGA2
WHERE TGA2.dtJoinDate < TGA1.dtJoinDate AND TGA2.strUserID = TGA1.strUserID), GETDATE()) AS dtLeaveDate
FROM TGroupAudit TGA1
==========================
Here's a sample query that gets run for a report:
SELECT TOP 30 PERCENT WITH TIES V1.*,
(SELECT COUNT(DISTINCT V2.subtotal)
FROM VUsageSubtotals V2 WHERE V1.subtotal <= V2.subtotal
AND V1.intGroupID = V2.intGroupID
AND V2.month = 3) AS rank
FROM VUsageSubtotals V1
WHERE strUserID IN ('a1, a2, b5, b7, b9, d12')
AND V1.month = 3
AND accesstype = 'STANDARD'
GROUP BY strUserID, intGroupID, subtotal, month, total, accesstype
The purpose of the report is an accounting one. It is desired to know how much each user used each accesstype during a month. Usage is further split up by the user's groups (which may never change or could several times during a month) for billing purposes.
View and query optimization
Joe Duffy
It is not uncommon that you may have views that perform acceptable on their own, but when you start to build queries that is based on views (and those may be based on other views etc), performance may start to degrade rapidly.
Whenever I encounter this, I try to identify the essentials that my 'new' query needs (in your case the report) and rewrite it in a 'raw' form, not using any views. For the most parts, I've found this approach to be the best way to optimize those kind of 'view-dependant' queries.
If it's a viable option, I guess would depend from case to case. There may be considerations regarding maintainability, having businnesslogic in several places etc..
/Kenneth
cRz
Sorry I haven't gotten back to this sooner. I've been shuffled around on several different projects and this hasn't been a priority.
Writing the entire thing in 'raw' form has proven extremely difficult to do. I often encounter errors where SQL won't let me use subqueries in statements. It's also a very complicated mess that I broke up into chunks (other views) to make it easier to wrap my mind around and code it.
I read elsewhere on these forums that SQL really isn't meant to do what I'm trying to do with it; e.g. creating reports. So I'm going to try using simpler queries and using code (vbscript) on the web server (dishing up asp pages with dynamic reports) to further refine the data to what I need to display and see how the web server handles it.
ChapiNaples
I usually start by running the SQL Server Database Engine Tuning Advisor which identifies any indexes which would be helpful in speeding up your query.
I know hard-core DBA's will cringe at the thought but it's very quick 'n' dirty
Joel.