4 Replies Latest reply on Jun 9, 2003 5:57 PM by ll

    excessive SELECTs when executing finder method

    ll Newbie

      Hello,
      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

        • 1. Re: excessive SELECTs when executing finder method
          Adrian Brock Master

          Looks like you are accessing the finder results
          in a different transaction.

          Regards,
          Adrian

          • 2. Re: excessive SELECTs when executing finder method
            ll Newbie


            How to make sure it's the same transaction? ( sorry if it sounds naive )

            The <transaction-type> value is "Required" in ejb-jar.xml, and on the client side the finder method is called within transaction.


            Thanks!

            • 3. Re: excessive SELECTs when executing finder method
              Jaime Salvador Newbie

              Your problem is like that:

              Collection col = home.findByDeliveryId( );
              Iterator it = colIterator( );
              while( it.hasNext() )
              {
              MyBean b = (MyBean )PortableRemoteObject.narrow( ... );
              b.getXXX( );
              }

              if this method is executed in a client side, the first transaction is executed when you execute findByDeliveryId, after this method execute, the transaction is closed. When you try to acces the getXX methods for every bean returden by the finder, a new transaction is created (one for each PortableRemoteObject.narrow), i.e. if your finder returns 100 objects, 100 transacations area created/destroyed for every iteration.


              One solution to solve this problem is envolve both (the finder and the iteration) in one transaction using UserTranscation (I don't like this technique).

              Another solution is create some bussines method that return a collection of Value Objects containing each the same information of teh real beans returned by the finder, the business method must be in a transaction. If you use a default transaction mechanism, when the business method start, a new transaction is created and all the operation inside the method are executed inside the same transaction (the finder and the iteration), with this technique you only create one transaction for all the objects.

              • 4. Re: excessive SELECTs when executing finder method
                ll Newbie


                I'd like to thank you very much!

                Works much better with your changes...

                Regards

                ll