Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.hanau.net!news.tiscali.de!newsfeed.freenet.de!newspeer1.nwr.nac.net!border2.nntp.dca.giganews.com!nntp.giganews.com!cyclone1.gnilink.net!gnilink.net!nx02.iad01.newshosting.com!newshosting.com!post01.iad01!news.aliant.net!not-for-mail Date: Sat, 05 May 2007 10:06:51 -0300 From: Bob Badour User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 Newsgroups: comp.databases.theory Subject: Re: A new proof of the superiority of set oriented approaches: numerical/time serie linear interpolation References: <1178028682.007857.36200@p77g2000hsh.googlegroups.com> <1178130430.502230.295300@u30g2000hsc.googlegroups.com> <1178174344.866049.272810@h2g2000hsg.googlegroups.com> <6ui_h.5979$YW4.662@trndny06> <4639fde8$0$4017$9a566e8b@news.aliant.net> <1178211370.791765.118760@l77g2000hsb.googlegroups.com> <1178221012.371056.145700@u30g2000hsc.googlegroups.com> <1178260494.811737.293620@y5g2000hsa.googlegroups.com> <8kY_h.7025$2v1.1573@newssvr14.news.prodigy.net> In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Lines: 232 Message-ID: <463c813c$0$4036$9a566e8b@news.aliant.net> X-Complaints-To: abuse@aliant.net Xref: news.f.de.plusline.net comp.databases.theory:43779 David Cressey wrote: > "Brian Selzer" wrote in message > news:8kY_h.7025$2v1.1573@newssvr14.news.prodigy.net... > >>"David Cressey" wrote in message >>news:QvJ_h.11$rk5.8@trndny06... >> >>>"Brian Selzer" wrote in message >>>news:eFF_h.7202$rO7.3983@newssvr25.news.prodigy.net... >>> >>>>"Cimode" wrote in message >>>>news:1178260494.811737.293620@y5g2000hsa.googlegroups.com... >>>> >>>>>On 4 mai, 02:35, "Brian Selzer" wrote: >>>>> >>>>>>"Cimode" wrote in message >>>>>> >>>>>>news:1178221012.371056.145700@u30g2000hsc.googlegroups.com... >>>>>> >>>>>> >>>>>> >>>>>> >>>>>>>On 3 mai, 21:04, "Brian Selzer" wrote: >>>>>>> >>>>>>>>"Cimode" wrote in message >>>>>> >>>>>>>>>On 3 mai, 18:45, "David Cressey" wrote: >>>>>>>>> >>>>>>>>>>"Bob Badour" wrote in message >>>>>>>>> >>>>>>>>>[Snipped] >>>>>>>>> >>>>>>>>>>Maybe I cut him too much slack. If the past is any guide, >>>>>>>>>>optimizers >>>>>>>>>>will >>>>>>>>>>get better, and Brian won't. It's instructive that Both > > Cimode > >>>and >>> >>>>>>>>>>Kevin >>>>>>>>>>provide actual data, while Brian merely states his claim. >>>>>>>>> >>>>>>>>>For my defense, I am trying to trigge questions here. It would >>>>>>>>>be >>>>>>>>>illusional ton my part to hope to get definite answers. >>>>>> >>>>>>>>>>But even if he wins this particular race, he will not > > convince > >>>me >>> >>>>>>>>>>that >>>>>>>>>>cursors are the way to go. I've seen too much evidence to the >>>>>>>>>>contrary. >>>>>>>>> >>>>>>>>>You will soon realize that Brian mainly tries to convince >>>>>>>>>himself. >>>>>>>>>Getting out of procedural mindset is nothing but *natural* >>> >>>instinct. >>> >>>>>>>>Could you please elaborate? I don't need convincing. Sometimes > > I > >>>dip >>> >>>>>>>>into >>>>>>>>waters that a bit too deep and end up chewing on my foot, but > > when > >>>>>>>>I >>>>>>>>know >>>>>>>>I'm right, I don't need convincing, and I certainly don't try to >>>>>>>>convince >>>>>>>>myself. >>>>>>> >>>>>>>You are in denial. Don't you realize that at least 4 people have >>> >>>told >>> >>>>>>>you the exact opposite of your claims and you are refusing to > > admit > >>>>>>>they may just be right. Is 'nt there a slight doubt in your mind >>> >>>that >>> >>>>>>>you may be missing something ? that some aspect of fundamental >>>>>>>theory >>>>>>>may have elluded you? >>>>>> >>>>>>There is an understandable but unreasonable bias against cursors in >>> >>>this >>> >>>>>>group. I wouldn't be surprised if 15 people told me I was full of > > it, > >>>>>>since >>>>>>most of the time, cursors are used incorrectly or where a set-based >>>>>>solution >>>>>>would perform better, and once someone has had a bad experience, > > it's > >>>>>>difficult to set aside emotionalism and examine a similar solution >>>>>>dispassionately. >>>>> >>>>>Have you considered the idea that some people here were once in your >>>>>exact shoes and they once may had the unshakable belief in what you >>>>>just wrote. >>>>> >>>> >>>>I only know what I have experienced, and to me it makes sense. >>> >>>This raises the following question: does "what you have experienced" >>>include what you have learned from other people? If not, what will it >>>take >>>to persuade you that some of the writers in this newgroup are onto >>>something >>>you might find worth while. >>> >> >>I admit that I am more willing to trust my own personal experience than > > that > >>of others. And even more so when I have a logical rationale to back it > > up. > >>What will it take? In this case, hard evidence. >> >> >>>>Every >>>>optimizer step is implemented in the engine by using some form of >>> >>>iteration. >>> >>>>What I'm suggesting effectively replaces several optimizer steps with a >>>>single user-defined one that makes a single pass through the >>>>data--similar >>>>to what a step that computes aggregates does. >>> >>>I think you are greatly underestimating what a good optimizer can do. > > I'm > >>>judging from a limited understanding of the Rdb/VMS optimizer, going > > back > >>>to 1994. I can only assume that things have gotten better since then. >>>Even >>>if they haven't gotten better, I'm sure they haven't gotten worse. >>> >> >>When you realize that you have to squeeze a batch process that's taking > > over > >>30 hours to run into a 2-hour window...when you've tweaked, rewritten, and >>rewritten again a set-based solution, spending weeks pouring over > > execution > >>plans and traces...when you've tried indexes, optimizer hints, forced >>plans--even thrown hardware at it, and still have a solution that performs >>better, but not good enough to meet requirements...when you've thrown up >>your hands and polished your resume...you gain a far too intimate >>understanding of what an optimizer can and cannot do. The most efficient >>set-based solution I could come up with still took 7 hours to run--even >>after adding hardware. But after introducing 5 cursors at key points in > > the > >>process, the execution time dropped to just over an hour. In three >>instances a cursor replaced a complex section of an execution plan > > resulting > >>from multiple self-joins; in another instance, two cursors executing >>simultaneously replaced a complex section of an execution plan resulting >>from several correlated subqueries. What can one say? Selzer's rant speaks to a shitty optimizer. It condemns a specific un-named product and nothing else. > And yet, someone else in this newgroup (not me) reported that he took a > batch job that was taking 45 hours using legacy code, rewrote it using > about 300 lines of SQL, and got something to run in about 45 minutes. Do > you think he was lying? Do you think he was using cursors? I have done lots of optimization. I have never achieved a gain from using a cursor, and I have achieved numerous huge big-O improvements by getting rid of cursors. Like taking an O(n^5) cursor algorithm and replacing it with a single select statement involving multiple joins, quantification and correlated subqueries that executed in O(n) -- and that was using a small footprint embedded dbms one could buy for a couple hundred dollars. Within a few moments of inheriting the cursor based code mentioned above, I recognized the cursor solution would not scale and I changed it. My boss was suitably impressed when he asked me to look into a performance problem found in the field, and I replied that I had already found it and fixed it. > I also have a long history of personal experience to draw on, including > optimizer workarounds. But I am not as dismissive of what others report as > you are. Cimode is offering as close to "hard evidence" as anything that > can be squeezed through the narrow pipeline of a newgroup. And yet you've > dismissed his conclusion out of hand, without really putting it to the > test. Selzer is long on opinion and short on facts or sense. > (Cimode, I realize that your main point was about interpolation, and not > about performance. But it seems to me that, as long as someone dismisses > your inputs because "he can make it run faster by manual algorithm > selection" that your flowers are wasting their freshness on the desert > air.)