SQL Anywhere database - Query LifeCycle & ReqType

Author

Query LifeCycle & ReqType

handheldmaster

2005-12-20, 8:24 pm

Using asa902 on w3k

With RememberLastStatemen
t ON and seeing queries via sa_conn_info and
sa_conn_activity the reqtype of the sql comes out to be either
CURSOR_PREFETCH, CURSOR_OPEN, CURSOR_FETCH, ROLLBACK, COMMIT

Was wanting to understand what each of these mean and what is the complete
lifecycle of an SQL command in the ASA Engine.

Can somebody point me to the right document inside ASA-DOCs or web-article
explaining each ReqType state and how-to analyze SQL's for performance.

TIA

Reg Domaratzki $iAnywhere Solutions$

2005-12-21, 11:23 am

What ReqType is used during a query depends heavily on the type of query and
how your front-end application handles the query. My best suggestion would
be to turn on request level logging ("-zr all -zo rll.txt" on engine start
line), and trace some of your common operations to see the life-cycle
yourself. If you have questions about you see, post them here.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

"handheldmaster" <abc@456.com> wrote in message
news:43a8896f$1@foru
ms-1-dub...
>
> Using asa902 on w3k
>
> With RememberLastStatemen
t ON and seeing queries via sa_conn_info and
> sa_conn_activity the reqtype of the sql comes out to be either
> CURSOR_PREFETCH, CURSOR_OPEN, CURSOR_FETCH, ROLLBACK, COMMIT
>
> Was wanting to understand what each of these mean and what is the complete
> lifecycle of an SQL command in the ASA Engine.
>
> Can somebody point me to the right document inside ASA-DOCs or web-article
> explaining each ReqType state and how-to analyze SQL's for performance.
>
> TIA
>
>

Breck Carter [TeamSybase]

2005-12-21, 11:23 am

On 20 Dec 2005 14:45:03 -0800, "handheldmaster" <abc@456.com> wrote:

>Can somebody point me to the right document inside ASA-DOCs or web-article
>explaining each ReqType state and how-to analyze SQL's for performance.

The newest (and IMO best) technique is described in the new SQL
Anywhere Developer's Corner at

http://www.ianywhere.com/developer/... /> jasper.html

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Nick Elson

2005-12-21, 11:23 am

Don't forget the older standards too:

Also see the system functions

sa_get_request_times
() and sa_get_request_profi
le()

They provide a good starting point and have been
around a lot longer than the facility Breck's plug
points to.

If stored procedures are a significant part of your design
see the tech. doc. at:

http://www.ianywhere.com/developer/...uery_plans.html

as well as profiling, debugging, and our index consultant's full
capabilities.

All of which addresses the "how-to analyze SQL's for performance"
part of your question. ReqType itself is of little use in this context.

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:0sviq1lf91dsvq9
n055j4qmduoi59dv22g@
4ax.com...
> On 20 Dec 2005 14:45:03 -0800, "handheldmaster" <abc@456.com> wrote:
>
>
> The newest (and IMO best) technique is described in the new SQL
> Anywhere Developer's Corner at
>
> http://www.ianywhere.com/developer/... /> jasper.html
>
> Breck
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

handheldmaster

2005-12-22, 9:23 am

An example would be why would like to know the different ReqType's and their
meaning is ....

When running this query I found in the "developer corner".
--------------------------------------------------------------------------------------------------------------------------------------------
SELECT
Activity.DBNumber,
info.ReqType,
Activity.number,
Activity.userID,
datediff(millisecond
, Activity.lastReqTime, current timestamp) / 1000.0
as duration,
Activity.lastStatement,
Info.nodeAddr IPAddr,
Info.reqType requestType
FROM sa_conn_activity() Activity
JOIN
sa_conn_info() Info
ON Info.number = Activity.number
WHERE
requestType not in ('COMMIT', 'CURSOR_CLOSE')
and
length (Activity.lastReqTime) > 0
ORDER BY
Activity.DBNumber, Activity.lastReqTime;
--------------------------------------------------------------------------------------------------------------------------------------------
Get following results.

DBNumber,ReqType,num
ber,userID,duration,
lastStatement,IPAddr
,requestType
0,'ROLLBACK',52350,'
myTestUsr',534.55300,'SELECT last_upload_time FROM
ml_user WHERE user_id = ?','192.168.1.35','ROLLBACK'

ml_user is a mobilink table ... Is mobilink session waiting on something ?
for more than 1/2 second to run that query seems like a bad thing.

Sponsored Links