Skip to Content

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

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

    Hi Pankaj

    Try something like below...

    select count(*) as rows from (

    your query )

    Regards

    Prithviraj.

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 24, 2017 at 07:41 PM

    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

    Add comment
    10|10000 characters needed 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

  • Mar 27, 2017 at 01:49 PM

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

    Add comment
    10|10000 characters needed characters exceeded