March 27, 2017 at 7:53 am
Have two queries trying to convince vendor to get out of using in but my exists one takes forever what am I missing?
Thanks
select AdminProduct_ID, RowVersionNumber, TherapyAdmin_ID, PackagedProduct_ID, AdminRecordMethod , ActionCode, DoseAmount, DoseUnits,
DispensableProduct_ID, DispenseSize, RoutedProduct_ID, DoseText , LotNumber, ExpirationDate, ManufacturerCode
from hcs.AdminProduct where TherapyAdmin_ID in (select TherapyAdmin_ID from hcs.PatientOrder,hcs.TherapyAdmin
where hcs.PatientOrder.Order_ID=hcs.TherapyAdmin.Order_ID and hcs.PatientOrder.PatientVisit_ID=2214106602 ) order by AdminProduct_ID
select AdminProduct_ID, RowVersionNumber, TherapyAdmin_ID, PackagedProduct_ID, AdminRecordMethod , ActionCode, DoseAmount, DoseUnits,
DispensableProduct_ID, DispenseSize, RoutedProduct_ID, DoseText , LotNumber, ExpirationDate, ManufacturerCode
from hcs.AdminProduct where exists (select 1 from hcs.PatientOrder inner join hcs.TherapyAdmin on hcs.PatientOrder.Order_ID=hcs.TherapyAdmin.Order_ID
inner join hcs.AdminProduct h on h.TherapyAdmin_ID = hcs.TherapyAdmin.TherapyAdmin_ID
where hcs.PatientOrder.PatientVisit_ID=2214106602
) order by AdminProduct_ID
March 27, 2017 at 8:04 am
To make it easier for others, here are formatted versions:
SELECT
AdminProduct_ID
, RowVersionNumber
, TherapyAdmin_ID
, PackagedProduct_ID
, AdminRecordMethod
, ActionCode
, DoseAmount
, DoseUnits
, DispensableProduct_ID
, DispenseSize
, RoutedProduct_ID
, DoseText
, LotNumber
, ExpirationDate
, ManufacturerCode
FROM hcs.AdminProduct
WHERE
TherapyAdmin_ID IN
(
SELECT TherapyAdmin_ID
FROM
hcs.PatientOrder
, hcs.TherapyAdmin
WHERE
hcs.PatientOrder.Order_ID = hcs.TherapyAdmin.Order_ID
AND hcs.PatientOrder.PatientVisit_ID = 2214106602
)
ORDER BY AdminProduct_ID;SELECT
AdminProduct_ID
, RowVersionNumber
, TherapyAdmin_ID
, PackagedProduct_ID
, AdminRecordMethod
, ActionCode
, DoseAmount
, DoseUnits
, DispensableProduct_ID
, DispenseSize
, RoutedProduct_ID
, DoseText
, LotNumber
, ExpirationDate
, ManufacturerCode
FROM hcs.AdminProduct
WHERE
EXISTS
(
SELECT 1
FROM
hcs.PatientOrder
INNER JOIN hcs.TherapyAdmin ON hcs.PatientOrder.Order_ID = hcs.TherapyAdmin.Order_ID
INNER JOIN hcs.AdminProduct h ON h.TherapyAdmin_ID = hcs.TherapyAdmin.TherapyAdmin_ID
WHERE hcs.PatientOrder.PatientVisit_ID = 2214106602
)
ORDER BY AdminProduct_ID;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 27, 2017 at 8:07 am
The first thing you are missing is formatting so we can read this.
SELECT AdminProduct_ID
,RowVersionNumber
,TherapyAdmin_ID
,PackagedProduct_ID
,AdminRecordMethod
,ActionCode
,DoseAmount
,DoseUnits
,DispensableProduct_ID
,DispenseSize
,RoutedProduct_ID
,DoseText
,LotNumber
,ExpirationDate
,ManufacturerCode
FROM hcs.AdminProduct
WHERE TherapyAdmin_ID IN (
SELECT TherapyAdmin_ID
FROM hcs.PatientOrder
,hcs.TherapyAdmin
WHERE hcs.PatientOrder.Order_ID = hcs.TherapyAdmin.Order_ID
AND hcs.PatientOrder.PatientVisit_ID = 2214106602
)
ORDER BY AdminProduct_ID
SELECT AdminProduct_ID
,RowVersionNumber
,TherapyAdmin_ID
,PackagedProduct_ID
,AdminRecordMethod
,ActionCode
,DoseAmount
,DoseUnits
,DispensableProduct_ID
,DispenseSize
,RoutedProduct_ID
,DoseText
,LotNumber
,ExpirationDate
,ManufacturerCode
FROM hcs.AdminProduct
WHERE EXISTS (
SELECT 1
FROM hcs.PatientOrder
INNER JOIN hcs.TherapyAdmin ON hcs.PatientOrder.Order_ID = hcs.TherapyAdmin.Order_ID
INNER JOIN hcs.AdminProduct h ON h.TherapyAdmin_ID = hcs.TherapyAdmin.TherapyAdmin_ID
WHERE hcs.PatientOrder.PatientVisit_ID = 2214106602
)
ORDER BY AdminProduct_ID
Those two queries do not appear to be equivalent. But without some details about the table structures and indexes it is difficult to tell why the performance is so different. But I would suggest in the first query to use ANSI-92 style joins, they have been around for 25 years now.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2017 at 8:09 am
I get the bad code on first attempt it is from vendor part of living with 3rd party applications
March 27, 2017 at 8:14 am
I might being silly here, but what does you're sub query have to do with your outer query? As far as I can tell, every row in the AdminProduct table will be returned if the Visit 2214106602 has data. Is that correct?
BTW, don't be scared to use ALIAS' on every table. it things a little less bulky 🙂SELECT
AdminProduct_ID
, RowVersionNumber
, TherapyAdmin_ID
, PackagedProduct_ID
, AdminRecordMethod
, ActionCode
, DoseAmount
, DoseUnits
, DispensableProduct_ID
, DispenseSize
, RoutedProduct_ID
, DoseText
, LotNumber
, ExpirationDate
, ManufacturerCode
FROM hcs.AdminProduct AP
WHERE
EXISTS(SELECT 1
FROM hcs.PatientOrder PO
INNER JOIN hcs.TherapyAdmin TA ON PO.Order_ID = TA.Order_ID
INNER JOIN hcs.AdminProduct AP2 ON AP2.TherapyAdmin_ID = TA.TherapyAdmin_ID
WHERE PO.PatientVisit_ID = 2214106602)
ORDER BY AdminProduct_ID;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 27, 2017 at 8:19 am
If you want an EXISTS equivalent to the IN try:
SELECT adminproduct_id,rowversionnumber,therapyadmin_id,packagedproduct_id,adminrecordmethod
,actioncode,doseamount,doseunits,dispensableproduct_id,dispensesize,routedproduct_id
,dosetext,lotnumber, expirationdate,manufacturercode
FROM hcs.adminproduct P
WHERE EXISTS
(
SELECT 1
FROM hcs.therapyadmin T
WHERE T.therapyadmin_id = P.therapyadmin_id
AND EXISTS
(
SELECT 1
FROM hcs.patientorder O
WHERE O.order_id = T.order_id
AND O.patientvisit_id = 2214106602
)
);
March 27, 2017 at 8:22 am
first query returns 11 rows thousands in the second. As I stated 3rd party app have to deal with their SQL, they also have schema names in all their sql which would be fine like to get them to use better TSQL
March 27, 2017 at 8:31 am
Thanks that works my brain freeze too long looking at something. Problem is did not help, the start of this is underlying issue with sql wanting to do a key lookup even though I have a covering index. Driving me nuts (these tables all have over 70 million rows) I was hoping different syntax would change plan but it won't. Even migrated over weekend to new 2016 server from 2012 same results.
March 27, 2017 at 8:37 am
Are you saying that the vendor's code uses the first query, but you want to persuade them to use something like the second query?
When you say the second one takes forever, what does that mean? Does it complete? Does it return the same results? You have added an additional join (to AdminProduct), so I'm not surprised it takes longer. What is the purpose of that additional join?
John
March 27, 2017 at 8:44 am
no the last iteration of 2nd query posting runs fine, almost as exactly fast as one vendor currently uses. Issue is with trying to get the engine to use my covering index and get away from key lookup. Both versions do this better syntax did not change it
March 27, 2017 at 8:46 am
tcronin 95651 - Monday, March 27, 2017 8:44 AMno the last iteration of 2nd query posting runs fine, almost as exactly fast as one vendor currently uses. Issue is with trying to get the engine to use my covering index and get away from key lookup. Both versions do this better syntax did not change it
OK I am now totally confused. Both queries work fine or they both don't or one of them does and the other doesn't? I am really fuzzy on what your question is at this point.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2017 at 8:49 am
tcronin 95651 - Monday, March 27, 2017 8:31 AMThanks that works my brain freeze too long looking at something. Problem is did not help, the start of this is underlying issue with sql wanting to do a key lookup even though I have a covering index. Driving me nuts (these tables all have over 70 million rows) I was hoping different syntax would change plan but it won't. Even migrated over weekend to new 2016 server from 2012 same results.
You will probably have to look in detail at the plan.
Have you checked your STATs?
Have you tried a patientvisit_id, order_id NC index on hcs.patientorder?
etc
NB Also test in a non-production environment first.
March 27, 2017 at 9:09 am
thanks tried and it still wants to do the key lookup, driving me nuts, this looks like a layup but the engine wants to do this no matter which strategy I try. However if I run Ozar's blitzindex or even the engine tuning advisor it does not recommend any changes
March 27, 2017 at 9:32 am
This should be the equivalent to the original query using EXISTS instead of IN.SELECT
ap.AdminProduct_ID
,ap.RowVersionNumber
,ap.TherapyAdmin_ID
,ap.PackagedProduct_ID
,ap.AdminRecordMethod
,ap.ActionCode
,ap.DoseAmount
,ap.DoseUnits
,ap.DispensableProduct_ID
,ap.DispenseSize
,ap.RoutedProduct_ID
,ap.DoseText
,ap.LotNumber
,ap.ExpirationDate
,ap.ManufacturerCode
FROM hcs.AdminProduct ap
WHERE EXISTS(
SELECT 1
FROM hcs.PatientOrder po
JOIN hcs.TherapyAdmin ta ON po.Order_ID = ta.Order_ID
WHERE po.PatientVisit_ID = 2214106602
AND ta.TherapyAdmin_ID = ap.TherapyAdmin_ID
)
ORDER BY AdminProduct_ID;
There's no need to include the AdminProduct table in the subquery, that's why it's a correlated subquery.
Most likely, there shouldn't be any difference in performance as shown by Gail Shaw in her blog, but I would suggest to always use table alias, fully qualified names and SQL-92 joins.
March 27, 2017 at 10:03 pm
Duplicate of previous answer - apologies.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply