SQL Console (ADT) fires additional query which takes a long time to execute

[NemanjaSimovic]

Hi,

I'm executing in SQL Console following script:

select ds~vbeln, dh~kunnr, dh~kunag, p~parnr
from likpuk as ds
join likp as dh on dh~vbeln = ds~vbeln
left join vbpa as p on p~vbeln = ds~vbeln and p~posnr = '000000' and p~parvw = 'RE'
where dh~erdat  >= '20240101'
  and dh~kunnr > '0000000000'
  and dh~kunag > '0000000000'

Looks like very simple, isn't it. 😉

But the SQL console don't execute just mine query (noticed in ST05). No, it wants first to count the rows! 😲Why?

And that kind of inquiry costs 35 seconds every time, as you can see below:

Is there a way to avoid this SELECT COUNT(*)?

ADT team please respond if possible? Placing a ticket to you is still a mystery to me. 🔮

Regards,
Nemanja

[catano]

I could imagine that the query itself also tooks a huge amount of time. Have you think about adding indexes to your tables?

[Ryan-Crosby]

One thing I notice is that the core table LIKPUK has no designated filter, and LIKP has a >= for ERDAT which could cause certain databases to do a full scan and lead to performance impairment. I suspect that if you picked a specific date (with some result count), you wouldn't observe that performance issue.

[NemanjaSimovic]

Hi there. I don't think indexes or query code are the problem. Problem is number of queries fired toward DB. I expect one and ADT fires two! 🤯 Mine, and ADT's "SELECT COUNT(*)". ADT's "SELECT COUNT(*)" is fired first and it eats all the time. That should be placed on the button insted of automatically fired to prepare count info.

[NemanjaSimovic · Accepted solution]

Anyhow, this issue is solved. Thanks ADT Team! 👍 Going to close the topic.

[Tomas_Buryanek]

There is a number of "rows retrieved" in SQL console, so I bet it must be done by this separate SELECT COUNT.

Not sure why ADT just not counts rows of the "original" SELECT?

[NemanjaSimovic]

I guess it's because it sends "up to 100 rows", and I'm not sure will DB (Oracle in my case) be polite and return total number of queried rows. 😀 But when I click on "Number of Entries" button it shows exact number or rows. Maybe they could put SELECT COUNT(*) upon that button is pressed? It's a question for design. 😉