Data insertion too too slow...

[Don Li]

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.

[Erland Sommarskog]

Don Li (ta********@gma il.com) writes:

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

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_conten t, article_id)?

What is the data type and collation of article_content ?
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Don Li]

On Nov 27, 5:34 pm, Erland Sommarskog <esq...@sommars kog.sewrote:

Don Li (tatata9...@gma il.com) writes:

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

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_conten t, article_id)?

What is the data type and collation of article_content ?

--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se

Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx- Hide quoted text -

- Show quoted text -

Erland,

I've added a non-clustered index to the targetTBl(artic le_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

[Erland Sommarskog]

Don Li (ta********@gma il.com) writes:

I've added a non-clustered index to the targetTBl(artic le_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?

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Manfred Sorg]

On 28 Nov., 01:12, Don Li <tatata9...@gma il.comwrote:

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

[Don Li]

On Nov 28, 10:30 am, Manfred Sorg <manfred.s...@g ooglemail.comwr ote:

On 28 Nov., 01:12, Don Li <tatata9...@gma il.comwrote:

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

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.

[Don Li]

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

On Nov 28, 2:29 am, Erland Sommarskog <esq...@sommars kog.sewrote:

Don Li (tatata9...@gma il.com) writes:

I've added a non-clustered index to the targetTBl(artic le_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.

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

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...@sommarsk og.se

Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

[Erland Sommarskog]

Don Li (ta********@gma il.com) writes:

Erland, I'm with you, thank you. Now, let's refer to a previous but
related thread,
http://groups.google.com/group/comp....7c382a0f8dbaa9

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?

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

Or get Kalen Delaney's "Inside SQL Server 2005: The Storage Engine".
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx