How to optimize this T-sql query?

Question

There is a loop in this query, in the last where condition. and this causes a severe problem to the performance of SQL.

I have no idea about how to modify it.

select pr.tavpun
 from mta110 pr
 where pr.taisoc = mta110.taisoc
   and pr.taitar = mta110.taitar
   and pr.taydat = mta110.taydat
   and pr.tairef = mta110.tairef
   and pr.tatind = (select max(pr2.tatind) from mta110 pr2
                    where pr2.taisoc = mta110.taisoc
                      and pr2.taitar = mta110.taitar
                      and pr2.taydat = mta110.taydat
                      and pr2.tairef = mta110.tairef
                      and pr2.tatind <= mgc100.gntind)) AS SalesPrice

Does that even compile with the alias for a subquery as a comparison operand? Anyway, please show the CREATE TABLE and CREATE INDEX statements of the table. Show the execution plan. And add some sample data as INSERT INTO statements and the desired result with that sample data along with an explanation about the logic behind your query. — sticky bit
– sticky bit, Commented Aug 13, 2019 at 10:15
Strange looking query, did you mean to have a selfjoin which you've missed off, if not the where clauses look suspect — Steve Ford
– Steve Ford, Commented Aug 13, 2019 at 10:16
That query can't be valid. mta110 has no context as an alias; so the clause pr.taisoc = mta110.taisoc will error with an error about the unknown object or alias mta110. Also, when would taisoc not have a value of taisoc apart from when it's NULL? If you are checking for NULL then you should just be using taisoc IS NOT NULL. — Thom A
– Thom A ♦, Commented Aug 13, 2019 at 10:16
An explanation of the logic with sample data and desired results would help. Your query is not valid because mta110 is not defined in the outer query. — Gordon Linoff
– Gordon Linoff, Commented Aug 13, 2019 at 10:36

Gordon Linoff · Accepted Answer · 2019-08-13 10:39:43Z

2

Your query makes little sense, because pr is not a reasonable alias for mta110, and mta110 is not recognized in the outer query.

I speculate that you have two tables, pr and mta110 which are joined and you want the "most recent" row of mta110 for each matching row.

If this interpretation is correct, then you can use row_number() and a proper join:

select . . .
from pr join
     (select m.*,
             row_number() over (partition by taisoc, taitar, taydat, tairef order by gntind desc) as seqnum
      from mta110 m
     ) m
     on pr.? = m.?
where seqnum = 1;

answered Aug 13, 2019 at 10:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chen J Over a year ago

Hi Gordon, your assumption is exactly what it is. Thank you so much for the answer. My SQL acknowledge is too poor... Could you please help me check another query ? I couldn't understand the logic..

Collectives™ on Stack Overflow

How to optimize this T-sql query?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related