JDBC for sql server: delays experienced when using Connection.prepareCall()

From: Anthony (anthony_at_jayasekera.net)
Date: 05/12/04 

Date: 12 May 2004 07:45:06 -0700

Hello,

I have been doing some performance tests of the MS jdbc driver for sql
server. I am observing behaviour that I can't fully understand. When
getting a connection from the driver
[DriverManager.getConnection(CONNECTION_STRING)] I am experiencing a
delay of about 15-16 seconds. This seems rather long but I am willing
to accept this initial performance hit (I am connecting to a remote
sql server instance). The first time I call prepareCall() to generate
a reusable CallableStatement based on a stored procedure, there is no
significant delay (less than 1 second). However the next time I call
prepareCall() (on the same Connection) to generate a different
CallableStatement I experience a delay which also is 15-16 seconds. I
have done other tests like using mulitple connections but it always
seems to be the case that the first prepareCall() call made on a
particular Connection instance is immediate but subsequent calls take
15-16 seconds (the same time taken to create the connection in the
first place). Why is this? I understand the principle of caching
CallableStatements but I do not understand why the prepareCall() call
takes so long. If I have to prepare 50 calls up front am I expected
to wait 750 seconds?!? Any advice in this would be hugely
appreciated. If you are not able to shed any light on this but have
used the ms sql server jdbc driver in a commercial environment in an
application that uses large numbers of stored procedures please let me
know. This will at least indicate that it is possible to address this
problem. Please note, my tests all involve straight java from a
client, not J2EE etc.

Thanks,

Anthony

ps. I am well aware of the differences of opinion out there relating
to sql server, MS etc. If you feel like replying with nothing more
than a tirade about how rubbish MS are please don't bother.

Relevant Pages

  • Re: Connection issues between .Net framwok and SQl Express 2005
    ... You will no longer need the database MDF file name in the connection string. ... simply attach the ASPNETDB.mdf to SQL Server permenantly, ... as USER INSTANCE on existing SQL Server Express, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Performance of ODBC
    ... if you have a software firewall; forward 1433/1434 to your server ... I've now created my own server with SQL Server 2005 Express, ... glitch concerns the Upsizing Wizard. ... I originally set up my ODBC connection via ...
    (microsoft.public.access.adp.sqlserver)
  • SQL 2005 Express and VS .Net 2003 wizard error More options
    ... Open the server explorer panel ... Go to the Provider tab and select SQL NAtive Client (DON'T use oledb ... Go back to the Connection tab and carry on setting up the connection ... I have installed Visual Studio 2005 Professional and then Visual Studio ...
    (microsoft.public.sqlserver)
  • Re: Timeout problem with SS2K, VS03
    ... I noticed calls in sql profiler: ... This is the server side debug stored procedure. ... a timeout issue that I cannot identify the source of. ... transaction is committed and the connection is closed cleanly and all ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Cannot connect to SQL Express on 2003 Machine from XP machine
    ... listening on UDP 1434 using netstat -abn, ... And i double checked the connection string, and i did have it the right ... But I have another 2000 server that I can connect to fine, ... I am trying to connect from an XP SP2 machine remotely to a SQL Express ...
    (microsoft.public.sqlserver.connect)