speed problem

  • Thread starter Thread starter Dzemo
  • Start date Start date
D

Dzemo

I use SQL 2000 for database and i have 2 tables.
Table1 :
ID (int, identity)
FirstName(int,text)
LastName(int,text)
FatherName(int,text)
with 200000 rows

Table2 :
ID (int, identity)
IDTable1(int)
with 1500 rows

when i search
select * from table1 where fathername like '%xxx%'
it work more or less good but when i change this statement into
select * from table1 where fathername like '%xxx%' and id in (select
idtable1 from table2)
speed go down. Why? Any sugestions.
Will speed improve to switch from text to char where text isn't needed?
thx
 
A couple of suggestions:

Use a JOIN instead of using "in (select idtable1 from table2)".

Be sure idTable1 column is indexed.


--
Mike

Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com


Select from table2 into a temporary table, programatically index it, then
 
Back
Top