Performance Problem after SP3

  • RalfK

    Grasshopper

    Points: 22

    Hello,

    after installation of SQL-Server SP3 i have problems with the Database access from ASP. I am using an ADO-Connection and ADO-Recordset objects. When the first recordset is opened everything is ok. For every other recordset that is opened after this there is a delay of about 2 sec. per recordset.

    If the first opened Recordset is closed before opening the next the delay doesn't occur.

    It seems that there can only be one open Recordset without having a massive delay.

    example1 (0.1 sec.):

    
    
    recordset1=connection.Execute("SELECT * FROM tab1");

    example2 (2 sec.):

    
    
    recordset1=connection.Execute("SELECT * FROM tab1");
    recordset2=connection.Execute("SELECT * FROM tab2");

    example3 (4 sec.):

    
    
    recordset1=connection.Execute("SELECT * FROM tab1");
    recordset2=connection.Execute("SELECT * FROM tab2");
    recordset3=connection.Execute("SELECT * FROM tab3");

    example4 (0.1 sec.):

    
    
    recordset1=connection.Execute("SELECT * FROM tab1");
    recordset1.Close()
    recordset2=connection.Execute("SELECT * FROM tab2");
    recordset2.Close()
    recordset3=connection.Execute("SELECT * FROM tab3");

    Does anyone have a Solution for this problem ?

    Thanks, Ralf

  • This was removed by the editor as SPAM

  • NPeeters

    SSChampion

    Points: 12179

    Just some wild guesses...

    Is your IIS running on the same server as the SQLServer?

    If not, did you upgrade the IIS to MDAC 2.7? Maybe there is some (unexplainable) effect if you're using different version.

    If you did upgrade to MDAC 2.7, check your connection string. Maybe some default parameter changed values...

  • kherc

    SSC Veteran

    Points: 204

    After 1 month with SP3 I can talk about a general performance degradation... And I'm almost sure that the problem came with sp3... and it does not depend on the client..

    Yes we updated Client's Mdac to 2.7sp1, we also tried update stats (this was a MustDo for an old SP), index defrag, etc, etc but we still experience a lot of timeouts...

    And please note that before Sp3 everything was working really smoothly.

  • RalfK

    Grasshopper

    Points: 22

    I figured something out:<br>

    There is a bug when a Clients uses Shared Memory for the Connection. After disabling the use of Shared Memory in the Client Network Utility the problem is gone.

    And now it is clear why this problem only occured from connections from the same machine (IIS).

    Ralf Kuhlendahl

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply