I have a grid that is populated by data from a query that takes and argument of Userid to filter on and has 4 left joins and two inner joins to filter projects based on user ID. The parent table in the join is a view that has two left joins as well. It takes more than a minute to load 5000 rows of data. I tried just loading 5000k of a simple Select statement with a filter based on client and it was similarly very slow. When I just load 100 rows it takes a few seconds.
Normally no one would be loading that much data but it is a possible scenario. I was wondering if this is just because I'm testing on my personal desktop with other applications running and if it would be significantly faster in a normal production environment with a beefy server?
Below are the queries I'm using to create the view and filter data. First is the view(part A) then the query (B) that I use in the alpha query builder. I was also wondering if there is anything I could do to improve the performance of the query/view. I'm using SQL server 2005.
A) THE VIEW vwPortalProjects
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vwPortalProjects]
AS
SELECT p.Id, p.ProjectName, p.ProjectCompany, p.ProjectStoreNumber, p.ProjectCity,
p.ProjectState, p.EstSTADate, p.EstENDDate, p.EstIncentive,
p.ClientId, p.SubClientId, p.RebatorId, p.Division, p.Geography, p.Initiative, p.EquipmentTypeId,
s.Description as Status, m.Description as MileStone, p.ProjectAddr1, p.ProjectZipCode, p.APPDate,
'EstimatedIncentive' =
CASE
WHEN m.Code = 'FCI' THEN NULL
WHEN s.Code = 'TERM' THEN 0
ELSE p.EstIncentive
END,
'ActualIncentive' =
CASE
WHEN m.Code != 'FCI' THEN NULL
ELSE p.Incentive
END
FROM (Projects p
LEFT OUTER JOIN Statuses as s
ON p.StatusId = s.Id
LEFT OUTER JOIN Milestones as m
on p.MileStoneId = m.Id
)
GO
B) The query
SELECT p.Id, p.ProjectName AS 'ProjectName', p.ProjectCompany AS 'Company', p.ProjectStoreNumber AS 'SiteId', p.ProjectCity AS 'City',
p.ProjectState AS 'State', p.EstSTADate AS 'StartDate', p.EstENDDate AS 'EndDate', p.EstIncentive,
p.Division AS 'DivisionId', d.Description AS 'Division', p.Geography AS 'GeographyId',
g.Description AS 'Geography', p.SubClientId, sc.SubClientName AS 'SubClient', p.Initiative AS 'InitiativeId',
i.Description AS 'Initiative', p.Status, p.MileStone, r.Name AS 'RebateProgram',
p.ProjectAddr1 AS 'StreetAddress', p.ProjectZipCode AS 'Zip', p.APPDate AS 'ApprovalDate', p.EquipmentTypeId,
e.Description AS 'EquipmentType', p.EstimatedIncentive, p.ActualIncentive
FROM (vwPortalProjects as p
LEFT JOIN SubClients as sc
ON p.SubClientId = sc.Id
LEFT JOIN Initiatives as i
ON p.Initiative = i.Id
--LEFT JOIN Statuses as s
-- ON p.StatusId = s.Id
--LEFT JOIN Milestones as m
-- on p.MileStoneId = m.Id
LEFT JOIN Divisions as d
on p.Division = d.Id
LEFT JOIN Geographies as g
on p.Geography = g.Id
LEFT JOIN Rebators as r
on p.RebatorId = r.Id
LEFT JOIN EquipmentTypes as e
on p.EquipmentTypeId = e.Id
INNER JOIN WebUserPermissions as up
ON p.ClientId = up.ClientId AND
(p.SubClientId = up.SubClientId OR up.SubClientId IS NULL) AND
(p.Initiative = up.InitiativeId OR up.InitiativeId IS NULL) AND
(p.Division = up.DivisionId OR up.DivisionId IS NULL) AND
(p.Geography = up.GeographyId OR up.GeographyId IS NULL) AND
(p.RebatorId = up.RebatorId OR up.RebatorId IS NULL) AND
(p.EquipmentTypeId = up.EquipmentTypeId OR up.EquipmentTypeId IS NULL)
INNER JOIN WebSecurityUsers as u
ON up.WSU_Userid = u.Userid)
WHERE u.Userid = :userid
ORDER BY p.Id
Normally no one would be loading that much data but it is a possible scenario. I was wondering if this is just because I'm testing on my personal desktop with other applications running and if it would be significantly faster in a normal production environment with a beefy server?
Below are the queries I'm using to create the view and filter data. First is the view(part A) then the query (B) that I use in the alpha query builder. I was also wondering if there is anything I could do to improve the performance of the query/view. I'm using SQL server 2005.
A) THE VIEW vwPortalProjects
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vwPortalProjects]
AS
SELECT p.Id, p.ProjectName, p.ProjectCompany, p.ProjectStoreNumber, p.ProjectCity,
p.ProjectState, p.EstSTADate, p.EstENDDate, p.EstIncentive,
p.ClientId, p.SubClientId, p.RebatorId, p.Division, p.Geography, p.Initiative, p.EquipmentTypeId,
s.Description as Status, m.Description as MileStone, p.ProjectAddr1, p.ProjectZipCode, p.APPDate,
'EstimatedIncentive' =
CASE
WHEN m.Code = 'FCI' THEN NULL
WHEN s.Code = 'TERM' THEN 0
ELSE p.EstIncentive
END,
'ActualIncentive' =
CASE
WHEN m.Code != 'FCI' THEN NULL
ELSE p.Incentive
END
FROM (Projects p
LEFT OUTER JOIN Statuses as s
ON p.StatusId = s.Id
LEFT OUTER JOIN Milestones as m
on p.MileStoneId = m.Id
)
GO
B) The query
SELECT p.Id, p.ProjectName AS 'ProjectName', p.ProjectCompany AS 'Company', p.ProjectStoreNumber AS 'SiteId', p.ProjectCity AS 'City',
p.ProjectState AS 'State', p.EstSTADate AS 'StartDate', p.EstENDDate AS 'EndDate', p.EstIncentive,
p.Division AS 'DivisionId', d.Description AS 'Division', p.Geography AS 'GeographyId',
g.Description AS 'Geography', p.SubClientId, sc.SubClientName AS 'SubClient', p.Initiative AS 'InitiativeId',
i.Description AS 'Initiative', p.Status, p.MileStone, r.Name AS 'RebateProgram',
p.ProjectAddr1 AS 'StreetAddress', p.ProjectZipCode AS 'Zip', p.APPDate AS 'ApprovalDate', p.EquipmentTypeId,
e.Description AS 'EquipmentType', p.EstimatedIncentive, p.ActualIncentive
FROM (vwPortalProjects as p
LEFT JOIN SubClients as sc
ON p.SubClientId = sc.Id
LEFT JOIN Initiatives as i
ON p.Initiative = i.Id
--LEFT JOIN Statuses as s
-- ON p.StatusId = s.Id
--LEFT JOIN Milestones as m
-- on p.MileStoneId = m.Id
LEFT JOIN Divisions as d
on p.Division = d.Id
LEFT JOIN Geographies as g
on p.Geography = g.Id
LEFT JOIN Rebators as r
on p.RebatorId = r.Id
LEFT JOIN EquipmentTypes as e
on p.EquipmentTypeId = e.Id
INNER JOIN WebUserPermissions as up
ON p.ClientId = up.ClientId AND
(p.SubClientId = up.SubClientId OR up.SubClientId IS NULL) AND
(p.Initiative = up.InitiativeId OR up.InitiativeId IS NULL) AND
(p.Division = up.DivisionId OR up.DivisionId IS NULL) AND
(p.Geography = up.GeographyId OR up.GeographyId IS NULL) AND
(p.RebatorId = up.RebatorId OR up.RebatorId IS NULL) AND
(p.EquipmentTypeId = up.EquipmentTypeId OR up.EquipmentTypeId IS NULL)
INNER JOIN WebSecurityUsers as u
ON up.WSU_Userid = u.Userid)
WHERE u.Userid = :userid
ORDER BY p.Id
Comment