RE: New JDBC 1.2 driver runs slower than JDBC 1.1 driver-- expecte

Here is the java code I used (The definition of person table is in a separate
post):

public class StatementOverMS
{
// Set options based on the database we're testing.

//Following for Bob SQL2005 db with MS driver
static String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
static String url =
"jdbc:sqlserver://localhost;databaseName=ccc_glasgow_spey";
//static String url =
"jdbc:sqlserver://localhost\\BOB2005;databaseName=ccc_manyPerson";


static String userid = "sa";
static String passwd = "sa";





public static void main (String args [])
throws SQLException, ClassNotFoundException
{
basicStatement(); //For update test
selectStatement(); //For Select Test
}

public static void basicStatement ()
throws SQLException, ClassNotFoundException
{
System.out.println("basic statement MS SQL:");
String query = null;

//StartConnection here:
Class.forName (driver);


Driver JDBCDriver = DriverManager.getDriver(url);

DriverManager.registerDriver(JDBCDriver);

Connection conn;

conn = DriverManager.getConnection(url, userid, passwd);

conn.setAutoCommit(false);

conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);


// From the connection, you have to create a statement context.

ResultSet rset;

//Loop


int iSeqIx = 1;
int iJ;
long lTotElap = 0;
int iTotExec = 0;
for (iJ=0; iJ < 6; iJ++)
{
Date dateSt = new Date();

int iPersonGID = 3000;
int iPerFound = 0;
int iClrFound = 0;
int iEnd = 200;
if (iJ == 0) iEnd = 5;
for (int iX=0; iX < iEnd; iX++)
{
try
{
String strQuery;
Statement stmt;
iPersonGID++;
iTotExec++;

strQuery = "UPDATE person SET HomeServerCID =
HomeServerCID WHERE PersonGID = "
+ iPersonGID;
//System.out.println(strQuery);
stmt = conn.createStatement ();
iPerFound += stmt.executeUpdate(strQuery);
stmt.close();

if ((iX % 3) == 0)
{
conn.commit(); //Commit every 100 adds
}
}
catch (Exception except)
{
System.out.println("Exception caught, stack trace
follows:");
except.printStackTrace();
}
}
conn.commit();
Date dateEnd= new Date();
long lElap = dateEnd.getTime() - dateSt.getTime();
lTotElap += lElap;
System.out.println("200 statements" + " Elapsed millisec= " +
lElap);
System.out.println("Tot Person:" + iPerFound + " Clr: " +
iClrFound);

}
System.out.println("Total statements = " + iTotExec + " total elap=
" + lTotElap + " Avg Ms: " +
lTotElap*1.0 / (iTotExec));
//All Done with Startconnection loop here
}



public static void selectStatement ()
throws SQLException, ClassNotFoundException
{
//Change following to use prepared stmt or not
final boolean bPrep = false;
//Change following to use XLOCK or not
final boolean bXlock = false;

//Set fetch size to 0 or higher
final int iFetchSize = 0;

System.out.println("xlock or not: " + bXlock + " statement Prepared
or not: " + bPrep + " fetch size: " + iFetchSize);
Class.forName (driver);

//DriverManager.setLogStream(System.out) ; //Enable debug logging to
console

Driver JDBCDriver = DriverManager.getDriver(url);

DriverManager.registerDriver(JDBCDriver);

Connection conn;
//Following changes cursor mode:
//url = url + ";selectMethod=cursor";
conn = DriverManager.getConnection(url, userid, passwd);


conn.setAutoCommit(false);

conn.rollback();

conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);


// From the connection, you have to create a statement context.

ResultSet rset;

String strXlock = "";
if (bXlock)
{
strXlock = " WITH (XLOCK)";
}
String strQuery = "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" + strXlock + " WHERE PersonGID = ";
String strQueryPrep = strQuery + "?";

System.out.println(strQueryPrep);
PreparedStatement psSelPerBig = conn.prepareStatement(strQueryPrep);

int iSeqIx = 1;
int iJ;
long lTotElap = 0;
int iTotExec = 0;
for (iJ=0; iJ < 6; iJ++)
{
Date dateSt = new Date();

int iPersonGID = 3001;
int iPerFound = 0;
int iClrFound = 0;
int iEnd = 200;
Statement stmt = null;
if (iJ == 0) iEnd = 5;
for (int iX=0; iX < iEnd; iX++)
{
try
{
iPersonGID++;
iTotExec++;
//System.out.println(strQuery);
//This way for non-prepared XLOCK:
if (!bPrep)
{
stmt = conn.createStatement();
if (iFetchSize > 0) stmt.setFetchSize(iFetchSize);
//This affects performance a lot!!
stmt.executeQuery(strQuery + iPersonGID);
rset = stmt.getResultSet();
}
else
{
//Select person ID w/Prepared statement:
if (iFetchSize > 0)
psSelPerBig.setFetchSize(iFetchSize);
psSelPerBig.setInt(1, iPersonGID);
psSelPerBig.execute();
rset = psSelPerBig.getResultSet();
}
while (rset.next())
{
String strRes = rset.getString(1) +
rset.getString(2) + rset.getString(3);
iPerFound++;
}

rset.close();
if (!bPrep)
{
stmt.close();
}
if ((iX % 3) == 0)
{
conn.commit(); //Commit every 100 adds
}
}
catch (Exception except)
{
System.out.println("Exception caught, stack trace
follows:");
except.printStackTrace();
}
}
conn.commit();
Date dateEnd= new Date();
long lElap = dateEnd.getTime() - dateSt.getTime();
lTotElap += lElap;
System.out.println("200 statements" + " Elapsed millisec= " +
lElap);
System.out.println("Tot Person:" + iPerFound + " Clr: " +
iClrFound);

}
System.out.println("Total statements = " + iTotExec + " total elap=
" + lTotElap + " Avg Ms: " +
lTotElap*1.0 / (iTotExec));
//All Done with Startconnection loop here
}

}



"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

  • [git patches] IDE updates part #5
    ... Palmchip BK3710 IDE driver ... int stat, err, sense_key; ... + * After each failed packet command we issue a request sense command and retry ...
    (Linux-Kernel)
  • [git patches] net driver updates for .26
    ... Fix a bug where the pointer never moves for dma_unmap... ... Update and fix driver debugging messages ... int reset); ... * header structure can be anywhere in the mcp. ...
    (Linux-Kernel)
  • [RFC/PATCH] Winbond CIR driver for the WPCD376I chip (ACPI/PNP id WEC1022)
    ... The driver currently supports receiving IR commands and wake from sleep/power-off. ... The input syscalls all seem to use an int for the scancode (which will be at least 32 bits on any platform which has ... unsigned int last_keycode; ... goto set_timer; ...
    (Linux-Kernel)
  • [PATCH 196/196] Driver core: coding style fixes
    ... extern int system_bus_init; ... struct klist_iter i; ... struct device *dev; ... * We iterate over each driver that belongs to @bus, ...
    (Linux-Kernel)
  • [git patches v2] net driver updates for 2.6.29
    ... sfc: Correct address of gPXE boot configuration in EEPROM ... bonding: Fix ALB mode to balance traffic on VLANs ... SMSC LAN911x and LAN921x vendor driver ... module_param(updelay, int, 0); ...
    (Linux-Kernel)