criteria typed manually vs criteria by subquery or inner join

[pat12]

Hello


I have a table with billing of phones.BILLING
(number,month,data,time,cost)
When I build a query NB1 based on BILLING and put few numbers and
months as criteria it calculates immidiately (2s). When I build query
NB2 which is almost the same as NB1 but use subquery or inner join
(tried with both) with tables "number_temp" and "month_temp" (where I
have the same numbers and months as in criteria of NB1) it calculates 5
times longer (10s)

Since I will use much more rows in BILLING then now and would like to
use easier way to presents billings then typing many numbers and months
manually every time in criteria of query NB1 (I would like to learn
something also) I would like to use tables (already existing) with
numbers and months as criteria. But what to do to use these tables as
criteria and have the efficiency of query NB1.

Thanks for any help or link

Regards
PAT

 

[mscertified]

Do you have 'indexed' on the fields you are joining?
If not, this could affect the speed of the query.

Dorian

 

[pat12]

mscertified napisal(a):

Do you have 'indexed' on the fields you are joining?
If not, this could affect the speed of the query.

Dorian

Thanks for reply.
All involved fields have index.

I started even to wonder if it is possible to build the query sql code
with macro with after WHERE part taken from tables number_temp and
month_temp but I dont know how to do this. Beside I am curios why there
is such a difference with efficiency and how are the queries with
multiple criteria build in professional database written to avoid such
problems

PAT