Re: Data insertion too too slow...

Don Li (tatata9999@xxxxxxxxx) writes:
OK, there are some spetacular differences between SQL collations +
varchar and Windows collations or anything with nvarchar with that type
of query.

Don't really follow you here, could you elaborate a bit further on the
collations topic?

Consider these tables:

CREATE TABLE sqlvarchar(
a int NOT NULL IDENTITY,
lotsoftext varchar(4000) COLLATE SQL_Latin_General1_CP1_CI_AS)

CREATE TABLE sqlunicode(
a int NOT NULL IDENTITY,
lotsoftext nvarchar(4000) COLLATE SQL_Latin_General1_CP1_CI_AS)

CREATE TABLE windowsvarchar(
a int NOT NULL IDENTITY,
lotsoftext varchar(4000) COLLATE Latin_General1_CI_AS)

CREATE TABLE windowsunicode(
a int NOT NULL IDENTITY,
lotsoftext nvarchar(4000) COLLATE Latin_General1_CI_AS)

Fill the tables with many rows. Then run queries like:

SELECT * FROM tbl WHERE lotsoftext LIKE '%sometext%'

You will find that the queries against sqlvarchar runs about seven times
faster than the other queries. At least that is my experience.

But as you already appears to be using SQL collation and varchar, that
observation does not help you much.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Data insertion too too slow...
    ... varchar and Windows collations or anything with nvarchar with that type ... a int NOT NULL IDENTITY, ... You will find that the queries against sqlvarchar runs about seven times ...
    (comp.databases.ms-sqlserver)
  • Re: Installing SQL_Latin1_General_CP1_CI_AS collation order??
    ... collations, where a collation controls things as the character repertoire, ... Tibor Karaszi, SQL Server MVP ... "Ward Horsfall" wrote in message ...
    (microsoft.public.sqlserver.setup)
  • Re: Replication Monitor didnt work because different collations
    ... Looking for a SQL Server replication book? ... SQL 2005 filtered merge replication. ... We changed collations to match the server collation and Replication ... After this change we started all publication snapshots. ...
    (microsoft.public.sqlserver.replication)
  • Re: sort order ID
    ... SQL Server allows to create different collations for each database inside ... Get the equalent collations in SQL 2000 from books online. ... > 52 collations differece from server. ...
    (microsoft.public.sqlserver.server)
  • RE: Collation....
    ... ANSI SQL-92 suggests that two strings which differ only in trailing spaces ... In SQL Server, with most collations a simple ...
    (microsoft.public.sqlserver.programming)