Stuart McLachlan
stuart at lexacorp.com.pg
Mon Oct 28 15:15:12 CDT 2013
Oustanding analysis! I'm going to file this one for use in future discussions with "managerila types" -- Stuart On 28 Oct 2013 at 15:56, Arthur Fuller wrote: > 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 > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >