March 18, 2010 at 5:43 am
Hello,
I have 2 SQL 2005 servers with the same sp installed on them.
Server A has a collation SQL_Latin1_General_CP1_CI_AS.
Server B has a collation French_CI_AS
Every configuration option is the same on both servers except "common criteria compliance enabled" that is not visible on server B (French) with this query :
SELECT * FROM sys.configurations
The same database with collation SQL_Latin1_General_CP1_CI_AS is installed on both servers.
The 2 databases contains exactly the same data.
My problem is when I run the following query to find tuples on server A, the result is correct, but on server B, the result is wrong:
;WITH [TmpDoublons] AS
(SELECT ROW_NUMBER() OVER (ORDER BY numero ASC) AS ROWID, numero_avis FROM TmpImportAvis)
SELECT * FROM TmpDoublons
WHERE TmpDoublons.ROWID > ANY (SELECT ROWID
FROM [TmpDoublons] T2
WHERE TmpDoublons.ROWID <> T2.ROWID
AND TmpDoublons.numero_avis = T2.numero_avis)
Another problem is when I try to insert 8000 rows into a table on Server A, it takes 3 seconds, but it takes 39 minutes on server B !!!
The destination table has a primary key of type Int which is identity.
If I remove the constraint of primary key on server B, the insert query is as fast as on server A.
Can anybody help me to make these queries working on server B without changing the server collation ?
I have already tried to change the database collation to FRENCH_CI_AS, but didn't solve my issue.
Thanks in advance.
Regards,
Philippe.
March 18, 2010 at 6:58 am
Had to guess at the data types a bit:
WITH TmpDoublons (ROWID, numero_avis)
AS (
SELECT ROW_NUMBER()
OVER (
ORDER BY numero ASC),
numero_avis COLLATE SQL_Latin1_General_CP1_CI_AS
FROM TmpImportAvis
)
SELECT *
FROM TmpDoublons
WHERE TmpDoublons.ROWID > ANY
(
SELECT ROWID
FROM TmpDoublons T2
WHERE TmpDoublons.ROWID <> T2.ROWID
AND TmpDoublons.numero_avis COLLATE SQL_Latin1_General_CP1_CI_AS =
T2.numero_avis COLLATE SQL_Latin1_General_CP1_CI_AS
);
For a fuller answer, please post the CREATE TABLE and CREATE INDEX statements for the tables concerned, some sample data INSERT statements, and the expected output.
If you are able to post the query plans (right-click on the graphical plan and save the .sqlplan file) for the two runs on servers A and B, that would be great. Plans from an actual execution are much more useful than estimated plans by the way.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 18, 2010 at 7:43 am
Yes collate clause is required if user database collation is different than server collation.
Tempdb database follows the same collation as server collation. If some query operation uses tempdb then.. collate clause is required other wise comparison or order by may use tempdb collation.
March 18, 2010 at 8:29 am
vidya_pande (3/18/2010)
Yes collate clause is required if user database collation is different than server collation.Tempdb database follows the same collation as server collation. If some query operation uses tempdb then.. collate clause is required other wise comparison or order by may use tempdb collation.
That is not my reason for including it. Windows collations are particularly inefficient when it comes to comparisons (complex linguistic rules).
SQL and binary collations can be 30 times faster.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 19, 2010 at 6:41 am
Thank you for your suggestions.
We have solved the insert problem in changing the primary key into a non-clustered index.
Regards,
Philippe.
March 19, 2010 at 6:45 am
pmoschkowitch (3/19/2010)
Thank you for your suggestions.We have solved the insert problem in changing the primary key into a non-clustered index.
Thank you for the feedback, but I thought you said the clustered index was on a column with the IDENTITY property? If you can share some more details, it might help others in a similar situation.
Oh, and what about problem #1?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 30, 2010 at 12:55 am
Sorry for posting so late.
The primary key is an int with identity.
It was automatically created as a clustered index, and we have changed it to a non clustered one but keeping the identity property.
Here is the new create table script :
CREATE TABLE [dbo].[TableA](
[Numero] [int] IDENTITY(1,1) NOT NULL,
[Field1] [nvarchar](50) NULL,
CONSTRAINT [[TableA]$PrimaryKey] PRIMARY KEY NONCLUSTERED
(
[Numero] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
About our first problem, we have changed our query. It's a bit longer, not very good, but it works:
- we select distinct rows from the table1 into a new table
- we drop table1
- then we rename the new table as table1
Phil.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply