[dba-SQLServer] Recursive Queries in Microsoft SQL Server 2008

Arthur Fuller fuller.artful at gmail.com
Mon Oct 28 14:56:58 CDT 2013


Two distinct questions here, and this concerns graph theory (see the
chapter on this subject at www.artfulsoftware.com). Short answer is this:
for performance, define a depth, else risk the consumptive levels of
performance. Slightly longer answer: if you have not already separated your
OLTP requirements from your data-analysis requirements, then wake up and
smell the servers! I'm not even talking Big Data here. As in all things
regarding databases, we're dealing with trade-offs, but the bottom line is,
"Separate the requirements of the OLTP part from the requirements of the
OLAP part." Basic rule. Two databases, in relative synch, but the OLAP
instance is guaranteed not to be up to the moment; a few seconds or minutes
or even hours behind, but the circumstances in which the OLAP part must be
within minutes or seconds from the OLTP part are extremely few and far
between. Analysis of the history should, IMO, seldom if never occur via
direct inquiry upon the OLTP instance, since such interrogations inevitably
demand aggregations, which are hugely expensive, and slow down the basic
nature of OLTP, which is to generate income. Slow that part down and here's
a shotgun and there is your foot, as it were.

Back to Jim's point: Oracle made an intelligent decision in limiting
recursive levels, but IMO this decision was based on the faulty assumption
that one was interrogating the OLTP database rather than the OLAP instance,
and perhaps further, that these two instances reside upon the same physical
server. Two mistakes, rolled into one bad decision. In a high-traffic
situation, the OLTP instance should have one server and the OLAP instance
reside on another physical server. Should the licensing costs prove
prohibitive, then switch to MySQL or MariaDB or PostGres.

The bottom line is, for managerial analyses, use a separate instance, else
risk performance on the OLTP side of things, which is the firm's bread and
butter. Various replication techniques can shrink the window between OLTP
and OLAP to a few minutes. Think of it this way: how many bytes must be
transmitted per hour or minute to update the OLAP instance? A given order
and its details amount to how many bytes? In a normalized database where
most of the delay-columns are FKs into related tables, all we're really
talking about is a few KB per transaction, so even if they come in at 1000
per second, it's not that big a problem. So the OLAP instance is a few
seconds or even minutes behind. If management can't live with that, then
I'm in search of another job. Even Google, with its fantastic scaled-out
engineering, does not guarantee contemporaneous results. No one can.

Database apps are all about trade-offs. Be realistic about this. Tell
Management that their results are subject to a temporal window, which
depending upon the hardware and the implementation and the budget, might
vary from an hour to a few minutes to a few seconds. The point is, the
money comes from the OLTP part, and the decisions come from the OLAP part,
and let's not step on our udder while trying to obtain some more food.

IME and IMO, any attempt to use the OLTP instance as the reporting instance
is doomed to fail -- especially so in the case when the OLTP instance is
successful. Don't undermine its success with analysis. That's not its job.
That job is for the OLAP instance. Combining them in a single instance is
equivalent to saying to the potential customer, "Yes I will sell you this,
but first I need you to fill out this 20-question form." Duh!

Final thought: in the face of opposition from managerial folks, just tell
them that "Real Time" means "an hour ago".

Arthur


On Mon, Oct 28, 2013 at 3:02 PM, Jim Lawrence <accessd at shaw.ca> wrote:

> Just an off side note: Oracle 10.x to 11.x used to have a limit of 6
> levels and at the time MS SQL had only one level...I would suspect that MS
> SQL now has no such level limit. The one thing I noted was that more that
> one level deep could bring my client's servers to "its knees" so to speak,
> so used multiple temp files instead. MS SQL may have a resolution for that
> problem.
>
> Jim
>
> ----- Original Message -----
> From: "Gustav Brock" <gustav at cactus.dk>
> To: "Discussion concerning MS SQL Server" <
> dba-sqlserver at databaseadvisors.com>
> Sent: Monday, October 28, 2013 9:43:33 AM
> Subject: Re: [dba-SQLServer] Recursive Queries in Microsoft SQL Server 2008
>
> Hi Arthur
>
> I think I recall that article. But wasn't that about a limited/fixed number
> of sublevels? True recursion should just continue one level down until no
> more levels are found, no matter how many levels.
>
> /gustav
>
> -----Oprindelig meddelelse-----
> Fra: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Arthur
> Fuller
> Sendt: 28. oktober 2013 16:34
> Til: Discussion concerning MS SQL Server
> Emne: Re: [dba-SQLServer] Recursive Queries in Microsoft SQL Server 2008
>
> Gustav,
>
> Yes and no. Somewhere on TechRepublic there's an article by me that
> discusses recursive SQL within a stored procedure. If you can't find it, I
> have a copy in Word format that I could send you off-list, but their search
> is pretty good. Try "recursion in t-sql". If that doesn't work try
> searching
> for my name and that ought to find it.
>
> Anyway, you're right: The WITH <name> AS (SELECT...) construct is T-SQL not
> pure SQL. In essence, what you're doing is creating a virtual temp table
> and
> then joining that to the real table (or view).
>
> And you're also wrong. In pure SQL it's quite common to join a table or
> view
> to itself. One typical use of this technique is to find the second-last
> item
> in a set (which you do by joining a set to itself but the WHERE clause uses
> a "<" comparison rather than an "=". Combine that with an ORDER BY ... DESC
> and you get the previous row. If that's a little fuzzy, I'll supply an
> example, but I encourage you to ponder it first. LOL.
>
> Arthur
>
>
> On Mon, Oct 28, 2013 at 11:06 AM, Gustav Brock <gustav at cactus.dk> wrote:
>
> > Hi Arthur
> >
> > So the WITH .. AS isolates the inner part?
> > Normally, in pure SQL, you can't let a query pull data from itself
> > without getting a circular reference error or the like.
> >
> > /gustav
>
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
Arthur


More information about the dba-SQLServer mailing list