Self selects galore - better way? subselects?
-
Is there a better way to do this?! It performs badly. Maybe w/ subselects? I'm at a loss. I changed some of the column names so if they don't quite match thats why. It works, just slooowww
ALTER VIEW dbo.RFJJS
AS
SELECT a.procid, b.[value] AS CName, c.[value] AS CompName, d.[value] AS CRef, e.[value] AS JNumber, f.[value] AS T,
g.[value] AS FName, h.[value] AS Sname, i.[value] AS JSummary, j.[value] AS SPlannedStartDate, k.[value] AS SPlannedStartTime,
l.[value] AS SPlannedEndDate, m.[value] AS SPlannedEndTime, n.[value] AS WPlannedStartDate, o.[value] AS WPlannedStartTime,
p.[value] AS WPlannedEndDate, q.[value] AS WPlannedEndTime, r.[value] AS RStreet1, s.[value] AS RStreet2, t.[value] AS RPostcode,
u.[value] AS RTown, v.[value] AS RCounty
FROM dbo.procs a INNER JOIN
dbo.rlvntdata b ON a.procid = b.procid INNER JOIN
dbo.rlvntdata c ON a.procid = c.procid INNER JOIN
dbo.rlvntdata d ON a.procid = d.procid INNER JOIN
dbo.rlvntdata e ON a.procid = e.procid INNER JOIN
dbo.rlvntdata f ON a.procid = f.procid INNER JOIN
dbo.rlvntdata g ON a.procid = g.procid INNER JOIN
dbo.rlvntdata h ON a.procid = h.procid INNER JOIN
dbo.rlvntdata i ON a.procid = i.procid INNER JOIN
dbo.rlvntdata j ON a.procid = j.procid INNER JOIN
dbo.rlvntdata k ON a.procid = k.procid INNER JOIN
dbo.rlvntdata l ON a.procid = l.procid INNER JOIN
dbo.rlvntdata m ON a.procid = m.procid INNER JOIN
dbo.rlvntdata n ON a.procid = n.procid INNER JOIN
dbo.rlvntdata o ON a.procid = o.procid INNER JOIN
dbo.rlvntdata p ON a.procid = p.procid INNER JOIN
dbo.rlvntdata q ON a.procid = q.procid INNER JOIN
dbo.rlvntdata r ON a.procid = r.procid INNER JOIN
dbo.rlvntdata s ON a.procid = s.procid INNER JOIN
dbo.rlvntdata t ON a.procid = t.procid INNER JOIN
dbo.rlvntdata u ON a.procid = u.procid INNER JOIN
dbo.rlvntdata v ON a.procid = v.procid
WHERE (a.parentprocid = 0) AND (b.rvntdataname = 'ClientName') AND (c.rvntdataname = 'CompanyName') AND
(d.rvntdataname = 'CustomerReferenceNumber') AND (e.rvntdataname = 'JobNumber') AND (f.rvntdataname = 'Title') AND
(g.rvntdataname = 'FirstName') AND (h.rvntdataname = 'Sname') AND (i.rvntdataname = 'JobSummary') AND (j.rvntdataname = 'EstJobSDate') AND
(k.rvntdataname = 'EstJobSTime') AND (l.rvntdataname = 'EstJobEDate') AND (m.rvntdataname = 'EstJobETime') AND
(n.rvntdataname = 'WrkJobSDate') AND (o.rvntdataname = 'WrkJobSTime') AND (p.rlvntdataname = 'WrkJobEDate') AND
(q.rvntdataname = 'WrkJobETime') AND (r.rvntdataname = 'Address') AND (s.rvntdataname = 'Address2') AND (t.rvntdataname = 'Postcode') AND
(u.rvntdataname = 'Town') AND (v.rvntdataname = 'County')
-
So, the rlvntdata table has a procid, a data type name, and the data value corresponding to that type name for that particular procid, correct?
In that case, the view is probably as good as it can get. My recommendation would be to transform that table to make the data types into columns. If you need the table in this form, maybe adding a couple of triggers to keep a 'flattened' version in sync would be a good idea. Then use the flattened version in reporting and ad-hoc queries for better performance.
And then again, I might be wrong ...
David Webb -
That sounds like a good idea I hadn't thought of. What about running a 'select *' with no where clause on the view and shoving that into an actual table every hour or so and searching on that?
Thanks.
-
That would work if you got al the new stuff every hour and 'flattened' it. That might be simpler and less maintenance overhead than the trigger solution. You could turn the view sql into an insert pretty simply.
It depends on how 'fresh' this data has to be.
I was originally thinking something like:
create table rlvntdata_flat (
-- replace the ?? with the correct data type
procid ?? NOT NULL,
CName ?? NULL,
CompName ?? NULL,
CRef ?? NULL,
JNumber ?? NULL,
T ?? NULL,
FName ?? NULL,
Sname ?? NULL,
JSummary ?? NULL,
SPlannedStartDate ?? NULL,
SPlannedStartTime ?? NULL,
SPlannedEndDate ?? NULL,
SPlannedEndTime ?? NULL,
WPlannedStartDate ?? NULL,
WPlannedStartTime ?? NULL,
WPlannedEndDate ?? NULL,
WPlannedEndTime ?? NULL,
RStreet1 ?? NULL,
RStreet2 ?? NULL,
RPostcode ?? NULL,
RTown ?? NULL,
RCounty ?? NULL)
go
create trigger rlvntdata_f_insert on rlvntdata for insert as
BEGIN
declare @procid int
declare @type varchar(50)
declare @value varchar(50)
select @procid = procid, @type = rlvntdataname, @value = [value] from inserted
-- check to see if this procid is already there. If not add it.
IF NOT EXISTS (select 1 from rlvntdata_flat where procid = @procid)
BEGIN
insert rlvntdata_flat (procid) values (@procid)
END
-- find out where this one goes and update the flat table.
-- Do this once for each rlvntdata column.
IF @type = 'ClientName'
BEGIN
UPDATE rlvntdata_flat set CName = @value where procid = @procid
END
.
.
.
.
END
Just a skeleton that would have to be fleshed out. You'd probably need update and maybe delete triggers also to keep things in sync.
And then again, I might be wrong ...
David Webb -
Something like this should perform *much* better
SELECT a.procid, b.[ClientName] AS CName, b.[CompanyName], b.[CustRefNum] AS CRef,
b.[JobNumber] AS JNumber, --...,
b.[County] AS RCounty
FROM dbo.procs a INNER JOIN (
SELECT procid,
MAX(CASE WHEN rvntdataname = 'ClientName' THEN value ELSE '' END) AS [ClientName],
MAX(CASE WHEN rvntdataname = 'CompanyName' THEN value ELSE '' END) AS [CompanyName],
MAX(CASE WHEN rvntdataname = 'CustomerReferenceNumber' THEN value ELSE '' END) AS [CustRefNum],
MAX(CASE WHEN rvntdataname = 'JobNumber' THEN value ELSE '' END) AS [JobNumber],
--...
MAX(CASE WHEN rvntdataname = 'County' THEN value ELSE '' END) AS [County]
FROM dbo.rlvntdata
GROUP BY procid
) AS b ON a.procid = b.procid
WHERE a.parentprocid = 0
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply