July 27, 2016 at 5:46 am
PcsAssmntDataID is part of a clustered index and is made up of a "VisitID" and a mnemonic. The VisitID are the numbers beginning with "F" and ending with "{". It is an identifier that can be used to join other tables in the db. The mnemonic identifies the type of data. In this case it is a patient's weight in both grams and something else. There are other mnemonics in the db for Height, BMI, etc.
I am creating a function that will return the patient's weight but, I need to supply only the VisitID part of the PcsAssmntDataID field as the parameter. I've tried using
where PcsAssmntDataID LIKE '%' + @VisitID + '%' but, the performance is not acceptable. Any thoughts?
create table #test
(
PcsAssmntDataID varchar(100),
IdentifierID varchar(100),
QueryValue varchar(100)
)
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160328143719043{A^VS.WEIGHT}','226183.000000062717590','{124737.901|4400}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160329141448248{A^VS.WEIGHT}','9029.000000276957940','{77110.703|2720}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160329142030169{A^VS.WEIGHT}','13131.000000088643025','{90718.474|3200}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160329142534150{A^VS.WEIGHT}','14967.000001053559352','{59874.193|2112}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160329143052967{A^VS.WEIGHT}','14901.000000077022834','{46266.422|1632}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160330083652968{A^VS.WEIGHT}','9674.000000283537468','{97975.951|3456}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160330085054230{A^VS.WEIGHT}','52560.000000117411725','{58967.008|2080}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160330114744698{A^VS.WEIGHT}','13762.000000085588847','{66678.078|2352}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160330122122043{A^VS.WEIGHT}','91897.000000371717043','{77110.703|2720}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160330132341691{A^VS.WEIGHT}','882169.000000022010693','{86182.55|3040}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160330135156007{A^VS.WEIGHT}','70114.000000048446538','{57152.638|2016}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160330142315687{A^VS.WEIGHT}','128565.000000170228888','{123377.124|4352}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160330170041111{A^VS.WEIGHT}','8692.000000867252704','{75749.925|2672}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160330171849365{A^VS.WEIGHT}','129955.000000104082377','{83007.403|2928}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160330172548006{A^VS.WEIGHT}','169756.000000140905661','{65770.893|2320}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160330172548006{A^VS.WEIGHT}','170917.000000063038782','{74842.741|2640}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160330173557155{A^VS.WEIGHT}','172845.000000236234457','{74842.741|2640}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160330175139614{A^VS.WEIGHT}','126888.000000011898842','{75749.925|2672}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160330180308433{A^VS.WEIGHT}','209113.000000191622180','{64863.709|2288}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160330181324040{A^VS.WEIGHT}','232676.000000252803593','{66678.078|2352}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160330181543738{A^VS.WEIGHT}','246593.000000452727976','{87996.919|3104}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160330182537802{A^VS.WEIGHT}','306331.000000190479459','{117026.831|4128}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160330184146634{A^VS.WEIGHT}','471148.000000285409678','{96615.174|3408}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160331112616123{A^VS.WEIGHT}','52340.000000161896957','{127005.863|4480}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160331144316230{A^VS.WEIGHT}','914705.000000161148954','{81193.034|2864}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160331152051930{A^VS.WEIGHT}','51936.000001509777418','{50802.345|1792}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160331165901450{A^VS.WEIGHT}','729951.000000141851195','{79378.664|2800}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401092843502{A^VS.WEIGHT}','470190.000000022019100','{99790.321|3520}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401094631507{A^VS.WEIGHT}','38124.000000057793193','{49441.568|1744}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401094631507{A^VS.WEIGHT}','51118.000000004277376','{48335.937|1705}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401102024255{A^VS.WEIGHT}','106762.000000006941252','{132193.826|4663}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401102024255{A^VS.WEIGHT}','125359.000000336500900','{132987.612|4691}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401102024255{A^VS.WEIGHT}','142777.000000010869914','{124919.338|4406.4}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401102024255{A^VS.WEIGHT}','155531.000000014946956','{123830.716|4368}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401102024255{A^VS.WEIGHT}','167496.000000116241839','{125282.212|4419.2}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401102024255{A^VS.WEIGHT}','186116.000000170114612','{127516.154|4498}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401102024255{A^VS.WEIGHT}','205746.000000016974181','{127187.3|4486.4}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401102024255{A^VS.WEIGHT}','209146.000000269918533','{127005.863|4480}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401102024255{A^VS.WEIGHT}','234104.000001713479188','{126608.97|4466}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401102024255{A^VS.WEIGHT}','24717.000000329903526','{133446.875|4707.2}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401102024255{A^VS.WEIGHT}','38140.000000010988701','{134354.059|4739.2}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401102024255{A^VS.WEIGHT}','59244.000000665902166','{135397.322|4776.0}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401102024255{A^VS.WEIGHT}','68791.000000232374912','{126144.038|4449.6}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401102024255{A^VS.WEIGHT}','77690.000000123467271','{126370.834|4457.6}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401102024255{A^VS.WEIGHT}','87337.000000005944662','{131950.02|4654.4}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401114914967{A^VS.WEIGHT}','917001.000000031091076','{77110.703|2720}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401133412563{A^VS.WEIGHT}','694403.000000393829501','{90718.474|3200}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401135910010{A^VS.WEIGHT}','916049.000000222363384','{95707.99|3376}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401140150814{A^VS.WEIGHT}','27383.000000096734040','{61189.61|2158.4}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401142041890{A^VS.WEIGHT}','171732.000000041142872','{47627.199|1680}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401143045201{A^VS.WEIGHT}','245371.000001334465737','{85275.365|3008}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401145114013{A^VS.WEIGHT}','133151.000000262006262','{45359.237|1600}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401152024752{A^VS.WEIGHT}','73919.000000030966368','{86182.55|3040}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401163419662{A^VS.WEIGHT}','247344.000000041490475','{76203.518|2688}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401191527641{A^VS.WEIGHT}','38132.000000010085819','{89675.211|3163.2}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401191527641{A^VS.WEIGHT}','51278.000000033707410','{88677.308|3128}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401193541881{A^VS.WEIGHT}','19180.000000017280637','{2575.000|90.830}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401193541881{A^VS.WEIGHT}','27555.000000011143181','{2665.000|94.005}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401193541881{A^VS.WEIGHT}','39741.000000268082170','{2690.00|94.887}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401200144642{A^VS.WEIGHT}','124916.000000567896998','{72177.886|2546}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401200144642{A^VS.WEIGHT}','125196.000000018528549','{72211.905|2547.2}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401200144642{A^VS.WEIGHT}','20474.000000511169452','{75948.372|2679}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401200144642{A^VS.WEIGHT}','21598.000000076957263','{75948.372|2679}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401203803331{A^VS.WEIGHT}','103918.000000604925553','{64364.757|2270.4}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401203803331{A^VS.WEIGHT}','122956.000000618167077','{66500|2345.718}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401203803331{A^VS.WEIGHT}','140079.000000258219317','{65300|2303.39}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401203803331{A^VS.WEIGHT}','153831.000000363153142','{60400|2130.547}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401203803331{A^VS.WEIGHT}','165689.000001780848443','{48400|1707.26}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401203803331{A^VS.WEIGHT}','184094.000000019300834','{46900|1654.349}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401203803331{A^VS.WEIGHT}','184363.000000111785526','{46900|1654.349}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401203803331{A^VS.WEIGHT}','206355.000000192372979','{44406.693|1566.4}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401203803331{A^VS.WEIGHT}','285380.000000366290843','{55054.774|1942}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401203803331{A^VS.WEIGHT}','286743.000000030180038','{55054.774|1942}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401203803331{A^VS.WEIGHT}','301097.000000597128373','{55933.609|1973}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401203803331{A^VS.WEIGHT}','320466.000000008149795','{54657.88|1928.0}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401203803331{A^VS.WEIGHT}','338148.000001586864031','{53098.657|1873}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401203803331{A^VS.WEIGHT}','355880.000000011284292','{52758.462|1861}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401203803331{A^VS.WEIGHT}','50495.000000407311277','{61800|2179.931}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160401203803331{A^VS.WEIGHT}','84834.000000037780445','{65000|2292.807}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160402085755850{A^VS.WEIGHT}','51224.000000030886230','{83120.801|2932}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160402132255404{A^VS.WEIGHT}','68349.000000041562123','{85020.219|2999}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160402144304696{A^VS.WEIGHT}','106294.000000007283130','{63956.524|2256.0}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160402144304696{A^VS.WEIGHT}','87551.000000026516635','{63502.932|2240.0}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160402155133890{A^VS.WEIGHT}','35916.000000381251049','{160300|5654.416}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160402155133890{A^VS.WEIGHT}','50596.000000038846857','{157169.756|5544}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160403154752143{A^VS.WEIGHT}','50803.000000059421392','{86267.598|3043}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160403175831961{A^VS.WEIGHT}','58176.000000077687543','{72121.187|2544.0}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160403184649063{A^VS.WEIGHT}','53260.000000027786284','{111130.13|3920}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160403192326878{A^VS.WEIGHT}','49333.000000711693318','{104825.196|3697.6}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160404052616749{A^VS.WEIGHT}','106514.000000011060608','{48625.102|1715.2}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160404052616749{A^VS.WEIGHT}','87282.000000053194280','{48216.869|1700.8}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160404065739731{A^VS.WEIGHT}','64467.000000044818982','{95299.757|3361.6}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160404065843410{A^VS.WEIGHT}','57038.000000031015593','{94574.009|3336.0}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160404085811542{A^VS.WEIGHT}','69255.000000147364236','{90038.085|3176.0}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160404115742853{A^VS.WEIGHT}','188677.000000023121910','{72574.779|2560}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160404151433841{A^VS.WEIGHT}','60517.000000090912564','{75840.644|2675.2}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160404152312344{A^VS.WEIGHT}','72489.000000088210897','{81646.626|2880}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160404161552043{A^VS.WEIGHT}','75189.000000425449048','{48987.976|1728}')
insert into #test(PcsAssmntDataID, IdentifierID, QueryValue) values('F0-B20160404163652900{A^VS.WEIGHT}','105289.000000352435944','{60781.377|2144}')
July 27, 2016 at 6:26 am
You don't need the wildcard on the left-hand side of the variable because you're matching from the left. It's also not SARGable (cannot exploit seeks). Try this instead:
where PcsAssmntDataID LIKE @VisitID + '%'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 27, 2016 at 6:30 am
Thanx but, in the meantime I came up with this solution..............................
alter function dbo.fnWeight(@VisitID varchar(100))
RETURNS float as
BEGIN
DECLARE @Return float
set @VisitID=@VisitID + '{A^VS.WEIGHT}'
select @Return=cast(replace(left(PADQ.QueryValue,charindex('|',PADQ.QueryValue)-1),'{','') as float)/1000
from livefdb.dbo.PcsAssmntData_Queries PADQ
inner join (selectSourceID,
PcsAssmntDataID,
max(IdentifierID) as IdentifierID
from livefdb.dbo.PcsAssmntData_Queries
where SourceID='BRO'
and QuerySetID=1
and QueryNumberID=1
and PcsAssmntDataID=@VisitID
group by SourceID,
PcsAssmntDataID) PADQ2
on PADQ.SourceID=PADQ2.SourceID
and PADQ.PcsAssmntDataID=PADQ2.PcsAssmntDataID
and PADQ.IdentifierID=PADQ2.IdentifierID
where PADQ.SourceID='BRO'
and PADQ.QuerySetID=1
and PADQ.QueryNumberID=1
RETURN Coalesce(@Return,'');
END
July 27, 2016 at 7:03 am
NineIron (7/27/2016)
Thanx but, in the meantime I came up with this solution..............................
alter function dbo.fnWeight(@VisitID varchar(100))
RETURNS float as
BEGIN
DECLARE @Return float
set @VisitID=@VisitID + '{A^VS.WEIGHT}'
select @Return=cast(replace(left(PADQ.QueryValue,charindex('|',PADQ.QueryValue)-1),'{','') as float)/1000
from livefdb.dbo.PcsAssmntData_Queries PADQ
inner join (selectSourceID,
PcsAssmntDataID,
max(IdentifierID) as IdentifierID
from livefdb.dbo.PcsAssmntData_Queries
where SourceID='BRO'
and QuerySetID=1
and QueryNumberID=1
and PcsAssmntDataID=@VisitID
group by SourceID,
PcsAssmntDataID) PADQ2
on PADQ.SourceID=PADQ2.SourceID
and PADQ.PcsAssmntDataID=PADQ2.PcsAssmntDataID
and PADQ.IdentifierID=PADQ2.IdentifierID
where PADQ.SourceID='BRO'
and PADQ.QuerySetID=1
and PADQ.QueryNumberID=1
RETURN Coalesce(@Return,'');
END
This is the solution to a completely different question! It could be improved by converting it to an inline table-valued function very easily if performance is an issue. It might also be improved by using row-number() rather than two reads of the same table.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 27, 2016 at 7:07 am
Like this:
SELECT ReturnValue
FROM (
SELECT
ReturnValue = ISNULL(cast(replace(left(QueryValue,charindex('|',QueryValue)-1),'{','') as float)/1000,0),
rn = ROW_NUMBER() OVER (PARTITION BY SourceID, PcsAssmntDataID ORDER BY IdentifierID DESC)
FROM livefdb.dbo.PcsAssmntData_Queries
WHERE SourceID = 'BRO'
and QuerySetID = 1
and QueryNumberID = 1
and PcsAssmntDataID = @VisitID
) d
WHERE rn = 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 27, 2016 at 7:25 am
Nice.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply