Author |
Topic |
LaurieCox
158 Posts |
Posted - 2006-10-31 : 12:43:32
|
Hi,Yesterday I asked for help in what I thought was a complicated query (Want to avoid cursors ... need help with query, but obviously wasn't (evidenced by the speed in which my question was answered and a comment made by another poster in the thread). I have figured out how the query works. I even figured out how to modify it so that the date diff was displayed on the first row instead of the second:SELECT t1.clt_num, t1.autbeg_dte, t1.autend_dte, isnull(datediff(day, t1.autend_dte, t2.autbeg_dte), 0)FROM #testit t1 left join #testit t2 on t1.clt_num = t2.clt_num and t2.autend_dte = (select min(autend_dte) from #testit x where x.clt_num = t2.clt_num and x.autend_dte > t1.autend_dte)order by t1.clt_num, t1.autbeg_dte What I would like to know (if possible) is what “thought process” was used to come up with this solution or do you just “intuit” it (I hope not, because I sure don’t).I know this is a very vague question, and maybe if I get a few responses I may be able to refine it.Also can anybody recommend any books or web pages that teach this sort of thing (not syntax … I don’t have a problem with that).Thanks,Laurie |
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-10-31 : 12:57:13
|
MMost application developers are brought on process one row/record at a time. Unless you are using a database, set methodolgy can't be applied there.To think in terms of sets, or basically mathmatics, you need to determine what the population is that you want to affect.For example, you want to update everyone in California with something. You could do it one person at a time, but that would take a long time, or you ocould just say, UPDATE Table SET Something = whatever WHERE STATE_CD = 'CA'HTHBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-31 : 14:09:32
|
Experience.I come from a row-by-row background - using ISAM back in the '70s and '80s.Reading stuff on SQLTeam, and generally "doing it" for a long time means I'm fairly set based now, but not to the extent that folk are here. Mind you, I very rarely use a cursor, and I expect I have enough experience that when I do there probably isn't a set-based solution.I don't know whether it will help, but considering solutions that use a table of numbers ("Tally Table") might get you onto the right wavelength. For example, the methods used to "split" a CSV into rows.Have a look at my post of 06/05/2005 03:41:12 on http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648. Its not my original work, and it takes some understanding, but its VERY quick compared to other processing methods.You could also look out for articles / books by Joe Celko - he has a book called "SQL for Smarties" which has some examples that seem a weird solution-path for old sequential-programming-hackers!Kristen |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-10-31 : 16:14:36
|
quote: Originally posted by AlgaeSea What I would like to know (if possible) is what “thought process” was used to come up with this solution or do you just “intuit” it (I hope not, because I sure don’t).
Remember High School math class?Remember learning "Venn Diagrams"?Remember how you thought they were totally stupid and a complete waste of time?Well, that is what SQL programmers do in their heads all day long.STAR SCHEMAS ARE NOT DATA WAREHOUSES! |
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2006-10-31 : 16:33:55
|
Who uses their heads or diagrams.I need to hold up my hands like I'm holding collections of things.I guess this is the "mits" approach.I have a mitfull of these and I need tochange, extract, affect the few of those in that mit that are related to the onesin the other mit. Kind of chisenbop for SQL folks.You can't work on one thing at a time, because all the otheritems in your hands would scatter all over the floor.For the original poster, here is a great spot to practice, mix real simple examples, with real high level reading, don't get too frustrated with what you think you know or has worked in the past. There is always a battle between set based and iterative operators. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-10-31 : 17:48:24
|
quote: For the original poster, here is a great spot to practice, mix real simple examples, with real high level reading
I agree with this completely, and Joe Celko's SQL For Smarties does this very effectively. Don't let the advanced topics worry you, even if you don't understand them, your brain will work its way up to them (one of my pet peeves are intro books that simplify set-based theory into nothingness).I also agree with the physical approach: look for real-world examples that could be thought of as sets, and how you deal with them. As far as practice, make a solid effort to NEVER write a cursor again. It's not as hard as you think, and forcing yourself to avoid it makes you learn faster. |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-11-01 : 02:44:33
|
Asking a question like this means that you are on the right track and in the right state of mind. People come here *all the time* asking for solutions to this and that and most of them get really good answers to their questions. But I don't think very many of them actually take the time to understand what is really going on when someone like Peso or khtan or whoever post some hardcore setbased solution. The same thing happened to me a few years back (and it still happens quite frequently!) and I remember especially once it took me several weeks to grasp what someone here threw together in a matter of minutes. Becoming a set-oriented developer doesn't come easily and there actually is a very good reason why some people have sql server development as a full time job. What made me "see the light" was when I learned the concept of derived tables and some pretty persistent people here giving me all the help I needed.In short I can say that SQLTeam and the truly great people that reside here taught me *everything* I know about sql-server admin/development. So if you are willing to learn and are prepared to do the work you are in the best of hands --Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-11-01 : 03:59:45
|
quote: Yesterday I asked for help in what I thought was a complicated query (Want to avoid cursors ... need help with query, but obviously wasn't (evidenced by the speed in which my question was answered and a comment made by another poster in the thread).
Laurie one of the reasons you got a fast reply was that you provided all the information needed to help you, as mentioned in the post you refer to. All too often people don't (or won't) do that.With regards to set based thinking - I once went to see Itzik Ben Gan. For me I found just watching him and getting an insight into the way people like him think quite inspirational.steve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2006-11-01 : 15:28:33
|
Here is a 30000 foot view from Microsofts DLINQ projectMost programs written today manipulate data in one wayor another and often this data is stored in a relational database. Yet there is a huge divide between modern programming languages and databases in how they represent and manipulate information. This impedance mismatch is visible in multiple ways. Most notable is that programming languages access information in databases through APIs that require queries to be specified as text strings. These queries are significant portions of the program logic yet they are opaque to the language, unable to benefit from compile-time verification and design-time features like IntelliSenseTM.Of course the differences go far deeper than that. How information is represented, the data model, is quite different between the two. Modern programming languages define information in the form of objects. Relational databases use rows. Objects have unique identity as each instance is physically different from another. Rows are identified by primary key values. Objects have references that identify and link instances together. Rows are left intentionally distinct requiring related rows to be tied together loosely using foreign keys. Objects stand alone, existing as long as they are still referenced by another object. Rows exist as elements of tables, vanishing as soon as they are removed.It is no wonder that applications expected to bridge this gap are difficult to build and maintain. It would certainly simplify the equation to get rid of one side or the other. Yet relational databases provide critical infrastructure for long-term storage and query processing, and modern programming languages are indispensable for agile development and rich computation. Until now, it has been the job of the application developer to resolve this mismatch in each application separately. The best solutions so far have been elaborate database abstraction layers that ferry the information between the application’s domain specific object models and the tabular representation of the database, reshaping and reformatting the data each way. Yet by obscuring the true data source these solutions end up throwing away the most compelling feature of relational databases; the ability for the data to be queried. "it's definitely useless and maybe harmful". |
|
|
samuelclay
Yak Posting Veteran
71 Posts |
Posted - 2006-11-01 : 17:06:39
|
I want to start by apologizing if you took my comment on your previous post as an insult. I meant quite the opposite. If you have read many posts here, you will know that there are many that say the equivalent of "I am getting bad results from my query, what is my mistake?" without giving data structures, sample data, sample code, output, expected output, or error messages and want people to magically know the answer to their problems. I wasn't commenting on the simplicity of your problem but the fact that you included everything needed to get fast help..As far as set based queries go, I'd say read lots and practice lots.. The fact that you are asking shows you are interested in learning to do something rather than just have a solution given to you. That, in itself says alot. As mentioned above, the people here are very knowledgable, helpful and patient (the patron saint goes above and beyond in the patience category). As Rob mentions, make the effort to never use cursors. You might have to step back and look at the data in different ways to see different solutions. Like everything in life, there are exceptions to the rule, but problems with cursors as the "only solution" are few and far between.practice, practice, practice (and come here often)... |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2006-11-01 : 17:12:34
|
Thinking in sets is a natural instinct...Imagine if you had 1000 paper files in a cabinet..Now imagine that you have to move those 1000 papers to another cabinet.Only a moron would choose to move them one at a time...DavidMProduction is just another testing cycle |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-11-02 : 01:50:19
|
Heh byrmol...that is actually a quite cool example...never thought of it that way --Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-02 : 01:58:38
|
Extend that thinking process with "move all paper files from cabinet a to cabinet b where last name begins with s".Do you find the first paper with s and move that to other cabinet, and look for next s and move that?Or do you find all paper with s before you move all at one time?Peter LarssonHelsingborg, Sweden |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-11-02 : 02:18:20
|
Wow...this gives a whole new perspective to explaining stuff! Hehe, explaining a clustered index will be a bliss from now on: "...imagine a book with 1000 pages, but all the pages are in random order..." --Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-11-02 : 07:41:50
|
Actually that's not a good example for a clustered index, it will reinforce the idea that you must always have a surrogate primary key and cluster on it. A better example is the phone book: the clustered key is last name, first name, and the primary key is phone number. It's a good example of when to break the clustered primary surrogate key model. It also has structured data, fits x number of rows on a page, and arranges pages in a specific order. You can also easily illustrate range selection, seeks, and B-tree navigation.Once they get that then you can better illustrate the random page numbers. Although none of these are really set-based concepts, but they help model the data storage better. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-11-02 : 07:50:11
|
AlgaeSea: here's a great example you can explore in your set-based meditation:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74383 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-02 : 07:54:56
|
AlgaeSea: Marks will be awarded for the extent to which your solution is Set Based |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-11-02 : 07:58:46
|
I agree totally with what is posted above. It takes training. Not MCDBA training ... not Books Online training. One of the best posters around here (missing for several years now in the cranberry fields of Wisconsin) went by the handle "setbasedisthetruepath" ... another awesome poster is "Arnold Fribble" ... go back through these forums and read every one of either of their posts. Amazing stuff.One suggestion I'd like to add (it is obvious) ... whenever you find yourself typing declare cursor or creating a while loop, this should set off a red flag that causes you to stop and try to consider a set based approach. Whenever you find yourself writing a batch with 3 update statements to the same table in a row ... figure out how to consolidate. Get good at testing different ways to write the same query in terms of execution time and plan evaluation.Another suggestion ... challenge yourself to understand the fundamentals of database systems. Celko's Smarties book are very interesting, but for the real deal, pick up C.J. Date's Introduction to Database Systems. It may take a year to get through Date's book and another 6 months to get through it a second time ... but if you do, you will be light years ahead of the pack in terms of a thinking database designer/programmer.Jay White |
|
|
LaurieCox
158 Posts |
Posted - 2006-11-02 : 08:27:12
|
Wow ... lots of stuff to read. I want to respond in more detail, but I have real work to do ... Will come back with a more detailed response laterThanks,Laurie |
|
|
|