Hi there, any Query Plans tonight?

Discussion

Benjamin De Boe · Jan 9, 2020

Hi,

we're planning some work on our SQL Query Plan functionality for a future release of InterSystems IRIS and are interested to hear how you're using them today, or what'd keep you from using them. Rather than try and fit everything in a rigid survey, I thought a simple thread on our beloved DC might also reveal some use patterns that we support or could do a better job on. Some questions to get you started (but hoping they don't constrain your spontaneous creativity!):

When do you go look for a query plan?
How are you currently accessing query plans? Using $SYSTEM.SQL.ShowPlan() in ObjectScript, through EXPLAIN or SHOW PLAN over SQL or just through the SMP?
Are there any pieces of information that are missing from the raw plan that you'd typically look up through catalog queries or class definitions? Any you're lacking but wouldn't even know how to find?
When you have that cunning plan right in front of you, what do you do with it?
Do you ever look at alternate query plans?

Robert Cemper · Jan 9, 2020

Hi Ben,

I have query plans tonight. but not in SQL ;-)

seriously. 90% from SMP, the rest from SQLshell.
Alternate Query never crossed my path.

3 0

Eduard Lebedyuk · Jan 10, 2020

When do you go look for a query plan?

Usually after monlbl indicating a problem.

How are you currently accessing query plans?

SMP, 100%.

I think the most interesting thing to know is which query slows down the system. If there are two queries:

Takes a minute, runs daily
Takes a second, runs thousands of times per hour

I'm more interested in optimizing the second one. SQL Runtime Statistics helps, but improvements in this area (and more visibility) would be great.

1 0

Dmitry Maslennikov · Jan 10, 2020

Embedded SQL queries, with variables, and INTO. When I would like to see query plan, I have to first clean it and replace with `?`.

Would be good, to be able to get query plans for such cases, a bit more easier.

Robert Cemper · Jan 10, 2020

Actually most queries run on a single server.

With Sharding It might be much more important to see possible effects of distributed queries.
Especially in the starting phase when optimizing distributions is still somehow like tapping in the fog.

Stephen Canzano · Jan 10, 2020

I'm generally looking at the query plans either from the SMP or from the context menu in Studio while writing class queries/embedded sql statements.

One issue that I've seen is that while the query plan is very good, an in many cases better than what other dbms's provide, when a sub-query is part of your query statement it's not exactly clear when it is utilized in the query plan. For example, I have this query plan

I cannot tell with 100% certainty where "subquery" is called.

Neerav Verma · Feb 4, 2020

I am bit late in the thread but still may be worth it to answer

- I use it always from the SQL Browser.

I did had a post about it too on DC a week or two back, asking how relevant those plans and answers I got were that the plans are not super relevant, what matters most is the response time.

Feedback : What I would love to have from these plans is also a suggestion, how you get in db2 / oracle etc telling us what indexes or improvements in query we can make to make the performance better.

alternate plans - yes, i know it exists. only when i feel the query is super slow, i actually go and take a look at it. not otherwise

if the alternate plan button was right there next to show plan, it will be used more. Infact all the sql optimization tools should be next to the query window on sql browser. that's where it belongs.