on 03-24-2017 11:33 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Pankaj
Try something like below...
select count(*) as rows from (
your query )
Regards
Prithviraj.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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].
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.