Right Outer Joins and performance
-
Hallo, I am working in an company which is using extensivly right outer joins. I argue that outer joins are bad for performance, and we have a lot of performance problems. We are using db2 from IBM. To show you an example (a query which returns page size error in system temporary tablespace) But nobody seems to care. My colleges add more and more right outer joins to querries. Here is the example: Please give me some feetback if this is an ordinary query (my colleges say a database must be able to handle this) or if I am right. I think this query is overkill. SELECT RentObj.Classific1 AS Classific1, RentObj.Classific2 AS Classific2, RentObj.Classific3 AS Classific3, RentObj.Classific4 AS Classific4, RentObj.Classific5 AS Classific5, RentObj.Classific6 AS Classific6, RentObj.Classific7 AS Classific7, RentObj.Classific8 AS Classific8, RentObj.Classific9 AS Classific9, RentObj.Classific10 AS Classific10, RentObj.RentObjId AS RentObjId, Car.RegistrationNumber AS RegistrationNumber, RentObj.Number AS Number, Classification_1.Code AS CCode_1, Classification_2.Code AS CCode_2, Classification_3.Code AS CCode_3, Classification_4.Code AS CCode_4, Classification_5.Code AS CCode_5, Classification_6.Code AS CCode_6, Classification_7.Code AS CCode_7, Classification_8.Code AS CCode_8, Classification_9.Code AS CCode_9, Classification_10.Code AS CCode_10, Car.RentObjId AS Car_RentObjId, Car.FirstLicense AS FirstLicense, Car.ChassisNo AS ChassisNo, Car.CarDocumentNo AS CarDocumentNo, Car.CarKeyNo AS CarKeyNo, Car.CubicCapacity AS CubicCapacity, Car.Kilowatt AS Kilowatt, Car.PayLoad AS PayLoad, Car.RadioCode AS RadioCode, Car.MayorRevison AS MayorRevison, Car.TankContent AS TankContent, Car.Consumption AS Consumption, Car.Kilometer AS Kilometer, Car.UnitNumber AS UnitNumber, Car.KilometerMax AS KilometerMax, Car.KMHigCos AS KMHigCos, Car.KMCI AS KMCI, Car.FuelType AS FuelType, Car.PS AS PS, Car.BrakeSpecInvest AS BrakeSpecInvest, Car.Totalweight AS Totalweight, Car.Unloadedweight AS Unloadedweight, Car.MatchRegNumber AS MatchRegNumber, Car.ConsumptionCityIn AS ConsumptionCityIn, Car.ConsumptionCityOut AS ConsumptionCityOut, Car.CO2Emission AS CO2Emission, RentObj.CompanyId AS CompanyId, RentObj.RentObjTypeId AS RentObjTypeId, RentObj.LicenceeId AS LicenceeId, RentObj.ObjEvent AS ObjEvent, RentObj.StationCIId AS StationCIId, RentObj.ObjPositionCIId AS ObjPositionCIId, RentObj.CostCarrierId AS CostCarrierId, RentObj.CheckInStatPlanId AS CheckInStatPlanId, R
-
Hallo, I am working in an company which is using extensivly right outer joins. I argue that outer joins are bad for performance, and we have a lot of performance problems. We are using db2 from IBM. To show you an example (a query which returns page size error in system temporary tablespace) But nobody seems to care. My colleges add more and more right outer joins to querries. Here is the example: Please give me some feetback if this is an ordinary query (my colleges say a database must be able to handle this) or if I am right. I think this query is overkill. SELECT RentObj.Classific1 AS Classific1, RentObj.Classific2 AS Classific2, RentObj.Classific3 AS Classific3, RentObj.Classific4 AS Classific4, RentObj.Classific5 AS Classific5, RentObj.Classific6 AS Classific6, RentObj.Classific7 AS Classific7, RentObj.Classific8 AS Classific8, RentObj.Classific9 AS Classific9, RentObj.Classific10 AS Classific10, RentObj.RentObjId AS RentObjId, Car.RegistrationNumber AS RegistrationNumber, RentObj.Number AS Number, Classification_1.Code AS CCode_1, Classification_2.Code AS CCode_2, Classification_3.Code AS CCode_3, Classification_4.Code AS CCode_4, Classification_5.Code AS CCode_5, Classification_6.Code AS CCode_6, Classification_7.Code AS CCode_7, Classification_8.Code AS CCode_8, Classification_9.Code AS CCode_9, Classification_10.Code AS CCode_10, Car.RentObjId AS Car_RentObjId, Car.FirstLicense AS FirstLicense, Car.ChassisNo AS ChassisNo, Car.CarDocumentNo AS CarDocumentNo, Car.CarKeyNo AS CarKeyNo, Car.CubicCapacity AS CubicCapacity, Car.Kilowatt AS Kilowatt, Car.PayLoad AS PayLoad, Car.RadioCode AS RadioCode, Car.MayorRevison AS MayorRevison, Car.TankContent AS TankContent, Car.Consumption AS Consumption, Car.Kilometer AS Kilometer, Car.UnitNumber AS UnitNumber, Car.KilometerMax AS KilometerMax, Car.KMHigCos AS KMHigCos, Car.KMCI AS KMCI, Car.FuelType AS FuelType, Car.PS AS PS, Car.BrakeSpecInvest AS BrakeSpecInvest, Car.Totalweight AS Totalweight, Car.Unloadedweight AS Unloadedweight, Car.MatchRegNumber AS MatchRegNumber, Car.ConsumptionCityIn AS ConsumptionCityIn, Car.ConsumptionCityOut AS ConsumptionCityOut, Car.CO2Emission AS CO2Emission, RentObj.CompanyId AS CompanyId, RentObj.RentObjTypeId AS RentObjTypeId, RentObj.LicenceeId AS LicenceeId, RentObj.ObjEvent AS ObjEvent, RentObj.StationCIId AS StationCIId, RentObj.ObjPositionCIId AS ObjPositionCIId, RentObj.CostCarrierId AS CostCarrierId, RentObj.CheckInStatPlanId AS CheckInStatPlanId, R
Joining 18 tables does not seem like a good idea. You need a data warehouse with denormalized design. Maybe the Right outer join is a business requirement. But we should not assume that the database will just handle this!! Here comes our responsibility as developers, designers and DBAs to optimize the structure to satisfy business needs.
Hesham A. Amin My blog
-
Hallo, I am working in an company which is using extensivly right outer joins. I argue that outer joins are bad for performance, and we have a lot of performance problems. We are using db2 from IBM. To show you an example (a query which returns page size error in system temporary tablespace) But nobody seems to care. My colleges add more and more right outer joins to querries. Here is the example: Please give me some feetback if this is an ordinary query (my colleges say a database must be able to handle this) or if I am right. I think this query is overkill. SELECT RentObj.Classific1 AS Classific1, RentObj.Classific2 AS Classific2, RentObj.Classific3 AS Classific3, RentObj.Classific4 AS Classific4, RentObj.Classific5 AS Classific5, RentObj.Classific6 AS Classific6, RentObj.Classific7 AS Classific7, RentObj.Classific8 AS Classific8, RentObj.Classific9 AS Classific9, RentObj.Classific10 AS Classific10, RentObj.RentObjId AS RentObjId, Car.RegistrationNumber AS RegistrationNumber, RentObj.Number AS Number, Classification_1.Code AS CCode_1, Classification_2.Code AS CCode_2, Classification_3.Code AS CCode_3, Classification_4.Code AS CCode_4, Classification_5.Code AS CCode_5, Classification_6.Code AS CCode_6, Classification_7.Code AS CCode_7, Classification_8.Code AS CCode_8, Classification_9.Code AS CCode_9, Classification_10.Code AS CCode_10, Car.RentObjId AS Car_RentObjId, Car.FirstLicense AS FirstLicense, Car.ChassisNo AS ChassisNo, Car.CarDocumentNo AS CarDocumentNo, Car.CarKeyNo AS CarKeyNo, Car.CubicCapacity AS CubicCapacity, Car.Kilowatt AS Kilowatt, Car.PayLoad AS PayLoad, Car.RadioCode AS RadioCode, Car.MayorRevison AS MayorRevison, Car.TankContent AS TankContent, Car.Consumption AS Consumption, Car.Kilometer AS Kilometer, Car.UnitNumber AS UnitNumber, Car.KilometerMax AS KilometerMax, Car.KMHigCos AS KMHigCos, Car.KMCI AS KMCI, Car.FuelType AS FuelType, Car.PS AS PS, Car.BrakeSpecInvest AS BrakeSpecInvest, Car.Totalweight AS Totalweight, Car.Unloadedweight AS Unloadedweight, Car.MatchRegNumber AS MatchRegNumber, Car.ConsumptionCityIn AS ConsumptionCityIn, Car.ConsumptionCityOut AS ConsumptionCityOut, Car.CO2Emission AS CO2Emission, RentObj.CompanyId AS CompanyId, RentObj.RentObjTypeId AS RentObjTypeId, RentObj.LicenceeId AS LicenceeId, RentObj.ObjEvent AS ObjEvent, RentObj.StationCIId AS StationCIId, RentObj.ObjPositionCIId AS ObjPositionCIId, RentObj.CostCarrierId AS CostCarrierId, RentObj.CheckInStatPlanId AS CheckInStatPlanId, R
No
-
Hallo, I am working in an company which is using extensivly right outer joins. I argue that outer joins are bad for performance, and we have a lot of performance problems. We are using db2 from IBM. To show you an example (a query which returns page size error in system temporary tablespace) But nobody seems to care. My colleges add more and more right outer joins to querries. Here is the example: Please give me some feetback if this is an ordinary query (my colleges say a database must be able to handle this) or if I am right. I think this query is overkill. SELECT RentObj.Classific1 AS Classific1, RentObj.Classific2 AS Classific2, RentObj.Classific3 AS Classific3, RentObj.Classific4 AS Classific4, RentObj.Classific5 AS Classific5, RentObj.Classific6 AS Classific6, RentObj.Classific7 AS Classific7, RentObj.Classific8 AS Classific8, RentObj.Classific9 AS Classific9, RentObj.Classific10 AS Classific10, RentObj.RentObjId AS RentObjId, Car.RegistrationNumber AS RegistrationNumber, RentObj.Number AS Number, Classification_1.Code AS CCode_1, Classification_2.Code AS CCode_2, Classification_3.Code AS CCode_3, Classification_4.Code AS CCode_4, Classification_5.Code AS CCode_5, Classification_6.Code AS CCode_6, Classification_7.Code AS CCode_7, Classification_8.Code AS CCode_8, Classification_9.Code AS CCode_9, Classification_10.Code AS CCode_10, Car.RentObjId AS Car_RentObjId, Car.FirstLicense AS FirstLicense, Car.ChassisNo AS ChassisNo, Car.CarDocumentNo AS CarDocumentNo, Car.CarKeyNo AS CarKeyNo, Car.CubicCapacity AS CubicCapacity, Car.Kilowatt AS Kilowatt, Car.PayLoad AS PayLoad, Car.RadioCode AS RadioCode, Car.MayorRevison AS MayorRevison, Car.TankContent AS TankContent, Car.Consumption AS Consumption, Car.Kilometer AS Kilometer, Car.UnitNumber AS UnitNumber, Car.KilometerMax AS KilometerMax, Car.KMHigCos AS KMHigCos, Car.KMCI AS KMCI, Car.FuelType AS FuelType, Car.PS AS PS, Car.BrakeSpecInvest AS BrakeSpecInvest, Car.Totalweight AS Totalweight, Car.Unloadedweight AS Unloadedweight, Car.MatchRegNumber AS MatchRegNumber, Car.ConsumptionCityIn AS ConsumptionCityIn, Car.ConsumptionCityOut AS ConsumptionCityOut, Car.CO2Emission AS CO2Emission, RentObj.CompanyId AS CompanyId, RentObj.RentObjTypeId AS RentObjTypeId, RentObj.LicenceeId AS LicenceeId, RentObj.ObjEvent AS ObjEvent, RentObj.StationCIId AS StationCIId, RentObj.ObjPositionCIId AS ObjPositionCIId, RentObj.CostCarrierId AS CostCarrierId, RentObj.CheckInStatPlanId AS CheckInStatPlanId, R
Basically that's not a hard select for a database, especially for db2. But if you have performance problems, something is wrong, no doubt about it. Perhaps you are lacking indices, logic in the query is wrong etc. It's not possible to say based on the query text what's the right corrective action (or I suspect actions) in your this case. For the performance: I think you don't have to argue about it. Simply take the execution plan and go through it. It will reveal your bottlenecks in this query. But most importantly, you should go through the logic of the database design. I don't say that it's bad since I'm not aware of the logic nor the requirements, but I doubt that there are problems. Some observations: - why are the tables having same names, with a running number at the end. Some kind of custom partitioning or is it reasoned - seems that there are cumulated fields in the tables - based on conditions this seems like an OLTP query but based on the select and from portions the modeling is more close to a star-model - if the rows in classification tables are just 0/1 related to rentobj, perhaps use scalar subqueries in select - this contains two different joining syntaxes (between car and rentobj). Use just one etc...
The need to optimize rises from a bad design.My articles[^]
-
Basically that's not a hard select for a database, especially for db2. But if you have performance problems, something is wrong, no doubt about it. Perhaps you are lacking indices, logic in the query is wrong etc. It's not possible to say based on the query text what's the right corrective action (or I suspect actions) in your this case. For the performance: I think you don't have to argue about it. Simply take the execution plan and go through it. It will reveal your bottlenecks in this query. But most importantly, you should go through the logic of the database design. I don't say that it's bad since I'm not aware of the logic nor the requirements, but I doubt that there are problems. Some observations: - why are the tables having same names, with a running number at the end. Some kind of custom partitioning or is it reasoned - seems that there are cumulated fields in the tables - based on conditions this seems like an OLTP query but based on the select and from portions the modeling is more close to a star-model - if the rows in classification tables are just 0/1 related to rentobj, perhaps use scalar subqueries in select - this contains two different joining syntaxes (between car and rentobj). Use just one etc...
The need to optimize rises from a bad design.My articles[^]
Hello, Many thanks for the replies. The problem is. This query does not work at all. The query returns with an error telling me that there is no temporary system tablespace which can handle this query (pagesize). Without the order by clause the query is working. Strange enough, no error number is returned, only the description (in german) An object (rentobj) are assigned 12 categories like manufactor , type of object (truck,van,bus) etc. The category descriptions are stored in an own table. There can be only one category assigned to one object (1:1) But one object can have up to 12 categories, this is why there are 12 categories numbered from 1 til 12. Basically there are 12 joins on the same table category using 12 different fields in rentobj. This joins are used to retrieve the description of each category of the object. I am not the DBA of this database so I can't change system tables space settings. And the design is done by somebody else. The base table rentobj has more than hundred thousend records. I can't look at the execution plan. The query tool we are using does not allow that. Many thanks again
modified on Wednesday, December 10, 2008 11:09 PM
-
Hello, Many thanks for the replies. The problem is. This query does not work at all. The query returns with an error telling me that there is no temporary system tablespace which can handle this query (pagesize). Without the order by clause the query is working. Strange enough, no error number is returned, only the description (in german) An object (rentobj) are assigned 12 categories like manufactor , type of object (truck,van,bus) etc. The category descriptions are stored in an own table. There can be only one category assigned to one object (1:1) But one object can have up to 12 categories, this is why there are 12 categories numbered from 1 til 12. Basically there are 12 joins on the same table category using 12 different fields in rentobj. This joins are used to retrieve the description of each category of the object. I am not the DBA of this database so I can't change system tables space settings. And the design is done by somebody else. The base table rentobj has more than hundred thousend records. I can't look at the execution plan. The query tool we are using does not allow that. Many thanks again
modified on Wednesday, December 10, 2008 11:09 PM
dl4gbe wrote:
Without the order by clause the query is working.
Obviously too much I/O and space usage.
dl4gbe wrote:
I can't change system tables space settings
Concentrate on the execution plan. Find the bottlenecks and eliminate them. For starters create efficient indices if not present.
dl4gbe wrote:
I can't look at the execution plan. The query tool we are using does not allow that
Have a chat with your DBA or whoever has the access to correct tools (if I remember correctly, graphical tool for db2 administration has a very nice execution plan visualization). If you're not allowed to modify system settings, nor design, I hope that you still may create indexes (perhaps with you DBA). Otherwise you don't have tools to correct the problem.
The need to optimize rises from a bad design.My articles[^]