0

1st Query: result 32 seconds (1,758,220 rows)

 SELECT
 CAST(ID.PYYY AS varchar(8))+','+
 CAST(ID.SYYY AS varchar(8))+','+
 (
  SELECT TOP 1 (
    CAST(IMM.INVOICE_DATE1 AS varchar(8))+','+
    CAST(IDD.QUANTITY AS varchar(20))    +','+
    CAST(IDD.PRICE AS varchar(20))       +','+
    CAST(IDD.DISCOUNT AS varchar(20))    +','+
    CAST(IDD.SWITCH AS varchar(20))      +','+IDD.UNIT
  ) 
  FROM INVOICE_DETAIL IDD,INVOICE_MASTER IMM 
  WHERE 
   IMM.MASTERNO=IDD.MASTERNO AND 
   IDD.SYYY=ID.SYYY AND 
   IDD.PYYY=ID.PYYY 
  ORDER BY 
   IMM.MASTERNO DESC
 ) AS myData
 FROM INVOICE_DETAIL ID, INVOICE_MASTER IM 
 WHERE IM.MASTERNO = ID.MASTERNO 
 GROUP BY ID.PYYY,ID.SYYY

2nd Query : result 39 seconds (1,758,220 rows)

 SELECT
  ID.PYYY,ID.SYYY, SQ.INVOICE_DATE1, SQ.QUANTITY, SQ.PRICE, 
  SQ.DISCOUNT, SQ.SWITCH, SQ.UNIT
 FROM 
  INVOICE_MASTER IM ,INVOICE_DETAIL ID
 OUTER APPLY 
  (
   SELECT top(1)    
    IMM.INVOICE_DATE1, IDD.QUANTITY, IDD.PRICE, 
    IDD.DISCOUNT, IDD.SWITCH, IDD.UNIT 
   FROM 
    INVOICE_DETAIL IDD,INVOICE_MASTER IMM  
   WHERE 
    IMM.MASTERNO=IDD.MASTERNO AND 
    IDD.SYYY=ID.SYYY AND 
    IDD.PYYY=ID.PYYY 
   ORDER BY 
    IMM.MASTERNO DESC
  ) as SQ
  WHERE IM.MASTERNO = ID.MASTERNO 
  GROUP BY 
    ID.PYYY, ID.SYYY, SQ.INVOICE_DATE1, SQ.QUANTITY, SQ.PRICE, 
    SQ.DISCOUNT,SQ.SWITCH,SQ.UNIT
Improve this question
3
  • 1
    Have you looked at the execution plan? Please consider reading this advice
    – mustaccio
    Commented May 29, 2023 at 23:59
  • 1
    At a guess: because you are using GROUP BY (unnecessarily it seems) so it's grouping by the values in the APPLY also. You can simulate your first query by putting the main part of the query into a derived table or CTE when grouping, then APPLY afterwards. But instead of all this, you should probably just use some kind of aggregation or window functio. Sample data, expected results, your existing table and index definitions, as well as the query plan, would be immensely useful. The question is not really answerable otherwise
    – Charlieface
    Commented May 30, 2023 at 0:05
  • Add your execution plan to brentozar.com/pastetheplan and one of the smart SQL folks will tell you pretty quickly! I would also advise running with SET STATISTICS IO, TIME ON and then pasting the output into www.statisticsparser.com. This will help highlight areas where the second query is doing more work.
    – FrugalShaun
    Commented May 30, 2023 at 19:53

1 Answer 1

Reset to default
0

I tried the way below and it worked. Thanks for all your comments

SELECT MQ.PYYY,MQ.SYYY,
 
 SQ.INVOICE_DATE1, 
 SQ.QUANTITY,
 SQ.PRICE,
 SQ.DISCOUNT,
 SQ.SWITCH,
 SQ.UNIT

FROM
( SELECT
 ID.PYYY,
 ID.SYYY
 FROM
 
 INVOICE_MASTER IM ,INVOICE_DETAIL ID WHERE IM.MASTERNO = ID.MASTERNO AND 
 IM.ITYPE = 1 AND IM.INVOICE_DATE1 >=20220101 group by ID.PYYY,ID.SYYY) AS MQ
 
 

OUTER APPLY 
(
   SELECT top(1) IMM.INVOICE_DATE1,IDD.QUANTITY,IDD.PRICE,IDD.DISCOUNT,IDD.SWITCH,IDD.UNIT FROM INVOICE_DETAIL IDD,INVOICE_MASTER IMM  WHERE IMM.MASTERNO=IDD.MASTERNO AND IDD.SYYY=MQ.SYYY AND IDD.PYYY=MQ.PYYY ORDER BY IMM.MASTERNO DESC
) as SQ
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.