[QPROF] Better option to manage few queries on same key_id

[yakirgb]

Hi,
I'd like to compare two or more queries using the QueryProfilerInterface.
For one query, I simply execute:

from verticapy.performance.vertica import QueryProfilerInterface
qprof_interface = QueryProfilerInterface('/Users/yakir.g/slow_query.sql',
    target_schema='tests',
    key_id='slow_queries',
    overwrite=True
    )

However, it would be more convenient to use the same key_id for multiple queries. This approach allows for easier investigation and provides the option to check the source tables or load the queries with a single command:

qprof_interface = QueryProfilerInterface(
    key_id='slow_queries',
    target_schema='tests'
)

Currently, this is not feasible due to limitations as indicated by the following warning message:

Warning: Parameter 'transactions' is not None, 'key_id' is defined, and parameter 'overwrite' is set to False. This implies an attempt to use an existing key for new transactions which isn't supported yet. A new key will be automatically generated: 742cae2e83f411ef9b7742cf969cb7fa

I'm inquiring if there's a possibility to allow multiple queries under the same key_id and i'll able to load all queries using one execution.

Or provide an option to manage several keys.
Like:

qprof_interface = QueryProfilerInterface(
    key_id=['slow_query_1', 'slow_query_2],
    target_schema='tests'
)

Thank you,
Yakir

[mail4umar]

There are two ways in which you can do what you want.

Firstly, to compare 2 queries we have now created a new class QueryProfilerComparison using which you can input any two queries and compare them in a side-by-side view. Please look at

VerticaPy/verticapy/performance/vertica/qprof_interface.py

Line 997 in ace4e19

class QueryProfilerComparison:

Secondly, if you want to add more queries to a key_id/schema that you have already stored, you can use the insert command.

For example:

from verticapy.performance.vertica import QueryProfilerInterface
qprof_interface = QueryProfilerInterface('/Users/yakir.g/slow_query.sql',
    target_schema='tests',
    key_id='slow_queries',
    overwrite=True
    )
    
# Now add more to the same
qprof_interface.insert([(transaction_id, statemnet_id),...])

Hope that helps!

Umar

[yakirgb]

Thank you @mail4umar , can you show small example please for QueryProfilerComparison ?
On most cases i want to run and compare two sql files , most queries are huge and more easy to simple call to sql files .

I did:

from verticapy.performance.vertica import QueryProfilerInterface

qprof_interface_1 = QueryProfilerInterface(
    key_id='yakir_1',
    target_schema='tests',
    )

qprof_interface_2 = QueryProfilerInterface(
    key_id='yakir_2',
    target_schema='tests',
    )

And:

from verticapy.performance.vertica import QueryProfilerComparison
compare = QueryProfilerComparison(qprof_interface_1,qprof_interface_2)

But cannot call to:

compare.get_qplan_tree()
AttributeError: 'QueryProfilerComparison' object has no attribute 'get_qplan_tree'

Thank you,
Yakir.

[mail4umar]

Oh we changed the attribute to display for this class. But now I can see that it is inconsistent. Especially without the new documentation, it may not be straight forward.

For now you can use:

compare.display()

But I think we may change it back to get_qplan_tree because i can see that it is more intuitive.

[yakirgb]

For a some reason i see the tree only on the right side with compare.display()
(It's some query from different times)

But qprof_interface_1 (key_id: yakir_1 ) looks valid if i'm calling it using:
qprof_interface_1.get_qplan_tree()

Maybe i'll test tomorrow on other queries to see if i'm getting same issue

Thank you, Yakir.

[mail4umar]

You just need to refresh the tree. It's probably a jupyter notebook think.
Go to the tab "Tree Style" > then click the "Apply" button. You will be able to see it.

[yakirgb]

Working, thank you .

[oualib]

As it seems to work, I'll close this discussion.