Posted by Erland Sommarskog on 11/27/07 22:34

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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]