SELECT... WHERE COD IN (6000 itens)

Question

SELECT... WHERE COD IN (6000 itens)

webnando

SSC Enthusiast

Points: 173
More actions
May 18, 2004 at 6:59 am

#359651

Help,
I have a query like SELECT... WHERE COD IN (6000 itens). This query take 1 minute and my execution plan show 100% cost for task Filter.
I just have a clustered index on column COD.

Help me...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 1

Can you procide a little more information please? It would be helpful if you could provide the actual query, as well as sample table structures.

Given the little information in your post, I suspect that the (6000 items) may be an inner join, resulting in a huge result set. It is difficult to say without more information.

Cheers

Wayne

webnando SSC Enthusiast Points: 173 More actions · Answer 2

Good,

I have one table like this:

CREATE TABLE [CUSTOMER] (

[COD] [bigint] NOT NULL,

[NAME] [VARCHAR(50)] NOT NULL,

CONSTRAINT [PK_COD] PRIMARY KEY CLUSTERED ([COD])

)

My table have 55.000 rows and my query is like this:

SELECT * FROM CUSTOMER WHERE COD IN (59864720, 75284280, 464260820, 464345830, 465174380, 467954080, 468262910, 468262920, 937001650, 937012040, 970555320, 976151740, 976381200, 976391630, 976546730, 976595730, 18245940, 43305730, 59905300, 70773890, 70788654, 70811770, 70811780, 70841070, 70845770, 70845780, 70846070, 70846200, 70846220, 75116735, 75990790, 77437570, 77512900, 77531154..........MORE 6000 cod´s)

My queyr take a long time to run and i need tuning.

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Create a table with a single non-null row that is the primary key and put all of those items in it. Then you can inner join to that table on the related field which will increase your performance.