excessive SELECTs when executing finder method
ll Jun 7, 2003 5:20 PMHello,
The setup consists of Jboss 3.0.6, MS SQL 2k (SP3). Created entity EJB which corresponds with following table:
CREATE TABLE [dbo].[AdHocListEntry] (
[DeliveryId] [numeric](22, 0) NOT NULL ,
[ListEntryId] [numeric](22, 0) NOT NULL ,
[EmailAddress] [varchar] (64) NULL ,
[FaxCountryCode] [varchar] (8) NULL ,
[FaxAreaCode] [varchar] (8) NULL ,
[FaxNumber] [varchar] (16) NULL ,
[AltFaxCountryCode] [varchar] (8) NULL ,
[AltFaxAreaCode] [varchar] (8) NULL ,
[AltFaxNumber] [varchar] (16) NULL ,
[PhoneNumber] [varchar] (32) NULL ,
[CompanyName] [varchar] (64) NULL ,
[DepartmentName] [varchar] (32) NULL ,
[FirstName] [varchar] (32) NULL ,
[LastName] [varchar] (32) NULL ,
[BillingCode] [varchar] (30) NULL ,
[Security] [varchar] (50) NULL ,
CONSTRAINT [PK_AdHocListEntry] PRIMARY KEY CLUSTERED
(
[DeliveryId],
[ListEntryId]
) ON [PRIMARY] ,
CONSTRAINT [FK_AddHocList_Delivery] FOREIGN KEY
(
[DeliveryId]
) REFERENCES [dbo].[Delivery] (
[DeliveryId]
) ON DELETE CASCADE NOT FOR REPLICATION
)
If findByPrimaryKey() is called the is almost no delay in search execution. But if other finder method, for example:
<query-method>
<method-name>findByDeliveryId</method-name>
<method-params>
<method-param>java.lang.Long</method-param>
</method-params>
</query-method>
<jboss-ql>
SELECT OBJECT(e)
FROM AdHocListEntryBean e
WHERE e.deliveryId = ?1
</jboss-ql>
<read-ahead>
on-find
<page-size>4</page-size>
<eager-load-group>*</eager-load-group>
</read-ahead>
the performance drops dramatically. After turning the DEBUG on the jboss server, the multiple SELECTs are appearing: one per every retrieved record. For example if there are 3 records meeting search criteria, the SELECT sequence looks like:
14:09:26,086 DEBUG [findByDeliveryId] Executing SQL: SELECT t0_e.listEntryId, t0_e.deliveryId, t0_e.emailAddress, t0_e.faxCountryCode, t0_e.faxAreaCode, t0_e.faxNumber, t0_e.altFaxCountryCode, t0_e.altFaxAreaCode, t0_e.altFaxNumber, t0_e.ph
oneNumber, t0_e.companyName, t0_e.departmentName, t0_e.firstName, t0_e.lastName, t0_e.billingCode, t0_e.security FROM db
o.AdHocListEntry t0_e WHERE t0_e.deliveryId = ?
14:09:26,597 DEBUG [AdHocListEntryBean] Executing SQL: SELECT listEntryId, deliveryId,emailAddress, faxCountryCode, faxAreaCode, faxNumber, altFaxCountryCode, altFaxAreaCode, altFaxNumber, phoneNumber, companyName, departmentName, firstName
, lastName, billingCode, security FROM dbo.AdHocListEntry WHERE (listEntryId=? AND deliveryId=?) OR (listEntryId=? AND deliveryId=?) OR (listEntryId=? AND deliveryId=?)
14:09:27,258 DEBUG [AdHocListEntryBean] Executing SQL: SELECT listEntryId, deliveryId,emailAddress, faxCountryCode, faxAreaCode, faxNumber, altFaxCountryCode, altFaxAreaCode, altFaxNumber, phoneNumber, companyName, departmentName, firstName
, lastName, billingCode, security FROM dbo.AdHocListEntry WHERE (listEntryId=? AND deliveryId=?) OR (listEntryId=? AND deliveryId=?) OR (listEntryId=? AND deliveryId=?)
14:09:27,899 DEBUG [AdHocListEntryBean] Executing SQL: SELECT listEntryId, deliveryId,emailAddress, faxCountryCode, faxAreaCode, faxNumber, altFaxCountryCode, altFaxAreaCode, altFaxNumber, phoneNumber, companyName, departmentName, firstName
, lastName, billingCode, security FROM dbo.AdHocListEntry WHERE (listEntryId=? AND deliveryId=?) OR (listEntryId=? AND deliveryId=?)
14:09:28,490 DEBUG [AdHocListEntryBean] Executing SQL: SELECT listEntryId, deliveryId,emailAddress, faxCountryCode, faxAreaCode, faxNumber, altFaxCountryCode, altFaxAreaCode, altFaxNumber, phoneNumber, companyName, departmentName, firstName
, lastName, billingCode, security FROM dbo.AdHocListEntry WHERE (listEntryId=? AND deliveryId=?) OR (listEntryId=? AND deliveryId=?)
14:09:29,081 DEBUG [AdHocListEntryBean] Executing SQL: SELECT emailAddress, faxCountryCode, faxAreaCode, faxNumber, altFaxCountryCode, altFaxAreaCode, altFaxNumber, phoneNumber, companyName, departmentName, firstName, lastName, billingCode,
security FROM dbo.AdHocListEntry WHERE (listEntryId=? AND deliveryId=?)
14:09:29,151 DEBUG [AdHocListEntryBean] Executing SQL: SELECT emailAddress, faxCountryCode, faxAreaCode, faxNumber, altFaxCountryCode, altFaxAreaCode, altFaxNumber, phoneNumber, companyName, departmentName, firstName, lastName, billingCode,
security FROM dbo.AdHocListEntry WHERE (listEntryId=? AND deliveryId=?)
Repeating the search issues the same excessive SELECTs against the database.
Any advice would be appreciated on how to get rid of extra database calls or point to right documentation on this subject.
Regards
ll