Sybase ASE: How to get number of estimated rows from a query in sybase

[SybDBA]

Hi All,

Thanks in advance!!

How to get number of estimated rows returned from a query in Sybase ASE ?

(assume that query in being executed)

Thank you!!

Regards

pankaj

[former_member89972]

Cool Mark.

Something to make note of !!

Thanks for the info.

Avinash

[former_member89972]

Pankaj

Very relevant question if your query is taking longer ( minutes to hours ) to process and you do not get the @@rowcount quickly enough.

In case of DML activity (i.e. insert/update/delete ) type of statement you can get the query progress in a column called RowsAffected in MDA table monProcessStatement for that SPID.

Number of result rows (actual and/or estimate) will also depend on underlying tables for joins , qualifying criteria, etc.

(e.g. a query doing table scan(s) but returning no qualifying rows !!)

If table statistics are up-to-date then query plan can get you number estimate of pages and i/o involved but not the estimate on number of result rows.

HTH

Avinash

[SybDBA]

Hi Avinash,

But what if, MDA tables are not active/enabled ? query plan is not handy always.

Can you please elaborate with an example, it will be good for me to understand.

Thanks

regards

pankaj

[Former Member]

Hi Pankaj

Try something like below...

select count(*) as rows from (

your query )

Regards

Prithviraj.

[SybDBA]

Hi Prithvi,

1>
2>
3>
4> select count(*) as rows from (select * from syslogins)
5> go
Msg 156, Level 15, State 2:
Server 'irm1', Line 4:
Incorrect syntax near the keyword 'rows'.
1>
2> select count(*)  from (select * from syslogins)
3> go
Msg 11753, Level 15, State 1:
Server 'irm1', Line 2:
The derived table expression is missing a correlation name. Check derived table syntax in the Reference Manual.
1>
2>
3> select count(*) as "rows" from (select * from syslogins)
4> go
Msg 11753, Level 15, State 1:
Server 'irm1', Line 3:
The derived table expression is missing a correlation name. Check derived table syntax in the Reference Manual.
1>

Please check the above output and comment.

regards,

pankaj

[former_member188958]

The 11753 errors are being raised because the derived table expression "(select * from syslogins)" is not followed by a correlation name, for example "(select * from syslogins) dt1 ".

However, even though this will cause the query to be able to execute, the result isn't an estimate of the number
of rows the query would return, it actually runs the query and returns the actual count.

You can use "set statistics plancost on" and execute the query to see the optimizer's estimate of the number of rows compared with the actual number of rows returned, but again this actually executes the query.

-bret

[SybDBA]

Hi Bret,

Thanks!!

Can you please explain the below , which I got at the end of the output , after executing

1>set statistics plancost on
2>go
1> select * from syslogins
2>go
==================== Lava Operator Tree ====================
            Emit
            (VA = 1)
            r:2906 er:2906
            cpu: 0
 /
TableScan
syslogins
(VA = 0)
r:2906 er:2906
l:571 el:569
p:0 ep:296
============================================================
(2906 rows affected)

And one more thing, if as a DBA if I want to check , suppose the query running from apps team.

regards

pankaj

[former_member188958]

The "r:2906" is the actual number of rows, while "er:2906" is what the optimizer estimated while creating the query plan based on the known statistics for the table's data distribution. Similarly, the "l:" and "el:" lines are the actual and estimated number of logical io, "p:" and "ep:" are actual and estimated numbers of physical io. [this query is too simple to have really interesting differences between the estimates and reality].

[SybDBA]

Bret,

if as a DBA if I want to check , suppose the query running from apps team.

regards

pankaj