Performance issue.

FernandoColmenarejo · **Posted:** Thu Mar 30, 2006 11:30 am

Hi, I am Fernando:

I have detected a performance issue and i need to kmow if there is any solution.

I am making stress testing with different data base enviroments. All enviroments are is same scenario. My results are as follows:

SQL-SERVER 2000-> de 10 a 100 workers -> 1800 tps.
ORACLE 9i -> de 10 a 100 workers -> only 15 tps.
SYBASE ASE -> de 10 a 100 workers -> only 21 tps.

In each one, i have used their specific dialects (MsSql2000Dialect, Oracle9Dialect and SybaseDialect).

After that i have chaged, only for Sybase the property "Hibermate.Dialect" and then the stress testing has grown to 900 tps, a success!. But naturally, i will not be able to use the same for Oracle because it will not generate properly the mapping (for example it would fail in ORACLE used .SetMaxResults()).

Is there any other way to increase the performance?
Is the dialect really the problem?

Regards.

jt_1000 · **Posted:** Thu Mar 30, 2006 6:27 pm

are you using identity keys? natural keys? sequence?
what is your batch size? -- autocommit on or off?

what about turning on the show_sql flag in the hibernate.properties and checking to see the SQL that is being issued... what is the diff there?

Just some things to consider.

FernandoColmenarejo · **Posted:** Mon Apr 03, 2006 1:39 pm

Hi!

Code:

<!--TO SYBASE ASE ODBC GOOD TPS:-->
    <!--
   <add key="hibernate.connection.provider" value="NHibernate.Connection.DriverConnectionProvider" />
    <add key="hibernate.connection.isolation" value="ReadCommitted" />
    <add  key="hibernate.dialect" value="NHibernate.Dialect.MsSql2000Dialect"  />
    <add      key="hibernate.connection.driver_class"      value="NHibernate.Driver.OdbcDriver"    />
    <add      key="hibernate.connection.connection_string"      value="DRIVER=Sybase ASE ODBC Driver; NA=INBDITX2K01, 6000;  DB=Stress; UID=sa;" />
    -->
    <!--END TO SYBASE ASE ODBC GOOD TPS:-->

    
    
    
    <!--TO SYBASE ASE ODBC BAD TPS:-->
    
    <add key="hibernate.connection.provider" value="NHibernate.Connection.DriverConnectionProvider" />
    <add key="hibernate.connection.isolation" value="ReadCommitted" />
    <add  key="hibernate.dialect" value="NHibernate.Dialect.SybaseDialect"  />
    <add      key="hibernate.connection.driver_class"      value="NHibernate.Driver.OdbcDriver"    />
    <add      key="hibernate.connection.connection_string"      value="DRIVER=Sybase ASE ODBC Driver; NA=INBDITX2K01, 6000;  DB=Stress; UID=sa; " />

    <!--END TO SYBASE ASE ODBC BAD TPS :-->
    
    
    
    
    <!--TO SQL :-->
        <!--
    <add key="hibernate.connection.provider" value="NHibernate.Connection.DriverConnectionProvider" />
    <add key="hibernate.connection.isolation" value="ReadCommitted" />
    <add key="hibernate.dialect" value="NHibernate.Dialect.MsSql2000Dialect"  />
    <add key="hibernate.connection.driver_class"      value="NHibernate.Driver.SqlClientDriver"    />
    <add key="hibernate.connection.connection_string"      value="Data Source=INBDITX2K10;Initial Catalog=STRESS;Persist Security Info=True;User ID=Usrstress;"    />
    -->
    <!-- END TO SQL-->

Only a table:

Code:

/*==============================================================*/
/* Tabla: T1                                                    */
/*==============================================================*/
create table T1
(
   T1_SERIAL               INTEGER      not null,
   T1_INTEGER               INTEGER,
   T1_SHORT_INTEGER         SMALLINT,
   T1_LONG_INTEGER            BIGINT,
   T1_BYTE                  TINYINT,
   T1_NUMBER               NUMERIC(10,2),
   T1_DECIMAL               DECIMAL(10,2),
   T1_FLOAT               FLOAT,
   T1_SHORT_FLOAT            REAL,
   T1_LONG_FLOAT            DOUBLE,
   T1_MONEY               NUMERIC(8,2),
   T1_BOOLEAN               SMALLINT,
   T1_CHARACTERS            CHAR(100),
   T1_VARIABLE_CHARACTERS      VARCHAR(100),
   T1_LONG_VARIABLE_CHARACTERS   VARCHAR,
   T1_LONG_CHARACTERS         VARCHAR(100),
   T1_TEXT                  VARCHAR,
   T1_MULTIBYTE            CHAR(100),
   T1_VARIABLE_MULTIBYTE      VARCHAR(100),
   T1_DATE                  DATE,
   T1_TIME                  TIME,
   T1_DATETIME               DATETIME,
   T1_TIMESTAMP            TIMESTAMP,
   primary key (T1_SERIAL)
);

with: 1048576 reg.

but the problem is, why not problem change the Dialect to SYBASE?

Why get up the tps with NHibernate.Dialect.MsSql2000Dialect in Sybase?

thanks!

FernandoColmenarejo · **Posted:** Mon Apr 03, 2006 1:42 pm

and the criteria is:

Code:

  session = _factory.OpenSession();
           
             Criterio = session.CreateCriteria(typeof(T1.T1))
                               .Add(NHibernate.Expression.Expression.Sql("T1_SERIAL < "+ (llInstance + auxRandom).ToString())).SetMaxResults(20);

for any worker and could be 100 workers by testing.

Fernando Colmenarejo

FernandoColmenarejo · **Posted:** Tue Apr 04, 2006 12:06 pm

hi Konstantin ;

I am Fernando Colmenarejo, I have the problem of performance issue.

The problem to Sybase is that not use "Top" to limit the rows in the SQL. SybaseDialect not use TOP for it, the time of the database is very long, because Nhibermate limits the number of rows after get in all. It´s the first problem because you would use SQL and not the SetMaxResults().

how can i optimize the criteria without using SQL?

sorry by mi english, I only read English.
[/code]