none
Query slower through JDBC than MS SQL Server server management studio RRS feed

  • Question

  • A Particular query I am trying to optimize is much slower through JDBC that through MS SQL Server Management Studio.

    The time in JDBC is about 2.5 seconds and instantaneous in MS SQL Server Management Studio.

    The only difference I can see is the JDBC side uses prepared statements (generated by the ORM we are using).

    Why would this be the case?

    Info regarding my set up:

    [1] Client side:

    1. Which OS platform are you running on? [MAC OS 10.5]
    2. Which JVM are you running on? [1.6]
    3. What is the connection URL in you app? [jdbcTongue Tiedqlserver://<server_host>;DatabaseName=<db_name>;sendStringParametersAsUnicode=true;applicationName=<app_name>]
    4. If client fails to connect, what is the client error messages? [connects fine]
    5. Is the client remote or local to the SQL server machine? [Remote]
    6. Is your client computer in the same domain as the Server computer? [WorkGroup]

     

    [2] Server side:

     

    1. What is the MS SQL version? [SQL Sever 2005]
    2. Does the server start successfully? [YES]
    3. If SQL Server is a named instance, is the SQL browser enabled? [not sure]
    4. What is the account that the SQL Server is running under? [Local System]
    5. Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? [not applicable]
    6. Do you make firewall exception for SQL Browser UDP port 1434? In SQL2000, you still need to make firewall exception for UDP port 1434 in order to support named instance. [not applicable]
    Wednesday, August 20, 2008 8:25 AM

Answers

  •  Peter Vandoros wrote:
    Thanks for your response. It is very much appreciated.

    That doesn't explain why the DB server runs at 100% CPU usage for those 2.5 seconds while it processes the query.

    It takes the same amount of time.

    Unfortunately this particular query is used as part of a "search page" in our application where the user can narrow down the search by entering more search criteria. However, we always limit the search results to 100 rows (SELECT TOP 100 ....).

     This still leads me to believe that the DB server is choosing a different query plan to execute the query when running from JDBC as opposed to running in SQL Server Management Studio.

    Peter,

    When testing in Management Studio, always include

    Code Snippet

    dbcc dropcleanbuffers

    before the query. Otherwise you maybe taking the wrong measurement.

    If you need only 100 rows, why are you returning 2800?  How about Google style paging in search result set?

    The query plans can't possibly be different for SELECT count(*) because it requires table scan.

    To see disk activity apply the option:

    Code Snippet

    set statistics io on

    Are your indexes up-to-date?

    Index rebuild sample:

    Code Snippet

    USE AdventureWorks;
    GO
    ALTER INDEX ALL ON Production.Product
    REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON);

    Are you up-to-date with all service packs, including windows, VS & SQL?

    The fact that you get 100% CPU spin during the JDBC call does not mean the query plan is spinning it up. It can be excessive (mis)communications between server and client.

    You should also turn on SQL Server Profiler to see if it shows any difference in READs for the 2 calls.

    The following article also deals with query optimization:

    http://www.sqlusa.com/articles/query-optimization/



    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Saturday, October 6, 2012 4:33 AM
    Monday, September 8, 2008 5:09 AM

All replies

  •  SQLUSA wrote:

    How about making the query into a stored procedure?

     

    That would make it the same speed.

     

    Let us know if works.

     


    I can't as I don't have control over how the ORM generates the query.

    Thanks for your help.
    Wednesday, August 20, 2008 8:47 AM
  • Can anyone help with the issue?

    Can someone from the JDBC team help please?
    Thursday, August 21, 2008 1:17 AM
  • Hi Peter,

     

    The execution path between JDBC and SQL Server Management Studio application is different so there will be differences in execution time.  Though a 2.5 seconds versus instantaneous is a big difference.  How are you measuring the times?

     

    If you can also mention the query that you are trying to optimize and the driver version, that will be very helpful for us to troubleshoot and continue to improve the product.

     

    Regards,

    Jimmy Wu

    Tuesday, September 2, 2008 8:50 PM
    Moderator
  • Hi Jimmy,

    Thanks for your response.

    I am measuring the times in SQL Server Management Studio by looking at the query execution time in the status bar at the bottom of the query window.

    In my application, I am measuring the query execution time by enabling SQL logging in the ORM we are using (which displays the execution time). 

    I have also executed the generated SQL query using direct JDBC with both 'normal' Statement's and PreparedStatement's and measured the time using System.nanoTime() directly before and after the query execute code. The query is still much slower than SQL Server Management Studio.

    I have tested this using both  JDBC driver versions 1.1 and 1.2. There is no difference in execution time between the driver versions.

    With regards to the query itself, is there a way I can send it to you that isn't publicly viewable? I prefer not to display the query in a public forum.

    Some details of the query are:
    - it selects all 22 columns of the table in question
    - it spans across 6 tables (5 joins)
    - is qualifies against 12 columns spanning those tables (ie. 12 columns in the 'WHERE' clause)

    Thanks

    Peter
    Wednesday, September 3, 2008 1:51 AM
  • Hi Jimmy,

    In addition to the my previous post, I also look at the CPU usage of the DB server while the query is executing. 

    From my application, the CPU stays at 100% usage for the duration of the query.
    From SQL Server Management Studio, the CPU hits !00% usages and then drops almost immediately.

    Thanks

    Peter
    Wednesday, September 3, 2008 1:56 AM
  • Hi Peter,

     

    Is this a forward only read only result set?  Does the query return a large number of rows?  Are the column values large or costly to compute?  Do you see a substantial increase in JVM memory consumption when executing the statement?  If the answer to these questions is 'yes', then performance may be improved by using the adaptive response buffering feature of the v1.2 driver (set responseBuffering=adaptive in the connection string).

     

    Regards,

    --David Olix [SQL Server]

    Thursday, September 4, 2008 6:06 AM
    Moderator
  • Hi David,

    Thanks for your feedback.

    I'm not 100% sure if it is a forward only read only result set as I don't override the default JDBC driver setting. 

    The query I have been testing this with returns 2818 rows.

    I believe the column values aren't expensive to compute as the values aren't manipulated at all, they're just returned as is. They are either BIGINT, INT, NVARCHAR(255), or BIT.

    I don't see a substantial increase in JVM memory consumption. Below are some quick figures gathered directly before and after the query is executed:

    before:
    free memory  : 19726936
    max memory   : 84410368
    total memory : 53469184
    after:
    free memory  : 19444160
    max memory   : 84410368
    total memory : 53469184
    differences:
    free memory  : 282776
    max memory   : 0
    total memory : 0

    Unfortunately, I have tried version 1.2 of the driver with and without responseBuffering=adaptive without any changes.

    I don't believe the issue is with the JVM performing badly but with the way in which the driver communicates with the server. The server is the machine that takes a long time to process the query. 

    At this point I think it might have something to do with the way the server interprets the message received from the JDBC driver that causes the server to not use an optimal query plan.

    Is there a way I see the query plan the server is using for the JDBC call using any of the SQL Server Management Tools?

    Thanks

    Peter
    Thursday, September 4, 2008 6:35 AM
  •  Peter Vandoros wrote:

    The time in JDBC is about 2.5 seconds and instantaneous in MS SQL Server Management Studio.

     

    Peter,

     

    When returning 2800 rows to a "real" client, I am not surprised at the timing you see as opposed to the super fast client Management Studio.

     

    Can you check the following timing?

     

    SELECT count(*)

    FROM......(same query)

     

    You may find that the 2.5 sec is taken up by processing 2800 rows.

     

    Since you cannot turn the queries into stored procedures, you may try to reduce the returned rows by stronger filtering (if you have control over it).

     

    On architectural note, people cannot process 2800 rows in a few minutes. Why is the app returning that many rows?

    Sunday, September 7, 2008 6:12 AM
  • Thanks for your response. It is very much appreciated.

     SQLUSA wrote:

    When returning 2800 rows to a "real" client, I am not surprised at the timing you see as opposed to the super fast client Management Studio.

     


    That doesn't explain why the DB server runs at 100% CPU usage for those 2.5 seconds while it processes the query.


     SQLUSA wrote:

    Can you check the following timing?

     

    SELECT count(*)

    FROM......(same query)

     

    You may find that the 2.5 sec is taken up by processing 2800 rows.

     

    It takes the same amount of time.


     SQLUSA wrote:

    Since you cannot turn the queries into stored procedures, you may try to reduce the returned rows by stronger filtering (if you have control over it).



    Unfortunately this particular query is used as part of a "search page" in our application where the user can narrow down the search by entering more search criteria. However, we always limit the search results to 100 rows (SELECT TOP 100 ....).

     

     SQLUSA wrote:

    On architectural note, people cannot process 2800 rows in a few minutes. Why is the app returning that many rows?


    This is a type of query (see previous comment about the search page) that i need to optimise. I chose this particular query as the baseline to compare against (things like the actual results returned, speed, scalability, etc...). All the 2800 rows aren't actually displayed to the user, only the first 100 rows are. As you can see from my SELECT COUNT(*) .... response above, the query still takes the same amount of time to process so it doesn't make a difference if i returned all 2800 rows, the first 100 rows, or just the count of the rows as the server still takes 2.5 seconds to process the query. This still leads me to believe that the DB server is choosing a different query plan to execute the query when running from JDBC as opposed to running in SQL Server Management Studio.

    Thanks

    Peter
    Monday, September 8, 2008 12:35 AM
  •  Peter Vandoros wrote:
    Thanks for your response. It is very much appreciated.

    That doesn't explain why the DB server runs at 100% CPU usage for those 2.5 seconds while it processes the query.

    It takes the same amount of time.

    Unfortunately this particular query is used as part of a "search page" in our application where the user can narrow down the search by entering more search criteria. However, we always limit the search results to 100 rows (SELECT TOP 100 ....).

     This still leads me to believe that the DB server is choosing a different query plan to execute the query when running from JDBC as opposed to running in SQL Server Management Studio.

    Peter,

    When testing in Management Studio, always include

    Code Snippet

    dbcc dropcleanbuffers

    before the query. Otherwise you maybe taking the wrong measurement.

    If you need only 100 rows, why are you returning 2800?  How about Google style paging in search result set?

    The query plans can't possibly be different for SELECT count(*) because it requires table scan.

    To see disk activity apply the option:

    Code Snippet

    set statistics io on

    Are your indexes up-to-date?

    Index rebuild sample:

    Code Snippet

    USE AdventureWorks;
    GO
    ALTER INDEX ALL ON Production.Product
    REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON);

    Are you up-to-date with all service packs, including windows, VS & SQL?

    The fact that you get 100% CPU spin during the JDBC call does not mean the query plan is spinning it up. It can be excessive (mis)communications between server and client.

    You should also turn on SQL Server Profiler to see if it shows any difference in READs for the 2 calls.

    The following article also deals with query optimization:

    http://www.sqlusa.com/articles/query-optimization/



    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Saturday, October 6, 2012 4:33 AM
    Monday, September 8, 2008 5:09 AM
  • Thank you for your feedback. It has helped me understand why the same query executed from SQL Server Management Studio appeared to be faster than executing it through JDBC.

     SQLUSA wrote:

    When testing in Management Studio, always include

    Code Snippet

    dbcc dropcleanbuffers

     

     

    before the query. Otherwise you maybe taking the wrong measurement.


    I didn't know about this command. This is indeed the actual problem.

    Running "dbcc dropcleanbuffers" before executing the query in SQL Server Management Studio made the query run in the roughly same amount of time as when it's executed through JDBC.

    The good news is this means that there is no issue with the server or my application's communication with the server through JDBC.

    The bad news is that the query isn't executing as fast as hoped. It looks like i have get back to the drawing board and tweak the query further.


     SQLUSA wrote:
     

    If you need only 100 rows, why are you returning 2800?  How about Google style paging in search result set?


    I'm not. I am only returning the first 100 rows using SELECT TOP 100 .... <rest of query>. I assume this instructs the server to only return the first 100 rows.


     SQLUSA wrote:

    The query plans can't possibly be different for SELECT count(*) because it requires table scan.

     


    You are correct. I only tested the SELECT count(*) from within my application. Executing SELECT count(*) in SQL Server Management Studio did indeed take the same amount of time as from my application.


    I greatly appreciate your help with this issue. I am very relieved to find out that it was my query that was performing badly and not with JDBC or the server itself. At least I know what the problem is and am able to "fix" it somehow.


    Thanks again.


    Peter

    Monday, September 8, 2008 5:44 AM
  • Hi again Peter,

     

    I'd like to add one more thing to SQLUSA's responses.  Since you mention that the query was SELECT TOP 100 ... <rest of query> and I thought you had said earlier in the thread that the query returns 2818 rows I have to ask: Do you use the JDBC method Statement.setMaxRows() rather than a TOP <n> clause in the query when executing the query through JDBC?  Statement.setMaxRows() uses SET ROWCOUNT rather than TOP <n> to limit the number of rows returned by the query.  SET ROWCOUNT <n> is not as efficient as TOP <n> because it causes SQL Server to generate all of the rows and then discard those beyond the first <n> in the response.  By contrast, TOP <n> causes SQL Server to avoid generating more than <n> rows in the first place.  In other words, if the app (or ORM) uses TOP <n> in the query rather than Statement.setMaxRows() calls to the JDBC driver, performance shouldn't be that much different from Management Studio, even if you didn't do a dbcc dropcleanbuffers first.

     

    Regards,

    --David Olix [SQL Server]

    Friday, September 12, 2008 12:22 AM
    Moderator
  • Hi David,

    Thanks for your response.

    To clarify, I use SELECT TOP 100 in my application, but I wasn't using that for the testing I was comparing execution times in SQL Server Management Studio to JDBC.

    So to be clear, my application uses SELECT TOP 100 ....

    The testing I was performing didn't use SELECT TOP 100 ...

    In terms of the difference between Management Studio and JDBC, I believe the first time I execute the query in Management Studio it indeed does take approximately the same amount of time as from JDBC. However, subsequent executions in Management Studio completed almost immediately, prompting this forum thread. The query consistently executes in approximately the same amount of time in Management Studio as from JDBC if I execute dbcc dropcleanbuffers before executing the query.

    If you are saying that there shouldn't be a difference in the amount of time the query takes to execute when I use SELECT TOP 100 and don't execute dbcc dropcleanbuffers to JDBC, then what do you suggest I do to get to the bottom of this issue?

    Regards

    Peter
    Friday, September 12, 2008 12:59 AM
  • Often times, these issues can be addressed by looking at the actual query execution plan.  Do you see different execution plans in both scenarios?

     

    Running a Profiler trace with the ShowPlanAll event turned on is an easy way to compare the execution plans.  If you see anything obviously different, please let us know on this thread.  However, comparing execution plans in this forum is likely to be pretty tough.  Unless something is obvious, you may be better served opening up a support incident with our Support team.

     

    Tuesday, September 23, 2008 1:56 AM
  • I have run SQLProfiler trace with the ShowPlanAll event turned and the only obvious difference is when the exact same query is run from my application using JDBC, the query plan has an extra 4 steps at the start compared to the query plan when run from management studio. The rest of the plan is the same.

    The extra 4 steps are:

    Clustered Index Insert(OBJECTSadCWT), SETSad[STREAM].[COLUMN0] = [db_name].[dbo].[table_name].[table_column_name] as [t0].[table_column_name],[STREAM].[COLUMN1] .... (continues for each column in the table)
      |--Sequence Project(DEFINESad[I4Rank1012]=i4_row_number))
           |--Compute Scalar(DEFINESad[Expr1022]=(1)))
                |--Segment

    If you need more information from the query plans, let me know.

    Thanks

    Peter
    Tuesday, September 23, 2008 3:01 AM
  • Peter,

     

    Based on your response it looks like we are going to need to get the full Profiler traces, etc. from you.  Unfortunately, that is going to be almost impossible in this forum.  If you really want to pursue this issue, I would strongly recommend you open up a support case with us.  If you do, please post back to this forum and I will take ownership of your case myself.

     

    Evan

    Tuesday, September 23, 2008 9:22 PM