June 3, 2004 at 11:27 am
Ran into this issue where the clause having " a.company_name like N'abc%' " takes a lot of time but " a.company_name like 'abc%' " returns fast. The JDBC driver inserts the unicode prefix. Plan has convert() function in the sort but cost of 0% ( estimate so..). table has 1.2 million rows. Anyone have any authoritative answers before i change the column to nvarchar() instead of varchar(). Also what are the implications of having nvarchar() for company_name ? It seems like it may not be able to turn off unicode in the JDBC driver!
Any t-sql perf experts out there
thx
-srini
June 3, 2004 at 11:33 am
How about changing the search to a proc and just passing in the parameter?
June 4, 2004 at 2:38 am
other than the main one of a doubling of the storage capacity for companyname....and the consequent reduction in total capacity for storage on a row....there is v.little operational difference between the use of nvarchar and varchar.....except all queries referencing the field need to have the 'N' placed before any constants.
Amending the application code to call a SP is the best advice.....regardless of the particular issue you are currently facing.
June 6, 2004 at 8:47 pm
LIKE supports ASCII pattern matching and Unicode pattern matching. When all arguments (match_expression, pattern, and escape_character, if present) are ASCII character data types, ASCII pattern matching is performed. If any of the arguments are of Unicode data type, all arguments are converted to Unicode and Unicode pattern matching is performed. When you use Unicode data (nchar or nvarchar data types) with LIKE, trailing blanks are significant; however, for non-Unicode data, trailing blanks are not significant. Unicode LIKE is compatible with the SQL-92 standard. ASCII LIKE is compatible with earlier versions of SQL Server.
Read bout Nchar and Nvarchar in BOL. Better to have an index on this field if there is no.
If u go through the above u can imagine why it is taking long time.
when you perform Ascii comparision there 2^7 chances for each charecter to compare for Unicode it will become 2^15. Let me know if I am wrong.
June 14, 2004 at 12:47 pm
Folks,
Thanks for your response. We turned off unicode in JDBC and the search string is "company_name like 'abc%' " . It works great now. Here is the link for those who are interested, others have had this problem as well.
The issue is that in EJB /Java App Server situations, the App server automatically generates SQL. Not an easy task to simply call stored procedures. DotNet fans will know that MSFT will have this in Objectspaces ( part of whidbey i think)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply