[jbossseam-issues] [JBoss JIRA] Created: (JBSEAM-1980) Using primary key in select count()
thierry accart (JIRA)
jira-events at lists.jboss.org
Thu Sep 27 05:10:40 EDT 2007
Using primary key in select count()
Project: JBoss Seam
Issue Type: Feature Request
Affects Versions: 1.2.1.GA
Environment: JBoss Seam with MSSQL database connections
Reporter: thierry accart
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.
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
More information about the seam-issues