Re: Data insertion too too slow...
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Tue, 27 Nov 2007 22:34:12 +0000 (UTC)
Don Li (tatata9999@xxxxxxxxx) 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@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
.
- Follow-Ups:
- Re: Data insertion too too slow...
- From: Don Li
- Re: Data insertion too too slow...
- References:
- Data insertion too too slow...
- From: Don Li
- Data insertion too too slow...
- Prev by Date: Re: ORDER BY AND GROUP BY CLAUSE
- Next by Date: Re: ORDER BY AND GROUP BY CLAUSE
- Previous by thread: Data insertion too too slow...
- Next by thread: Re: Data insertion too too slow...
- Index(es):
Relevant Pages
|
