[jbossseam-issues] [JBoss JIRA] Closed: (JBSEAM-1980) Using primary key in select count()

[Christian Bauer (JIRA)]

     [ http://jira.jboss.com/jira/browse/JBSEAM-1980?page=all ]

Christian Bauer closed JBSEAM-1980.
-----------------------------------

    Resolution: Rejected

I'd certainly say that this is a basic optimization any SQL engine is doing.


> Using primary key in select count() 
> ------------------------------------
>
>                 Key: JBSEAM-1980
>                 URL: http://jira.jboss.com/jira/browse/JBSEAM-1980
>             Project: JBoss Seam
>          Issue Type: Feature Request
>          Components: Tools
>    Affects Versions: 1.2.1.GA
>         Environment: JBoss Seam with MSSQL database connections
>            Reporter: thierry accart
>            Priority: Optional
>   Original Estimate: 1 day
>  Remaining Estimate: 1 day
>
> I see in my JBoss log several lines like 
>      Hibernate: select count(*) as col_0_0_ from ....
> I'm not sure how it's handled in mysql, oracle or other databases, but in mssql, indexes are stored in different datapages.
> When counting rows, if we use select count (primarykey), we scan a much lower number of pages than we currently do with select(*)
> Example : if one row has a 4 bytes long primary key, and one row stores 1000 bytes,
> if the table has 20000 rows, sql will store this table in 2500 datapages (one page stores 8096 bytes).
> a select (*) will scan the 2500 datapages, when a select (primarykey) will scan 1 (one) datapage.
> As you can see, the impact on database server can be important for large tables with a lot of rows.
> Rgds

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.jboss.com/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira