Data insertion too too slow...

#1

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.

#2

Don Li (tatata9999 (AT) gmail (DOT) com) writes:

Quote:

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

What is the data type and collation of article_content?

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

#3

On Nov 27, 5:34 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

Don Li (tatata9... (AT) gmail (DOT) 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_content, article_id)?

What is the data type and collation of article_content?

--
Erland Sommarskog, SQL Server MVP, esq... (AT) sommarskog (DOT) 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- Hide quoted text -

- 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

#4

Don Li (tatata9999 (AT) gmail (DOT) com) writes:

Quote:

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.

Quote:

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, esquel (AT) sommarskog (DOT) 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

#5

On 28 Nov., 01:12, Don Li <tatata9... (AT) gmail (DOT) com> wrote:

Quote:

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

#6

On Nov 28, 10:30 am, Manfred Sorg <manfred.s... (AT) googlemail (DOT) com> wrote:

Quote:

On 28 Nov., 01:12, Don Li <tatata9... (AT) gmail (DOT) com> wrote:

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.

#7

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

On Nov 28, 2:29 am, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

Don Li (tatata9... (AT) gmail (DOT) com) writes:
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.
Don't really follow you here, could you elaborate a bit further on the

collations topic?

Quote:

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.

Quote:

Have you considered using full-text indexing?
I'm using another technique for text data search, looks superior to

full-text indexing.

Quote:

--
Erland Sommarskog, SQL Server MVP, esq... (AT) sommarskog (DOT) 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

#8

Don Li (tatata9999 (AT) gmail (DOT) com) writes:

Quote:

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 (AT) sommarskog (DOT) 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

#9

On Nov 29, 3:17 am, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

Don Li (tatata9... (AT) gmail (DOT) com) 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, esq... (AT) sommarskog (DOT) 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

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?

Don

#10

Don Li (tatata9999 (AT) gmail (DOT) com) writes:

Quote:

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

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

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Data insertion too too slow...

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Data insertion too too slow... - 11-27-2007 , 04:06 PM

Re: Data insertion too too slow... - 11-27-2007 , 04:34 PM

Re: Data insertion too too slow... - 11-27-2007 , 06:12 PM

Re: Data insertion too too slow... - 11-28-2007 , 01:29 AM

Re: Data insertion too too slow... - 11-28-2007 , 09:30 AM

Re: Data insertion too too slow... - 11-28-2007 , 12:55 PM

Re: Data insertion too too slow... - 11-28-2007 , 07:33 PM

Re: Data insertion too too slow... - 11-29-2007 , 02:17 AM

Re: Data insertion too too slow... - 11-29-2007 , 09:17 PM

Re: Data insertion too too slow... - 11-30-2007 , 04:06 PM