comparing two huge tables in sql server 2005

Question

Post reply

comparing two huge tables in sql server 2005

ps_prakash02

SSC Enthusiast

Points: 199
More actions
August 4, 2009 at 1:23 am

#135050

Hi Guys,
I'm in need of your help.
my problem is, i have two tables namely MASTER and LINECONTENT.
"MASTER" table contains 5,00,000 records,
The columns in MASTER table is
masterid int, masterdesc varchar(1000).
"LINECONTENT" table contains more than 1000 records &
its columns are pageno int, lineno int, content varchar(2000).
I want to compare each row of "MASTER" table, masterdesc column value with the "LINECONTENT" table content column,
If any records matched i want to take the matched master record and store it in another table.
for this i used the below query.
select b.* from linecontent a
join master b on a.content like '%'+b.masterdesc+'%'
but this query is taking much.
nearly more than 15 minutes. but i want to decrease the time its taking.
give me some suggestions so that time can be decreased
please help me
thanks & regards
P.Prakash

Viewing 9 posts - 1 through 8 (of 8 total)

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

Chand00 SSCertifiable Points: 6466 More actions · Answer 1

Hi,

Can you post the query execution plan.

[font="Verdana"]Thanks
Chandra Mohan[/font]

aprasy Mr or Mrs. 500 Points: 511 More actions · Answer 2

Is that content column be a primary key?

If its not then please add that field in index so that u could get the result faster.

can u send both the table structure?

And why do u use Like clause?

Thanks

ps_prakash02 SSC Enthusiast Points: 199 More actions · Answer 3

Hi,

i have attached the estimated execution plan. actual execution plan is taking long time

regards

p.prakash

ps_prakash02 SSC Enthusiast Points: 199 More actions · Answer 4

Hi,

i have created nonclustered index in both tables. In "MASTER" i have created non-clustered index for masterdesc column similarly in "LINECONTENT" table i have created non-clustered index for "content" field.

MASTER Structure

masterid int, masterdesc varchar(1000).

i have created nonclustered index for masterdesc column.

LINECONTENT Table structure

pageno int, lineno int, content varchar(2000)

i have created non clustered index for content column.

The purpose why i'm going for like is,

i want to take the master rows which are matched in linecontent table

thanks & regards

P.Prakash

Chand00 SSCertifiable Points: 6466 More actions · Answer 5

Hi,

Can you post the create scripts for tables,indexes and some sample data. and also can you post the plan in .sqlplan format. so that we can get more info. We cannot get info from a picture file when compared to .sqlplan file.

[font="Verdana"]Thanks
Chandra Mohan[/font]

ps_prakash02 SSC Enthusiast Points: 199 More actions · Answer 6

CREATE TABLE Master

(MasterId int, MasterDesc varchar(2000))

CREATE TABLE LineContent

(PageNo int, PageLineNo int, Content varchar(2000))

sorry i can't give data becos its my companies.

thanks

prakash

steveb. SSC-Forever Points: 46733 More actions · Answer 7

You are joining using a LIKE with leading % so this is never going to perform well, also this will mean that the index on

masterdesc may not be used correctly.

Do you really need the leading % and is it possible to use an exact match?

Also can you post the actual plan as the estimated plan can be inaccurate?

knickels SSC Rookie Points: 30 More actions · Answer 8

I don't think indexes on the varchars will help too much because you are working on the entire table anyway. I'll bet your query plan has table scan written all over it. But 15 minutes is not too bad for such a task as you describe. At the very least put clustered indexes on these varchars. Thinking ahead, you might rethink the join to be on masterid. Then you'd have to have this as a foreign key in the other table, which it probably should be anyway. Masterid(int) should be your index. And if you did this, the seeks will be quick. The query could be re-written as a while loop, seeking the masterid in master, writing the description columns into variables and then comparing the variables.