how to replace exists in query

[tirf]

I have a query like

select * from tbl1 t
where exists (select 1 from tbl2 t2
where t2.id = t.id and t2.name = t.name)

--this is taking time what would be the efficient way to rewrite this code

Thanks,

 

[RiverGuy]

I'm sure you need an index which covers id and name in both tables.

SELECT t.*
FROM tbl1 t
INNER JOIN tbl2 t2
  ON t.id = t2.id
  AND t.name = t2.name

 

[gmmastros]

What indexes do you have on the tables?

sp_helpindex 'tbl1'
sp_helpindex 'tbl2'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

You need to have indexes on ID and Name in both tables.

PluralSight Learning Library

 

[tirf]

No index was defined on both tables

 

[gmmastros]

You will get a massive speed improvement if you index them. How big are these tables (how many rows)? The more rows you have, the more indexes will help.

Create Index idx_tbl1_id_Name On tbl1(Id, Name)
Create Index idx_tbl2_id_name on tbl2(id, Name)



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tirf]

these tables are not big, the biggest is 3800 rows;
I tryed the query with index created on both tables, it runs below 4 sec (used to be 10 secs)

Thank you all

 

[gmmastros]

Now try the query that RiverGuy suggested (the first response to your initial question).


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

These two queries are not the same.

PluralSight Learning Library