Posted by Don Li on 11/28/07 00:12

On Nov 27, 5:34 pm, Erland Sommarskog <esq...@sommarskog.se> 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/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

[Back to original message]