Why can't recursive queries contain...?
in [Programming SQL]
|
Prev: Sql Server on VMWare
Next: AdventureWorks database
From: Michael Coles on 20 Nov 2009 00:01 The LINQ provider for SQL actually generates SQL code behind the scenes. To be fair, it does a fair job of basic optimization (properly parameterizing variables, etc.), but it doesn't really have an "industrial" optimizer built into its code generator. Not sure how you feel LINQ will replace SQL--who's to say EF won't replace LINQ next year? > As an example, I can write my own connection object which connects to > "Mikes Propriotry File(c)" and do anything I like with it. In sqlserver if > you've got an object, eg a cursor, then you're pretty much stuck with what > they give you. What extensibility they give you is usually extremely > restrictive. I was following your conversation intently, but you completely lost me on this comparison... What problem are you trying to solve with a cursor here, and what's the comparison between a cursor and "an object" that you're making? If you really want to compare apples to apples (though possibly different varieties) then you'd be better off comparing a "SqlDataReader" "object" to a cursor. I guess you don't see a lot of comparisons of "Connection" objects (like "SqlConnection") to "cursors", in much the same way that you don't see people comparing grizzly bears to drywall. -- Thanks Michael Coles SQL Server MVP Author, "Expert SQL Server 2008 Encryption" (http://www.apress.com/book/view/1430224649) ---------------- "Michael C" <mike(a)nospam.com> wrote in message news:etDz7xZaKHA.616(a)TK2MSFTNGP04.phx.gbl... > "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message > news:Xns9CC0F0D571492Yazorman(a)127.0.0.1... >> That must explain ahy in VB .Net can write: >> >> Using cn As SqlConnection = setup_connection(), _ >> cmd As SqlCommand = cn.CreateCommand() >> >> But I in C# has to write: >> >> using (SqlConnection cn = setup_connection()) { >> using (SqlCommand cmd = cn.CreateCommand()) { > > That's is about as minor an issue as you can get. Doing what you've > described would be handy in C# but it doesn't stop you doing something. > While you picked on something completely trivial you've missed all the > massive amounts of extensibility they give you in c#. As an example, I can > write my own connection object which connects to "Mikes Propriotry > File(c)" and do anything I like with it. In sqlserver if you've got an > object, eg a cursor, then you're pretty much stuck with what they give > you. What extensibility they give you is usually extremely restrictive. > >> So in any other product you work with there is a perfect functionality >> matrix? Everything works with everything else? > > I never said there was. What I meant is that I have never worked with any > product (except sqlserver of course) that does something as silly as give > the programmer only 8k to work with. I don't think you understand what a > HUGE step back into the 80s that that is. This stands out as pretty much > the silliest restriction I have ever encountered in my time as a > programmer. > >> No, I'm saying that selling cars with a joystick instead of a steering >> wheel is dead in the water. > > There was a time when people thought selling cars with headlights was a > bad idea. Maybe we'll never sell cars with a joystick but I don't think > that linq is comparable to a joystick. LINQ has really taken off and could > easily become a real competitor to sql. You've got to take into account > that you can pretty much write standard sql but back to front (select > statement last) but have the huge advantage of writing the less friendly > but more functional version eg: > > From i in SomeCollect Where i.STATE = 'VIC' select i.Postcode > or > SomeCollection.Where(i => i.State == Vic).Select(i => i.POstcode) > >> OK, can you then provide a sample in a C# program using LINQ that calls >> a stored procedure with a table-valued parameter? > > But that's not linq, that's calling a stored proc in sqlserver. In a pure > linq environment you just pass in an IEnumerable instance. > >> Or write in LINQ a query that in Northwind database (or AdventureWorks) >> retrieves this information from the first order of each customer: >> CustomerID, CustomerName, order date, number of articles, and the total >> order order amount. > > I think I see what you're friend was talking about, you can't use a table > variable when working with linq that is used as a wrapper for sqlserver? > Linq to sql I think they call it. This is just crappy auto generated code, > the limitation is in what generates the code, not linq itself. IMO, linq > to sql isn't going to go anywhere. > > If you're going to ignore linq-to-sql and write code yourself to call a > stored proc then surely you can pass in a table variable. I haven't used > sql2008 so don't know about this feature. If this is a limitation then the > limitation is in ado.net, not linq itself. If ado.net can pass a table > variable to sqlserver then linq can. > > Michael >
From: Erland Sommarskog on 20 Nov 2009 18:08 Michael C (mike(a)nospam.com) writes: > "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message >> But I in C# has to write: >> >> using (SqlConnection cn = setup_connection()) { >> using (SqlCommand cmd = cn.CreateCommand()) { > > That's is about as minor an issue as you can get. Doing what you've > described would be handy in C# but it doesn't stop you doing something. Certaintly it's a minor issue. But I write less than 50 lines of C# code per month an average, so I don't run into that many limitations. My point was just that any language and environment has its limitations. > While you picked on something completely trivial you've missed all the > massive amounts of extensibility they give you in c#. As an example, I > can write my own connection object which connects to "Mikes Propriotry > File(c)" and do anything I like with it. In sqlserver if you've got an > object, eg a cursor, then you're pretty much stuck with what they give > you. What extensibility they give you is usually extremely restrictive. On the other hand, imagine that you have a gross amount of data spread on disk in a couple of flat files, and you need to compute some sort of report from this data. Furthermore, there is a desire that execution time is as quick as possible. All you have is C#. Your code may be extensible, but unless you first implement a relational engine, your code will use the same access path every time, even if the data distribution changes over time. And the amount of code you write will be considerable, possible several thousands lines of code. In an RDBMS you may be able to write a query in less of 50 lines of SQL, and the optimizer will make sure that you get a new query plan as data changes. Or put it another ways: boats are extremely crappy on motorways. Even if you put wheels on them, they are overtaken by the slowest cars. Now, put the cars into the water... > I never said there was. What I meant is that I have never worked with > any product (except sqlserver of course) that does something as silly as > give the programmer only 8k to work with. I don't think you understand > what a HUGE step back into the 80s that that is. This stands out as > pretty much the silliest restriction I have ever encountered in my time > as a programmer. Reality check here. When did you last see a C# program that on its own was able to deal with terabytes of data? A C# program exists under the assumption that it will never deal with that much data. Never more than the amount of main memory in the box. So there is no problem with having an unbounded string. There will not be terribly many of them anyway. SQL Server on the other hand works in a realm of potentially unbounded amount of data. There is a real cost for handling volumes. The reason for the border between a limited and unlimited (well 2GB) string at 8000 bytes relates to the page size on disk. There is nothing that says that this size has to be 8000, or the page size has to be fixed. I've worked with a product where you could set the page size yourself. But it's also easy to see that a uniform page size, permits quite a few simplifications which are likely to be good for performance. And an RDBMS is lot about performance. If you really think the limitation of 8000 chars gets in your way, you can always declare you string variables and string columns as (n)varchar(MAX), but I advice against it. I've found that there is a performance penalty, even if the actual length is below 8000. >> No, I'm saying that selling cars with a joystick instead of a steering >> wheel is dead in the water. > > There was a time when people thought selling cars with headlights was a > bad idea. Maybe we'll never sell cars with a joystick but I don't think > that linq is comparable to a joystick. I think you missed the point. The analogy was not meant to denigrate LINQ as such. Rather, even if joysticks would prove to be a better way to manoever a car, it would be a hard sell, since people are used to steering wheels. >> OK, can you then provide a sample in a C# program using LINQ that calls >> a stored procedure with a table-valued parameter? > > But that's not linq, that's calling a stored proc in sqlserver. In a pure > linq environment you just pass in an IEnumerable instance. Well, you made a number of that T-SQL had so many limitations. You will have to admit that if I cannot call a stored procedure with a table-variable from LINQ, be that LINQ-to-SQL or LINQ-to-EF, that is quite a limitation, since it deprives you of a very useful feature in SQL 2008. (To be fair, VS 2008 was released before SQL 2008 came out. Maybe they are adressing it in VS 2010?) >> Or write in LINQ a query that in Northwind database (or AdventureWorks) >> retrieves this information from the first order of each customer: >> CustomerID, CustomerName, order date, number of articles, and the total >> order order amount. It seems that you missed this one. But I'm still interested in seeing the query. > If you're going to ignore linq-to-sql and write code yourself to call a > stored proc then surely you can pass in a table variable. I haven't used > sql2008 so don't know about this feature. If this is a limitation then the > limitation is in ado.net, not linq itself. If ado.net can pass a table > variable to sqlserver then linq can. You can pass data to TVPs from ADO .Net, using either a DataSet, a List<SqlDataRecord> or an IDataReader. But how to do it in LINQ? Or by using EF? -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Michael C on 22 Nov 2009 19:06 "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CCA1876E651Yazorman(a)127.0.0.1... > Certaintly it's a minor issue. But I write less than 50 lines of C# > code per month an average, so I don't run into that many limitations. > My point was just that any language and environment has its limitations. I never said otherwise. What I did say is that the limitations in sqlserver are simply bigger and sillier than the limitations in other products I have encountered. Sometimes there are valid reasons for this, eg indexes would place a large number of limitations on things but in other cases there is no good reason except maybe compatibility with stuff in the past. > On the other hand, imagine that you have a gross amount of data spread > on disk in a couple of flat files, and you need to compute some sort of > report from this data. Furthermore, there is a desire that execution time > is as quick as possible. All you have is C#. Your code may be extensible, > but unless you first implement a relational engine, your code will use > the same access path every time, even if the data distribution changes > over time. And the amount of code you write will be considerable, > possible several thousands lines of code. > > In an RDBMS you may be able to write a query in less of 50 lines of > SQL, and the optimizer will make sure that you get a new query plan as > data changes. > > Or put it another ways: boats are extremely crappy on motorways. Even > if you put wheels on them, they are overtaken by the slowest cars. Now, > put the cars into the water... C# with linq would be more than capable of being the language for a database (not sure of the technical term here). It is certainly not a boat out of water. > Reality check here. When did you last see a C# program that on its own > was able to deal with terabytes of data? Currently it does not but we don't have that ability yet. MS would need to write a database that uses linq instead of sql for this to be a reality. > A C# program exists under the assumption that it will never deal with > that much data. Never more than the amount of main memory in the box. That's just not true. C# can easily handle more data than there is memory available. I have a C# program that handles 80GB files. SQLServer is simply C code, there is no reason C# can't replicate anything sqlserver does. Besides, I'm not suggesting this "Linq database" be actually written in C#, just that C# is the front end for it. > SQL Server on the other hand works in a realm of potentially unbounded > amount of data. There is a real cost for handling volumes. The reason > for the border between a limited and unlimited (well 2GB) string at > 8000 bytes relates to the page size on disk. There is nothing that > says that this size has to be 8000, or the page size has to be fixed. > I've worked with a product where you could set the page size yourself. > But it's also easy to see that a uniform page size, permits quite a few > simplifications which are likely to be good for performance. And an > RDBMS is lot about performance. There is absolutely no reason sqlserver would have an 8K limit today, except for hangovers from the past. Maybe if we go past 8K we get a performance hit but that would be fine. > If you really think the limitation of 8000 chars gets in your way, > you can always declare you string variables and string columns as > (n)varchar(MAX), but I advice against it. I've found that there is a > performance penalty, even if the actual length is below 8000. It's not varchar that's the problem for me, it's calling CLR code for a custom aggregate that is the issue. > I think you missed the point. The analogy was not meant to denigrate LINQ > as such. Rather, even if joysticks would prove to be a better way to > manoever a car, it would be a hard sell, since people are used to steering > wheels. Ah, ok, that might be true but then again it might not. Certainly the industry hasn't had any problem picking up .net and C# or vb.net. > Well, you made a number of that T-SQL had so many limitations. You > will have to admit that if I cannot call a stored procedure with a > table-variable from LINQ, be that LINQ-to-SQL or LINQ-to-EF, that is > quite a limitation, since it deprives you of a very useful feature in > SQL 2008. > > (To be fair, VS 2008 was released before SQL 2008 came out. Maybe they > are adressing it in VS 2010?) But my point was this is not a limitation of linq. If it is not possible then this is because ado.net does not allow it. If ado.net allows it then linq can do it. > It seems that you missed this one. But I'm still interested in seeing > the query. I don't have sql2008 here and it would take me several hours to get it installed and write a sample so I cannot do this. Even if this is a limitation of linq then it's really just a limitation interacting with sql. > You can pass data to TVPs from ADO .Net, using either a DataSet, a > List<SqlDataRecord> or an IDataReader. But how to do it in LINQ? Or > by using EF? In Linq I would just cast the data I have to a List<SqlDataRecord>. Something like this: MyCollection.Select(i => i.GetSqlDataRecord).ToList(); where GetSqlDataRecord is a function that would return an SqlDataRecord. Michael
From: Michael C on 22 Nov 2009 23:32 "Michael Coles" <admin(a)geocodenet.com> wrote in message news:eI7wW6ZaKHA.4668(a)TK2MSFTNGP06.phx.gbl... > The LINQ provider for SQL actually generates SQL code behind the scenes. > To be fair, it does a fair job of basic optimization (properly > parameterizing variables, etc.), but it doesn't really have an > "industrial" optimizer built into its code generator. Not sure how you > feel LINQ will replace SQL--who's to say EF won't replace LINQ next year? Linq to sql is rubbish imo. Why bother writing in a similar language with a translation when you can write in sql directly. Writing native linq against a linq database would be much better :-)) > I was following your conversation intently, but you completely lost me on > this comparison... What problem are you trying to solve with a cursor > here, and what's the comparison between a cursor and "an object" that > you're making? If you really want to compare apples to apples (though > possibly different varieties) then you'd be better off comparing a > "SqlDataReader" "object" to a cursor. I guess you don't see a lot of > comparisons of "Connection" objects (like "SqlConnection") to "cursors", > in much the same way that you don't see people comparing grizzly bears to > drywall. I'm not comparing the 2 because they have the same functionality, just using 2 random objects as an example. In sqlserver you have a cursor. You can not extend this object in any way at all. You cannot write your own cursor object in C++ and then reference that in sqlserver, you just have a cursor object as is. In C# when we have an object it can usually be extended in some sort of way. As an example, people or companies can write their own data providers for C#, as I'm sure Oracle have done for their database. If we had a cursor object in C# we would be able to extend it using extension methods, most likely inherit from it, we could possible implement its interface and pretend to be a cursor object or we could write our own cursor object from scratch. Michael
From: Michael Coles on 23 Nov 2009 13:27
"Michael C" <mike(a)nospam.com> wrote in message news:%23IpMsX$aKHA.2680(a)TK2MSFTNGP04.phx.gbl... > "Michael Coles" <admin(a)geocodenet.com> wrote in message > news:eI7wW6ZaKHA.4668(a)TK2MSFTNGP06.phx.gbl... >> The LINQ provider for SQL actually generates SQL code behind the scenes. >> To be fair, it does a fair job of basic optimization (properly >> parameterizing variables, etc.), but it doesn't really have an >> "industrial" optimizer built into its code generator. Not sure how you >> feel LINQ will replace SQL--who's to say EF won't replace LINQ next year? > > Linq to sql is rubbish imo. Why bother writing in a similar language with > a translation when you can write in sql directly. Writing native linq > against a linq database would be much better :-)) I didn't realize there was such an animal as a "linq database". LINQ generally provides a common interface to query a variety of different datasources--SQL databases, Objects, websites, or anything else that you care to write a provider for. LINQ's strength is its ability to expose disparate data sources via a common query interface, so I'm not sure about a couple of things: 1) what would a "linq database" even look like, and 2) how it would be "better", given LINQ's primary purpose. >> I was following your conversation intently, but you completely lost me on >> this comparison... What problem are you trying to solve with a cursor >> here, and what's the comparison between a cursor and "an object" that >> you're making? If you really want to compare apples to apples (though >> possibly different varieties) then you'd be better off comparing a >> "SqlDataReader" "object" to a cursor. I guess you don't see a lot of >> comparisons of "Connection" objects (like "SqlConnection") to "cursors", >> in much the same way that you don't see people comparing grizzly bears to >> drywall. > > I'm not comparing the 2 because they have the same functionality, just > using 2 random objects as an example. In sqlserver you have a cursor. You > can not extend this object in any way at all. You cannot write your own > cursor object in C++ and then reference that in sqlserver, you just have a > cursor object as is. In C# when we have an object it can usually be > extended in some sort of way. As an example, people or companies can write > their own data providers for C#, as I'm sure Oracle have done for their > database. If we had a cursor object in C# we would be able to extend it > using extension methods, most likely inherit from it, we could possible > implement its interface and pretend to be a cursor object or we could > write our own cursor object from scratch. As Erland pointed out, different languages have different strengths and weaknesses. SQL's strength is it's ability to efficiently store and retrieve data in sets and to abstract away a lot of the details so you can focus on your end results instead of focusing on the steps needed to get there. C#'s strengths lie in other areas that are more procedural, such as bit manipulation, looping, string manipulation, etc. While it is entirely possible to write your own data providers for C# and create your own databases using C++ and generate your own query engine using VB, you could potentially run into a couple of issues. The first question is how much time and money is it worth to write your own database engine using C# and create your own query language built to your own specifications? How much of your resources do you want to throw at a project of this scale? Another issue is do you want to make your database engine general enough that it can be reused, or is it a one-off thing that will only ever be used for one dedicated project? Obviously you'll get better performance out of a dedicated one-off solution, but you won't be able to use it for any other database projects down the road. Finally, who are you going to hire to run it and administer it for you? Since it's a homemade project that's not standards-compliant you'll have to teach someone from scratch and won't really be able to leverage your admin's and developer's experience on SQL-based DBMS's. This could be a fairly expensive proposition. |