Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Hierarchies in SQL Expand / Collapse
Author
Message
Posted Wednesday, January 28, 2009 8:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
ventsislav.mladenov (1/28/2009)
Or you could use CLR store procedures which are very fast in execution and sometimes are faster than T-SQL.
http://blog.vmladenov.com/?p=197


Have you speed-tested the queries on that against nested sets hierarchies?


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #644971
Posted Wednesday, January 28, 2009 8:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
Lukk: definitely agree with you. One of my biggest concerns when I left the job where I built this hybrid hierarchy was whether or not anyone left behind would be able to manage it. I documented the heck out of it, asked them if they were comfortable with it, got reassurances that either they'd figure it out or they'd call me (and pay consulting rates) if they needed help. Not a very good sign, I have to say.

Unfortunately, that company didn't have any 2008 servers, and had only vague plans about maybe obtaining some one day in the indeterminate future, so that wasn't really an option. The stuff in this article is moderately complex, but not horribly so, and doesn't depend on a proprietary data type nor on features that most companies don't have yet.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #644974
Posted Wednesday, January 28, 2009 8:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
Thanks Noel and Jeff. I appreciate the critique and compliments. :)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #644977
Posted Wednesday, January 28, 2009 8:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 10, 2009 8:51 AM
Points: 3, Visits: 9
danielk1 (1/28/2009)
I just feel, to tie myself to a DB structure type, like you mentioned using the hierarchcical ID, might limit my application's portability - say, if I want to take it from MS SQL server to MySQL or Oracle or DB2.

Doing a small amount of coding in order to remain with ISO standard fields, is in my opinion, making for a far better application.


That is a very good point Daniel and I would definitely consider that if I knew there is a chance the app will be ported to other DB servers, otherwise I would definitely go for hierarchyID (if it proves to be fast enough).

This discussion also applies to app architecture - e.g. should we have a separate DAL in the project if we know that we will only use MS Sql Server?

Thanks,
Lukasz
Post #644999
Posted Wednesday, January 28, 2009 10:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 35,366, Visits: 31,901
ventsislav.mladenov (1/28/2009)
Or you could use CLR store procedures which are very fast in execution and sometimes are faster than T-SQL.
http://blog.vmladenov.com/?p=197


Since you say "Now the fact is that the C# store procedure is faster than T-SQL code. I’m wondering why but this is the result." in you blog, I'd like to see the T-SQL method code and test data you tested against. I'm always willing to learn something new.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #645085
Posted Wednesday, January 28, 2009 10:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 35,366, Visits: 31,901
danielk1 (1/28/2009)
I just feel, to tie myself to a DB structure type, like you mentioned using the hierarchcical ID, might limit my application's portability - say, if I want to take it from MS SQL server to MySQL or Oracle or DB2.

Doing a small amount of coding in order to remain with ISO standard fields, is in my opinion, making for a far better application.

My 2cents.


Just double checking... You've never used and will never use CONNECT BY until it becomes part of the ISO Standard. And you never used DATEDIFF until it became part of the standard. And you've never used GETDATE() because it's not part of the standard.

Here we go again... I can understand that some GUI code might be made to follow ISO standards (heh, depending on which release you're talking about), but since none of the RDBMS vendors are 100% compliant with ISO standards and the very standards themselves tend to lag what's available as powerful extensions to ANSI SQL, I'll just go ahead and start a war by saying that true code portability in the batch world is a myth.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #645091
Posted Wednesday, January 28, 2009 11:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 22, Visits: 156


Just double checking... You've never used and will never use CONNECT BY until it becomes part of the ISO Standard. And you never used DATEDIFF until it became part of the standard. And you've never used GETDATE() because it's not part of the standard.


Actually, no. I leave this to the code to handle.

The DBAs in all the companies I work for, demand us to follow all ISO standards without deviation.

Moreover, the applications I have written, both GUI and Web, are 100% portable to many of the top databases available including: MS SQL, MySQL, DB2, Oracle, and others (tested and implemented).

I will not debate the issue as I am not saying I am right nor am I saying I am wrong - it is just one way of solving an issue. The article and others like it are of course another way. In IT, there are many ways to solve a given issue, some better than others.

With this said, if performance using a non-ISO standard was significantly improved, the DBAs and other Stake Holders would have to evaluate their thinking and perhaps make an exception, however, as of now, performance is virtually instant and there are no areas to improve, so we will remain with the ISO standards.

Why have standards if they are not to be followed and adhered? Sure, they may become outdated. No one is saying they are perfect and even if they were, in six months there will likely be something else better, different, or faster, requiring the standards to be revisited, invoking a new standard.

Ahhh - I love IT - it is ever changing and evolving.

Take care all.
Post #645173
Posted Wednesday, January 28, 2009 11:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 35,366, Visits: 31,901
Ah... so you're not a batch programmer. And that's the area of the code I was talking about. GUI programmers have all sorts of tools like Hibernate, etc, to write "portable" code... well, at least DB "agnostic" code so far as the GUI is concerned.

It would be really interesting to see if any of your DBA's use GETDATE() or any of the other "proprietary" code offerings of SQL Server. I'd have to say that the first time they reference Master.sys.sysColumns, they're probably in violation. :)


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #645192
Posted Wednesday, January 28, 2009 2:47 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
I have to admit, my code is usually about as portable as the Rocky Mountains. In other words, you could move it to another database engine in geologic time spans, but not much faster than that.

I also have to admit that I've seen projects that were "completely standard and portable", where someone actually did try to move it to another RDBMS, and spent more time debugging because of partially documented differences, undocumented differences, variations in standards compliance in the RDBMS itself, etc., than they would have spent building the thing over from scratch.

I have yet to see a case where code portability actually solved anything worthwhile for anyone. Doesn't mean they don't exist, just means I haven't seem them, in my limited experience.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #645407
Posted Wednesday, January 28, 2009 3:15 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 6:06 AM
Points: 6,259, Visits: 2,030

I have yet to see a case where code portability actually solved anything worthwhile for anyone. Doesn't mean they don't exist, just means I haven't seem them, in my limited experience.

++1



* Noel
Post #645435
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse