Archive

Archive for July, 2012

Universe Parameters – ANSI 92

Universe Parameters – ANSI92

Business Objects Universe Designer supports ANSI 92 syntax for SQL joins. ANSI 92 is not supported by default. We have to activate it by setting the SQL universe parameterANSI92 to YES. If this option is set to yes, then the SQL generated will be in ANSI92 format.

 

clip_image002

 

Some of us may want to set this option to ‘yes’ when universe has mostly outer joins, since outer joins are less problematic for ANSI92 format of SQL.

Comparison between default join syntax and ANSI 92 syntax

Join syntax for two joins is shown below. The first shows the default behavior where the join is defined in the WHERE clause, the second shows the same join in the FROM clause using the ANSI 92 standard.

Default join syntax

SELECT ST.Name, SP.SalesPersonID, C.FirstName, C.LastName, SOH.SalesOrderNumber, SOH.TotalDue
FROM Sales.SalesTerritory ST,
Sales.SalesPerson SP,
HumanResources.Employee E,
Person.Contact C ,
Sales.SalesOrderHeader SOH
where ST.TerritoryID = SP.TerritoryID and
SP.SalesPersonID = E.EmployeeID and
E.ContactID = C.ContactID and
SP.SalesPersonID = SOH.SalesPersonID
GROUP BY ST.Name, SP.SalesPersonID, C.FirstName, C.LastName, SOH.SalesOrderNumber, SOH.TotalDue
ORDER BY ST.Name

Implicit

  • Out-dated format
  • Difficult to read
  • Promotes errors
  • Does not follow best practices and naming conventions.

Same query using the ANSI 92 standard Join

SELECT ST.Name, SP.SalesPersonID, C.FirstName, C.LastName, SOH.SalesOrderNumber, SOH.TotalDue
FROM Sales.SalesTerritory ST INNER JOIN
Sales.SalesPerson SP ON ST.TerritoryID = SP.TerritoryID INNER JOIN
HumanResources.Employee E ON SP.SalesPersonID = E.EmployeeID INNER JOIN
Person.Contact C ON E.ContactID = C.ContactID INNER JOIN
Sales.SalesOrderHeader SOH ON SP.SalesPersonID = SOH.SalesPersonID
GROUP BY ST.Name, SP.SalesPersonID, C.FirstName, C.LastName, SOH.SalesOrderNumber, SOH.TotalDue
ORDER BY ST.Name

Explicit

  • SQL 92 ANSI compliant
  • Easier to read
  • Less prone to errors
  • Keeps code consistent
  • Performance benefits

Let’s understand this concept better by using any oracle’s explain plan or sql server profiler. For my example I have taken profiler. Following screenshot shows the information of cost and duration for above two queries. With which we can easily understand the cost, performance and duration issues when using the two formats of join.

 

clip_image004

 

Even if we take the execution plan, most of the times there won’t be any differences like the above one. If we add some join conditions (Outer) there are chances for seeing some differences. It’s up to the users or developers whether which format they can use, some of us might feel comfortable using old syntax and some don’t. But when it comes to performance, we should check and use it appropriately.

Thanks.

Advertisements
%d bloggers like this: