Data insertion too too slow...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Don Li - 27 Nov 2007 22:06 GMT

Hi,

Env is ms sql server 2000.

ddl:
create table srchPool(tid int primary key, taid int, s tynyint, uid
tynyint);
-- and sql server automatically creates a clustered index for the pk

dml:
insert into srchPool(taid,s,uid)
select article_id, 99, 1484
     from targetTBL
     where article_content LIKE '% presentation %';

insert into srchPool(taid,s,uid)
select article_id, 55, 1484
    from targetTBL
     where article_content LIKE '% demonstration %';
-- a few more similar queries ...

The above insertion query TOOK about 2000ms to execute, too too slow,
would be much faster if I insert the data sets into a temp tbl like

select article_id, 99, 1484 into #srchPool(taid,s,uid)
     from targetTBL
     where article_content LIKE '% presentation %';

-- once its use is finished and drop it

?

Many thanks.

Reply to this Message

Erland Sommarskog - 27 Nov 2007 22:34 GMT

> ddl:
> create table srchPool(tid int primary key, taid int, s tynyint, uid
[quoted text clipped - 21 lines]
>
> -- once its use is finished and drop it

It depends. What takes time? Inserting the rows or finding them? Given
that the condition requires a scan, I would place my bets at the latter.
But just run the statements to test.

In targetTBL is there an index on (article_content, article_id)?

What is the data type and collation of article_content?

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

Reply to this Message

Don Li - 28 Nov 2007 00:12 GMT

> > ddl:
> > create table srchPool(tid int primary key, taid int, s tynyint, uid
[quoted text clipped - 24 lines]
>
> - Show quoted text -

Erland,

I've added a non-clustered index to the targetTBl(article_content,
article_id),
article_content is varchar(896) and article_id is int. On collation
for article_content, it uses "database default", which is SQL_Latin1.

However, the scan is still taking too long, insertion of about 12
rows took about 7000ms. And I even added index hint. It's odd though
yesterday and the day before yesterday, the same query ran at least
100% faster.

Thank you.

Don

Reply to this Message

Erland Sommarskog - 28 Nov 2007 07:29 GMT

> I've added a non-clustered index to the targetTBl(article_content,
> article_id),
> article_content is varchar(896) and article_id is int. On collation
> for article_content, it uses "database default", which is SQL_Latin1.

OK, there are some spetacular differences between SQL collations + varchar
and Windows collations or anything with nvarchar with that type of query.

> However, the scan is still taking too long, insertion of about 12
> rows took about 7000ms. And I even added index hint. It's odd though
> yesterday and the day before yesterday, the same query ran at least
> 100% faster.

But it's not really the insertion that takes time, is it? I mean, if you
the SELECT alone, it does not respond in 70 milliseconds, does it?

Have you considered using full-text indexing?

Signature

Reply to this Message

Don Li - 29 Nov 2007 01:33 GMT

Erland, please see my embedded comments/further questions below.
Thanks.
Don

> > I've added a non-clustered index to the targetTBl(article_content,
> > article_id),
[quoted text clipped - 3 lines]
> 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?

> > However, the scan is still taking too long, insertion of about 12
> > rows took about 7000ms. And I even added index hint. It's odd though
[quoted text clipped - 3 lines]
> But it's not really the insertion that takes time, is it? I mean, if you
> the SELECT alone, it does not respond in 70 milliseconds, does it?

Yeah, the SELECT, table scan thing sucks.

> Have you considered using full-text indexing?

I'm using another technique for text data search, looks superior to
full-text indexing.

> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

Erland Sommarskog - 29 Nov 2007 08:17 GMT

>> 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.

Signature

Reply to this Message

Don Li - 30 Nov 2007 03:17 GMT

> >> OK, there are some spetacular differences between SQL collations +
> >> varchar and Windows collations or anything with nvarchar with that type
[quoted text clipped - 36 lines]
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Erland, I'm with you, thank you. Now, let's refer to a previous but
related thread,
http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/
07bf13e23d6bf643/2b7c382a0f8dbaa9#2b7c382a0f8dbaa9

There you mentioned about cache, I searched BOL for more on the
subject to almost no avail, googling gets something like "BPool
(buffer pool) and MemToLeave", too theorical not practical. Any
pointer for this?

Don

Reply to this Message

Erland Sommarskog - 30 Nov 2007 22:06 GMT

> Erland, I'm with you, thank you. Now, let's refer to a previous but
> related thread,
[quoted text clipped - 4 lines]
> (buffer pool) and MemToLeave", too theorical not practical. Any
> pointer for this?

To learn more about SQL Server Memory I recommend reading the sections
in Books Online headed by
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/9caf0d8d-5261-40e5-8730-5b88009272ea.htm

Or get Kalen Delaney's "Inside SQL Server 2005: The Storage Engine".

Signature

Reply to this Message

Manfred Sorg - 28 Nov 2007 15:30 GMT

> rows took about 7000ms. And I even added index hint. It's odd though
> yesterday and the day before yesterday, the same query ran at least
> 100% faster.

The question is: What has happened?
Did anyone create a trigger onto your table?
That's a good guess if inserted takes suddenly much longer.

Bye, Manfred

Reply to this Message

Don Li - 28 Nov 2007 18:55 GMT

> > rows took about 7000ms. And I even added index hint. It's odd though
> > yesterday and the day before yesterday, the same query ran at least
[quoted text clipped - 5 lines]
>
> Bye, Manfred

That's a good question. As I recall the first few times when I ran
queries, they were not too slow (about 2 days ago) otherwise I would
have my "teeth" on them... theoritically no one other than me has
access to the machine, some hacker did nasty thing on my box?
possible but less likely, what's his/her motivation?

Thanks.

Reply to this Message