0

When i use Like, access stuck

SELECT ox.*, '+++++++++++' AS judo, sh2.*
FROM OXUS AS ox INNER JOIN Sheet2 AS sh2 ON sh2.ID_P LIKE "*" & ox.ID_P;

IF i use

SELECT ox.*, '+++++++++++' AS judo, sh2.*
FROM OXUS AS ox INNER JOIN Sheet2 AS sh2 ON sh2.ID_P=ox.ID_P;

opened immediately.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
Fara
  • 217
  • 2
  • 3
  • 9
  • performance issues using wildcard as first part of like: http://stackoverflow.com/a/20538/1370425 : essentially, it's having to do a full search without the benefit of the index – SeanC Aug 13 '12 at 21:17

2 Answers2

1

In this case, you can use an implicit join, that is WHERE:

SELECT DISTINCT ox.P_ID, '+++++++++++' AS judo, sh2.P_ID
FROM OXUS AS ox, Sheet2 AS sh2
WHERE Instr(sh2.P_ID, ox.P_ID)>0

However, it is only useful for looking up rows.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
0

The first statement is a direct join between tables based on matching id's. i suspect theres an index involved too. The second statement is making the table joined based on using a wild card to check for every and anything(!) which is also concatenated onto the ID. It could also be converting from int to varchar depending on type .... all without using an index.

Like statements will always be more costly because they have to individually check the column content for a specific match. Using them as part of a join would not be advised.

I assume this is a cut down of the real problem because the like statement makes no sense.

What are you trying to achieve?

Scot Heed
  • 71
  • 5
  • ID-P - is the number of passport (Varchar). I want to find the same customers in the 2 tables by the passport – Fara Aug 13 '12 at 05:42
  • note that having the wildcard at the end of the string will use the index – SeanC Aug 13 '12 at 21:19
  • Dear all, i can't solve this problem, with t-sql same problem, but i use function in t-sql. Thank yor. – Fara Aug 14 '12 at 06:15