Poorly Performing Unicode Query

The main performance issue is performaing the "N" function infront of the strings in the where clause. I get 20x-45x perfromance boost by removing it, but unfortunately my reporting tool doesn't allow me that option.

Anything I can do on the DB end to solve this? The DB is not unicode.

tia,

paul

SELECT

MAX(Dim9s.FunctionDescription) [Dim9s_FunctionDescription],

MAX(Dim9s.BU_Description) [Dim9s_BU_Description],

SUM(convert(decimal(18,2),ixvTransAx.GLS_AMOUNTMST)) [GLS_AMOUNTMST]

FROM ixvLedgerBalanceDimTransAx AS ixvTransAx

JOIN ixvDim9s AS Dim9s ON (ixvTransAx.Dim9Id=Dim9s.Id)

LEFT OUTER JOIN ixvAccounts AS Accounts ON (ixvTransAx.AccountId=Accounts.Id)

JOIN ixvCompany AS CompanyId ON (ixvTransAx.CompanyId=CompanyId.Id)

WHERE 1=1

AND ((1=1)) AND ((Dim9s.Client ='M99') OR Dim9s.Client IS NULL)

AND ((1=1)) AND ((Accounts.Client ='M99') OR Accounts.Client IS NULL)

AND ((Accounts.Code BETWEEN N' 5000' AND N' 6796') AND (Accounts.ACCOUNTPLTYPE != N'^AC_Total^'))

AND ((Dim9s.BU_Description = N'PI/Dwights') AND (Dim9s.OrganizationalViewDescription = N'2006 Current'))

AND ((1=1))

AND (CompanyId.Code = N'56')

AND ((ixvTransAx.TransDate >= '2006-3-1') AND (ixvTransAx.TransDate < '2006-4-1'))

AND NOT (ixvTransAx.GLS_AMOUNTMST IS NULL)

GROUP BY Dim9s.FunctionDescription, Dim9s.BU_Description

Hi,

I recommend that you post your question on the Transact-SQL forum. The Documentation forum is usually not the best spot for code-specific questions.

Regards,

Gail

Please help: how to convert database to unicode?

I already posted this question some time ago but I didn't get any answer, so please forgive for posting it again.

We plan to support Unicode in our next software version. This means that we will need to upgrade many (> 200) databases from our customers from single byte collation to Unicode. Is there an easy way to do this with SQL Server?

With Oracle we can dump the current database, create a new instance that supports Unicode and then import the dump and all char fields get automatically converted to unicode.

As I see it, it doesn't seem to be so easy with SQL Server. At the moment my solution would be to create a new database schema with all the columns changed to the N types (nvarchar, ntext, ...) and then use a DTS Package to transfer the "old" data to the new database. This seems like a lot of work, so that I would like to know if someone has a better idea.

Thank you in advance for your ideas!!

Raul

Im not sure but try changing the collation type. It should change the underlying data to the new collation type.. jst a thought.

with smiles

santhosh

|||Raul

If you are merely changing columns from CHAR to NCHAR and VARCHAR to NVARCHAR, you should be able to just perform ALTER TABLE ALTER COLUMN statements to change the columns to unicode.

CREATE TABLE dbo.UTest
( C CHAR(10) NOT NULL,
VC VARCHAR(10) NOT NULL
)
GO
INSERT INTO dbo.UTest(C, VC) VALUES ('Test', 'Test')
GO
ALTER TABLE dbo.UTest
ALTER COLUMN C NCHAR(10) NOT NULL
GO
ALTER TABLE dbo.UTest
ALTER COLUMN VC NVARCHAR(10) NOT NULL
GO
SELECT * FROM dbo.UTest