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