RE: New JDBC 1.2 driver runs slower than JDBC 1.1; my db definition

I do think this would happen in general, not just for my database; however, I
do not have time right now to reproduce against a generic database. Both
queries are against a single table (the person table)
Here is the definition of this table:

USE [ccc_glasgow_spey]
GO
/****** Object: Table [dbo].[Person] Script Date: 03/17/2008 08:51:21
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Person](
[HomeServerCID] [int] NULL,
[PersonEUID] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PersonGID] [decimal](19, 0) NOT NULL CONSTRAINT [DF_Person_PersonGID]
DEFAULT ((0)),
[FirstName] [nvarchar](63) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_Person_FirstName] DEFAULT (''),
[MiddleName] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_Person_MiddleName] DEFAULT (''),
[LastName] [nvarchar](63) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_Person_LastName] DEFAULT (''),
[PersonType] [int] NOT NULL CONSTRAINT [DF_Person_PersonType] DEFAULT ((0)),
[PIN] [int] NOT NULL CONSTRAINT [DF_Person_PIN] DEFAULT ((0)),
[BadgeLayoutID] [int] NOT NULL CONSTRAINT [DF_Person_BadgeLayoutID]
DEFAULT ((0)),
[FacilityCode] [int] NOT NULL CONSTRAINT [DF_Person_FacilityCode] DEFAULT
((0)),
[Inactive] [bit] NOT NULL CONSTRAINT [DF_Person_Inactive] DEFAULT ((0)),
[Disabled] [bit] NOT NULL CONSTRAINT [DF_Person_Disabled] DEFAULT ((0)),
[ADA] [bit] NOT NULL CONSTRAINT [DF_Person_ADA] DEFAULT ((0)),
[Deleted] [bit] NOT NULL CONSTRAINT [DF_Person_Deleted] DEFAULT ((0)),
[Noticed] [bit] NOT NULL CONSTRAINT [DF_Person_Noticed] DEFAULT ((0)),
[LastModDT] [int] NULL CONSTRAINT [DF_Person_LastModDT] DEFAULT ((0)),
[GlobalLastModDT] [int] NULL CONSTRAINT [DF_Person_GlobalLastModDT]
DEFAULT ((0)),
[LastModPersonEUID] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastModPersonGID] [decimal](19, 0) NULL CONSTRAINT
[DF_Person_LastModPersonGID] DEFAULT ((0)),
[Text1] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_Person_Text1] DEFAULT (''),
[Text2] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_Person_Text2] DEFAULT (''),
[Text3] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_Person_Text3] DEFAULT (''),
[Text4] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_Person_Text4] DEFAULT (''),
[Text5] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_Person_Text5] DEFAULT (''),
[Int1] [decimal](28, 0) NULL CONSTRAINT [DF_Person_Int1] DEFAULT ((0)),
[Int2] [decimal](28, 0) NULL CONSTRAINT [DF_Person_Int2] DEFAULT ((0)),
[Int3] [decimal](28, 0) NULL CONSTRAINT [DF_Person_Int3] DEFAULT ((0)),
[Int4] [decimal](28, 0) NULL CONSTRAINT [DF_Person_Int4] DEFAULT ((0)),
[Int5] [decimal](28, 0) NULL CONSTRAINT [DF_Person_Int5] DEFAULT ((0)),
[Logical1] [bit] NOT NULL CONSTRAINT [DF_Person_Logical1] DEFAULT ((0)),
[Logical2] [bit] NOT NULL CONSTRAINT [DF_Person_Logical2] DEFAULT ((0)),
[Date1] [datetime] NULL,
[Date2] [datetime] NULL,
[ActivateAPEvent] [bit] NOT NULL CONSTRAINT [DF_Person_ActivateAPEvent]
DEFAULT ((0)),
[AssetAdministrator] [bit] NOT NULL CONSTRAINT
[DF_Person_AssetAdministrator] DEFAULT ((0)),
[ImageCaptureDT] [int] NULL CONSTRAINT [DF_Person_ImageCaptureDT] DEFAULT
((0)),
[BadgePrintDT] [int] NULL CONSTRAINT [DF_Person_BadgePrintDT] DEFAULT ((0)),
[SignatureCaptureDT] [int] NULL CONSTRAINT [DF_Person_SignatureCaptureDT]
DEFAULT ((0)),
[TransactionNum] [int] NOT NULL CONSTRAINT [DF_Person_TransactionNum]
DEFAULT ((0)),
[IsCcmUser] [bit] NOT NULL CONSTRAINT [DF_Person_IsCcmUser] DEFAULT ((0)),
[HasBeenCcmUser] [bit] NOT NULL CONSTRAINT [DF_Person_HasBeenCcmUser]
DEFAULT ((0)),
[ImageReplicationCategory] [int] NOT NULL CONSTRAINT
[DF_Person_ImageReplicationCategory] DEFAULT ((2)),
[ImageLastModDT] [int] NULL CONSTRAINT [DF_Person_ImageLastModDT] DEFAULT
((0)),
[Temp] [bit] NOT NULL CONSTRAINT [DF_Person_Temp] DEFAULT ((0)),
[CardLastModDT] [int] NULL CONSTRAINT [DF_Person_CardLastModDT] DEFAULT
((0)),
[PersonIdentifier] [decimal](20, 0) NOT NULL CONSTRAINT
[DF_Person_PersonIdentifier] DEFAULT ((0)),
[OrganizationalCategory] [decimal](20, 0) NOT NULL CONSTRAINT
[DF_Person_OrganizationalCategory] DEFAULT ((0)),
[OrganizationalIdentifier] [decimal](20, 0) NOT NULL CONSTRAINT
[DF_Person_OrganizationalIdentifier] DEFAULT ((0)),
[POAssociatCategory] [decimal](20, 0) NOT NULL CONSTRAINT
[DF_Person_POAssociatCategory] DEFAULT ((0)),
[Int6] [decimal](28, 0) NULL,
[Int7] [decimal](28, 0) NULL CONSTRAINT [DF_Person_Int7] DEFAULT ((0)),
[Int8] [decimal](28, 0) NULL CONSTRAINT [DF_Person_Int8] DEFAULT ((0)),
[Int9] [decimal](28, 0) NULL CONSTRAINT [DF_Person_Int9] DEFAULT ((0)),
[Logical3] [bit] NOT NULL CONSTRAINT [DF_Person_Logical3] DEFAULT ((0)),
[Logical4] [bit] NOT NULL CONSTRAINT [DF_Person_Logical4] DEFAULT ((0)),
[Date3] [datetime] NULL,
[Date4] [datetime] NULL,
[Text6] [nvarchar](129) COLLATE Latin1_General_CI_AI NULL,
[Text7] [nvarchar](129) COLLATE Latin1_General_CI_AI NULL,
[Text8] [nvarchar](129) COLLATE Latin1_General_CI_AI NULL CONSTRAINT
[DF_Person_Text8] DEFAULT (''),
[Text9] [nvarchar](129) COLLATE Latin1_General_CI_AI NULL CONSTRAINT
[DF_Person_Text9] DEFAULT (''),
[Text10] [nvarchar](129) COLLATE Latin1_General_CI_AI NULL CONSTRAINT
[DF_Person_Text10] DEFAULT (''),
[Text11] [nvarchar](129) COLLATE Latin1_General_CI_AI NULL CONSTRAINT
[DF_Person_Text11] DEFAULT (''),
[Text12] [nvarchar](129) COLLATE Latin1_General_CI_AI NULL CONSTRAINT
[DF_Person_Text12] DEFAULT (''),
[Text13] [nvarchar](129) COLLATE Latin1_General_CI_AI NULL CONSTRAINT
[DF_Person_Text13] DEFAULT (''),
[Text14] [nvarchar](129) COLLATE Latin1_General_CI_AI NULL CONSTRAINT
[DF_Person_Text14] DEFAULT (''),
[Text15] [nvarchar](129) COLLATE Latin1_General_CI_AI NULL CONSTRAINT
[DF_Person_Text15] DEFAULT (''),
[Text16] [nvarchar](129) COLLATE Latin1_General_CI_AI NULL CONSTRAINT
[DF_Person_Text16] DEFAULT (''),
[Text17] [nvarchar](129) COLLATE Latin1_General_CI_AI NULL CONSTRAINT
[DF_Person_Text17] DEFAULT (''),
[Text18] [nvarchar](129) COLLATE Latin1_General_CI_AI NULL CONSTRAINT
[DF_Person_Text18] DEFAULT (''),
[Text19] [nvarchar](129) COLLATE Latin1_General_CI_AI NULL CONSTRAINT
[DF_Person_Text19] DEFAULT (''),
[Text20] [nvarchar](129) COLLATE Latin1_General_CI_AI NULL CONSTRAINT
[DF_Person_Text20] DEFAULT (''),
[Text21] [nvarchar](129) COLLATE Latin1_General_CI_AI NULL CONSTRAINT
[DF_Person_Text21] DEFAULT (''),
[Text22] [nvarchar](129) COLLATE Latin1_General_CI_AI NULL CONSTRAINT
[DF_Person_Text22] DEFAULT (''),
[Text23] [nvarchar](129) COLLATE Latin1_General_CI_AI NULL CONSTRAINT
[DF_Person_Text23] DEFAULT (''),
[Text24] [nvarchar](129) COLLATE Latin1_General_CI_AI NULL CONSTRAINT
[DF_Person_Text24] DEFAULT (''),
[Text25] [nvarchar](129) COLLATE Latin1_General_CI_AI NULL CONSTRAINT
[DF_Person_Text25] DEFAULT (''),
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersonGID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

/** Many fields have indexes, they are all like this */
USE [ccc_glasgow_spey]
GO
/****** Object: Index [FirstName] Script Date: 03/17/2008 08:54:43 ******/
CREATE NONCLUSTERED INDEX [FirstName] ON [dbo].[Person]
(
[FirstName] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

/* Full list of fields with indexes, defined as above:
FirstName
FullName
GlobalLastModDT
Int1
Int2
Int6
Int7
LastModDT
PersonEUID
Text1
Text2
Text6
Text7
Text8
Text9
Text10
------------------------------------------------------------------------------------
"Evan T. Basalik (MSFT)" wrote:

Bob,

While we did not specifically target performance with the v1.2 driver beyond the Adapative Buffering changes, I am surprised that you are seeing slower
performance with the v1.2 driver. Do you only see the problem with your custom database? Do you think the problem would reproduce against a generic
database?

Also, it looks like both your SELECT and UPDATE statement are restricted to a single table. If that is the case, then can you provide the definition for your
table and your Java code? I should be able to use those to generate some sample data pretty easily so I can do a similar test in my environment.

Evan
--------------------
Thread-Topic: New JDBC 1.2 driver runs slower than JDBC 1.1 driver-- expected??
thread-index: Ach+02cBj6yAlt+wSw2Q7Yv4oIbaxA==
X-WBNR-Posting-Host: 63.69.129.2
From: =?Utf-8?B?Qm9iIEhlaW5nYXJ0bmVy?= <BobHeingartner@xxxxxxxxxxxxxxxxxxxxxxxxx>
Subject: New JDBC 1.2 driver runs slower than JDBC 1.1 driver-- expected??
Date: Wed, 5 Mar 2008 07:13:01 -0800
Lines: 22
Message-ID: <D64A11AB-06AA-4210-AA6C-0B2686ED5E08@xxxxxxxxxxxxx>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
Newsgroups: microsoft.public.sqlserver.jdbcdriver
Path: TK2MSFTNGHUB02.phx.gbl
Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.jdbcdriver:453
NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver

I have some test programs I run which do repeated simple SQL Select and SQL
update statements. These test programs consistently take 5 to 10% longer to
run with the new JDBC 1.2 driver than they do with the JDBC 1.1 driver. Is
this expected? I thought the new driver was supposed to be higher
performance, not lower performance. Here is the select statement I am doing:

SELECT HomeServerCID, FirstName, MiddleName, LastName, PersonType, Pin,
FacilityCode, Inactive, Disabled, ADA, Deleted, Noticed, LastModDT, Text1,
Text2, Text3, Text4, Text5, text6, text7, text8, text9, Int1, Int2, Int3,
Int4, Int5, Logical1, Logical2, Logical3, Logical4, Date1, Date2,
ActivateAPEvent, AssetAdministrator, ImageCaptureDT, SignatureCaptureDT,
PersonGID, LastModPersonGID, IsCCmUSer, HasBeenCCMUser FROM person WHERE
PersonGID = xxxx

Here is the update statement:
UPDATE person SET HomeServerCID = HomeServerCID WHERE PersonGID = xxxx

(In both cases, xxxx varies in a loop so different records are selected.)


I can provide the complete java source code if needed, but you will also
need a database backup to run them.


Evan T. Basalik
This posting is provided “AS IS” with no warranties, and confers no rights.



.

Relevant Pages

  • RE: New JDBC 1.2 driver runs slower than JDBC 1.1; my db definitio
    ... If the support incident ends up verifying a slowdown in the new driver, ... New JDBC 1.2 driver runs slower than JDBC 1.1; ... I do think this would happen in general, not just for my database; ...
    (microsoft.public.sqlserver.jdbcdriver)
  • RE: New JDBC 1.2 driver runs slower than JDBC 1.1; my db definition
    ... New JDBC 1.2 driver runs slower than JDBC 1.1; ... [PersonGID] ... While we did not specifically target performance with the v1.2 driver beyond the Adapative Buffering changes, I am surprised that you are seeing slower ... it looks like both your SELECT and UPDATE statement are restricted to a single table. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Problem with JDBC MSSQL
    ... >> JDBC from an applet. ... > classes and you can easily use another persistent storage instead of JDBC. ... > JDBC driver talks to the database. ...
    (comp.lang.java.databases)
  • Re: SAP direct JDBC access
    ... > data with JDBC. ... > SQL Server. ... you can use a data source to hide the actual database ... driver used. ...
    (comp.lang.java.programmer)
  • RE: New JDBC 1.2 driver runs slower than JDBC 1.1; my db definitio
    ... I won't be able to know if this will be a paid support incident until after we figure out the source of the issue. ... If the support incident ends up verifying a slowdown in the new driver, ... New JDBC 1.2 driver runs slower than JDBC 1.1; ... [PersonGID] ...
    (microsoft.public.sqlserver.jdbcdriver)