N
Nano
Hi,
In a .mdb with related tables, related via ODBC to a SQL 2K server, I am
creating some havely joined queries. All INNER JOINS, which functions OK
and without any noticable delay.
Accept when I design a query with more than 4 INNER JOINS, then the
performance drops. I mean 10 seconds at least for 4 INNER JOINS, apposed
to <1 sec for 3 INNER JOINS. I have tried with joining different tables,
but it seems to be a problem of the number of joins, not which tables.
This is an example query:
SELECT sl_purchase.number, sl_purchase.numberextern,
sl_purchase.datecreate, sl_purchase.dateexpect, sl_purchase.weight,
sl_purchase.cost, sl_purchase.memoheader, sl_purchase.memofooter,
sl_relation.name AS relation,
sl_address.heading, sl_address.street, sl_address.zip, sl_address.city,
sl_address.telephone, sl_address.fax,
sl_country.countrycode,
sl_valuta.valutacode,
sl_deliverycondition.name AS deliverycondition
FROM ((((
sl_purchase
INNER JOIN sl_relation ON sl_purchase.relation=sl_relation.ids)
INNER JOIN sl_address ON sl_relation.ids=sl_address.idp)
INNER JOIN sl_country ON sl_address.country=sl_country.ids)
INNER JOIN sl_valuta ON sl_purchase.valuta=sl_valuta.ids)
INNER JOIN sl_deliverycondition ON
sl_purchase.deliverycondition=sl_deliverycondition.ids
WHERE sl_address.row=1
AND sl_purchase.ids='abs';
Is this a common problem in Access? Anything I can do about it? Because I
would really like to even join more tables...
Thanks in advance!
Nano
PS1 When not connected via integrated security to the SQL 2K, with 3
joins the question for a name and password pops up almost instantly, with
4 joins the question for a name and password takes just as long to pop up
as when processing the query with int.sec.
PS2 For an application written in vb .net, I use MS Access as a
report/document print front end. It's all setup and functioning
(automating Access through vb, selecting and printing, even passing where
statement and printername). But all testing is done directly from MS
Access
In a .mdb with related tables, related via ODBC to a SQL 2K server, I am
creating some havely joined queries. All INNER JOINS, which functions OK
and without any noticable delay.
Accept when I design a query with more than 4 INNER JOINS, then the
performance drops. I mean 10 seconds at least for 4 INNER JOINS, apposed
to <1 sec for 3 INNER JOINS. I have tried with joining different tables,
but it seems to be a problem of the number of joins, not which tables.
This is an example query:
SELECT sl_purchase.number, sl_purchase.numberextern,
sl_purchase.datecreate, sl_purchase.dateexpect, sl_purchase.weight,
sl_purchase.cost, sl_purchase.memoheader, sl_purchase.memofooter,
sl_relation.name AS relation,
sl_address.heading, sl_address.street, sl_address.zip, sl_address.city,
sl_address.telephone, sl_address.fax,
sl_country.countrycode,
sl_valuta.valutacode,
sl_deliverycondition.name AS deliverycondition
FROM ((((
sl_purchase
INNER JOIN sl_relation ON sl_purchase.relation=sl_relation.ids)
INNER JOIN sl_address ON sl_relation.ids=sl_address.idp)
INNER JOIN sl_country ON sl_address.country=sl_country.ids)
INNER JOIN sl_valuta ON sl_purchase.valuta=sl_valuta.ids)
INNER JOIN sl_deliverycondition ON
sl_purchase.deliverycondition=sl_deliverycondition.ids
WHERE sl_address.row=1
AND sl_purchase.ids='abs';
Is this a common problem in Access? Anything I can do about it? Because I
would really like to even join more tables...
Thanks in advance!
Nano
PS1 When not connected via integrated security to the SQL 2K, with 3
joins the question for a name and password pops up almost instantly, with
4 joins the question for a name and password takes just as long to pop up
as when processing the query with int.sec.
PS2 For an application written in vb .net, I use MS Access as a
report/document print front end. It's all setup and functioning
(automating Access through vb, selecting and printing, even passing where
statement and printername). But all testing is done directly from MS
Access