Solved

SQL Query Performance

Posted on 2010-09-02
24
775 Views
Last Modified: 2012-05-10
Hello:

Below is a sql code that has been timing out. When I execute, it takes about 20 mins. The database expert links might help...I'll attach them shortly...

The report uses multiple versions of the same tables as it searches/looks or returns to the ED within 72 hours of the original arrival. Please can you advice/help on tuning the SQL Query or what I should do to solve the time out issue? TIA.


SELECT DISTINCT "PAT_ENC_HSP"."ED_EPISODE_ID", "PAT_ENC_HSP"."ADMIT_CONF_STAT_C", "PAT_ENC_HSP_past"."ADMIT_CONF_STAT_C", "CLARITY_ADT"."EVENT_ID", "CLARITY_ADT"."EVENT_TYPE_C", "CLARITY_ADT"."EVENT_SUBTYPE_C", "CLARITY_ADT_past"."EVENT_ID", "CLARITY_ADT_past"."EVENT_TYPE_C", "CLARITY_ADT_past"."EVENT_SUBTYPE_C", "PATIENT"."PAT_NAME", "PAT_ENC_HSP"."ADT_ARRIVAL_TIME", "PAT_ENC_HSP_past"."ADT_ARRIVAL_TIME", "PAT_ENC_HSP_past"."ED_EPISODE_ID", "PAT_ENC_HSP"."SSM_TEST_TYPE", "CLARITY_LOC"."LOC_ID", "IDENTITY_ID"."IDENTITY_TYPE_ID", "IDENTITY_ID"."IDENTITY_ID", "PATIENT"."BIRTH_DATE", "PAT_ENC_HSP"."BILL_NUM", "PAT_ENC_HSP_past"."BILL_NUM", "SSM_DIAGNOSIS"."PRIMARY_DX", "SSM_DIAGNOSIS"."COMBINED_DX", "SSM_DIAGNOSIS_past"."PRIMARY_DX", "SSM_DIAGNOSIS_past"."COMBINED_DX", "ZC_ED_DISPOSITION_past"."NAME", "ZC_ED_DISPOSITION"."NAME", "CLARITY_LOC_past"."LOC_ID"
 FROM   (((((((((((("Clarity"."dbo"."PAT_ENC_HSP" "PAT_ENC_HSP" LEFT OUTER JOIN "Clarity"."dbo"."PAT_ENC_HSP" "PAT_ENC_HSP_past" ON "PAT_ENC_HSP"."PAT_ID"="PAT_ENC_HSP_past"."PAT_ID") LEFT OUTER JOIN "Clarity"."dbo"."PATIENT" "PATIENT" ON "PAT_ENC_HSP"."PAT_ID"="PATIENT"."PAT_ID") LEFT OUTER JOIN "Clarity"."dbo"."CLARITY_ADT" "CLARITY_ADT" ON ("PAT_ENC_HSP"."PAT_ID"="CLARITY_ADT"."PAT_ID") AND ("PAT_ENC_HSP"."PAT_ENC_DATE_REAL"="CLARITY_ADT"."PAT_ENC_DATE_REAL")) LEFT OUTER JOIN "Clarity"."dbo"."SSM_DIAGNOSIS" "SSM_DIAGNOSIS" ON "PAT_ENC_HSP"."PAT_ENC_CSN_ID"="SSM_DIAGNOSIS"."PAT_ENC_CSN_ID") LEFT OUTER JOIN "Clarity"."dbo"."ZC_ED_DISPOSITION" "ZC_ED_DISPOSITION" ON "PAT_ENC_HSP"."ED_DISPOSITION_C"="ZC_ED_DISPOSITION"."ED_DISPOSITION_C") LEFT OUTER JOIN "Clarity"."dbo"."CLARITY_ADT" "CLARITY_ADT_past" ON ("PAT_ENC_HSP_past"."PAT_ID"="CLARITY_ADT_past"."PAT_ID") AND ("PAT_ENC_HSP_past"."PAT_ENC_DATE_REAL"="CLARITY_ADT_past"."PAT_ENC_DATE_REAL")) LEFT OUTER JOIN "Clarity"."dbo"."SSM_DIAGNOSIS" "SSM_DIAGNOSIS_past" ON "PAT_ENC_HSP_past"."PAT_ENC_CSN_ID"="SSM_DIAGNOSIS_past"."PAT_ENC_CSN_ID") LEFT OUTER JOIN "Clarity"."dbo"."ZC_ED_DISPOSITION" "ZC_ED_DISPOSITION_past" ON "PAT_ENC_HSP_past"."ED_DISPOSITION_C"="ZC_ED_DISPOSITION_past"."ED_DISPOSITION_C") LEFT OUTER JOIN "Clarity"."dbo"."CLARITY_DEP" "CLARITY_DEP_past" ON "CLARITY_ADT_past"."DEPARTMENT_ID"="CLARITY_DEP_past"."DEPARTMENT_ID") LEFT OUTER JOIN "Clarity"."dbo"."CLARITY_LOC" "CLARITY_LOC_past" ON "CLARITY_DEP_past"."REV_LOC_ID"="CLARITY_LOC_past"."LOC_ID") LEFT OUTER JOIN "Clarity"."dbo"."CLARITY_DEP" "CLARITY_DEP" ON "CLARITY_ADT"."DEPARTMENT_ID"="CLARITY_DEP"."DEPARTMENT_ID") LEFT OUTER JOIN "Clarity"."dbo"."CLARITY_LOC" "CLARITY_LOC" ON "CLARITY_DEP"."REV_LOC_ID"="CLARITY_LOC"."LOC_ID") LEFT OUTER JOIN "Clarity"."dbo"."IDENTITY_ID" "IDENTITY_ID" ON "PATIENT"."PAT_ID"="IDENTITY_ID"."PAT_ID"
 WHERE  "PAT_ENC_HSP"."SSM_TEST_TYPE" IS  NULL  AND "PAT_ENC_HSP_past"."ED_EPISODE_ID" IS  NOT  NULL  AND ("PAT_ENC_HSP_past"."ADT_ARRIVAL_TIME">={ts '2010-08-01 00:00:00'} AND "PAT_ENC_HSP_past"."ADT_ARRIVAL_TIME"<{ts '2010-09-01 00:00:00'}) AND ("CLARITY_ADT_past"."EVENT_ID" IS  NULL  OR "CLARITY_ADT_past"."EVENT_TYPE_C"=1 AND "CLARITY_ADT_past"."EVENT_SUBTYPE_C"<>2) AND ("CLARITY_ADT"."EVENT_ID" IS  NULL  OR "CLARITY_ADT"."EVENT_TYPE_C"=1 AND "CLARITY_ADT"."EVENT_SUBTYPE_C"<>2) AND ("PAT_ENC_HSP_past"."ADMIT_CONF_STAT_C" IS  NULL  OR  NOT ("PAT_ENC_HSP_past"."ADMIT_CONF_STAT_C"='2' OR "PAT_ENC_HSP_past"."ADMIT_CONF_STAT_C"='3')) AND ("PAT_ENC_HSP"."ADMIT_CONF_STAT_C" IS  NULL  OR  NOT ("PAT_ENC_HSP"."ADMIT_CONF_STAT_C"='2' OR "PAT_ENC_HSP"."ADMIT_CONF_STAT_C"='3')) AND "PAT_ENC_HSP"."ED_EPISODE_ID" IS  NOT  NULL  AND "PAT_ENC_HSP"."ED_EPISODE_ID">"PAT_ENC_HSP_past"."ED_EPISODE_ID" AND "CLARITY_LOC_past"."LOC_ID"=130992023 AND "CLARITY_LOC"."LOC_ID"=130992023 AND "IDENTITY_ID"."IDENTITY_TYPE_ID"=7

0
Comment
Question by:Nakuru1234
  • 8
  • 6
  • 4
  • +3
24 Comments
 

Author Comment

by:Nakuru1234
Comment Utility
Here is additional information on the Database links in case they might help...

TIA,
N
Database-Table-links-09022010.doc
0
 
LVL 35

Accepted Solution

by:
David Todd earned 100 total points
Comment Utility
Hi,

Yip, the diagram of the table links helped.

Can you give the FULL table definition for PAT_ENC_HSP?

And this is definitely better handled as a SQL Stored Procedure than as an Access linked query.

THe key to me having spent something like 30 secs to 5 minutes is that the PAT_ENC_HSP table needs a self join, and the other stuff is for reporting.

I'd get that join performing first, and then worry about the related information.

The other thing to think about is how often this report is being run. That is, if it is run weekly, then there is no point going further back than a month - a week or two in case a run is missed, and 72 hours, and because I'm just a tinsy winsy bit paranoid. I imagine that at a busy hospital or medical facility, just limiting how far back in PAT_ENC_HSP will speed up performance greatly.

If you are still stuck, you will need to hire someone - its almost impossible to do anything real without your real data and volume - obscured maybe.

Do get the actual query plan, and look through it. While its not perfect it will highlight major missing indexes. That is, it went looking to see if it could use a particular index and found it didn't exist.

HTH
  David
0
 

Author Comment

by:Nakuru1234
Comment Utility
Hi David:

I understand. What do you mean by "I'd get that join performing first, and then worry about the related information" Please can you elaborate or give an example?

Please see the FULL table definition for PAT_ENC_HSP in the attachment...TIA

N.
PAT-ENC-HSP-Table-Definition-090.doc
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Without real data it's hard to help you but there's some rules that you can always use in queries:
- Avoid OR operator
- Create indexes on foreign keys columns to speed up joins

If you can post the query plan will be helpfull.

Greetings
0
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 250 total points
Comment Utility
If I see it correctly joining the same tables multiple times with _past suffix in alias is really not necessary. For instance lets start with the driving table:

Clarity.dbo.PAT_ENC_HSP PAT_ENC_HSP
LEFT OUTER JOIN Clarity.dbo.PAT_ENC_HSP PAT_ENC_HSP_past
      ON PAT_ENC_HSP.PAT_ID=PAT_ENC_HSP_past.PAT_ID

what you do really you join the table with itself on the SAME PAT_IDs, there is nothing there that goes in the past really and you simply double the columns there. The only element that joins the PAT_ENC_HSP table with values that or different than the courent row is

AND PAT_ENC_HSP.ED_EPISODE_ID>PAT_ENC_HSP_past.ED_EPISODE_ID

but if you comment that you will have all ED_EPISODE_IDs available for the patient.

The same you do with all the other double joins because no matter if you add "_past" the IDs you join on are the exact same with the versions without the "_past".

Is there any reason why you do that?

Beside the double joins you have to make sure that you have indexes on ALL the columns used in the ON clauses and where you use 2 columns to join it will help if you have composite indexes on them, for instance:

LEFT OUTER JOIN Clarity.dbo.CLARITY_ADT CLARITY_ADT
      ON      PAT_ENC_HSP.PAT_ID=CLARITY_ADT.PAT_ID
      AND PAT_ENC_HSP.PAT_ENC_DATE_REAL=CLARITY_ADT.PAT_ENC_DATE_REAL

you should create a composite index on CLARITY_ADT table with PAT_ID and PAT_ENC_DATE_REAL columns and also on PAT_ENC_HSP.

The query if you eliminate the "_past" joins really comes dow to the query below and I bet on all the money it will be much faster.




SELECT -- DISTINCT 

	PAT_ENC_HSP.ED_EPISODE_ID, 

	

	PAT_ENC_HSP.ADMIT_CONF_STAT_C, 

	--PAT_ENC_HSP_past.ADMIT_CONF_STAT_C,

	 

	CLARITY_ADT.EVENT_ID, 

	CLARITY_ADT.EVENT_TYPE_C, 

	CLARITY_ADT.EVENT_SUBTYPE_C, 

	

	--CLARITY_ADT_past.EVENT_ID, 

	--CLARITY_ADT_past.EVENT_TYPE_C, 

	--CLARITY_ADT_past.EVENT_SUBTYPE_C, 

	

	PATIENT.PAT_NAME, 

	PAT_ENC_HSP.ADT_ARRIVAL_TIME, 

	

	--PAT_ENC_HSP_past.ADT_ARRIVAL_TIME, 

	--PAT_ENC_HSP_past.ED_EPISODE_ID, 

	

	PAT_ENC_HSP.SSM_TEST_TYPE, 

	CLARITY_LOC.LOC_ID, 

	IDENTITY_ID.IDENTITY_TYPE_ID, 

	IDENTITY_ID.IDENTITY_ID, 

	PATIENT.BIRTH_DATE, 

	PAT_ENC_HSP.BILL_NUM,

	 

	--PAT_ENC_HSP_past.BILL_NUM, 

	

	SSM_DIAGNOSIS.PRIMARY_DX, 

	SSM_DIAGNOSIS.COMBINED_DX, 

	

	--SSM_DIAGNOSIS_past.PRIMARY_DX, 

	--SSM_DIAGNOSIS_past.COMBINED_DX,

	--ZC_ED_DISPOSITION_past.NAME, 

	

	ZC_ED_DISPOSITION.NAME

	 

	--CLARITY_LOC_past.LOC_ID

 FROM   

		Clarity.dbo.PAT_ENC_HSP PAT_ENC_HSP 

		--LEFT OUTER JOIN Clarity.dbo.PAT_ENC_HSP PAT_ENC_HSP_past 

		--	ON PAT_ENC_HSP.PAT_ID=PAT_ENC_HSP_past.PAT_ID

			

		LEFT OUTER JOIN Clarity.dbo.PATIENT PATIENT 

			ON PAT_ENC_HSP.PAT_ID=PATIENT.PAT_ID

			

		LEFT OUTER JOIN Clarity.dbo.CLARITY_ADT CLARITY_ADT 

			ON	PAT_ENC_HSP.PAT_ID=CLARITY_ADT.PAT_ID 

			AND PAT_ENC_HSP.PAT_ENC_DATE_REAL=CLARITY_ADT.PAT_ENC_DATE_REAL

			

		LEFT OUTER JOIN Clarity.dbo.SSM_DIAGNOSIS SSM_DIAGNOSIS 

			ON PAT_ENC_HSP.PAT_ENC_CSN_ID=SSM_DIAGNOSIS.PAT_ENC_CSN_ID 

			

		LEFT OUTER JOIN Clarity.dbo.ZC_ED_DISPOSITION ZC_ED_DISPOSITION 

			ON PAT_ENC_HSP.ED_DISPOSITION_C=ZC_ED_DISPOSITION.ED_DISPOSITION_C 

			

		--LEFT OUTER JOIN Clarity.dbo.CLARITY_ADT CLARITY_ADT_past 

		--	ON	PAT_ENC_HSP_past.PAT_ID=CLARITY_ADT_past.PAT_ID

		--	AND PAT_ENC_HSP_past.PAT_ENC_DATE_REAL=CLARITY_ADT_past.PAT_ENC_DATE_REAL

			

		--LEFT OUTER JOIN Clarity.dbo.SSM_DIAGNOSIS SSM_DIAGNOSIS_past 

		--	ON PAT_ENC_HSP_past.PAT_ENC_CSN_ID=SSM_DIAGNOSIS_past.PAT_ENC_CSN_ID 

			

		--LEFT OUTER JOIN Clarity.dbo.ZC_ED_DISPOSITION ZC_ED_DISPOSITION_past 

		--	ON PAT_ENC_HSP_past.ED_DISPOSITION_C=ZC_ED_DISPOSITION_past.ED_DISPOSITION_C 

			

		--LEFT OUTER JOIN Clarity.dbo.CLARITY_DEP CLARITY_DEP_past 

		--	ON CLARITY_ADT_past.DEPARTMENT_ID=CLARITY_DEP_past.DEPARTMENT_ID 

			

		--LEFT OUTER JOIN Clarity.dbo.CLARITY_LOC CLARITY_LOC_past 

		--	ON CLARITY_DEP_past.REV_LOC_ID=CLARITY_LOC_past.LOC_ID 

			

		LEFT OUTER JOIN Clarity.dbo.CLARITY_DEP CLARITY_DEP 

			ON CLARITY_ADT.DEPARTMENT_ID=CLARITY_DEP.DEPARTMENT_ID 

			

		LEFT OUTER JOIN Clarity.dbo.CLARITY_LOC CLARITY_LOC 

			ON CLARITY_DEP.REV_LOC_ID=CLARITY_LOC.LOC_ID 

			

		LEFT OUTER JOIN Clarity.dbo.IDENTITY_ID IDENTITY_ID 

			ON PATIENT.PAT_ID=IDENTITY_ID.PAT_ID

 WHERE

	PAT_ENC_HSP.SSM_TEST_TYPE IS  NULL  

	AND PAT_ENC_HSP.ED_EPISODE_ID IS  NOT  NULL  

	AND PAT_ENC_HSP.ADT_ARRIVAL_TIME>={ts '2010-08-01 00:00:00'} 

	AND PAT_ENC_HSP.ADT_ARRIVAL_TIME<{ts '2010-09-01 00:00:00'} 

	

	--AND PAT_ENC_HSP_past.ED_EPISODE_ID IS  NOT  NULL  

	--AND PAT_ENC_HSP_past.ADT_ARRIVAL_TIME>={ts '2010-08-01 00:00:00'} 

	--AND PAT_ENC_HSP_past.ADT_ARRIVAL_TIME<{ts '2010-09-01 00:00:00'} 

	--AND 

	--(

	--	CLARITY_ADT_past.EVENT_ID IS  NULL  

	--	OR 

	--	CLARITY_ADT_past.EVENT_TYPE_C=1 AND CLARITY_ADT_past.EVENT_SUBTYPE_C<>2

	--)

	AND 

	(

		CLARITY_ADT.EVENT_ID IS  NULL  

		OR 

		CLARITY_ADT.EVENT_TYPE_C=1 AND CLARITY_ADT.EVENT_SUBTYPE_C<>2

	) 

	--AND 

	--(

	--	PAT_ENC_HSP_past.ADMIT_CONF_STAT_C IS  NULL  

	--	OR  

	--	NOT (PAT_ENC_HSP_past.ADMIT_CONF_STAT_C='2' OR PAT_ENC_HSP_past.ADMIT_CONF_STAT_C='3')

	--) 

	AND 

	(

		PAT_ENC_HSP.ADMIT_CONF_STAT_C IS  NULL  

		OR  

		NOT (PAT_ENC_HSP.ADMIT_CONF_STAT_C='2' OR PAT_ENC_HSP.ADMIT_CONF_STAT_C='3')

	) 

	AND PAT_ENC_HSP.ED_EPISODE_ID IS  NOT  NULL  

	

	-- AND PAT_ENC_HSP.ED_EPISODE_ID>PAT_ENC_HSP_past.ED_EPISODE_ID

	

	--AND CLARITY_LOC_past.LOC_ID=130992023 

	AND CLARITY_LOC.LOC_ID=130992023 

	AND IDENTITY_ID.IDENTITY_TYPE_ID=7

Open in new window

0
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 250 total points
Comment Utility
One clarification. When I said:

"The only element that joins the PAT_ENC_HSP table with values that or different than the courent row is

AND PAT_ENC_HSP.ED_EPISODE_ID>PAT_ENC_HSP_past.ED_EPISODE_ID"

I was referring to the condition from the WHERE clause of course.


And speaking the WHERE clause, you also have to make sure that there are indexes on the columns in the WHERE clause. So the rule of thumb would be:

For queries you need indexes on all the columns(composite if the case) in the ON (JOIN) clauses and in the WHERE clauses. If you have that the performance will be much better.

There are also other considerations about how you join and how you build the condition in the WHERE clause but that cannot help unless the rule above is applied.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
Comment Utility
If you are going to put conditions in your WHERE clause (other than check for NULL) for columns in a LEFT JOIN than all you have achieved is implicitly convert your LEFT JOIN to an INNER JOIN.  So please confirm you need a LEFT JOIN on:
Clarity.dbo.PAT_ENC_HSP
Clarity.dbo.CLARITY_ADT
Clarity.dbo.CLARITY_LOC
Clarity.dbo.IDENTITY_ID

Conversely doing this:
LEFT OUTER JOIN Clarity.dbo.PAT_ENC_HSP PAT_ENC_HSP_past
And then doing this:
  AND PAT_ENC_HSP_past.ED_EPISODE_ID IS NOT NULL
Is potentially contradictory.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 100 total points
Comment Utility
Would run it up in SQL query analyser in SQL Server Managment Studio first up and see what it says about indexes and such like.

Was hard to read, so, made it suitable for the above task, and still unsure about all those double ups (yes, can see some distinctions in the where clause and will take some more work to figure it all out)


SELECT DISTINCT PAT_ENC_HSP.ED_EPISODE_ID, PAT_ENC_HSP.ADMIT_CONF_STAT_C
, PAT_ENC_HSP_past.ADMIT_CONF_STAT_C, CLARITY_ADT.EVENT_ID, CLARITY_ADT.EVENT_TYPE_C, CLARITY_ADT.EVENT_SUBTYPE_C
, CLARITY_ADT_past.EVENT_ID, CLARITY_ADT_past.EVENT_TYPE_C, CLARITY_ADT_past.EVENT_SUBTYPE_C, PATIENT.PAT_NAME
, PAT_ENC_HSP.ADT_ARRIVAL_TIME, PAT_ENC_HSP_past.ADT_ARRIVAL_TIME, PAT_ENC_HSP_past.ED_EPISODE_ID, PAT_ENC_HSP.SSM_TEST_TYPE
, CLARITY_LOC.LOC_ID, IDENTITY_ID.IDENTITY_TYPE_ID, IDENTITY_ID.IDENTITY_ID, PATIENT.BIRTH_DATE, PAT_ENC_HSP.BILL_NUM
, PAT_ENC_HSP_past.BILL_NUM, SSM_DIAGNOSIS.PRIMARY_DX, SSM_DIAGNOSIS.COMBINED_DX, SSM_DIAGNOSIS_past.PRIMARY_DX
, SSM_DIAGNOSIS_past.COMBINED_DX, ZC_ED_DISPOSITION_past.NAME, ZC_ED_DISPOSITION.NAME, CLARITY_LOC_past.LOC_ID

FROM Clarity.dbo.PAT_ENC_HSP PAT_ENC_HSP
LEFT OUTER JOIN Clarity.dbo.PAT_ENC_HSP PAT_ENC_HSP_past ON PAT_ENC_HSP.PAT_ID=PAT_ENC_HSP_past.PAT_ID
LEFT OUTER JOIN Clarity.dbo.PATIENT PATIENT ON PAT_ENC_HSP.PAT_ID=PATIENT.PAT_ID
LEFT OUTER JOIN Clarity.dbo.CLARITY_ADT CLARITY_ADT ON (PAT_ENC_HSP.PAT_ID=CLARITY_ADT.PAT_ID) AND (PAT_ENC_HSP.PAT_ENC_DATE_REAL=CLARITY_ADT.PAT_ENC_DATE_REAL)
LEFT OUTER JOIN Clarity.dbo.SSM_DIAGNOSIS SSM_DIAGNOSIS ON PAT_ENC_HSP.PAT_ENC_CSN_ID=SSM_DIAGNOSIS.PAT_ENC_CSN_ID
LEFT OUTER JOIN Clarity.dbo.ZC_ED_DISPOSITION ZC_ED_DISPOSITION ON PAT_ENC_HSP.ED_DISPOSITION_C=ZC_ED_DISPOSITION.ED_DISPOSITION_C
LEFT OUTER JOIN Clarity.dbo.CLARITY_ADT CLARITY_ADT_past ON (PAT_ENC_HSP_past.PAT_ID=CLARITY_ADT_past.PAT_ID) AND (PAT_ENC_HSP_past.PAT_ENC_DATE_REAL=CLARITY_ADT_past.PAT_ENC_DATE_REAL)
LEFT OUTER JOIN Clarity.dbo.SSM_DIAGNOSIS SSM_DIAGNOSIS_past ON PAT_ENC_HSP_past.PAT_ENC_CSN_ID=SSM_DIAGNOSIS_past.PAT_ENC_CSN_ID
LEFT OUTER JOIN Clarity.dbo.ZC_ED_DISPOSITION ZC_ED_DISPOSITION_past ON PAT_ENC_HSP_past.ED_DISPOSITION_C=ZC_ED_DISPOSITION_past.ED_DISPOSITION_C
LEFT OUTER JOIN Clarity.dbo.CLARITY_DEP CLARITY_DEP_past ON CLARITY_ADT_past.DEPARTMENT_ID=CLARITY_DEP_past.DEPARTMENT_ID
LEFT OUTER JOIN Clarity.dbo.CLARITY_LOC CLARITY_LOC_past ON CLARITY_DEP_past.REV_LOC_ID=CLARITY_LOC_past.LOC_ID
LEFT OUTER JOIN Clarity.dbo.CLARITY_DEP CLARITY_DEP ON CLARITY_ADT.DEPARTMENT_ID=CLARITY_DEP.DEPARTMENT_ID
LEFT OUTER JOIN Clarity.dbo.CLARITY_LOC CLARITY_LOC ON CLARITY_DEP.REV_LOC_ID=CLARITY_LOC.LOC_ID
LEFT OUTER JOIN Clarity.dbo.IDENTITY_ID IDENTITY_ID ON PATIENT.PAT_ID=IDENTITY_ID.PAT_ID

WHERE PAT_ENC_HSP.SSM_TEST_TYPE IS NULL
AND PAT_ENC_HSP_past.ED_EPISODE_ID IS NOT NULL
AND (PAT_ENC_HSP_past.ADT_ARRIVAL_TIME>='2010-08-01 00:00:00' AND PAT_ENC_HSP_past.ADT_ARRIVAL_TIME<'2010-09-01 00:00:00')
AND (CLARITY_ADT_past.EVENT_ID IS NULL OR CLARITY_ADT_past.EVENT_TYPE_C=1 AND CLARITY_ADT_past.EVENT_SUBTYPE_C<>2)
AND (CLARITY_ADT.EVENT_ID IS NULL OR CLARITY_ADT.EVENT_TYPE_C=1 AND CLARITY_ADT.EVENT_SUBTYPE_C<>2)
AND (PAT_ENC_HSP_past.ADMIT_CONF_STAT_C IS NULL OR NOT (PAT_ENC_HSP_past.ADMIT_CONF_STAT_C='2' OR PAT_ENC_HSP_past.ADMIT_CONF_STAT_C='3'))
AND (PAT_ENC_HSP.ADMIT_CONF_STAT_C IS NULL OR NOT (PAT_ENC_HSP.ADMIT_CONF_STAT_C='2' OR PAT_ENC_HSP.ADMIT_CONF_STAT_C='3'))
AND PAT_ENC_HSP.ED_EPISODE_ID IS NOT NULL
AND PAT_ENC_HSP.ED_EPISODE_ID>PAT_ENC_HSP_past.ED_EPISODE_ID
AND CLARITY_LOC_past.LOC_ID=130992023
AND CLARITY_LOC.LOC_ID=130992023
AND IDENTITY_ID.IDENTITY_TYPE_ID=7


Would definitely be looking at throwing it into a stored procedure and let SQL Server manage it on the server side rather than trying to resolve all that on the client end.

0
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 250 total points
Comment Utility
@ acperkins

"Conversely doing this:
LEFT OUTER JOIN Clarity.dbo.PAT_ENC_HSP PAT_ENC_HSP_past
And then doing this:
  AND PAT_ENC_HSP_past.ED_EPISODE_ID IS NOT NULL
Is potentially contradictory"

Potentially maybe and in other situation, not here though in this case because the LEFT JOIN is done to the same table, PAT_ENC_HSP, on PAT_ID column:

FROM
Clarity.dbo.PAT_ENC_HSP PAT_ENC_HSP
LEFT OUTER JOIN Clarity.dbo.PAT_ENC_HSP PAT_ENC_HSP_past
      ON PAT_ENC_HSP.PAT_ID=PAT_ENC_HSP_past.PAT_ID
...
WHERE
...
      AND PAT_ENC_HSP_past.ED_EPISODE_ID IS NOT NULL

so practically is eliminating the rows where ED_EPISODE_ID is null. I agree that it doesn't really make sense to LEFT JOIN a table to itself, INNER should be used. Also it is correct the using LEFT with WHERE IS NOT NULL is equivalent to a INNER JOIN but only if the filter is on the joining column.

In this case however as I said I think the _past JOIN to itself is not necessary.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Think the idea for PAT_ENC_HSP_past is to get the previous EPISODE within a specific date range (seems to be prior month), and getting that, he then needs the associated data for that previous ed_episode_id hence the other _past table aliases as well.

But it could be an inner join because of that where clause, and maybe that where clause needs to still exist because possibly ed_episode_id is not mandatory.

So, can see why the _past might be needed, and why the is not null is also needed, and can even see why the left joins are being used for everything other than PAT_ENC_HSP_past where it looks like it could be an inner join (and would even move the check for episode_id being less than up there).

But this is being constructed in Access and there are a few gotchas when doing complex joins...

0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Let me put it this way as I obviously was not clear the first time, if the author understands the concept of a LEFT JOIN and really needs it than the conditions in the WHERE clause are simply not going to produce the right result.  Period.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
And an inner join might not negate the need to check for NULLS either :)
Not sure it was considered that way.
Besides which I do agree that PAT_ENC_HSP_past could be an inner join...

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>And an inner join might not negate the need to check for NULLS either<<
Absolutely.

If the LEFT JOIN is really needed than the following conditions should not be in the WHERE clause but rather on the OUTER JOIN:
CLARITY_LOC_past.LOC_ID = 130992023
CLARITY_LOC.LOC_ID = 130992023
IDENTITY_ID.IDENTITY_TYPE_ID = 7
0
 

Author Comment

by:Nakuru1234
Comment Utility
Hi Mark Wills:

Why are you saying this is being constructed in Access? TIA.

N.
0
 

Author Comment

by:Nakuru1234
Comment Utility
Hi Zberteoc:

I totally agree, the _past JOIN to itself is not necessary. TIA.

N.

0
 

Author Comment

by:Nakuru1234
Comment Utility
mark_wills:

You said, was hard to read, so, made it suitable for the above task, and still unsure about all those double ups (yes, can see some distinctions in the where clause and will take some more work to figure it all out)
// Have you figured out anything? TIA.

N.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Would simply run it up in a query window in SSMS, and then (under the "query" menu option) analyse the query in database engine query analyser.

See what it says about indexing... And probably should ask, are you in a position to make changes on the back end SQL server ?

Would also be making a Stored Procedure out of the query (if possible) and passing a few parameters to the stored procedure.

If we can do that, then we can reconstruct some of those joins into a more SQL Server profficient way. Access will complain about a couple of the techniques we have been discussing.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
The main problem here is the query itself and the indexes. What needs to be done is to make sure the indexes are how are supposed to be and then maybe rewriting the code a little bit to optimize it. Last concern here is encapsulate the query in a stored procedure. That easily can be done after the main problem is solved.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Ah well, I guess we will never know whether the author intended to use a LEFT JOIN or not.  Perhaps they do not know the difference, because as written they are certainly not OUTER JOINs.

In any case, it looks like they are in capable hands so I will bow out.

Good luck.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Dont bow out just yet - the fun is yet to happen :)
0
 

Author Comment

by:Nakuru1234
Comment Utility
I am going through all the experts comments/advice as we speak before I close the question...just in case I might have one last question to ask. TIA.

N.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Great, will be good to hear some feedback.

And just noticed a question you asked me...

>> Why are you saying this is being constructed in Access

An assumption because of all the brackets that access insists on doing, and the word / line wraps, and thought I saw it mentioned somewhere.
0
 

Author Comment

by:Nakuru1234
Comment Utility
To all Experts:

You were very helpful in all your comments/advice. I really appreciate even though it took some time for me to close the question. Again, kudos to everybody who contributed. TIA.

N.
0
 

Author Closing Comment

by:Nakuru1234
Comment Utility
All the Experts did an excellent job to help me understand the problem and the solution!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now