Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Hierarchies in SQL


Hierarchies in SQL

Author
Message
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
Thanks Noel and Jeff. I appreciate the critique and compliments. Smile

- 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
Lukk
Lukk
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45006 Visits: 39880
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45006 Visits: 39880
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
danielk1
danielk1
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 185


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.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45006 Visits: 39880
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. Smile

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
noeld
noeld
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6320 Visits: 2048

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search