Why can't recursive queries contain...?
in [Programming SQL]
|
Prev: Sql Server on VMWare
Next: AdventureWorks database
From: Michael Coles on 26 Nov 2009 22:29 "Michael C" <mike(a)nospam.com> wrote in message news:ejxkHowbKHA.2164(a)TK2MSFTNGP02.phx.gbl... > "Michael Coles" <admin(a)geocodenet.com> wrote in message > news:FCC6E2B4-2B6A-45C8-BDF5- > Do you really think this declarative approach works that well? I still > find that I tell that database what I want to do and a lot of the time it > does it the wrong way and I have to go back in and tell it how to do it > anyway. Actually I do think it works well. Can you give some examples of your issue here? I'm very interested, as I've been doing this for a while now and only very rarely run into issues like you describe here. And they are usually fixed with a hotfix or SP. I would be really interested to know why the DBMS does it "in the wrong way" for you "a lot of the time". > Nothing is impossible. No one was screaming for .net but it's been a big > success. Everything is possible, just throw time and money at it. The question is this: is it worth it? If you can convince MS that it is, then they'll go for it. If not, then they won't. > That's true but I am only 1 version behind. Besides, I'm not against > backwards compatibility, I just think that after a certain point in time > it becomes necessary to make a complete break from the past sins. We're > still in the very early stages of computing in 2009, do we really want > sql2050 to still only be able to return a square block of data from a > stored proc? In addition to "square blocks of data" (i.e., tabular result sets), how about returning scalar values? Or XML? Or a .NET *object*? 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? -- Thanks Michael Coles SQL Server MVP Author, "Expert SQL Server 2008 Encryption" (http://www.apress.com/book/view/1430224649) ----------------
From: Michael C on 27 Nov 2009 00:29 "Michael Coles" <admin(a)geocodenet.com> wrote in message news:%23gsYZ8wbKHA.5796(a)TK2MSFTNGP06.phx.gbl... > One-off administrative tasks are the bulk of the procedures you're talking > about (or possibly you're talking about the IF EXISTS ... code that SSMS > adds to build scripts). The recommended use for procedural statements is > in fact cases where you have code that deals in very small data sets and > performs administrative tasks, and one-offs like checking for existence of > objects before executing DDL statements. Using IF statements in > production stored procs that perform DML will affect performance; the > extent of the effect is dependent on several factors. Pretty much an stored proc I write will start off with an IF statement. For example, when writing a stored proc for a daily sales report, the stored proc will have a single parameter of date. The first line will raise an error if the date parameter is null. Surely this doesn't cost more than 1/10000th of a second to execute. > You can easily design your tables to simulate OO inheritance if you so > desire, so I don't really see this as being an issue. What you just > stated is actually really, really easy to do if you so desire. If you > look at the SQL Server 2005/2008 Catalog views you will see that they > expose data using this same inheritance design pattern. I might be > missing your point on this as well... I'm not exactly sure what you mean but surely it's a bit like doing fake inheritance is VB6? You can do it but it's a bit kludgy. > Getting a chance to refactor your code to take advantage of new features > while not instantaneously breaking all of your existing code is not > necessarily a bad thing. I never said it was, it's just one way to do things that has advantages and disadvantages. While it's great to have backwards compatibility it does hold things back *significantly*. Can you imagine how far behind .net would have been if it was really just vb7? > Not sure who you think in the "SQL Server camp" doesn't want > advancement--there were lots of advances made in SQL 2008 covering a wide > range of functionality; from B.I. to DB Admin and Security to SQL > Development. 2008 R2 adds more advances, and I suspect there are plenty > of additional advances in the works for the next version or two as well, > so I suppose I'm missing your point on this... Of course as I understand > from your previous posts you haven't actually installed SQL 2008 yet, so > you may not have even used any of the existing advancements at this point. > Is that an accurate assessment? That's partially accurate. I haven't tried sql 2008 as yet but I was not saying that people in the sqlserver camp don't want advancement. I was just saying they lean towards compatibility over advancement. Michael
From: Michael C on 27 Nov 2009 00:46 "Michael Coles" <admin(a)geocodenet.com> wrote in message news:% > Actually I do think it works well. Can you give some examples of your > issue here? I'm very interested, as I've been doing this for a while now > and only very rarely run into issues like you describe here. And they are > usually fixed with a hotfix or SP. I would be really interested to know > why the DBMS does it "in the wrong way" for you "a lot of the time". 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. > Everything is possible, just throw time and money at it. The question is > this: is it worth it? If you can convince MS that it is, then they'll go > for it. If not, then they won't. 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. > In addition to "square blocks of data" (i.e., tabular result sets), how > about returning scalar values? That's just a 1x1 square really. > 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? :-) > 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... :-) Michael
From: Erland Sommarskog on 27 Nov 2009 03:16 Michael C (mike(a)nospam.com) writes: > "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message > news:Xns9CCE637FCD26FYazorman(a)127.0.0.1... >> Again: a relational database is just so much more than the language >> elements. You need memory management. You need process management. You >> need storage management. You need a cost-based optimizer. > > Again, yes I know. That is why someone would need to write a linq > database. And in the end you would maybe end up with as many "silly" restrictions as you have in SQL Server. You need to ship at some point. >> Keep in mind that C# is a procedural language. SQL is declarative. What >> you >> write in C# not only instructs the computer what to do, but also how. In >> SQL you only say what. This means that when you use LINQ, you essentially >> tell the compiler what SQL to generate. > > Sounds like a gray area to me. Certainly linq could be optimised by an > optimiser in the same way sql is. Yes, it could, but today it isn't, but LINQ is only something that generates something else, which brings us to: > I have provided a code sample (check back through the posts, did you miss > it?) but as I said I am not going to install sql2008 just to write some > sample code. If LINQ is not able to generate the code needed to pass a TVP, it isn't. My source says that LINQ does not have this capability. You claim it has, but you don't decline to back up your statement with working code. The code you provided tells me nada. (And why not install SQL 2008? That would save you from the limitation with 8000 bytes in a UDA.) -- 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: Erland Sommarskog on 27 Nov 2009 03:33
Michael C (mike(a)nospam.com) writes: > 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. Yes, it's true that the declarative model is not perfect, but we sometimes have to help the optimizer quite a bit. A good example is a running sum. I had to compute one recently, and wrote this query: update #insids set running = (SELECT SUM(nooftrans) from #insids b where b.insid <= a.insid) from #insids a There were 170000 rows in the table. I killed the query after a few minutes, and then wrote a cursor which completed within a minute. There is an construct in ANSI-SQL to express a running sum using the OVER clause, and it is high on my wishlist for SQL Server that Microsoft implements this clause like other vendors already have. But strictly speaking, this clause is not needed: I already have expressed my intent above, but the optimizer does not realise that the query can be executed in a single pass over the table. But still SQL is a lot better that you would get if you would with a C# program. Well, in this particular example the correct plan is trivial, but for more complex plans it isn't, and this is an area where SQL beats C#. -- 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 |