2 Replies Latest reply on Mar 17, 2003 6:35 PM by thl-mot

    Performance issue with SQLServer

    thl-mot

      Hi,
      I am using JBoss 3.0.4 and JBoss 3.0.6 with MS SQL Server 2000. My application is using CMP 2.0 entity beans to represent all data.
      If i set properties of an entity the update statement send to the DB takes 70ms (seen so in the database profiler). The statement sent to the db looks like
      "exec sp_executesql N'update table set field=.....'
      if I execute the same statement from a sql console (Query analyzer), the same update statement 'update table set ....' takes less than 10ms.
      My question is why does the jdbc-driver use a stored procedure tu execute the statement, that takes 5 times longer then just the prepared statement?
      Did anybody experience the same effect?
      Is it possible to configure the SQLServer jdbc driver to use the statements directly without the stored procedure?
      In my case its a very big problem, because this update statement is executed within a loop, so its really time consuming.

      thl-mot

        • 1. Re: Performance issue with SQLServer

          This depends on what driver you are using.

          If using the M$ driver, they execute all statements via the stored prpocedure, but that is not the problem usually.

          Problems happen when you have varchar fields but the driver wants to deal with all strings as unicode, which is why you see the N' before all of your strings.

          The way to turn this off is to specify your ConnectionURL as such

          jdbc:microsoft:sqlserver://servername:1433;DatabaseName=DBNAME;SelectMethod=cursor;sendStringParametersAsUnicode=false

          I never tried the opposite though, making all of your fields nvarchar instead of varchar. That may work too.

          I hope this helps...

          • 2. Re: Performance issue with SQLServer
            thl-mot

            Yes thats it, I could reproduce it. As you can see in the attached screen shot.
            Statement 1 was executed with the parameter
            "sendStringParametersAsUnicode=false".
            Statement 2 was executed without that parameter.