SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Never, Ever Use Clustered Indexes


Never, Ever Use Clustered Indexes

Author
Message
chrisfradenburg
chrisfradenburg
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3038 Visits: 2077
Paul Hunter (5/31/2012)
I hear the argument "we don't want to be locked in" from the app dev folks - the same ones that aren't afraid to be locked in to their programming language and/or OS. It's a "logic free" argument.


Ah, but the programming language and the OS they understand. The DB is just this black box that spits out the right answers (and at the same time is the source of their problems.)
chrisn-585491
chrisn-585491
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3970 Visits: 2565
Unless you are willing to dedicate the resources, performance in cross platform data applications will not be optimized. Which is fine if it's a trival app, but can become a quagmire on an a product that requires performance.
chrisfradenburg
chrisfradenburg
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3038 Visits: 2077
Chris.C-977504 (5/31/2012)
Now that I've read the whole article, I'm really not clear what your message is.


In fact, I’d argue that you need to design precisely for specific relational database management systems because, let’s face it, they don’t implement the fundamentals in the same way. If you mess up the fundamentals, you’ve just messed up your entire design.


The point is that you can't take a database that's designed for Oracle, change the t-sql to be Microsoft SQL (or MySQL, etc) compliant and expect it to work well. It'll function. It'll just be slow because the core engines are optimized for different DB design.
jfogel
jfogel
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1031 Visits: 1176
The older version of our app is one of those that supports either a Oracle or SQL Server back end. This is not the first time where I've either read or head someone say it is impossible to have any application that does this efficiently. This isn't the case here. From what I have seen it appears to come down to the development platform when it comes to our app. In the 90's our app was written in a language called CET BASIC. You may not be familiar with it but it was/is a great full featured dev platform that we carried over from THEOS OS when we went to Windows.

Performance was very good for both Oracle or a SQL server back end. Then we went to a VB based dev platform. The SQL Server back end was still very fast while the Oracle back end lagged in ways that I sometimes found unacceptable. This speed issue had nothing to do with indexes or any other tuning issue. It seemed to me it was related to the data access tools used to get the data out of Oracle. I could verify that the Oracle server was practically asleep waiting for request from the app. I'm not a VB programmer but I always kind of thought Microsoft intentionally wanted access to any DB besides SQL Server to be slower. I have no proof of that specific claim but I have results from testing.

Cheers
hakkie42
hakkie42
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 104
Probably being a bit ignorant here but once you're writing T-SQL you're already specializing for MS SQL Server and Sybase ASE.

So not using vendor-specific stuff (e.g. clustered indexes) after that - that has no impact on the way the application is programmed except for performance - seems ridiculous.

I mean, even the data types differ per vendor so you'll have to specialize already. Right?
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100911 Visits: 33014
Chris.C-977504 (5/31/2012)
I almost flipped the bozo bit before I got to the fourth paragraph.

Now that I've read the whole article, I'm really not clear what your message is.

FWIW,
-Chris C.


Sorry about that. I was trying to make the point that "generic" database designs aren't really possible due to the nature of the underlying infrastructure. Clearly failed.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
DEK46656
DEK46656
Right there with Babe
Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)

Group: General Forum Members
Points: 786 Visits: 581
This should have been an April Fool's editorial; well with a little tweaking of content.

Beer's Law: Absolutum obsoletum
"if it works it's out-of-date"
TravisDBA
TravisDBA
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3368 Visits: 3069
Sorry about that. I was trying to make the point that "generic" database designs aren't really possible due to the nature of the underlying infrastructure. Clearly failed.



I'm a little confused too Grant . Who really uses "generic' database designs nowadays anyway? Particularly, when the underlying database structure is constantly changing in a fast paced environment. At least mine anyway. This seems to me like something that is already very apparent to most DBA's IMHO anyway. So, Oracle and SQL Server are fundamentally different, Uhhhhh... yes, they are....no kidding! I'm with Chris, I'm not sure what point the message was trying to convey, that we didn't already know.:-D

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100911 Visits: 33014
TravisDBA (5/31/2012)
Sorry about that. I was trying to make the point that "generic" database designs aren't really possible due to the nature of the underlying infrastructure. Clearly failed.



I'm a little confused too Grant . Who really uses "generic' database designs nowadays anyway? Particularly, when the underlying database structure is constantly changing in a fast paced environment. At least mine anyway. This seems to me like something that is already very apparent to most DBA's IMHO anyway. So, Oracle and SQL Server are fundamentally different, Uhhhhh... yes, they are....no kidding! I'm with Chris, I'm not sure what point the message was trying to convey, that we didn't already know.:-D


The message may have been unclear, again apologies, but you're wrong that this is common knowledge. Tons and tons of developers and plenty of DBAs still believe in not hooking yourself to a single vendor. I interviewed someone just a little over a year ago who lectured me on my technical questions because they were too "Microsoft & SQL Server specific." His argument was that only lowest common denominator SQL should be used so that it's easier to port between database systems. People really do still believe this.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
TravisDBA
TravisDBA
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3368 Visits: 3069
Tons and tons of developers and plenty of DBAs still believe in not hooking yourself to a single vendor. I interviewed someone just a little over a year ago who lectured me on my technical questions because they were too "Microsoft & SQL Server specific." His argument was that only lowest common denominator SQL should be used so that it's easier to port between database systems. People really do still believe this.



Agreed, people believe lots of things, but that doesn't necessarily make them relevant, or the best way to go. Like I have said to many customers in the past without reservation. "There is what you want, and then there is what you end up getting." and my boss backs me on it every time too. Our business is still thriving. As far as the interview goes, you are asking the questions not the interviewee. You can ask anything you want within reason to satisfy your need to assess his/her specific skill set.. :-D

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
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