Post reply

First time semi-complex query - its slow!!!

  • December 27, 2010 at 1:52 pm

    #1266569

    -

  • December 28, 2010 at 10:23 am

    #1266815

    Please go away Joe. Your rants and caustic attitude were never welcome on the forums and they are not welcome here either. And you obviously failed to see that the OP had already been marvelously helped by other smarter and nicer users.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • December 28, 2010 at 9:34 pm

    #1267007

    CELKO (12/27/2010)

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. yada-yada-yada

    You're a day late and a dollar short, Joe. And remember... mag tape processing and IDENTITY columns works a charm even in an RDBMS! 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 30, 2010 at 2:17 pm

    #1267951

    CELKO (12/29/2010)

    >> mag tape processing and IDENTITY columns works a charm even in an RDBMS! <<

    LOL! I keep seeing people who never worked with tapes re-discovering them in SQL. I guess a deck of punch cards is "more natural" than a set. Since the future of RDBMS is with parallelism, I guess we just unwind the mag tapes and put a read/write head over every record on the tape :w00t:

    Getting serious for a moment, I have a theory that we think this way because we grew up with alphabetic and numeric ordering, hierarchies and left to right sequential processing. The "big three" of Western Culture's view of data and process.

    A friend taught American culture in Red China just before Tian'anmen Square. She would get her class roster in Chinese in one column and Latin transliteration in a second column. She about 150 students. The roster was never in the same order twice -- no concept of alphabetical order.

    My Arab students frequently ordered the sort columns on the right side of their printouts. They also did their loops as "FOR i FROM n TO 1 BY -1" instead of "FOR i FROM 1 TO n" when they had a diagram. My Asian students wrote "IF 5 > a THEN.." rather than "F a < 5 THEN .." and often put the exceptions in the the THEN clauses and normal processing in the ELSE.

    People make systematic errors; random errors are rare. But before you can teach them, you have to know their system. The problem is that "fish don't think about water" -- they don't know their own mindset. You really have to grab them and point out every little thing and where it came from if you want them to learn.

    Mind game for the day: What if Western culture had matrix organization instead of hierarchies?

    Heh... you keep saying that "we" think that way. I don't think that way... not by individual rows nor by punch card. Call it "set based" thinking or not, I think in columns instead of rows and compared to a mag tape or punch cards, there's still not much difference. There ARE things in an RDBMS where processing or selection order is important just as much as they were on mag tape and punch cards. That's why they created ranking functions and ORDER BY and other crazy stuff that has made life in databases a whole lot easier.

    The other thing is that (usually) SQL is actually more like mag tape than you'd think. When you needed just certain data, hints were placed on the tape allowing you to do a high speed "skip" to certain blocks of data much like the indexes of today do. If you didn't have such things, then just like in a database, you'd end up doing the equivalent of a table scan by reading the whole tape.

    Mind game for the day: What if Western culture had matrix organization instead of hierarchies?

    What mind game? If it had a matrix organization instead of a hierarchy, it wouldn't be "Western Culture". 😉

    And I'm also not one of those folks who hasn't worked with Mag Tapes or Punched Cards. My first "program" was on unit-record equipment using a wired punch board. There are tricks from mag tapes, punched cards, and assembly language that may insult the sensibilities of relational zealots all over the world but that doesn't make the methods any less effective. Besides... who cares? According to rumor, most versions of SQL and the underlying engines don't actually meet Codd's 100% of the rules for relational databases anyway. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 30, 2010 at 2:26 pm

    #1267954

    Ok... forgot a question I was going to ask you, Joe. If you're so set against "mag tape" technology, why do you always recommend converting an Adjacency List to a Nest Set by using a (gasp!) "Push-Stack"? You're the pot calling the kettle "black" because your methods for that conversion use RBAR on steriods (ie: 1950's sequential records thinking) in a given order (mag tape/punched cards thinking) using a (gasp!) bloody "Push-Stack" (Assembly Language thinking).

    If you're so against all 3 methods, why do you keep pushing people to use all 3 methods? There actually are several set-based methods of the Adjacency List conversion to Nested Sets that blow the doors off the tired old "FILO" "Push-Stack" method you keep pushing all over the net.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 30, 2010 at 2:48 pm

    #1267966

    You know, I thought his reply was rude and was elitist - but then i researched who he was and removed my post berating his communication style because I thought he would be well respected.

    I am glad that it is not the case (!). I thought it very odd that a response to my problem was resolved in the first few posts within hours - something i've come to love SSC for. At some point when my skills allow I hope to be able to give back to the community.

    Thanks, all.

  • December 30, 2010 at 4:41 pm

    #1267987

    ProKelly (12/30/2010)

    You know, I thought his reply was rude and was elitist - but then i researched who he was and removed my post berating his communication style because I thought he would be well respected.

    I am glad that it is not the case (!). I thought it very odd that a response to my problem was resolved in the first few posts within hours - something i've come to love SSC for. At some point when my skills allow I hope to be able to give back to the community.

    Thanks, all.

    His knowledge and abilities are well respected, even if all of us don't agree with his approaches on certain topics (Hard-core ANSI/ISO standardization, for example). His style of communication and his personal communcation abilities, at least online, are not.

    I'm sure you'll give back eventually, Kelly. It's part of learning. I've learned more helping people then I did helping myself. It's part of the cycle. 🙂

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • December 31, 2010 at 12:47 pm

    #1268175

    CELKO (12/30/2010)

    EXACTLY! But would it work the same way? 🙂

    Heh... neither one works correctly so I'd have to say, who cares? 😉 The other answer would be "It Depends"... if you're one of the "have nots", then the answer is likely "Yes", they work the same way because crap can flow "downhill" even in a matrix. 😉

    But, we digress... let's get back to the subject at hand. How is it that you can berate so many people for using technoligies that you yourself push as being the right way to do things? 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 3, 2011 at 6:31 pm

    #1268714

    CELKO (1/3/2011)

    Jeff Moden (12/30/2010)

    Ok... forgot a question I was going to ask you, Joe. If you're so set against "mag tape" technology, why do you always recommend converting an Adjacency List to a Nest Set by using a (gasp!) "Push-Stack"? ... There actually are several set-based methods of the Adjacency List conversion to Nested Sets that blow the doors off the tired old "FILO" "Push-Stack" method you keep pushing all over the net.

    Traversals are procedural, so why would I mind using a procedural technique on a procedural problem? I also recommended using report writers to write reports, document systems to handle documents, etc.

    When people do an adjacency list model, I have found that most of the time, the data is dirty. There are cycles, redundancies, multiple superiors and orphans all over the place. The constraints to prevent these problems are a bitch to write, but you seldom see even something as simple as" CHECK (emp_id <>boss_emp_id)" to catch a singleton node/cycle.

    If you hide the push-down stack in a recursive CTE, you cannot easily step thru the path traversal and catch these things.

    Do you have a favorite technique you'd like to share?

    Heh... yeah... agreed on almost all of that although I've not see any of your conversion code on the web check for such aberrations as what you mention. 😉 I have, however, taken your constraint recommendations for Adjacency Lists to heart.

    I do disagree with traversals to build the nested set necessarily being procedural and no, it wouldn't be a push-stack... CTE or not. And, yes, I do have a favorite technique... but I'll wait until you publish the second edition of "trees" first. 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 4, 2011 at 12:08 am

    #1268762

    Craig Farrell (12/30/2010)

    ProKelly (12/30/2010)

    You know, I thought his reply was rude and was elitist - but then i researched who he was and removed my post berating his communication style because I thought he would be well respected.

    I am glad that it is not the case (!). I thought it very odd that a response to my problem was resolved in the first few posts within hours - something i've come to love SSC for. At some point when my skills allow I hope to be able to give back to the community.

    Thanks, all.

    His knowledge and abilities are well respected, even if all of us don't agree with his approaches on certain topics (Hard-core ANSI/ISO standardization, for example). His style of communication and his personal communcation abilities, at least online, are not.

    I'm sure you'll give back eventually, Kelly. It's part of learning. I've learned more helping people then I did helping myself. It's part of the cycle. 🙂

    Now that is soooo true!

    And the other part of the cycle - when you do start helping, don't get upset/disappointed if what you suggest is "thrown out the window" by other, better code that someone else posts - that's just the continuation of your learning. (In fact, once you start helping others, you should expect this to occur at any time!)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • January 6, 2011 at 12:20 am

    #1269890

    CELKO (1/4/2011)

    >> do disagree with traversals to build the nested set necessarily being procedural and no, it wouldn't be a push-stack... CTE or not. And, yes, I do have a favorite technique... but I'll wait until you publish the second edition of "trees" first. <<

    I just mailed the contract for it today! 😀 Work starts this month! Send in new material and correction to get fame, glory and a line in the index!

    We are not using the outsourced typesetters that completely screwed up the 4-th edition of SQL FOR SMARTIES this time.

    I'll have to pass on that. Good luck on your book.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply