All Forums Database
GianTD 47 posts Joined 11/14
19 Nov 2014
Identify the queries

While working on multiple queries for tuning... How i can identify the queries while getting the DBQL details
For ex: I have 20 queries from developer, need to provide recommendations for performance. I need to run thru multiple iterations, making changes, again run, & check for CPU, Spool, rows etc.
Is there any easy ways i can add like Query1, Query2 in querytext or any other way to identify the query while working on them simultaneously.

Raja_KT 1246 posts Joined 07/09
19 Nov 2014

I am not able to understand clearly, what you want to do. Do you use viewpoint? or DBQLs....

Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

ulrich 816 posts Joined 09/09
19 Nov 2014

Check the queryband option - you can set for each query a different queryband parameter.
This will be visiable in DBQL.
Or add a comment to the query text like

-- my query 1
from mytab;

in this case you can see it from the querytext in dbql.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

GianTD 47 posts Joined 11/14
20 Nov 2014

Thanks ulrich !
But these comments are not visible in querytext/SqlTextInfo in DBQL...Thats the problem...
Do you think this could be due to some logging option on my side?
Could you pls let me know the quick way to set queryband for the query?
Raja: I use both VP & DBQL.  What i was looking like ... I have many queries to tune.
Those take long time to run. I'll run those at sametime and i need to see their resouces consumption. And i need to do many iterartions in order to make chages & again see the improvement.
 hope i am not confusing you  :-)

ulrich 816 posts Joined 09/09
20 Nov 2014

SET QUERY_BAND = 'sqltype=perftest;sqlnr=1;runid=1;' UPDATE for SESSION;
and you need to run this before each SQL and change the numbers;
/*my query 1 */
at least I see sometimes SQL in DQBL with comments in it...

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

SmarakDas 51 posts Joined 02/12
24 Nov 2014

Hello Gian,

There is an option which needs to be enabled/disabled to ensure that comments are also getting logged into DBQL as part of SQL Text. The exact option cannot be recalled by me now. I encountered the same scenario as you & used the comments to identify the different iterations.

Check the options (Basic & Advanced) available in the ODBC Setting.




GianTD 47 posts Joined 11/14
24 Nov 2014

Thanks Smarak. Will check that.

GianTD 47 posts Joined 11/14
25 Nov 2014

 couldn't find such option.
Anybody...How we can see the comments made within the query when looking at 'QueryText' in DBQL?

ulrich 816 posts Joined 09/09
26 Nov 2014

As above statements indicate it also depends on how you connect to the DB - but so far you didn't say which tool you use, how you connect (.net, ODBC, JDBC) etc.
Did you try the queryband option?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

GianTD 47 posts Joined 11/14
26 Nov 2014

I use ODBC.
Query band worked fine,
Thanks ! Ulrich. That solved my purpose.
Comment thing i wanted to check just in case...

You must sign in to leave a comment.