Data insertion too too slow...

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.

Re: Data insertion too too slow...

Don Li (tatata9999@gmail.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_content, article_id)?

What is the data type and collation of article_content?


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

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

Re: Data insertion too too slow...

On Nov 27, 5:34 pm, Erland Sommarskog wrote:
> Don Li (tatata9...@gmail.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...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.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

Re: Data insertion too too slow...

Don Li (tatata9999@gmail.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.

> 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@sommarskog.se

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

Re: Data insertion too too slow...

On 28 Nov., 01:12, Don Li 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

Re: Data insertion too too slow...

On Nov 28, 10:30 am, Manfred Sorg wrote:
> On 28 Nov., 01:12, Don Li 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.

Re: Data insertion too too slow...

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

On Nov 28, 2:29 am, Erland Sommarskog wrote:
> Don Li (tatata9...@gmail.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?

>
> > 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...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Re: Data insertion too too slow...

Don Li (tatata9999@gmail.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, esquel@sommarskog.se

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

Re: Data insertion too too slow...

On Nov 29, 3:17 am, Erland Sommarskog wrote:
> Don Li (tatata9...@gmail.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...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.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/b rowse_thread/thread/07bf13e23d6bf643/2b7c382a0f8dbaa9#2b7c38 2a0f8dbaa9

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

Re: Data insertion too too slow...

Don Li (tatata9999@gmail.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.databases.ms-sqlserver/b rowse_thread/thread/07bf13e23d6bf643/2b7c382a0f8dbaa9#2b7c38 2a0f8dbaa9
>
> 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-526 1-40e5-8730-5b88009272ea.htm

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


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

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