| Author |
Topic |
|
slacker
Posting Yak Master
115 Posts |
 Posted - 2003-06-28 : 04:19:59
|
| How far can you normalize where its just pretty much impossible for sql server to handle. Assuming you had funds to buy the hardware. Can sql server basically keep scaling and scaling by adding new hardware? Im a huge fan of normalizing tables. And my tree's are getting pretty gnarly for the current project im on. Im using table inheritence where each sub table has a parent table. So i could potentially have 5 joins for one object in my application. Each page could hit the database 6 or more times. Could the hardware cap off or could we just keep adding machines as we need them to meet the requirements. Our application logic is more important than de-normalizing. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-28 : 11:53:43
|
| Normalisation shouldn't be a problem as relational databases are built to handle joins efficiently. It normally means that you have to read less data pages and so use less memory and so could be faster than a flat table.>> Each page could hit the database 6 or more times.This is a bad thing though. Network packets, allocating input/output buffer, locking system tables, ... If you have a lot of users it will eventually be a bottleneck.Better to minimise the number of database calls by making all access via stored proces. The proc returns all the data needed to form the page.You can also then restructure the database if needed and change the stored procs to fit without the application even knowing anything has changed.It also makes it easier to optimise as you can call the SPs independently to obtain the query plan and also give optimiser hints if necessary.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2003-06-28 : 13:50:22
|
| I'm with nr here: absolutely make sure that you minimize the number of round-trips needed to generate a page.My app is structured so that each page involves 3 database calls, but only two of them are synchronous: 1 to generate the banner ad and page navigation, and 1 for the particular page's data. The final call is an asynchronous call for logging purposes.If a page needs 6 queries that generate 4 recordsets and a bunch of output parameters, just make it one SP: p_page_Whatever, and provide all of the inputs necessary.That will have more of a performance impact than any amount of normalization or denormalization.Cheers-b |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-06-28 : 21:31:51
|
quote:
Im using table inheritence where each sub table has a parent table. So i could potentially have 5 joins for one object in my application.
This statement worries me..First of all, there is no such thing as "table inheritance". It seems you are applying OO methodology in you DB instead of relational modelling.A serious question.. What is an object and what is its equivalent in a relational database?DavidM"SQL-3 is an abomination.." |
|
|
|
slacker
Posting Yak Master
115 Posts |
Posted - 2003-06-28 : 23:01:55
|
| An object is anything you can represent in the real world. With properties and behavior. What is its representation in a relational database? Thats a good question. Data doesnt have behavior. So I guess only the properties of the object are stored in the db. So in a sense there is no equivelent. Only the state of the object is stored. No such thing as table inheritence? Its an object relational software pattern. Commonly used by oo programmers who write oo enterprise applications on rdbms systems. And yes I would try to reduce db calls as much as possible. But the application requires that certain functionality be built into encapsulated modules that dont know about other parts of the system. If I get 3 or 4 of these objects on a page. Each object would make its own calls. However the same object isnt ever requested twice because it is cached per request. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-06-29 : 00:07:49
|
quote:
No such thing as table inheritence? Its an object relational software pattern. Commonly used by oo programmers who write oo enterprise applications on rdbms systems.
You have obviously never read much of Date and Darwen. Please take the time to read every single article at this site [url]www.dbdebunk.com[/url]And if you have the cash go out and buy FOUNDATION FOR FUTURE DATABASE SYSTEMS: THE THIRD MANIFESTO.But to give you a taste...They argue that OO programmers make the mistake when they use the table==class formulae, when in fact domain==class is the correct usage.The "Table Inheritance" mistake is a direct consequence of the above, when they argue that you really want type (domain) inheritance.We all knwo what sub and super types are but what the hell are subrelations and superrelations?An "object/relational" DBMS is nothing more that a true RDBMS with full User Defined Types available.DavidM"SQL-3 is an abomination.." |
|
|
|
slacker
Posting Yak Master
115 Posts |
Posted - 2003-06-30 : 17:48:09
|
| Thanks for the info. I will check it out. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-06-30 : 20:16:54
|
| As far as "Inheritance" goes, I believe your just talking about normal RI.True "Inheritance" (at least in my book, stores the "lineage" of the relationships.So if you have to join a great great great great grandparent to the child, all of the relationship info is on the child...giving a 2 table join instead of 6.This is a physical implentation issue.Logical and physical can be 2 VERY different things.Implemeneting a pure logical model for logical model sake I don't think is a very good thing.Any other opinions?MOOBrett8-) |
|
|
|
|
How far can you scale?