performance problem in asp.net with orm.net

[Antoine Induni]

Hello,
We have used with great pleasure orm.net for our intranet project.

The framework is very fine but we have some performance problems. After 2
or 3 day, the site become very slow. If we restart iis, the performances
are good again. What wrong have we done?

We develop the site with vs.net 2003 in c# and ormlib 1.7.2. The site is
hosted on a win2k server with iis 5.0.

Please help us.

Thanks for your help and congratulations for your great work.

[Dave Kekish]

Are you cleaning up after the datamanager?   Ie closing your connections?

[Antoine Induni]

Thanks for your quick answer,

we don't clean up the datamanager, Why is it necessary?

Can we do that with dm.Connection.Close()?

We monitor the sql server and there is only 4 or 5 connections.

Would it help to use a singleton for the datamanager and share it in the whole web application.

[Huseyin Hascelik]

I think I have found something interesting about query of Orm.

if you trace (with sqlporfiler) OrmLib queries you can find sql sommands beginning with

EXEC sp_executesql @stmt = N'...

where ... is the query text generated by OrmLib

this text is interesting because for ex. an insert query generated by OrmLib is unique by its constant structure. if you have 10000 inserts in dm.CommitAll() you will have 10000 different procedures generated by OrmLib.

an example of this proc is:

DECLARE @RecipientPKey1000000    AS bigint
INSERT    [Recipient] ([AccountID], [FirstName], [LastName], [Email], [GSM], [BirthDate], [GenderID], [WorkingCountryID], [WorkingCityID], [WorkingTownID], [LivingCountryID], [LivingCityID], [LivingTownID], [CompanyID], [EducationStatusID], [MaritalStatusID], [MonthlyIncomeID], [HasHouse], [NumberOfChildren], [RefCode], [AppCode])
VALUES    ( 1, N'Huseyin4', N'Hasçelik', N'', N'', default, default, default, default, default, default, default, default, default, default, default, default, default, default, N'12345', default)

IF @@ERROR <> 0 BEGIN
    RETURN
END
SET    @RecipientPKey1000000 = SCOPE_IDENTITY()

SELECT @RecipientPKey1000000 'Recipient_7'

.........................................

The 'Recipient_7' is important here. it makes this procedure different from other 10000 procedures generated by Ormlib. But this uniqueness is necessary for the ORM to work right.

...........................................

Then what is happening in SQLSever:

ORMLib makes 10000 storedprocedure call to SQLServer which are in different structure by making calls like

EXEC sp_executesql @stmt = N'...

So???

Problem: The SQL Server SP Cahce !!!

SQL Server tries to cache this every procedure and spend memory.

it caches 10000 sps generated by OrmLib and memory fails.

I have traced this behaviour whole day. You can see this cahce behaviour by tracing

SP:CacheHit
SP:CacheInsert
SP:CacheMiss

events of SqlProfiler

................................................
................................................

I have tried to insert 10000 records to database with original Orm.

sql server cahced all 10000 sp generated by Orm and eaten all of my memory.

...............................................

if you change the code in OrmLib --> DatamanagerBase --> FillDataSet Method like:

------------------------------------------------

    [EditorBrowsable(EditorBrowsableState.Never)]
        protected internal void FillDataSet( string sql, string tables, DataSet dataSet, bool transaction)
        {
            // dont run if there is nothing in it
            if ( sql.Trim('\n','\r', '\t', ' ') .Length == 0 )
                return;

            SqlCommand cmd = GetSqlCommand(transaction);

            try
            {
                // Commented by huseyin 22.01.2006 03:32
//                cmd.CommandType = CommandType.StoredProcedure;
//                cmd.CommandText = "sp_executesql";
//
//                SqlParameter param = cmd.Parameters.Add("@stmt",SqlDbType.NText);
//                param.Direction = ParameterDirection.Input;
//                param.Value = sql;
                // End Commented

                // Added by huseyin 22.01.2006 03:32

                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;

                // End Added

...

------------------------------------------------

the sql server will not cahce the unique sql statements generated by OrmLib, because they are not sp.

------------------------------------------------

this solved the memory problem of Orm.

but I do not tested if I broken any functionality with Orm by changing the call from sp to text statement.

I think I did not.

------------------------------------------------

this was really interesting issue to search like a detective which take my all day to realize what is really happening inside.