Slow query using SQL Server and JDBC

Slow query using SQL Server and JDBC

Post by M Pir » Sat, 12 Jul 2003 22:10:07



Hi all,

First of all I am sorry if this is a FAQ but I couldn't find any for
this newsgroup. Please be kind enough to redirect me to it, if it
exists!

I have a JSP application running on Apache and I have to query a SQL
Server database. I have the Microsoft JBDC drivers for SQL Server
installed and they "seem" to be working OK.

The thing is, when I submit and query the database, I watch in SQL
profiler that there is a sucessful login but the first statement I get

approx. 300/400 seconds (!!!) later comes the first statement in my
code which is an ordinary "select" from a table which also lasts an
eternity...! I am testing the query from the same server where the SQL
server is, so there shouldn't be any network delay!

What I am doing wrong? Help!

Thank you.

 
 
 

Slow query using SQL Server and JDBC

Post by Joseph Weinstei » Sat, 12 Jul 2003 23:30:30



> Hi all,

> First of all I am sorry if this is a FAQ but I couldn't find any for
> this newsgroup. Please be kind enough to redirect me to it, if it
> exists!

> I have a JSP application running on Apache and I have to query a SQL
> Server database. I have the Microsoft JBDC drivers for SQL Server
> installed and they "seem" to be working OK.

> The thing is, when I submit and query the database, I watch in SQL
> profiler that there is a sucessful login but the first statement I get

> approx. 300/400 seconds (!!!) later comes the first statement in my
> code which is an ordinary "select" from a table which also lasts an
> eternity...! I am testing the query from the same server where the SQL
> server is, so there shouldn't be any network delay!

Hi. Don't worry about the MAX_PREC... stuff. The driver will make some
of it's own queries to set itself up according to the capabilities of the DBMS
it is connected to. The delay seems to be simply waiting for the DBMS to respond.
You *should* expect some delay, because unless the machine has more than one
CPU, there will be some inevitable delay while the CPU switches from running
your Apache system and the DBMS. Depending on how much memory you
have, and how much other load the box has, the delay might be less or more.

Joe Weinstein at BEA

- Show quoted text -

Quote:

> What I am doing wrong? Help!

> Thank you.


 
 
 

Slow query using SQL Server and JDBC

Post by noel woo » Sun, 13 Jul 2003 19:06:15


I'm only a uni student but I think the time delays you have indicate
something is wrong.  I use jsp with oracle and results are returned at most
3 seconds after the query is submitted.  300 to 400 seconds seems like there
must be something wrong.
Noel


> > Hi all,

> > First of all I am sorry if this is a FAQ but I couldn't find any for
> > this newsgroup. Please be kind enough to redirect me to it, if it
> > exists!

> > I have a JSP application running on Apache and I have to query a SQL
> > Server database. I have the Microsoft JBDC drivers for SQL Server
> > installed and they "seem" to be working OK.

> > The thing is, when I submit and query the database, I watch in SQL
> > profiler that there is a sucessful login but the first statement I get

> > approx. 300/400 seconds (!!!) later comes the first statement in my
> > code which is an ordinary "select" from a table which also lasts an
> > eternity...! I am testing the query from the same server where the SQL
> > server is, so there shouldn't be any network delay!

> Hi. Don't worry about the MAX_PREC... stuff. The driver will make some
> of it's own queries to set itself up according to the capabilities of the
DBMS
> it is connected to. The delay seems to be simply waiting for the DBMS to
respond.
> You *should* expect some delay, because unless the machine has more than
one
> CPU, there will be some inevitable delay while the CPU switches from
running
> your Apache system and the DBMS. Depending on how much memory you
> have, and how much other load the box has, the delay might be less or
more.

> Joe Weinstein at BEA

> > What I am doing wrong? Help!

> > Thank you.

 
 
 

1. Slow query execution through JDBC - runs fast in Query Analyzer

Hi Martin,

Was that trace done while SelectMethod was Cursor or Direct? If
SelectMethod was Cursor, what is in a Profiler trace when the SelectMethod
is set to Direct with executeQuery? Can you look at (/add) the Execution
Plan Event Class (in a Profiler trace), along with the Duration column, to
compare a Query Analyzer run with an executeQuery run? If the plans are
different and the parameters (if any) are identical, then I will find a
JDBC person to help us investigate why this is happening.  If the plans and
parameter are identical, then it appears we have network latency (unless
the Duration accounts for most of the time). Can you test using the latest
SQL Server Service Pack? It may turn out that I will need a replayable
repro, in which case we can work offline (and of course publish the useful
results back on the newsgroup when done). Just remove "online." from my
alias.

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

2. ODBC driver for Interbase 4.1

3. Query over JDBC much slower than over SQL*Plus

4. Uhh...

5. Insert into SQL Server slow, slow, slow...

6. How to identify which SQL code is being called?

7. Query (SQL Server 7.0 - Windows 2000 Server) too slow

8. Slow queries using link servers

9. SQL Server 2000 jdbc driver is extremely slow !!!!

10. PL/SQL query using dates and host variables - slow response

11. Help: slow SQL query performance using date range

12. Database updates very slow with JDBC and SQL-Server 7.0