September 16, 2009 at 10:06 pm
Why does the Unicode syntax statement take 6 secs to return the results vs 0 secs for the sql query without the unicode prefix? This query is a simple one. The Vendor and our own DBA's don't have an answer. The vendor application is now Unicode compliant and all sql statements coming from the application are unicode formatted and performance just went into the tank................ This is one statement pulled from a profiler trace with the following results:
CPUReadsWritesDuration
60942599206090820
SELECT COUNT ( * ) FROM t_owner_inventory WHERE owner_id = N'FAC' AND item_number = N'4P16420000010' AND location_id = N'25053825'
VS.
SELECT COUNT ( * ) FROM t_owner_inventory WHERE owner_id = 'FAC' AND item_number = '4P16420000010' AND location_id = '25053825'
# of rows in the table t_owner_inventory
5,187,077
Database collation: Latin1_General_BIN
SQL Server 2005
16215(mb) Memory
4 Xeon Processors
CREATE TABLE [dbo].[t_owner_inventory](
[owner_id] [varchar](20) NOT NULL,
[wh_id] [varchar](10) NOT NULL,
[item_number] [varchar](30) NOT NULL,
[location_id] [varchar](10) NOT NULL,
CONSTRAINT [PK_t_owner_inventory] PRIMARY KEY CLUSTERED
(
[owner_id] ASC,
[wh_id] ASC,
[item_number] ASC,
[location_id] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
September 16, 2009 at 10:33 pm
Unless I'm missing something, you have a column defined as varchar - and you are passing in nvarchar data types. If you view the execution plans, I would bet that you'll see a difference where the query with the varchar parameters is using indexes (seek or even index scan), whereas the nvarchar query is performing full table scans.
Just a guess though - so review the execution plans and verify the actual differences.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 17, 2009 at 8:58 am
Since your columns are defined as non-unicode then when you provide unicode values to the where clause SQL Server has to do an implicit conversion which is likely forcing scans instead of seeks you may be getting when passing the correct data type.
As Jeffrey has said, check the execution plans.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 17, 2009 at 9:08 am
The difference in the execution plans is the use of the Conver_Implicit on the Unicode query.
They are both using Index Scans, It is the Conver_Implicit that is consuming the CPU and Time.
September 17, 2009 at 9:18 am
I didn't take the time originally to examine your table structure, but if you were to re-arrange the order of the key columns in your primary key to put wh_id last the query using the correct data type would give you a seek. The optimizer cannot choose a seek operation because statistics for indexes work from left to right so as soon as there is not a use of a column in the index key the optimizer in effect short circuits and chooses a scan. I'd recommend that you read this blog post[/url] by Gail Shaw about index selectivity and column order.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 17, 2009 at 9:32 am
That's a great point Jeff ! I will make that correction.
Both query plans actually are using Index Seeks, the Unicode is doing a clusterd Seek. My bad for miss reading or typing.
I am assuming that if the application is now formatting all sql statement in this manner, that short of converting all of our table columns from varchar to nvarchar, sql will continue to use the Convert_Implicit when it encounters the Unicode statements, which is degrading our performance.
Will need to go back to the Software Vendor to have them turn this formatting off.
September 17, 2009 at 9:36 am
Are there other indexes on the table which you did not include?
Yes the only options you have is to either change your columns to unicode or have the vendor use correct data types when querying SQL Server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 17, 2009 at 9:43 am
Yes, there is another index that got missed when I did the cut and paste as it was off the window along with the grant select statements.
And if the primary key was changed we would nolonger need the other index. We would gain a little less overhead on index space.
September 17, 2009 at 9:46 am
Was hoping that there was a SQL Database option to ignore Unicode formatted statements.
September 17, 2009 at 10:15 am
scott.lothert (9/17/2009)
That's a great point Jeff ! I will make that correction.Both query plans actually are using Index Seeks, the Unicode is doing a clusterd Seek. My bad for miss reading or typing.
I am assuming that if the application is now formatting all sql statement in this manner, that short of converting all of our table columns from varchar to nvarchar, sql will continue to use the Convert_Implicit when it encounters the Unicode statements, which is degrading our performance.
Will need to go back to the Software Vendor to have them turn this formatting off.
I know you meant Jack on this 😛
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply