Why can't recursive queries contain...?
in [Programming SQL]
|
Prev: Sql Server on VMWare
Next: AdventureWorks database
From: Erland Sommarskog on 27 Nov 2009 03:35 Michael Coles (admin(a)geocodenet.com) writes: > The SQL Server optimizer is the result of 2 decades of R&D, production > implementations and response to customer feedback. To completely start > creating a new transactional database engine from scratch based on the > OO model (as opposed to relational), complete with an > "industrial-strength" optimizer would require huge investments of time, > money and resources. It would also require huge investments in > marketing, trying to convince people they need this new DBMS while not > annihilating the client base for your SQL DBMS. I'm sure it could be > done, but is it worth it to anyone? Who knows... Object-oriented databases exists, or at least they existed in the first half of the nineties, when was the days I heard about them. I seem to recall that there were a few areas where they were better than relational databases, like CAD. But it is quite clear that for one reason or another, they have not been able to do a major breakthrough. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Michael Coles on 27 Nov 2009 14:17 > I'm just talking about performance with a large number of records. As an > example recently I had a query that ran slowly and I solved it by using an > indexed view. Basically I started off by telling sqlserver what I wanted > and left it up to sqlserver how to do that. But it did it too slowly so I > had to tell it how to do it anyway (I had to tell it to index a view). It > seems that with everything query I write I have to tell sqlserver how to > do it in some way. Unfortunately for you proper indexing is another tool used to express your intent to the optimizer. If you did not properly index the underlying tables then you only did half the job to begin with. You have many options that you can take advantage of to better define your intent and to help the optimizer achieve better performance, but you don't have to use them. As you point out above, your performance may suffer but it will complete eventually. > I think this is similar to VB6 and/or COM. I remember Dan Appleman said > that he didn't realise how broken COM was until after it was replaced. > While it worked and was very useful it had many faults and we now have > something much better. If MS didn't write .net the world would have still > spun so it wasn't essential. Exactly. At some point someone convinced MS that .NET was worth it. Now you just have to do the same... >> In addition to "square blocks of data" (i.e., tabular result sets), how >> about returning scalar values? > > That's just a 1x1 square really. No, not a "1x1 square", a "1x1 square with a data type". >> Or XML? Or a .NET *object*? > > Both are a big kludgy don't you think? Imagine returning a dot net > collection into a dot net client with a dot net back end? Sure everything > works now but wouldn't that be soooooo much better? :-) If you want to return a .NET collection, you can do that currently. I'm not convinced that an enterprise .NET/LINQ OODBMS will be "soooooo much better", but that's OK because you don't have to convince me... Convince the folks who have to throw resources at it to make your dream a reality. >> Obviously with 2005 (and 2008) all of these are possible today. Maybe >> I'm missing your point here... Perhaps you could describe in more detail >> what else you would like to be able to return from a stored proc? > > I would like to be able to return hierarchical data without implementing > kludges. Surely you would not recommend returning a dot net collection or > XML from an sqlserver stored proc if you needed performance? It would make > more sense just to return multiple square blocks of data and marry them > back up in the client. Having to use XML or .net objects to get > hierarchical data is really what I don't like about sqlserver in that > pretty much everything is possible in sqlserver but more often than not > there is something kludgy about it (ever used calculared columns?). It > reminds me of VB6 a lot in that everything was possible but mostly kludgy. > I know sqlserver is a much more serious product than VB6 but it does have > the same feel to me. You can do what you need if you stand on your head > and hold your tounge to your left nostril... :-) Have you used FOR XML to return hierarchical data from SQL Server? It's well-optimized, and I have absolutely no problem returning XML from a stored proc when performance is an issue. If you really want to do your joins in the client you definitely have the option to read all of your "square blocks of data" as you call them back and perform the joins on the client with your own C# nested loops. You may run into bandwidth limitations when you start pulling gigabytes of data across the wire and performing custom nested loops on the client to "marry them up", but on the plus side this could be the foundation for your very own custom database engine... So in reality you want a hierarchical DBMS as opposed to an OODBMS or a relational/SQL DBMS? Unfortunately SQL is optimized for relational data, not OO or hierarchical data, although there are ways to work around the limitations. There are definitely hierarchical DBMSs available if that's where your needs are, but you'll face other limitations there. At any rate this has been an interesting conversation, but I think we've eventually gotten to the root of your complaint here -- essentially you don't want a SQL DBMS, you want a hierarchical DBMS. You already know that the users here can't really rewrite SQL Server to your specifications, but you've also been informed that you can file your recommendations at http://connect.microsoft.com to get them heard by the people who can rewrite it to your specifications. So I think I've come to the end of my portion of this thread. You'll have to sell MS on your vision of rewriting SQL Server as a hierarchical DBMS or an OODBMS or whatever model you would like them to implement. -- Thanks Michael Coles SQL Server MVP Author, "Expert SQL Server 2008 Encryption" (http://www.apress.com/book/view/1430224649) ----------------
From: Michael Coles on 27 Nov 2009 14:27 > Object-oriented databases exists, or at least they existed in the first > half of the nineties, when was the days I heard about them. I seem to > recall that there were a few areas where they were better than relational > databases, like CAD. But it is quite clear that for one reason or another, > they have not been able to do a major breakthrough. Absolutely, and so do hierarchical DBMS's, which would solve his issues with the limitations on hierarchical data inherent in the relational/SQL model. I don't know why OODBMS's haven't caught on outside of specialized applications either -- maybe they're inferior in some respects, could be an issue of support, or it might just be good old-fashioned marketing? Either way, I think he needs to convince people who have the necessary resources to implement his dream DBMS... -- Thanks Michael Coles SQL Server MVP Author, "Expert SQL Server 2008 Encryption" (http://www.apress.com/book/view/1430224649) ----------------
From: Jeffrey Williams on 27 Nov 2009 15:24 If you really think you want an OODBMS or Hierarchical DBMS which also has capabilities to utilize SQL you might want to take a look at Intersystems Cache. It does all of the above and is an Enterprise class system that can also run on various hardware platforms. You have the options of accessing data as objects, or running queries using SQL. Just note that the same applies to this system that applies to any DBMS - there are things they do well, and things they do not. "Michael Coles" <admin(a)geocodenet.com> wrote in message news:F34B74AD-D2DC-448B-B7BE-7131FD68B2F7(a)microsoft.com... >> I'm just talking about performance with a large number of records. As an >> example recently I had a query that ran slowly and I solved it by using >> an indexed view. Basically I started off by telling sqlserver what I >> wanted and left it up to sqlserver how to do that. But it did it too >> slowly so I had to tell it how to do it anyway (I had to tell it to index >> a view). It seems that with everything query I write I have to tell >> sqlserver how to do it in some way. > > Unfortunately for you proper indexing is another tool used to express your > intent to the optimizer. If you did not properly index the underlying > tables then you only did half the job to begin with. You have many > options that you can take advantage of to better define your intent and to > help the optimizer achieve better performance, but you don't have to use > them. As you point out above, your performance may suffer but it will > complete eventually. > >> I think this is similar to VB6 and/or COM. I remember Dan Appleman said >> that he didn't realise how broken COM was until after it was replaced. >> While it worked and was very useful it had many faults and we now have >> something much better. If MS didn't write .net the world would have still >> spun so it wasn't essential. > > Exactly. At some point someone convinced MS that .NET was worth it. Now > you just have to do the same... > >>> In addition to "square blocks of data" (i.e., tabular result sets), how >>> about returning scalar values? >> >> That's just a 1x1 square really. > > No, not a "1x1 square", a "1x1 square with a data type". > >>> Or XML? Or a .NET *object*? >> >> Both are a big kludgy don't you think? Imagine returning a dot net >> collection into a dot net client with a dot net back end? Sure everything >> works now but wouldn't that be soooooo much better? :-) > > If you want to return a .NET collection, you can do that currently. I'm > not convinced that an enterprise .NET/LINQ OODBMS will be "soooooo much > better", but that's OK because you don't have to convince me... Convince > the folks who have to throw resources at it to make your dream a reality. > >>> Obviously with 2005 (and 2008) all of these are possible today. Maybe >>> I'm missing your point here... Perhaps you could describe in more >>> detail what else you would like to be able to return from a stored proc? >> >> I would like to be able to return hierarchical data without implementing >> kludges. Surely you would not recommend returning a dot net collection or >> XML from an sqlserver stored proc if you needed performance? It would >> make more sense just to return multiple square blocks of data and marry >> them back up in the client. Having to use XML or .net objects to get >> hierarchical data is really what I don't like about sqlserver in that >> pretty much everything is possible in sqlserver but more often than not >> there is something kludgy about it (ever used calculared columns?). It >> reminds me of VB6 a lot in that everything was possible but mostly >> kludgy. I know sqlserver is a much more serious product than VB6 but it >> does have the same feel to me. You can do what you need if you stand on >> your head and hold your tounge to your left nostril... :-) > > Have you used FOR XML to return hierarchical data from SQL Server? It's > well-optimized, and I have absolutely no problem returning XML from a > stored proc when performance is an issue. If you really want to do your > joins in the client you definitely have the option to read all of your > "square blocks of data" as you call them back and perform the joins on the > client with your own C# nested loops. You may run into bandwidth > limitations when you start pulling gigabytes of data across the wire and > performing custom nested loops on the client to "marry them up", but on > the plus side this could be the foundation for your very own custom > database engine... > > So in reality you want a hierarchical DBMS as opposed to an OODBMS or a > relational/SQL DBMS? Unfortunately SQL is optimized for relational data, > not OO or hierarchical data, although there are ways to work around the > limitations. There are definitely hierarchical DBMSs available if that's > where your needs are, but you'll face other limitations there. > > At any rate this has been an interesting conversation, but I think we've > eventually gotten to the root of your complaint here -- essentially you > don't want a SQL DBMS, you want a hierarchical DBMS. You already know > that the users here can't really rewrite SQL Server to your > specifications, but you've also been informed that you can file your > recommendations at http://connect.microsoft.com to get them heard by the > people who can rewrite it to your specifications. > > So I think I've come to the end of my portion of this thread. You'll have > to sell MS on your vision of rewriting SQL Server as a hierarchical DBMS > or an OODBMS or whatever model you would like them to implement. > > -- > Thanks > > Michael Coles > SQL Server MVP > Author, "Expert SQL Server 2008 Encryption" > (http://www.apress.com/book/view/1430224649) > ---------------- >
From: Jeffrey Williams on 27 Nov 2009 15:26
As I replied on another branch - I actually think his dream already exists. I know that everything he is asking for is already available from Intersystems Cache. "Michael Coles" <admin(a)geocodenet.com> wrote in message news:567B3528-2C5F-4748-852B-8A6D96399B70(a)microsoft.com... >> Object-oriented databases exists, or at least they existed in the first >> half of the nineties, when was the days I heard about them. I seem to >> recall that there were a few areas where they were better than relational >> databases, like CAD. But it is quite clear that for one reason or >> another, >> they have not been able to do a major breakthrough. > > Absolutely, and so do hierarchical DBMS's, which would solve his issues > with the limitations on hierarchical data inherent in the relational/SQL > model. I don't know why OODBMS's haven't caught on outside of specialized > applications either -- maybe they're inferior in some respects, could be > an issue of support, or it might just be good old-fashioned marketing? > Either way, I think he needs to convince people who have the necessary > resources to implement his dream DBMS... > > -- > Thanks > Michael Coles > SQL Server MVP > Author, "Expert SQL Server 2008 Encryption" > (http://www.apress.com/book/view/1430224649) > ---------------- > |