Friday, February 15, 2008

XQuery 1.0 Implementation - SQL Server 2005 vs Oracle 10g

In the past couple of months, I had the opportunity to implement XQuery on 2 separate projects, one using SQL Server 2005 back-end, and the other using Oracle 10g. Having created fairly complex XML queries with multiple joins in both applications, I was able to compare the XQuery implementations of both database providers. Bottom-line... the MSSQL 2005 implementation of the XQuery 1.0 spec is painfully non-standard thereby rendering it utterly useless for even simple joins. Oracle on the other hand, has done a fairly complete and accurate implementation of the spec, making it a pleasure to develop with.
I combed the net looking for accounts and opinions of other people who have worked with both implementations, but couldn't find any. Anyway, here are my top 3 reasons for calling the XQuery support in MSSQL 2005 a massive disaster....

1. Limiting Approach - MSSQL 2005 has implemented the XQuery syntax as an extension method of their XML data type. This approach limits the type of data stores that XML data can be queried from using XQuery (namely, XML type column and XML type variable in TSQL). In Oracle 10g XQueries are written as a separate statements, and can query XML data from the XML type column, XML type variables, but also from XML files within the filesystem, or XML files stored in XMLDB .

2. Where's the XQuery? - Another side-effect of the MSSQL approach to XQuery implementation is that joining disparate XML columns from different tables cannot be done exclusively using XQuery syntax. The only way to achieve a join is by using the "CROSS APPLY" clause to first extract the joining values in relational form and then using those to join the two XMLs. That also adds the extra step of having to use the FOR XML clause to convert the retrieved values back into XML form, aside from the fact that the resulting query does not look like XQuery at all. I believe the reason the XML data has to be initially converted to relational form in order to perform the join is because Microsoft has implemented XQuery support on only the initial tiers of their database engine. The core of their engine probably still only understands and processes data in relational form. That explains the need for CROSS APPLY to convert data from XML to Relational form, only to be converted back into XML form after the join is performed. I have not even dared to wonder what kind of performance hits are entailed in this approach.
Here is an example of a simple XQuery join in MSSQL. It looks nothing like an XQuery statement, but I will let you be the judge of that...

select R.i.value('@name', 'varchar(30)')   [@name], 
S.s.value('@name', 'varchar(30)') [@section],
R.i.value('@result', 'varchar(30)') [text()]
from Results cross apply xml_data.nodes('/results/item') R(i),
Sections cross apply xml_data.nodes('/sections/section') S(s)
where R.i.value('@section-id', 'varchar(30)') = S.s.value('@id', 'varchar(30)')
for xml path('result'), type



On the other hand, Oracle's XQuery implementation is truely native, allowing the developer to join XML data from disparate XML data sources using the XQuery FLWOR syntax. An example of a similar XQuery join in Oracle 10g...




SELECT XMLQuery('for $i in ora:view("REGIONS"), $j in  ora:view("COUNTRIES")
where $i/ROW/REGION_ID = $j/ROW/REGION_ID
and $i/ROW/REGION_NAME = "Asia"
return $j'

RETURNING CONTENT) AS asian_countries
FROM DUAL;



3. Don't LET - According to the XQuery 1.0 spec, the FLWOR (pronounced flower) statement comprises of the FOR, LET, WHERE, ORDER BY and RETURN clauses. Microsoft decided not to implement the LET clause in MSSQL 2005. I guess given the above mentioned limitations, the SQL Server team figured leaving out the LET clause would not cause any additional loss of functionality. But I have read that the team has since relented and implemented it in the upcoming SQL Server 2008 release.



I for one am very disappointed in SQL Server's kludgy and non-standard approach to supporting the XQuery 1.0 standard. All thoughts or comments on this topic welcome.







Technorati Tags: ,,,,




del.icio.us Tags: ,,,,









Wednesday, February 13, 2008

Well Rounded - Rounding to fractional precision in VBScript

The challenge was to figure out an algorithm to round any given floating point number to the nearest quarter (0.25) precision. Once you figure out the mathematical formula, it is fairly simple to implement it in .Net. Implementing it in VBScript is another ball game altogether. It took me a while to figure out why the code that worked so well in VB.Net, gave me the dreaded "Divide by zero" error in VBScript. The offending element in the code is the "MOD" operator. Of course it had to behave differently in VBscript. Read the below snippet that Microsoft mentions in MSDN reference for the VBscript "MOD" operator.

"The modulus, or remainder, operator divides number1 by number2 (rounding floating-point numbers to integers) and returns only the remainder as result."

"rounding floating-point numbers to integers"? Why... that is so obvious. Why didn’t I think of that before? (ignore my lame attempt at sarcasm!) How can any computer language provide a mathematical operator that rounds its input numbers to integers before performing the operation? Apparently, Microsoft thought nobody uses the "MOD" operator, so no one would notice. Enough ragging on Microsoft and VBScript. Needless to say, the outcome of this is that if your divisor is less than 0.5, it will be rounded to zero before the MOD operation is performed (For example: 10.76 MOD 0.25 will be converted to 11 MOD 0). That's when the mystical "Divide by zero" error will appear.

Undeterred by this little setback, I decided to write my own algorithm for rounding to the nearest given fractional precision without using the infamous MOD operator. My application only required rounding a floating-point number to the nearest quarter (0.25) precision, but I tried to make it generic enough so it could be rounded to any fraction. Here is the small code snippet that I came up with...

RoundFraction = 0.25
HalfFraction = RoundFraction / 2
RoundDown = FloatNum - (Fix(FloatNum/RoundFraction) _
* RoundFraction)
RoundUp = (FloatNum + HalfFraction) _
- (Fix((FloatNum + HalfFraction)/RoundFraction) _
* RoundFraction)
If RoundDown > HalfFraction Then
Result = _
Round((FloatNum + HalfFraction) - RoundUp, 2)
Else
Result = Round(FloatNum - RoundDown, 2)
End If



It works like a charm every time. Comments or better code is always appreciated, so let me know what you think of it.