SQL Server Performance

Bad performance with perpared statement

Discussion in 'Performance Tuning for DBAs' started by JD24, Sep 19, 2007.

  1. JD24 New Member

    Hello,i have a weird problem regarding SQL performance.
    I have a table 'tab_rep_4' with 29 columns with 3 indices on
    - tab_rep4.row_number
    - tab_rep4.report_version
    - tab_rep4.Field2
    (The table has about 2 million rows)
    I'm also using Java to connect to the SQL Server via a JDBC driver.
    Currently i'm using prepared statements for the query andthe profiler is giving
    me the following results
    declare @P1 int
    exec sp_prepare @P1 output, N'@P0 varchar(8000)', N' select TOP 200 tab_rep_4.row_number,tab_rep_4.report_version,tab_rep_4.Field1,tab_rep_4.Field2,tab_rep_4.Field3,tab_rep_4.Field4,tab_rep_4.Field5,tab_rep_4.Field6,tab_rep_4.Field7,tab_rep_4.Field8,tab_rep_4.Field9,tab_rep_4.Field10,tab_rep_4.Field11,tab_rep_4.Field12,tab_rep_4.Field13,tab_rep_4.Field14,tab_rep_4.Field15,tab_rep_4.Field16,tab_rep_4.Field17,tab_rep_4.Field18,tab_rep_4.Field19,tab_rep_4.Field20,tab_rep_4.Field21,tab_rep_4.Field22,tab_rep_4.Field23,tab_rep_4.Field24,tab_rep_4.Field25,tab_rep_4.Field26,tab_rep_4.Field27,tab_rep_4.Field28,tab_rep_4.Field29 from tab_rep_4 where tab_rep_4.Field2 LIKE @P0 order by tab_rep_4.row_number', 1
    select @P1
    exec sp_execute @P1, '17381'

    The query takes very long with a lot of 'I/O' (reads).The Query Analyzer shows me 100% costs at 'bookmark lookup'.
    But if i'm entering the following query in the Query Analyzer, it execute much faster than with the above query.
    select TOP 200 tab_rep_4.row_number,tab_rep_4.report_version,tab_rep_4.Field1,tab_rep_4.Field2,tab_rep_4.Field3,tab_rep_4.Field4,tab_rep_4.Field5,tab_rep_4.Field6,tab_rep_4.Field7,tab_rep_4.Field8,tab_rep_4.Field9,tab_rep_4.Field10,tab_rep_4.Field11,tab_rep_4.Field12,tab_rep_4.Field13,tab_rep_4.Field14,tab_rep_4.Field15,tab_rep_4.Field16,tab_rep_4.Field17,tab_rep_4.Field18,tab_rep_4.Field19,tab_rep_4.Field20,tab_rep_4.Field21,tab_rep_4.Field22,tab_rep_4.Field23,tab_rep_4.Field24,tab_rep_4.Field25,tab_rep_4.Field26,tab_rep_4.Field27,tab_rep_4.Field28,tab_rep_4.Field29 from tab_rep_4 where tab_rep_4.Field2 LIKE '17379' order by tab_rep_4.row_number
    This query is much faster with lesser 'I/O'.
    Can somebody explain the problem here to me?What should i do? I thought prepared statement should always be prefered?
    Thank you!
    JD
  2. martins New Member

    Hi,
    I don't know too much about the JDBC driver and how it works, but my take on it is that SQL Server is not able to compile the appropriate execution plan from your query and hence the poor performance.
    I have also noticed that you use the "LIKE" operator, but without any wildcard characters. Is this correct? If there are not going to be any wildcard characters in your parameter, then you should rather use "=". It is quite correct that Query Analyzer shows 100% for the "bookmark lookup", and unless all the fields returned in your qurey is included in an index (which is probably impossible), a bookmark lookup will always be done to find the data from the actual data pages.
    Something else you might also want to have a look at is how much time the actual query takes via JDBC (use profiler), and how much data your application is sending back and forth. This might also be the cause for poor performance. Also check the length of your parameter. You have it as varchar(8000) at the moment, and I might be completely wrong (not knowing how JDBC works) but it might make a difference if you shorten this to just what is needed (especially if the length of "Field2" is not 8000 in your db).
    Hope this helps...or at least gets you going in the right direction.
  3. JD24 New Member

    Hi martins,
    thanks for the quick reply!
    Your explanation is very detailed and helpfull.
    Actually the profiler shows me for the first Query:
    4000 ms with over 1.5 million reads
    for the second:
    0 ms with 157 reads
    How come there is such a big difference?
    The 'like' and '=' thing i do also understand now and in this case it really works.
    But here i have a counter example:
    Example 1:
    declare @P1 int
    exec sp_prepare @P1 output, N'@P0 varchar(8000)', N' select TOP 200 ... where cic_rep_5.Field2 LIKE @P0 order by cic_rep_5.row_number', 1
    select @P1
    exec sp_execute @P1, '1737_'

    This query took about >2 minutes and over 6 million reads (table with 3.5 million rows)
    Example 2:
    declare @P1 int
    exec sp_prepare @P1 output, N'', N' select TOP 200 tab_rep_5.row_number ... from tab_rep_5 where tab_rep_5.Field2 LIKE ''1737_'' order by tab_rep_5.row_number', 1
    select @P1
    exec sp_execute @P1

    This query took only <1 seconds and <200 reads

    The varchar(8000) doesn't have any effects on those cases.
    Do you have any idea why there is such a big difference?
    Thank you!



  4. martins New Member

    The difference here is that in the first example you have a parameter you pass into the query, while in the second you have it hard-coded. I'm going to try and explain it the clearest possible way...hope it makes sense [:)]
    Consider the following:
    In the case of the first query, SQL Server does not know what the value will be it would have to search for in Field2. With using the "LIKE" operator, you could have passed a '%' which would have returned all rows. SQL Server's optimizer will establish what the best execution plan would be for your query (by looking at the query as is), without knowing what the parameter is going to be. Based on the wide range of possibilities it then decides to scan the whole table rather than using your index. That is why you see the millions of reads
    In the second query you have hardcoded the value of Field2 in yuor query, therefore the optimizer already knows the value and decides subsequently to use the index (which is what you want).
    You will find the same kind of thing because SQL Server will only decide on an execution plan when you run the query. I hope all this makes sense so far.
    Now, let's consider some solutions:
    1. You can force the query to use your index by using the index hint. Your query will look something like ....from tab_rep_5 (with index=<your_index_name>)...
    2. Create a stored procedure that receives a parameter, and then call the stored proc (with the parameter as you do now) from your application. The advantage is that the stored proc is compiled by SQL Server, and the execution plan saved. You can check the execution plan in Query Analyzer to ensure that it uses the index. If not (it happens sometimes), then you are forced to either use option 1 above or to reconsider your indexing strategy.
    I would personally go for option 2. These 3rd party ODBC's don't always work best, especially when you want to improve performance. Rather utilize stored procs which are easier to maintain and better in terms of performance.
  5. JD24 New Member

    Hello martins,
    thank you for your very detailed explanation. That helped me a lot.
    I did try out some queries and now it seems that most of my problems are solved.
    I really appreciate you help [:)]

    Regards,
    JD




Share This Page