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

[thierry accart (JIRA)]

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



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