January 2, 2019 at 2:33 pm
Good afternoon all,
trying to optimize a portion of my sql. I've simplified it below, but the portion that it's hang up on is the inner join to the SSICategory, specifically the 2nd part ((n.SSICatyear = year(ep.proceduredate)). Below is a simplified query with the data types of the joins and what I've tried so far is below, but not really making a difference. Could really use some help. Thanks!
declare @startdate as date
set @startdate='2016-01-01'
;with SSICTE as (
SELECT distinct
ep.[VisitId]
,n.SSICat
,ep.ProcedureDate
,ep.proccode
FROM EncounterProc ep
inner join SSiCategory n on (n.ICDcode=ep.proccode) and (n.SSICatyear = year(ep.proceduredate))
Select ssi.visitid, ssi.SSICat, ssi.ProcedureDate, ssi.proccode
From SSICTE SSI
where ssi.proceduredate>=@startdate
Data Types
n.icdcode = nvarchar(255);
ep.proccode = varchar(10);
n.ssiCatyear = nvarchar(255);
ep.proceduredate = datetime
I have tried:
declare @startdate as date
set @startdate='2016-01-01'
;with SSICTE as (
SELECT distinct
ep.[VisitId]
,n.SSICat
,ep.ProcedureDate
,ep.proccode
FROM EncounterProc ep
inner join SSiCategory n on (n.ICDcode=ep.proccode) and (covert(int,n.SSICatyear) = year(convert(int,ep.proceduredate)))
n.icdcode = nvarchar(255);
ep.proccode = varchar(10);
n.ssiCatyear = nvarchar(255);
ep.proceduredate = datetime
Select ssi.visitid, ssi.SSICat, ssi.ProcedureDate, ssi.proccode
From SSICTE SSI
where ssi.proceduredate>=@startdate
January 2, 2019 at 3:09 pm
Without data to test with not sure if this would work, here is what I would try:DECLARE @startdate AS DATE;
SET @startdate = '2016-01-01';
WITH EncounterProc_CTE AS
(
SELECT ep.VisitId,
ep.ProcedureDate,
ep.proccode,
YEAR(ep.ProcedureDate) AS ProcedureYear
FROM EncounterProc AS ep
WHERE ep.proceduredate >= @startdate
)
,
SSICTE AS
(
SELECT
ep.VisitId,
n.SSICat,
ep.ProcedureDate,
ep.proccode
FROM EncounterProc_CTE AS ep
INNER JOIN SSiCategory AS n
ON n.ICDcode = ep.proccode
AND n.SSICatyear = ep.ProcedureYear
GROUP BY
ep.VisitId,
n.SSICat,
ep.ProcedureDate,
ep.proccode
)
Select ssi.VisitId,
ssi.SSICat,
ssi.ProcedureDate,
ssi.proccode
From SSICTE AS SSI
;
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 2, 2019 at 3:31 pm
You should generally avoid functions/conversions on fields in ON/WHERE clauses. You have multiple functions/conversions in your ON clause, which is why you are having problems.
Part of the issue is that you are storing your data in the wrong format. A Year is not VARCHAR() data, so it should not be stored in a VARCHAR() field.
You can address some of the other issues using a calendar table, and you can even construct a partial one on the fly. You can search for a calendar table to see examples of how this might work.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 3, 2019 at 5:13 am
Specifically this stuff:
(covert(int,n.SSICatyear) = year(convert(int,ep.proceduredate)))
Is going to kill performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 3, 2019 at 10:01 am
Reforrmat the SSICat data and not the ep data, since there should be far fewer SSICat rows.
How is the EncounterProc table clustered?
Hopefully you don't actually need the distinct, but uncomment it if you do. Distinct adds overhead because it requires a sort.
SELECT /*DISTINCT --uncomment if you genuinely require DISTINCT*/
ep.[VisitId]
,n.SSICat
,ep.ProcedureDate
,ep.proccode
FROM EncounterProc ep
INNER JOIN SSiCategory n ON (n.ICDcode=ep.proccode) AND
ep.proceduredate >= n.SSICatyear + '0101' AND
ep.proceduredate < cast(convert(int, n.SSICatyear + 1) as varchar(4)) + '0101'
WHERE ep.proceduredate>=@startdate
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply