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


Database Queries and Scalability


Database Queries and Scalability

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62661 Visits: 19111
Comments posted to this topic are about the item Database Queries and Scalability

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Gary Varga
Gary Varga
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16477 Visits: 6534
I think that an essential point is that scalability is considered in the design.

If everyone agrees on that point then we are then down to selecting appropriate designs and implementation that are specific for each set of circumstances. Having said that there will be some designs/implementations that will suit many circumstances (dare I call them patterns?) and it is a good thing that we actively seek them, document them and discover the caveats of their use.

I am not going to comment on the implementation discussed because I will end up either being told or telling someone "it depends" ;-)

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
call.copse
call.copse
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3834 Visits: 1925
I don't think the article is applicable to the kind of sites and development I do, is the way I would put this one.

I can see that for certain circumstances it would be fine but isn't that always the way?
lshanahan
lshanahan
SSChasing Mays
SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)

Group: General Forum Members
Points: 622 Visits: 436
Two things:

- Maybe my browser rendered the page incorrectly, but I had a hard time reading it as it was all black text on a dark grey background.

- The author seemed to be talking more about the efficiency (or lack thereof) of caching strategies and blaming it on joins. At the risk of oversimplification, it's about when and where joins are performed rather that whether or not they are performed. To little old me over here in the cheap seats of the peanut gallery, it seems more of an architecture issue than a design pattern per se as the author puts it.

____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
Gary Varga
Gary Varga
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16477 Visits: 6534
lshanahan (10/21/2013)
...To little old me over here in the cheap seats of the peanut gallery, it seems more of an architecture issue than a design pattern per se as the author puts it.


Architectural pattern / design pattern: just differences in granularity on the way to implementation ;-) (Oh, OK there is more to it than that!!!)

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
tabinsc
tabinsc
SSC Eights!
SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)

Group: General Forum Members
Points: 824 Visits: 917
Most of the time I try to design things assuming the scale will increase, and may increase significantly. To me it seems to depend more on how many columns of data we're keeping rather than how many rows. Harder to consider scalability for hundreds of columns verses only a few.

Tony
------------------------------------
Are you suggesting coconuts migrate?
lshanahan
lshanahan
SSChasing Mays
SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)

Group: General Forum Members
Points: 622 Visits: 436
Gary Varga (10/21/2013)
lshanahan (10/21/2013)
...To little old me over here in the cheap seats of the peanut gallery, it seems more of an architecture issue than a design pattern per se as the author puts it.


Architectural pattern / design pattern: just differences in granularity on the way to implementation ;-) (Oh, OK there is more to it than that!!!)


Point. Still, the article just seemed to harp overmuch on just not using JOINs when his real issue is finding more efficient ways of moving data from front- to back-end and vice-versa while maintaining data quality and integrity among other things.

____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
TomThomson
TomThomson
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: 14310 Visits: 12197
I certainly don't like the article referenced, it seems to me that it is a heap of assertions for whose credence no basis is offered. Yes, scalability needs to be a factor in design and in architecture and in selection of technologies, to the extent that scalability is a requirement. But claiming that the use of databases is the one thing that prevents scalability of websites is just unvarnished rubbish; almost always the main cause of lack of scalability is bad design. Bad design such as, for example, using 16 byte binary string identifiers for everything, instead of whichever of 16 bit, 32 bit, and 64 bit integer values provides enough range (even 8 bit in rare cases). Conversion between natural key and these identifiers through look-up in the database should be done rarely, at most once per session for each one, with the results cached in session data, which should not be in the database - - and of course consideration should be given to simplifying the design by using natural keys, which are unlikely to cause any more overhead than the 16-byte monstrosities advocated in the article. Another example of bad design which Mr Ozar seems to believe is a consequence of having a database is that session data is held in the database, something which I have never had the misfortune to see. At least I've learnt something useful from the article: that one should never allow PHP to be used for a website (at least not if what Mr Ozar says it does can't be switched off). Why does Mr Ozar translate that into "never use a database for website"? I can see nothing in use of a database that mandates use of PHP. I've been in the IT business since long before there were any web servers; even in the 1960s we didn't go back to the platform's security database and revalidate the login for every command issued by a MAC user; neither did we ask the platform software to store the current context of each logged in user, instead of having the MAC application do it for itself; the distributed TP systems for which I contributed parts of the infrastructure back in the 70s didn't make the mistake of putting the session data in the database (IDMSX, not an RdBMS at that date) either. Perhaps Mr Ozar's belief is that web designers with access to a database always make mistakes that we already knew, half a century ago for MAC systems and at least 40 years ago for TP, should be avoided.

Tom

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86201 Visits: 41096
L' Eomot Inversé (10/21/2013)
I certainly don't like the article referenced, it seems to me that it is a heap of assertions for whose credence no basis is offered. Yes, scalability needs to be a factor in design and in architecture and in selection of technologies, to the extent that scalability is a requirement. But claiming that the use of databases is the one thing that prevents scalability of websites is just unvarnished rubbish; almost always the main cause of lack of scalability is bad design. Bad design such as, for example, using 16 byte binary string identifiers for everything, instead of whichever of 16 bit, 32 bit, and 64 bit integer values provides enough range (even 8 bit in rare cases). Conversion between natural key and these identifiers through look-up in the database should be done rarely, at most once per session for each one, with the results cached in session data, which should not be in the database - - and of course consideration should be given to simplifying the design by using natural keys, which are unlikely to cause any more overhead than the 16-byte monstrosities advocated in the article. Another example of bad design which Mr Ozar seems to believe is a consequence of having a database is that session data is held in the database, something which I have never had the misfortune to see. At least I've learnt something useful from the article: that one should never allow PHP to be used for a website (at least not if what Mr Ozar says it does can't be switched off). Why does Mr Ozar translate that into "never use a database for website"? I can see nothing in use of a database that mandates use of PHP. I've been in the IT business since long before there were any web servers; even in the 1960s we didn't go back to the platform's security database and revalidate the login for every command issued by a MAC user; neither did we ask the platform software to store the current context of each logged in user, instead of having the MAC application do it for itself; the distributed TP systems for which I contributed parts of the infrastructure back in the 70s didn't make the mistake of putting the session data in the database (IDMSX, not an RdBMS at that date) either. Perhaps Mr Ozar's belief is that web designers with access to a database always make mistakes that we already knew, half a century ago for MAC systems and at least 40 years ago for TP, should be avoided.


+1000. I just went through some real hooie on one of the apps designed before the current team started. One of the apps checks for the credentials of the user on ever screeen and every object touched. Some of the pull downs check for credentials for every item in the pull down. One click would generate a dozen calls to the same credential proc (and we have a LOT of credentials. It's one of the worst designs I've ever seen but not the only one I've seen like it. Of course, the outcry was "the database has a performance problem". It sure did... the problem was the application using it. ;-)

It'll become a problem in the future again because their "fix" was to cache the credentials. It's still making a dozen checks per object clicked. Time for the webserver to start taking the blame for crap code. Hehe It's a real shame that they don't want to spend the time to fix the bloody root problem!!! Sick

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jim P.
Jim P.
SSC Eights!
SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)

Group: General Forum Members
Points: 909 Visits: 2215
Jeff Moden (10/21/2013)
+1000. I just went through some real hooie on one of the apps designed before the current team started. One of the apps checks for the credentials of the user on ever screeen and every object touched. Some of the pull downs check for credentials for every item in the pull down. One click would generate a dozen calls to the same credential proc (and we have a LOT of credentials. It's one of the worst designs I've ever seen but not the only one I've seen like it. Of course, the outcry was "the database has a performance problem". It sure did... the problem was the application using it. ;-)

It'll become a problem in the future again because their "fix" was to cache the credentials. It's still making a dozen checks per object clicked. Time for the webserver to start taking the blame for crap code. Hehe It's a real shame that they don't want to spend the time to fix the bloody root problem!!! Sick


This idea reminds me that Mongo DB Is Web Scale. Making a website try to act like a miniDB is actually sort of scary. Is it guaranteed to be written into the DB at some point? Will you be sure that the the user didn't loose connectivity and connect back to a different web server. I have an aircard at home for my web experience. I'm not sure if it is the aircard, the provider network, or my laptop's USB ports that are screwing up but I am having weak signals and dropouts on a regular basis for the last few weeks. Am I guaranteed to go back to the same web server? Can the cache on webserver1 overwrite webserver2?

Another bad design example is the healthcare.gov website. They needed it to eventually get over 16M people to use it. Could you imagine designing a website that you are dumping every bit of your personal financial and health information into and it is trying to store it in live memory and can only handle 50-60K users at a time?



----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.
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