Transcript SurviveJava_dfwx

Weird Stuff I Saw While ...
Supporting a Java Team
Rick Lowe
[email protected]
@dataflowe
What I’m Not
 I am not a Java Expert
 I deny that I’m a developer
 Probably not especially knowledgeable about
using MSSQL from Java
 …
 I am interested in starting a conversation
about these issues
2 | 10/10/2015
Session Title Here
Example 1 : Looking for the cache
3 | 10/10/2015
Session Title Here
SelectMethod Options
 “SelectMethod = full” : client retrieves entire
result set
 “SelectMethod = cursor” : server side cursor
 “responseBuffering=adaptive” : Results
retrieved as they are needed
 Some are unaware of adaptive buffering.
 Adaptive buffering is default in JDBC 2.0 and
later
4 | 10/10/2015
Session Title Here
Server Side Cursors




Works much like a normal cursor
Cursor is created from a query and opened
Results are read back row-by-agonizing-row
Every single one of these steps involves a
trip across the network
 One row at a time minimizes memory
requirements on the client
5 | 10/10/2015
Session Title Here
Removing server side cursors
 Not just annoying - potential performance
issue
 In recent JDBC versions, simply remove
“SelectMethod=cursor” from URL
 Evaluate on test system first
 Workloads do exist where cursors are
appropriate from a client side perspective
6 | 10/10/2015
Session Title Here
Example 2 : Conversion to CRUD
7 | 10/10/2015
Session Title Here
How things work in ADO.Net
 try {
mySqlCommand.executeUpdate();
} catch ….
 executeUpdate() is not actually void, it
returns # of rows imapacted.
 … except we usually use NOCOUNT which
means the return is always 0
 And errors are more interesting than counts
8 | 10/10/2015
Session Title Here
How things work in Java
 PreparedStatement.executeUpdate() returns
the number of rows impacted
 … and it often actually gets paid attention to
 When inserting, often check for return == 1
 Quick fix is to remove “SET NOCOUNT ON”
from Create stored procedures
9 | 10/10/2015
Session Title Here
Example 3 : OUTPUT parameters
10 | 10/10/2015
Session Title Here
It all seemed so easy…
 PreparedStatement.executeUpdate() will not
allow you to simply ignore parameters
 PreparedStatement does not understand
output parameters
 Must use PreparedCall instead
 To add confusion, there’s a new weird syntax
you can use
11 | 10/10/2015
Session Title Here
Windows Authentication
 Doesn’t work.
 Just kidding, it works. It just doesn’t work by
default.
 Setup depends on version
12 | 10/10/2015
Session Title Here
Prior to JDBC 4
 Copy sqljdbc_auth.dll from
<jdbc>\sqljdbc_version\<language>\auth\<arc
h> to windows\system32
 Be sure to grab the correct architecture – x86
vs x64
 Then integratedSecurity=SSPI will work as
expected.
 Alternately, use the –Djava.library.path
13 | 10/10/2015
Session Title Here
Starting With JDBC 4





Can still use sqljdbc_auth
Alternately, can use Java Kerberos
authenticationScheme=JavaKerberos
integratedSecurity=true
Must now specify FQDN for server name
14 | 10/10/2015
Session Title Here
V1 Enterprise Java Beans
 Enterprise Java Beans help encapsulate DB
code
 Developers implement methods to Create,
Update, and Delete records
 Called automatically as code works with
objects
 Issue with early versions – update method
can be called whether or not data changes
15 | 10/10/2015
Session Title Here
The Talk
16 | 10/10/2015
Session Title Here
Query Hints
 In .Net shops, the NOLOCK wars rage on
 Abusing hints is a bad habit …
 … So we shouldn’t expect that getting folks to
stop using NOLOCK just because we tell
them the 101st time.
 We usually settle for making our personal
sandbox a “no smoking zone”
17 | 10/10/2015
Session Title Here
Query Hints Contd.
 Java people typically aren’t used to the
Microsoft stack
 This may be a new discussion for them
 There is still room to change habits in this
community
18 | 10/10/2015
Session Title Here