The execution plan captured by SQL Profiler shows us that the SQL Server is
doing an Index Scan on this particular select statement running through our
java application:
Select col_A from tbl_name WHERE col_B like 'string'
col_A is an identity col, PK , and is clustered index.
col_B is a unique constraint non clustered index.
However, when the same select statement is executed using SQL Server Query
Analyzer, the execution plan shows us that the SQL Server did an Index Seek.
Any idea why we are experiencing an Index Scan running the query through our
java application vs. an Index Seek running the same query in SQL Server Quer
y
Analyzer?
Thank you,
--
MitraAlejandro,
I appreciate for pointing out the "convert" and questioning why sql server
is using it.
We looked into our code and confirmed that our query did not include
"convert". We added "convert" to our query and ran it in Query Analyzer. Thi
s
time Sql Server did an Index Scan.
Obvioulsy, jdbc driver (jTDS) is doing the convert, why we don't know. Any
Idea?
The column data type is char(60).
We thought of testing the jdbc driver by changing the data type char(60) to
Varchar(60) and see if it would do the convert again. It did not!
Is there a way that we could enforce jdbc driver not to pass the select
statement with convert? Currently, we are reluctant to make any schema
changes.
Again I appreciate your prompt help.
Thank you so much!
--
Mitra
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> mitra,
> Something called my attention, why is sql server using "convert" in the
> execution plan?
>
> It seems that the data type of the parameter is diff than the data type of
> column [smtp_mail] and it has greater precedence.
> What is the datatype of column [smtp_mail]?
>
> AMB
> "mitra" wrote:
>|||On Tue, 6 Sep 2005 17:19:43 -0700, "mitra"
<mitra@.discussions.microsoft.com> wrote:
>Is there a way that we could enforce jdbc driver not to pass the select
>statement with convert? Currently, we are reluctant to make any schema
>changes.
Wrapping the SQL in an SP, where you can specify the types of the
parameters, is often helpful.
J.|||You are not setting the size of the parameter so SQL Server will make a best
attempt. I don't know much about Java but I would be very suppressed to
find that it doesn't allow for you to specify a datatype and size other than
a String.
Andrew J. Kelly SQL MVP
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:BC5C89FB-E999-4ACA-B498-A0684EC3C42C@.microsoft.com...[vbcol=seagreen]
> The query was done via jdbc using a prepared statement. The select
> statement
> is very simple -
> PreparedStatement stmt = conn.prepareStatment("SELECT id from tablex where
> colA = ?");
> stmt.setString(1, "abcdedfg");
> ResultSet rslt = stmt.executeQuery();
> (There is no "LIKE" clause)
> Both execution plans follow. The SQL Profiler first showing the index scan
> and the Query Analyzer second showing the index seek.
> NOTE: These particular samples came from two different databases however
> we
> are getting the same result when the queries are run against the same
> database..
> ==============================
> SQL Server Profiler
> ===============================
> Rows Executes StmtText
> StmtId NodeId Parent PhysicalOp
> LogicalOp Argument
> DefinedValues EstimateRows EstimateIO EstimateCPU
> AvgRowSize TotalSubtreeCost OutputList
> Warnings
> Type Parallel EstimateExecutions
> -- -- --
> -- -- -- --
> -- --
> -- -- -- --
> -- -- -- --
-
> -- -- --
> 1 1 Filter(WHERE
Convert([smtp_mail].[pli_id])=[@.P0]))
> 0 1 Filter
> Filter
> WHERE
Convert([smtp_mail].[pli_id])=[@.P0])> 41.7855 0 8.41E-005 89
> 0.0386413 [smtp_mail].[id]
> PLAN_ROW
> 0 1
> 147 1 |--Index
> Scan(OBJECT
[SecurWrap].[SecurityServer].[smtp_mail].[ux_smtp_mail])) 0
> 2 1 Index Scan Index Scan
> OBJECT
[SecurWrap].[SecurityServer].[smtp_mail].[ux_smtp_mail])
> [smtp_mail].[pli_id], [smtp_mail].[id] 145 0.0383
192 0.000238
> 89
> 0.0385572 [smtp_mail].[pli_id], [smtp_mail].[
;id]
> PLAN_ROW 0 1
> ========================
> SQL Server Query Analyze
> =======================
> Rows Executes StmtText
> StmtId
> NodeId Parent PhysicalOp LogicalOp Argument
> DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize
> TotalSubtreeCost OutputList Warnings Type Parallel
> EstimateExecutions
> -- -- --
> --
> -- -- -- -- --
> -- -- -- -- --
> -- -- -- -- --
> --
> 1 1 Index
> Seek(OBJECT
[stress].[SecurityServer].[smtp_mail].[ux_smtp_mail]),
> SEEK
[smtp_mail].[pli_id]=[@.1]) ORDERED FORWARD) 0 1> Index Seek Index Seek
> OBJECT
[stress].[SecurityServer].[smtp_mail].[ux_smtp_mail]),
> SEEK
[smtp_mail].[pli_id]=[@.1]) ORDERED FORWARD [smtp_mail].[id] 1
> 0.00320343 7.96E-005 11 0.00328303 [smtp_mail].[id
]
> PLAN_ROW 0 1
>
> Thank you for your prompt response!
> --
> Mitra
>
> "mitra" wrote:
>|||Interesting problem. Some developers where I work had a similar issue -
fairly well indexed tables but all of their queries coming through JDBC
from their Java app were doing clustered index scans (not using the
indexes essentially) and thrashing the life out of the processors in the
box, resulting in really bad performance obviously.
As a DBA it was fairly puzzling to me because the schema all looked
fairly nice (including their indexes, although I suggested a few more
given their workload) and their queries logically should have worked
fine and in QA they did. In the end it came down to the fact that the
JDBC driver was implicitly converting all of their text data to Unicode,
but the underlying data was all non-unicode (varchar, char & text), so
SQL Server was implicitly converting the underlying data to unicode
during the plan compilation phase because all the unicode datatypes
(nvarchar, nchar & ntext) have a higher precedence than their respective
non-unicode datatypes. That meant it couldn't use the indexes defined
on the string data because the implicit conversion made the expressions
non-SARGable (I think, extrapolating, that was the basic issue).
Anyway, the developers finally discovered this from the client side
(well, middle tier actually) and changed some setting on the JDBC driver
and it all suddenly started working perfectly. I doubt I would have
ever figured that one out, as I only had access to the DB & what I could
glean from profiler, and not the middle tier drivers or client-side code.
I've left some voicemail for the dev guy, who found the problem and
changed the setting, to find out what setting it was. If he gets back
to me about it then I'll post the JDBC driver setting/switch.
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
mitra wrote:
>Alejandro,
>I appreciate for pointing out the "convert" and questioning why sql server
>is using it.
>We looked into our code and confirmed that our query did not include
>"convert". We added "convert" to our query and ran it in Query Analyzer. Th
is
>time Sql Server did an Index Scan.
>Obvioulsy, jdbc driver (jTDS) is doing the convert, why we don't know. Any
>Idea?
>The column data type is char(60).
>We thought of testing the jdbc driver by changing the data type char(60) to
>Varchar(60) and see if it would do the convert again. It did not!
>Is there a way that we could enforce jdbc driver not to pass the select
>statement with convert? Currently, we are reluctant to make any schema
>changes.
>Again I appreciate your prompt help.
>Thank you so much!
>|||It's part of the connection string. The parameter is
*SendStringParametersAsUnicode* and it's true by default.
Connection Parameters:
SendStringParametersAsUnicode
Determines whether string parameters are sent to the SQL Server
database in Unicode or in the default character encoding of the
database. True means that string parameters are sent to SQL Server
in Unicode. False means that they are sent in the default encoding,
which can improve performance because the server does not need to
convert Unicode characters to the default encoding. You should,
however, use default encoding only if the parameter string data that
you specify is consistent with the default encoding of the database.
Default value is true.
Apparently it's documented in the documentation that comes with the
Microsoft JDBC driver (I haven't played with MS-JDBC myself). Here's a
pretty good page (on the WebLogic website) that talks about it (I can't
readily find an official Microsoft page outlining the JDBC connection
parameters):
http://e-docs.bea.com/wls/docs81/jd...ssqlserver.html
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Mike Hodgson wrote:
[vbcol=seagreen]
> Interesting problem. Some developers where I work had a similar issue
> - fairly well indexed tables but all of their queries coming through
> JDBC from their Java app were doing clustered index scans (not using
> the indexes essentially) and thrashing the life out of the processors
> in the box, resulting in really bad performance obviously.
> As a DBA it was fairly puzzling to me because the schema all looked
> fairly nice (including their indexes, although I suggested a few more
> given their workload) and their queries logically should have worked
> fine and in QA they did. In the end it came down to the fact that the
> JDBC driver was implicitly converting all of their text data to
> Unicode, but the underlying data was all non-unicode (varchar, char &
> text), so SQL Server was implicitly converting the underlying data to
> unicode during the plan compilation phase because all the unicode
> datatypes (nvarchar, nchar & ntext) have a higher precedence than
> their respective non-unicode datatypes. That meant it couldn't use
> the indexes defined on the string data because the implicit conversion
> made the expressions non-SARGable (I think, extrapolating, that was
> the basic issue).
> Anyway, the developers finally discovered this from the client side
> (well, middle tier actually) and changed some setting on the JDBC
> driver and it all suddenly started working perfectly. I doubt I would
> have ever figured that one out, as I only had access to the DB & what
> I could glean from profiler, and not the middle tier drivers or
> client-side code.
> I've left some voicemail for the dev guy, who found the problem and
> changed the setting, to find out what setting it was. If he gets back
> to me about it then I'll post the JDBC driver setting/switch.
> Hope this helps.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> mitra wrote:
>|||Mike,
Thanks a lot for sharing that with the group.
Regards,
Alejandro Mesa
"Mike Hodgson" wrote:
> It's part of the connection string. The parameter is
> *SendStringParametersAsUnicode* and it's true by default.
> Connection Parameters:
> SendStringParametersAsUnicode
> Determines whether string parameters are sent to the SQL Server
> database in Unicode or in the default character encoding of the
> database. True means that string parameters are sent to SQL Server
> in Unicode. False means that they are sent in the default encoding,
> which can improve performance because the server does not need to
> convert Unicode characters to the default encoding. You should,
> however, use default encoding only if the parameter string data that
> you specify is consistent with the default encoding of the database.
> Default value is true.
> Apparently it's documented in the documentation that comes with the
> Microsoft JDBC driver (I haven't played with MS-JDBC myself). Here's a
> pretty good page (on the WebLogic website) that talks about it (I can't
> readily find an official Microsoft page outlining the JDBC connection
> parameters):
> http://e-docs.bea.com/wls/docs81/jd...ssqlserver.html
> Hope this helps.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Mike Hodgson wrote:
>
>
No comments:
Post a Comment