Martin Reid
mwp.reid at qub.ac.uk
Tue Oct 29 04:46:46 CDT 2013
Spot on Arthur. Like Stewart I have immediate need of this as well. Martin -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller Sent: 28 October 2013 19:57 To: Discussion concerning MS SQL Server Subject: Re: [dba-SQLServer] Recursive Queries in Microsoft SQL Server 2008 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