db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Robert Kromkamp" <r.kromk...@pagelink.nl>
Subject Joins and performance
Date Fri, 01 Jun 2007 13:05:18 GMT
Hi All,

I've a question about joins. 

I've got 3 tables, namely:

- Company
- Company_building (Company has one ore more building)
- Employees (Employee works in one ore more building)

Getting all the companies will result in a low level SQL by Torque like:

SELECT 	COMPANY.COMPANY_PK, COMPANY.COMPANY_NAME,
COMPANY.COMPANY_FUNCTION 
FROM 		COMPANY 
WHERE 	COMPANY.COMPANY_FUNCTION = 'SCHOOL'



To get all the buildings I will loop through all the companies and call
Company.getBuildings() this will result in:

SELECT 	COMPANY_BUILDING.COMPANY_BUILDING_PK,
COMPANY_BUILDING.BUILDING_NAME, COMPANY_BUILDING.COMPANY_FK 
FROM 		COMPANY_BUILDING 
WHERE 	COMPANY_BUILDING.COMPANY_FK = 1

SELECT 	COMPANY_BUILDING.COMPANY_BUILDING_PK,
COMPANY_BUILDING.BUILDING_NAME, COMPANY_BUILDING.COMPANY_FK 
FROM 		COMPANY_BUILDING 
WHERE 	COMPANY_BUILDING.COMPANY_FK = 2

SELECT 	COMPANY_BUILDING.COMPANY_BUILDING_PK,
COMPANY_BUILDING.BUILDING_NAME, COMPANY_BUILDING.COMPANY_FK
FROM 		COMPANY_BUILDING
WHERE 	COMPANY_BUILDING.COMPANY_FK = 3

Etc.




To get all the employees I will loop in Java through all the building
and call CompanyBuilding.getEmployees() this will result in another
lowlevel query.



If I've 100 companies (which are schools) and 200 buildings, I will
executes 100 * 200 queries to get all the employees. In low level SQL
this query can be combined in one query. Is there a way to handle this
within Torque? So I will get something like:

SELECT 	* 
FROM 		COMPANY, COMPANY_BUILDING, EMPLOYEES 
WHERE 	COMPANY.COMPANY_FUNCTION = 'SCHOOL'
AND 		COMPANY_BUILDING.COMPANY_FK = COMPANY.COMPANY_PK
AND 		EMPLOYEE.COMPANY_BUILDING_FK =
COMPANY_BUILDING.COMPANY_BUILDING_PK


I'm using Torque 3.1.1 (I will upgrade to the newest version very soon)

Many Thanks!

Met vriendelijke groet,
Robert Kromkamp



Pagelink | improves business

Bornsestraat 1d
7556 BA  Hengelo

Postbus 76
7550 AB  Hengelo

tel 074 750 12 50
fax 074 750 12 51

mailto:r.kromkamp@pagelink.nl
http://www.pagelink.nl
http://www.perrit.nl
http://www.armeo.nl











---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message