| Single Join Query vs Individual Table Queries [message #217548] |
Fri, 02 February 2007 13:48  |
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 #217795 is a reply to message #217636] |
Mon, 05 February 2007 09:59  |
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.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
