Hello,
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 Query
Analyzer?
Thank you,
--
Mitramitra,
Something called my attention, why is sql server using "convert" in the
execution plan?
> WHERE:(Convert([smtp_mail].[pli_id])=[@.P0])
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:
> 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.0383192 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:
> > Hello,
> >
> > 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 Query
> > Analyzer?
> >
> > Thank you,
> > --
> > Mitra|||How are you executing this statement from your client app?
- Are you constructing the statement dinamically and sending it to sql server?
- Are you executing a stored procedure that expect some parameters?
AMB
"mitra" wrote:
> Hello,
> 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 Query
> Analyzer?
> Thank you,
> --
> Mitra|||Also,
> > java application vs. an Index Seek running the same query in SQL Server
using which index?
> > Select col_A from tbl_name WHERE col_B like 'string'
are you using wildcard characters in the string used with the like operator?
Can you post both execution plans?
AMB
"Alejandro Mesa" wrote:
> How are you executing this statement from your client app?
> - Are you constructing the statement dinamically and sending it to sql server?
> - Are you executing a stored procedure that expect some parameters?
>
> AMB
> "mitra" wrote:
> > Hello,
> >
> > 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 Query
> > Analyzer?
> >
> > Thank you,
> > --
> > Mitra|||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.0383192 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:
> Hello,
> 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 Query
> Analyzer?
> Thank you,
> --
> Mitra|||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. This
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:
> mitra,
> Something called my attention, why is sql server using "convert" in the
> execution plan?
> > WHERE:(Convert([smtp_mail].[pli_id])=[@.P0])
> 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:
> > 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.0383192 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:
> >
> > > Hello,
> > >
> > > 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 Query
> > > Analyzer?
> > >
> > > Thank you,
> > > --
> > > Mitra|||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...
> 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.0383192 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:
>> Hello,
>> 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
>> Query
>> Analyzer?
>> Thank you,
>> --
>> Mitra|||This is a multi-part message in MIME format.
--010607080102080004040000
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
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. This
>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!
>
--010607080102080004040000
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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.<br>
<br>
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).<br>
<br>
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.<br>
<br>
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.<br>
<br>
Hope this helps.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
mitra wrote:
<blockquote cite="mid3AD861E2-2627-412A-BBD5-3E3A173C8D3D@.microsoft.com"
type="cite">
<pre wrap="">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. This
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!
</pre>
</blockquote>
</body>
</html>
--010607080102080004040000--|||This is a multi-part message in MIME format.
--080701050701000804060200
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
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/jdbc_drivers/mssqlserver.html
Hope this helps.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Mike Hodgson 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. This
>>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!
>>
--080701050701000804060200
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>It's part of the connection string. The parameter is <b>SendStringParametersAsUnicode</b>
and it's true by default.<br>
</tt>
<blockquote><tt>Connection Parameters:</tt><br>
<br>
<tt>SendStringParametersAsUnicode</tt><br>
<br>
<tt>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.<br>
</tt></blockquote>
<tt>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):<br>
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://e-docs.bea.com/wls/docs81/jdbc_drivers/mssqlserver.html</a><br>">http://e-docs.bea.com/wls/docs81/jdbc_drivers/mssqlserver.html">http://e-docs.bea.com/wls/docs81/jdbc_drivers/mssqlserver.html</a><br>
<br>
Hope this helps.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Mike Hodgson wrote:
<blockquote cite="midOUsRAz3sFHA.664@.tk2msftngp13.phx.gbl" type="cite">
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<tt>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.<br>
<br>
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).<br>
<br>
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.<br>
<br>
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.<br>
<br>
Hope this helps.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
mitra wrote:
<blockquote
cite="mid3AD861E2-2627-412A-BBD5-3E3A173C8D3D@.microsoft.com"
type="cite">
<pre wrap="">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. This
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!
</pre>
</blockquote>
</blockquote>
</body>
</html>
--080701050701000804060200--|||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/jdbc_drivers/mssqlserver.html
> Hope this helps.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Mike Hodgson 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. This
> >>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!
> >>
> >>
>sql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment