September 29, 2016 at 5:04 am
Here is a sample query. There is a clustered index on the table consisting of SourceID, PcsAssmntDataID, QuerySetID, QueryNumberID. My question is, how best to search on PcsAssmntDataID? The % is in place of a unique number. So, I need to find all of the records that end with {A^GEN.ADMPART2}. Would using RIGHT(PcsAssmntDataID,16) be better?
selectSourceID,
PcsAssmntDataID,
QueryValue
from livefdb.dbo.PcsAssmntData_Queries
where SourceID='BRO'
and PcsAssmntDataID like '%{A^GEN.ADMPART2}'
and QuerySetID=11
and QueryNumberID=16
September 29, 2016 at 5:21 am
RIGHT should give you exactly the same results and performance. The clustered index order is not ideal for that query, but might be hard to change (you'll have to make sure no other query is hindered)
Adding a nonclustered index on (QuerySetID, QueryNumberID, SourceID) INCLUDE (PcsAssmntDataID, QueryValue) will probably be easier and safer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 29, 2016 at 5:28 am
Thanx. The value that the % is replacing is called a VisitID. If I was able to "supply" that value, is there a way that I could pass it to this query and make it run faster?
September 29, 2016 at 5:33 am
Sure, because then it'll be
PcsAssmntDataID = @SomeValue + '{A^GEN.ADMPART2}', and that's SARGable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 29, 2016 at 5:56 am
Pardon my ignorance but, how do I pass the values? Here is what I have so far. The first query will get me the VisitID's. The second query is what I need to pass the VisitID's into.
declare @StartDate datetime,
@EndDate datetime
set @StartDate='2016-09-01'
set @EndDate='2016-09-30'
-- Get list of VisitID's
selectSourceID,
VisitID
from livefdb.dbo.HimRec_VisitData
where SourceID='BRO'
and VisitType_MisRegTypeID='IN'
and RegistrationDateTime>=@StartDate and RegistrationDateTime<dateadd(day, 1, @EndDate)
select*
from livefdb.dbo.PcsAssmntData_Queries
where SourceID='BRO'
and QuerySetID=11
and QueryNumberID=16
and PcsAssmntDataID like '%{A^GEN.ADMPART2}'
create table #Test
(
VisitID varchar(30)
)
insert into #Test(VisitID) values('F1-B20160831224547982')
insert into #Test(VisitID) values('F0-B20160901011917931')
insert into #Test(VisitID) values('F0-B20160831184008016')
insert into #Test(VisitID) values('F1-B20160831202859671')
insert into #Test(VisitID) values('F0-B20160901020555781')
insert into #Test(VisitID) values('F1-B20160901035021227')
insert into #Test(VisitID) values('F1-B20160830221753728')
insert into #Test(VisitID) values('F1-B20160901065348590')
insert into #Test(VisitID) values('F1-B20160901102101421')
insert into #Test(VisitID) values('F0-B20160901075240947')
insert into #Test(VisitID) values('F0-B20160829124650987')
insert into #Test(VisitID) values('F1-B20160831212328214')
insert into #Test(VisitID) values('F1-B20160901084433810')
insert into #Test(VisitID) values('F1-B20160901030534444')
insert into #Test(VisitID) values('F1-B20160901084849786')
insert into #Test(VisitID) values('F0-B20160901131204832')
insert into #Test(VisitID) values('F1-B20160901113311115')
insert into #Test(VisitID) values('F0-B20160901091323743')
insert into #Test(VisitID) values('F0-B20160901104552007')
insert into #Test(VisitID) values('F0-B20160831115939697')
insert into #Test(VisitID) values('F1-B20160901110736936')
insert into #Test(VisitID) values('F1-B20160901130027318')
insert into #Test(VisitID) values('F1-B20160901155027323')
insert into #Test(VisitID) values('F1-B20160901082717534')
insert into #Test(VisitID) values('F0-B20160901162235357')
insert into #Test(VisitID) values('F1-B20160901160234546')
insert into #Test(VisitID) values('F1-B20160901160540745')
insert into #Test(VisitID) values('F1-B20160901204205372')
insert into #Test(VisitID) values('F0-B20160901173831208')
insert into #Test(VisitID) values('F1-B20160901232248685')
insert into #Test(VisitID) values('F1-B20160901235525113')
insert into #Test(VisitID) values('F0-B20160901192828517')
insert into #Test(VisitID) values('F0-B20160902002211240')
insert into #Test(VisitID) values('F1-B20160901221131488')
insert into #Test(VisitID) values('F0-B20160902030744260')
insert into #Test(VisitID) values('F1-B20160901171218371')
insert into #Test(VisitID) values('F1-B20160831184431212')
insert into #Test(VisitID) values('F0-B20160902062909200')
insert into #Test(VisitID) values('F0-B20160726111711304')
insert into #Test(VisitID) values('F1-B20160902104027002')
insert into #Test(VisitID) values('F0-B20160831094244103')
insert into #Test(VisitID) values('F0-B20160902115413404')
insert into #Test(VisitID) values('F1-B20160902133222326')
insert into #Test(VisitID) values('F1-B20160902085747205')
insert into #Test(VisitID) values('F0-B20160902054617541')
insert into #Test(VisitID) values('F0-B20160902073334979')
insert into #Test(VisitID) values('F0-B20160902123757207')
insert into #Test(VisitID) values('F1-B20160902122134232')
insert into #Test(VisitID) values('F1-B20160902123956133')
insert into #Test(VisitID) values('F0-B20160901005203438')
insert into #Test(VisitID) values('F1-B20160901164251262')
insert into #Test(VisitID) values('F0-B20160902182023747')
insert into #Test(VisitID) values('F1-B20160902144932667')
insert into #Test(VisitID) values('F0-B20160902130923217')
insert into #Test(VisitID) values('F0-B20160902141005707')
insert into #Test(VisitID) values('F1-B20160902131907830')
insert into #Test(VisitID) values('F1-B20160902163047552')
insert into #Test(VisitID) values('F0-B20160902174426900')
insert into #Test(VisitID) values('F1-B20160902144934920')
insert into #Test(VisitID) values('F1-B20160902203441999')
insert into #Test(VisitID) values('F0-B20160902164944570')
insert into #Test(VisitID) values('F1-B20160903000847833')
insert into #Test(VisitID) values('F0-B20160902223428956')
insert into #Test(VisitID) values('F1-B20160902212338250')
insert into #Test(VisitID) values('F1-B20160903011058435')
insert into #Test(VisitID) values('F0-B20160902223406582')
insert into #Test(VisitID) values('F0-B20160902234323112')
insert into #Test(VisitID) values('F0-B20160903110048078')
insert into #Test(VisitID) values('F0-B20160902182204226')
insert into #Test(VisitID) values('F1-B20160903113749996')
insert into #Test(VisitID) values('F1-B20160903110852295')
insert into #Test(VisitID) values('F0-B20160903164032998')
insert into #Test(VisitID) values('F1-B20160903144330752')
insert into #Test(VisitID) values('F1-B20160902173901863')
insert into #Test(VisitID) values('F1-B20160902143842167')
insert into #Test(VisitID) values('F0-B20160903150058387')
insert into #Test(VisitID) values('F1-B20160903160728055')
insert into #Test(VisitID) values('F0-B20160903153614251')
insert into #Test(VisitID) values('F0-B20160903183455008')
insert into #Test(VisitID) values('F0-B20160903190651784')
insert into #Test(VisitID) values('F1-B20160903170859859')
insert into #Test(VisitID) values('F0-B20160903201014185')
insert into #Test(VisitID) values('F1-B20160904020021248')
insert into #Test(VisitID) values('F0-B20160904004039559')
insert into #Test(VisitID) values('F0-B20160904052149019')
insert into #Test(VisitID) values('F0-B20160904093052700')
insert into #Test(VisitID) values('F1-B20160904112353734')
insert into #Test(VisitID) values('F0-B20160904090226804')
insert into #Test(VisitID) values('F0-B20160903140107702')
insert into #Test(VisitID) values('F1-B20160904124526766')
insert into #Test(VisitID) values('F0-B20160904111208668')
insert into #Test(VisitID) values('F0-B20160904131623111')
insert into #Test(VisitID) values('F1-B20160904122456020')
insert into #Test(VisitID) values('F1-B20160904194359675')
insert into #Test(VisitID) values('F1-B20160904165322909')
insert into #Test(VisitID) values('F1-B20160904212222558')
insert into #Test(VisitID) values('F1-B20160904173055376')
insert into #Test(VisitID) values('F0-B20160904155030038')
insert into #Test(VisitID) values('F1-B20160904165004950')
September 29, 2016 at 5:59 am
You're looking at a join there, not a 'passed value'.
FROM from livefdb.dbo.HimRec_VisitData INNER JOIN livefdb.dbo.PcsAssmntData_Queries ...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 29, 2016 at 6:09 am
like this? What would replace the ???? in the where clause?
declare @StartDate datetime,
@EndDate datetime
set @StartDate='2016-09-01'
set @EndDate='2016-09-30'
--Get inpatient VisitID's to look up
selectHRVD.SourceID,
HRVD.VisitID,
PADQ.*
from livefdb.dbo.HimRec_VisitData HRVD
inner join livefdb.dbo.PcsAssmntData_Queries PADQ
on HRVD.SourceID=PADQ.SourceID
and HRVD.VisitID=PADQ.????
where HRVD.SourceID='BRO'
and HRVD.VisitType_MisRegTypeID='IN'
and HRVD.RegistrationDateTime>=@StartDate and HRVD.RegistrationDateTime<dateadd(day, 1, @EndDate)
and PADQ.QuerySetID=11
and PADQ.QueryNumberID=16
and PADQ.PcsAssmntDataID like '%{A^GEN.ADMPART2}'
September 29, 2016 at 6:15 am
More like
declare @StartDate datetime,
@EndDate datetime;
set @StartDate='2016-09-01';
set @EndDate='2016-09-30';
--Get inpatient VisitID's to look up
selectHRVD.SourceID,
HRVD.VisitID,
PADQ.*
from livefdb.dbo.HimRec_VisitData HRVD
inner join livefdb.dbo.PcsAssmntData_Queries PADQ
on HRVD.SourceID=PADQ.SourceID
and PADQ.PcsAssmntDataID = <fill this in>
where HRVD.SourceID='BRO'
and HRVD.VisitType_MisRegTypeID='IN'
and HRVD.RegistrationDateTime>=@StartDate and HRVD.RegistrationDateTime<dateadd(day, 1, @EndDate)
and PADQ.QuerySetID=11
and PADQ.QueryNumberID=16;
I'm sure you can finish that.
Oh, and don't select *, specify just what columns you need from the PADQ table
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 29, 2016 at 6:17 am
Would this be the best way? Replace the ???? with left(PADQ.PcsAssmntDataID,21)?
declare @StartDate datetime,
@EndDate datetime
set @StartDate='2016-09-01'
set @EndDate='2016-09-30'
--Get inpatient VisitID's to look up
selectHRVD.SourceID,
HRVD.VisitID,
PADQ.*
from livefdb.dbo.HimRec_VisitData HRVD
inner join livefdb.dbo.PcsAssmntData_Queries PADQ
on HRVD.SourceID=PADQ.SourceID
and HRVD.VisitID=left(PADQ.PcsAssmntDataID,21)
where HRVD.SourceID='BRO'
and HRVD.VisitType_MisRegTypeID='IN'
and HRVD.RegistrationDateTime>=@StartDate and HRVD.RegistrationDateTime<dateadd(day, 1, @EndDate)
and PADQ.QuerySetID=11
and PADQ.QueryNumberID=16
and PADQ.PcsAssmntDataID like '%{A^GEN.ADMPART2}'
September 29, 2016 at 6:20 am
Thanx.
declare @StartDate datetime,
@EndDate datetime;
set @StartDate='2016-09-01';
set @EndDate='2016-09-30';
--Get inpatient VisitID's to look up
selectHRVD.SourceID,
HRVD.VisitID,
PADQ.*
from livefdb.dbo.HimRec_VisitData HRVD
inner join livefdb.dbo.PcsAssmntData_Queries PADQ
on HRVD.SourceID=PADQ.SourceID
and PADQ.PcsAssmntDataID = HRVD.VisitID + '{A^GEN.ADMPART2}'
where HRVD.SourceID='BRO'
and HRVD.VisitType_MisRegTypeID='IN'
and HRVD.RegistrationDateTime>=@StartDate and HRVD.RegistrationDateTime<dateadd(day, 1, @EndDate)
and PADQ.QuerySetID=11
and PADQ.QueryNumberID=16;
September 29, 2016 at 6:21 am
Oh, and don't select *, specify just what columns you need from the PADQ table
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 29, 2016 at 6:31 am
Yes. I only used * to save on typing. I never use it in practice.
Thanx again.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply