The performance impact from multiple logical reads could be a result of your UDF.
Provided is a great article which describes using inline table-valued UDFs to reduce the row-by-row calls to the scalar UDF. UPDATE: I noticed you were already using table-valued UDF.
http://dataeducation.com/scalar-functions-inlining-and-performance-an-entertaining-title-for-a-boring-post/
To replicate your scenario, I executed the following query:
DECLARE
@Measurements TABLE(Precidence int, -- the relative scale of a measurement
Measurement varchar(max), -- Grams, Kilograms, Liters, etc
MeasurementType varchar(max)); -- Weight, Volume, etc`
DECLARE
@Items TABLE(ItemType varchar(max),
Quantity float,
Measurement varchar(max),
ToMeasurement varchar(max));
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','G');
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','KG');
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','ML');
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','L');
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('2','G','Weight');
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('1','KG','Weight');
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('1','GAL','Volume');
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('2','L','Volume');
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('3','ML','Volume');
Afterwards, I complied and executed the following update statement:
WITH Items_CTE AS
(
SELECT A.ItemType,
A.Quantity,
A.Measurement,
(SELECT TOP 1 M.Measurement FROM @Measurements M
JOIN @Items C ON C.Measurement = M.Measurement
WHERE M.MeasurementType = B.MeasurementType
AND C.ItemType = A.ItemType
ORDER BY Precidence)ToMeasurement
FROM @Items A
JOIN @Measurements B
ON A.Measurement = B.Measurement
)
UPDATE @Items
SET ToMeasurement = cte.ToMeasurement
FROM ITEMS_CTE as cte
JOIN @Items as i
ON cte.measurement = i.measurement;
The UPDATE statement uses a CTE and the results were as follows:
ItemType | Quantity | Measurement | ToMeasurement
Widget | 1 | G | KG
Widget | 1 | KG | KG
Widget | 1 | ML | L
Widget | 1 | L | L
This can be done using the PIVOT function, but since it sounds like you want to change the query based on the schemaId, then you will want to use dynamic SQL.
If you had a known number of values or knew the columns for a specific schemaID, then you could hard-coded the query. A static query would be:
select loannumber,
[First Name],
[Middle Name],
[Last Name]
from
(
select
l.loannumber,
sf.fieldname,
lf.fieldvalue
from loans l
left join loanfields lf
on l.id = lf.loanid
left join schemafields sf
on lf.schemafieldid = sf.id
and l.schemaid = sf.schemaid
) src
pivot
(
max(fieldvalue)
for fieldname in ([First Name], [Middle Name], [Last Name])
)piv;
See SQL Fiddle with Demo.
If you had an unknown number or you want the columns to change based on a SchemaId that you are passing into a procedure, then you will use dynamic SQL to generate the SQL string:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@schemaId int = 1
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(FieldName)
from SchemaFields
where schemaid = @schemaid
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT loannumber,' + @cols + '
from
(
select
l.loannumber,
sf.fieldname,
lf.fieldvalue
from loans l
left join loanfields lf
on l.id = lf.loanid
left join schemafields sf
on lf.schemafieldid = sf.id
and l.schemaid = sf.schemaid
where sf.schemaid = '+cast(@schemaid as varchar(10))+'
) x
pivot
(
max(fieldvalue)
for fieldname in (' + @cols + ')
) p '
execute(@query);
See SQL Fiddle with Demo. Both of these queries will generate the result:
| LOANNUMBER | FIRST NAME | LAST NAME | MIDDLE NAME |
-----------------------------------------------------
| ABC123 | John | Doe | (null) |
| XYZ789 | Charles | Smith | Lee |
Best Answer
As far as you're using a hierarchical structure you can apply a recursive solution. But for a large number of rows you must deal with MAXRECURSION, have a look at MS Docs about it.
Quoted from docs:
WITH tree AS ( SELECT PositionCode as CurrCode, PositionCode, PositionCode as Lvl0Code, PositionName as Lvl0Name, cast('na' as nvarchar(10)) as Lvl1Code, cast('na' as nvarchar(10)) as Lvl1Name, cast('na' as nvarchar(10)) as Lvl2Code, cast('na' as nvarchar(10)) as Lvl2Name, cast('na' as nvarchar(10)) as Lvl3Code, cast('na' as nvarchar(10)) as Lvl3Name, ParentCode, Level FROM tbl WHERE Level = 0 UNION ALL SELECT tree.CurrCode, tbl.PositionCode, tree.Lvl0Code, tree.Lvl0Name, case when tbl.Level = 1 then tbl.PositionCode else tree.Lvl1Code end as Lvl1Code, case when tbl.Level = 1 then tbl.PositionName else tree.Lvl1Name end as Lvl1Name, case when tbl.Level = 2 then tbl.PositionCode else tree.Lvl2Code end as Lvl2Code, case when tbl.Level = 2 then tbl.PositionName else tree.Lvl2Name end as Lvl2Name, case when tbl.Level = 3 then tbl.PositionCode else tree.Lvl3Code end as Lvl3Code, case when tbl.Level = 3 then tbl.PositionName else tree.Lvl3Name end as Lvl3Name, tbl.ParentCode, tbl.Level FROM tbl JOIN tree ON tree.PositionCode = tbl.ParentCode ) SELECT CurrCode, Lvl0Code, Lvl0Name, Lvl1Code, Lvl1Name, Lvl2Code, Lvl2Name, Lvl3Code, Lvl3Name FROM tree OPTION (MAXRECURSION 0);db<>fiddle here
If you don't like or don't want or simply you cannot use a recursive solution, you can use a series of nested CTE's (one for each level), and finally combine the results using a UNION operation:
WITH l0 AS ( SELECT PositionCode as CurrCode, PositionCode as Lvl0Code, PositionName as Lvl0Name, 'na' as Lvl1Code, 'na' as Lvl1Name, 'na' as Lvl2Code, 'na' as Lvl2Name, 'na' as Lvl3Code, 'na' as Lvl3Name, level FROM tbl WHERE level = 0 ), l1 AS ( SELECT l0.CurrCode, l0.Lvl0Code, l0.Lvl0Name, t1.PositionCode Lvl1Code, t1.PositionName Lvl1Name, 'na' as Lvl2Code, 'na' as Lvl2Name, 'na' as Lvl3Code, 'na' as Lvl3Name, t1.level FROM tbl t1 JOIN l0 ON t1.ParentCode = l0.Lvl0Code WHERE t1.level = 1 ), l2 AS ( SELECT l1.CurrCode, l1.Lvl0Code, l1.Lvl0Name, l1.Lvl1Code, l1.Lvl1Name, t2.PositionCode Lvl2Code, t2.PositionName Lvl2Name, 'na' as Lvl3Code, 'na' as Lvl3Name, t2.level FROM tbl t2 JOIN l1 ON t2.ParentCode = l1.Lvl1Code WHERE t2.level = 2 ), l3 AS ( SELECT l2.CurrCode, l2.Lvl0Code, l2.Lvl0Name, l2.Lvl1Code, l2.Lvl1Name, l2.Lvl2Code, l2.Lvl2Name, t3.PositionCode Lvl3Code, t3.PositionName Lvl3Name, t3.level FROM tbl t3 JOIN l2 ON t3.ParentCode = l2.Lvl2Code WHERE t3.level = 3 ) SELECT * FROM l0 UNION SELECT * FROM l1 UNION SELECT * FROM l2 UNION SELECT * FROM l3 ORDER BY CurrCode, level;db<>fiddle here