Click here to monitor SSC
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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1732 Visits: 2057
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
SSC Eights!
SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)

Group: General Forum Members
Points: 944 Visits: 2311
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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1732 Visits: 2057
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
SSC-Addicted
SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)

Group: General Forum Members
Points: 445 Visits: 1151
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
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: 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
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17569 Visits: 32254
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
SSC-Addicted
SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)

Group: General Forum Members
Points: 460 Visits: 563
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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1462 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
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17569 Visits: 32254
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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1462 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