Skip to Content
0

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

Mar 24, 2017 at 11:33 AM

597

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

avatar image
Former Member Mar 24, 2017 at 01:17 PM
0

Hi Pankaj

Try something like below...

select count(*) as rows from (

your query )

Regards

Prithviraj.

Show 5 Share
10 |10000 characters needed characters left characters exceeded

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

0

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

0

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

0

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].

0

Bret,

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

regards

pankaj

0
Avinash Kothare Mar 24, 2017 at 07:41 PM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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

0
Mark A Parsons Mar 27, 2017 at 01:49 PM
0

To get an *estimate* of rows without running the actual query:

set switch on 3604
go
set statement_cache off
go
set option show_best_plan long
go
set noexec on
go
>>run your desired query<<
go

In the results look for the I/O estimate > rowcount readings; there will likely be several rowcount readings (should match up closely with the er numbers from the set statistica plancost on output); I suggest you run some test queries and review the resulting output to get a better understanding.

* Keep in mind that this is an *estimated* rowcount and may not reflect the actual number of rows to be returned.

* Keep in mind that this output is sent to the client and as such cannot be captured/stored into T-SQL variables for processing (ie, you'll need to use a client-side process to parse/scan the resulting output ... or just eyeball the results to get your *estimated* rowcount).

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Cool Mark.

Something to make note of !!

Thanks for the info.

Avinash

0