Home » RDBMS Server » Performance Tuning » Single Join Query vs Individual Table Queries
Single Join Query vs Individual Table Queries [message #217548] Fri, 02 February 2007 13:48 Go to next message
tolas
Messages: 5
Registered: January 2007
Junior Member
I'm working on a JDBC API for interacting with an Oracle database of 7 tables. A "get" method I'm creating needs to retrieve PK/FK connected results from all 7 tables. Everything I've read would indicate to me that utilizing SQL to retrieve everything in one large result set should be fastest.

On a database with 100,000 records (some tables have up to 300,000 records due to "one to many" PK to FK connection).. I notice a much faster total query time when performing 7 individual queries (1 to get PK record, and then just getting the rest by FK).

Doing a single large join query takes roughly 2.8 seconds.
While the sum of all 7 individual queries is only 1.3 seconds.

This is all being done over a network connection, so some delay is involved there.

I just wanted to get your opinion if this is normal for individual table queries to outperform a single join query.

Thanks, and sorry for my "newbie" terminology on everything.

- John
Re: Single Join Query vs Individual Table Queries [message #217636 is a reply to message #217548] Sat, 03 February 2007 10:02 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi, John.

The single query is supposed to be faster.
Rut it inside SQLPlus with AUTOTRACE ON and post the explain plan.
Even better - post TKPROF results.

IMHO the single query does NOT use optimal access path (somewhere).

HTH.
Re: Single Join Query vs Individual Table Queries [message #217795 is a reply to message #217636] Mon, 05 February 2007 09:59 Go to previous messageGo to next message
tolas
Messages: 5
Registered: January 2007
Junior Member
Unfortunately I don't think I have access to do these things on the database.

Perhaps there is something wrong with the structure of my query. Again, I'm very new to SQL and database's in general.

SELECT * FROM PRIMARY_TABLE, TAB1, TAB2, TAB3, TAB4, TAB5, TAB6 WHERE TAB1.FIELD1="blah" AND TAB1.FK=PRIMARY_TABLE.PK AND TAB1.FK=TAB2.FK AND TAB1.FK=TAB3.FK AND TAB1.FK=TAB4.FK AND TAB1.FK=TAB5.FK AND TAB1.FK=TAB6.FK


So I'm searching on FIELD1 of TAB1, and then matching the PK on the PRIMARY_TABLE, and getting the values from all other tables with a matching FK.
Re: Single Join Query vs Individual Table Queries [message #217874 is a reply to message #217795] Mon, 05 February 2007 23:13 Go to previous messageGo to next message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
hi
instead of selecting everything from all tables ( SELECT * FROM ...)
select only the required columns to improve the performance.

pravin
Re: Single Join Query vs Individual Table Queries [message #218183 is a reply to message #217548] Wed, 07 February 2007 02:39 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
At least post EXPLAIN plan
Previous Topic: STATSPACK
Next Topic: Index based on subtype
Goto Forum:
  


Current Time: Sat Dec 03 20:12:09 CST 2016

Total time taken to generate the page: 0.07630 seconds