RE: SQL Server SP3a via JDBC SP2

From: Carb Simien [MSFT] (CarbinoS_at_online.microsoft.com)
Date: 09/21/04

  • Next message: Carb Simien [MSFT]: "RE: slow connections with jdbc driver and no entry in hosts file" 
    Date: Tue, 21 Sep 2004 18:00:51 GMT

    --------------------
    | Content-Class: urn:content-classes:message
    | From: "Anna" <anonymous@discussions.microsoft.com>
    | Sender: "Anna" <anonymous@discussions.microsoft.com>
    | References: <483c01c490a0$7a33f8d0$a501280a@phx.gbl>
    <KH3RgUDnEHA.752@cpmsftngxa06.phx.gbl>
    | Subject: RE: SQL Server SP3a via JDBC SP2
    | Date: Tue, 21 Sep 2004 01:04:08 -0700
    | Lines: 75
    | Message-ID: <2b7b01c49fb1$92816d80$a501280a@phx.gbl>
    | MIME-Version: 1.0
    | Content-Type: text/plain;
    | charset="iso-8859-1"
    | Content-Transfer-Encoding: 7bit
    | X-Newsreader: Microsoft CDO for Windows 2000
    | thread-index: AcSfsZKB3365J8qRRS+v2TlJ9x88Xw==
    | X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
    | Newsgroups: microsoft.public.sqlserver.jdbcdriver
    | Path: cpmsftngxa06.phx.gbl
    | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.jdbcdriver:6286
    | NNTP-Posting-Host: tk2msftngxa13.phx.gbl 10.40.1.165
    | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
    |
    | Carb Simien, thanks for your reply, but the query
    | execution plan have not be changed. Becuase I use the same
    | DB for my testing, I just write a sample program to switch
    | the different JDBC driver to execute the SQL, any more
    | suggestion on it?
    |
    | >-----Original Message-----
    | >
    | >--------------------
    | >| Content-Class: urn:content-classes:message
    | >| From: "Anna" <anonymous@discussions.microsoft.com>
    | >| Sender: "Anna" <anonymous@discussions.microsoft.com>
    | >| Subject: SQL Server SP3a via JDBC SP2
    | >| Date: Wed, 1 Sep 2004 20:53:59 -0700
    | >| Lines: 8
    | >| Message-ID: <483c01c490a0$7a33f8d0$a501280a@phx.gbl>
    | >| MIME-Version: 1.0
    | >| Content-Type: text/plain;
    | >| charset="iso-8859-1"
    | >| Content-Transfer-Encoding: 7bit
    | >| X-Newsreader: Microsoft CDO for Windows 2000
    | >| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
    | >| thread-index: AcSQoHozPTxwM606QkGX0zwV3qKyXQ==
    | >| Newsgroups: microsoft.public.sqlserver.jdbcdriver
    | >| Path: cpmsftngxa10.phx.gbl
    | >| Xref: cpmsftngxa10.phx.gbl
    | microsoft.public.sqlserver.jdbcdriver:6298
    | >| NNTP-Posting-Host: tk2msftngxa13.phx.gbl 10.40.1.165
    | >| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
    | >|
    | >| I have a java program that run on SQL Server 8.00.760
    | and
    | >| original JDBC drive is no problem, but after apply the
    | SQL
    | >| Server SP3a 8.00.818, when execute a long sql script
    | will
    | >| got error (Invalid buffer received from client.. Error:
    | >| 17805). Then I apply JDBC SP2, the script can run
    | >| successfully, but the overall the sql script is execute
    | >| very slow.
    | >| Anyone to face the same problem?
    | >|
    | >
    | >The original issue regarding an invalid buffer is a
    | client issue, and so it
    | >is good to hear that the updated JDBC driver resolved the
    | issue. However,
    | >your latest question sounds more like a database
    | performance issue. By
    | >updating your SQL Server build, it is likely that your
    | query execution plan
    | >could have changed. Ensure that your indexes and
    | statistics are up to
    | >date. Then you should use SQL Profiler to see what type
    | of query execution
    | >plan is produced from one of the slow queries. The plan
    | will tell you if
    | >indexes are being used and which portion of the plan is
    | the most expensive.
    | >
    | >Carb Simien, MCSE MCDBA MCAD
    | >Microsoft Developer Support - Web Data
    | >
    | >Please reply only to the newsgroups.
    | >This posting is provided "AS IS" with no warranties, and
    | confers no rights.
    | >
    | >Are you secure? For information about the Strategic
    | Technology Protection
    | >Program and to order your FREE Security Tool Kit, please
    | visit
    | >http://www.microsoft.com/security.
    | >
    | >.
    | >
    |

    Upgrading SQL Server does change the sqlservr.exe database engine, which in
    turn has a high degree of potential to alter the query plan. In most
    cases, this change is for the better, but not always.

    Query performance depends on many factors. You should isolate one query
    you wish to optimize, then use SQL Profiler, Perfmon, etc, to compare its
    performance with the performance statistics captured from the original
    scenario to see where the discrepancy lies. Profiler can tell you which
    query was the most expensive (duration, reads, writes, CPU, etc) if you are
    not sure where to start. As I mentioned before, proper indexing and
    updated statistics will yield the most benefit. Next, you must ensure
    that your code is written to manipulate data efficiently. If you are
    retrieving small resultsets, one suggestion is to use SelectMethod=direct
    rather than SelectMethod=cursor in your JDBC connection string. It is
    worth measuring the performance of your query using various clients (JDBC,
    Query Analyzer, etc) to see if the behavior is specific to one API or not
    specific at all. After you have optimized the first query, repeat the
    process for the remaining queries.

    I should also mention that you should test with JDBC SP3, as it is the most
    recent driver that we have available:

        SQL Server 2000 Driver for JDBC Service Pack 3
        
    http://www.microsoft.com/downloads/details.aspx?FamilyId=07287B11-0502-461A-
    B138-2AA54BFDC03A&displaylang=en

    If you have a repro, please post the following:
    * Java code
    * the query and table schema you are using
    * the execution plan and performance stats from Profiler (query duration,
    reads, writes, CPU, number of rows returned, etc)
    * the query duration that you observed prior to upgrading SQL Server and
    JDBC

    Carb Simien, MCSE MCDBA MCAD
    Microsoft Developer Support - Web Data

    Please reply only to the newsgroups.
    This posting is provided "AS IS" with no warranties, and confers no rights.

    Are you secure? For information about the Strategic Technology Protection
    Program and to order your FREE Security Tool Kit, please visit
    http://www.microsoft.com/security.

  • Next message: Carb Simien [MSFT]: "RE: slow connections with jdbc driver and no entry in hosts file"

    Relevant Pages

    • Re: CONTAINS performance
      ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
      (microsoft.public.sqlserver.fulltext)
    • Re: Indexing Service, Openquery and sp_executesql
      ... SQL Server version and sp are you running? ... > data from the indexing service catalog when pasted into ... > query analyzer, but failed when put against sp_executesql ... I would choose Microsoft Indexing ...
      (microsoft.public.sqlserver.fulltext)
    • Re: Problem using Access or Query Designer to run queries in SQL Serve
      ... >or Query Designer within Enterprise Manager, it works and I get data back. ... >ODBC Call Failed [ODBC SQL Server Driver] Timeout Expirederror in Access ... >[ODBC SQL Server Driver] Timeout Expired ...
      (microsoft.public.sqlserver.odbc)
    • Re: sendStringParameterAsUnicode: How to insert unicode data corre
      ... After checking with Microsoft it turns out that transmitting Unicode ... requires a change in the data format sent to the JDBC Receiver channel. ... We have contacted SAP and Microsoft. ... We are using SAP XI and connecting it to SQL Server 2005 using JDBC. ...
      (microsoft.public.sqlserver.jdbcdriver)
    • Re: Transaction Isolation Level
      ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
      (comp.databases.ms-sqlserver)
    Loading