Performance cost of joins

We are in the stage of designing a database for our web application. We have two design options, one is to use normalized tables and using joins to get data from more than one table, the other is to duplicate fields in tables and avoid using joins.
The main purpose is to get high performance from the system. the system is supposed to produce reports that contain information from about 100,000 records from 5 or 6 related tables.
which of these approaches is better from the performance point of view? What is the performance cost of using joins over getting data from one table?Generally, one should not blindly assume that it's always 'normalized and slow' vs. 'de-normalized and fast’. Normalized databases when reading data typically perform better too provided that the optimizer is able to benefit from useful indexes for the frequent joins. Having said that, obviously there are corner cases where de-normalization does improve things for certain data access patterns especially when de-normalization allows you to get rid of some indexes and hence reduce the update costs. But, again, it all depends on your particular logical database schema and the prevalent data access/update patterns.|||

It is generally better to normalize your tables for better data integrity and to reduce duplicate data. After you have a good normalized foundation, then you can think about denormalizing with rollup tables for exceptional cases. For an OLTP workload, you may see some performance issues if you have frequently executed queries that have joins to more than four or five tables, since the query optimizer has to use heuristics to come up with a plan.

Your indexing strategy will have more effect on performance than anything else. You have to analyze how volatile your tables are and what kind of workload you have in order to determine what indexes to create. There are good DMV queries that will let you easily see which queries are being executed the most and which indexes are being used. Here is an example:

-- Get Top 200 executed SP's ordered by calls/minute
SELECT TOP 200 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',
qs.total_worker_time/ISNULL(qs.execution_count, 1) AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/ISNULL(qs.execution_count, 1) AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
qs.execution_count/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Calls/Minute',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = 5 -- Change this for the database you are interested in
ORDER BY qs.execution_count/DATEDIFF(SECOND, qs.creation_time, GetDate())DESC

|||

its a question of tradeoff..... for faster dmls u need a normalized structures...for faster (generaly) reports de normalized is better..... so u have to decide where actually u want to optimize the performance...

for me normalized database does the trick... as it may prove to be more helpful in longer run ... ie u always have a greater integrity of data....

for reports u may use views to avoid repeated use of joins...

so optimal for me = normalized + views

Performance cost of joins

We are in the stage of designing a database for our web application. We have two design options, one is to use normalized tables and using joins to get data from more than one table, the other is to duplicate fields in tables and avoid using joins.
The main purpose is to get high performance from the system. the system is supposed to produce reports that contain information from about 100,000 records from 5 or 6 related tables.
which of these approaches is better from the performance point of view? What is the performance cost of using joins over getting data from one table?Generally, one should not blindly assume that it's always 'normalized and slow' vs. 'de-normalized and fast’. Normalized databases when reading data typically perform better too provided that the optimizer is able to benefit from useful indexes for the frequent joins. Having said that, obviously there are corner cases where de-normalization does improve things for certain data access patterns especially when de-normalization allows you to get rid of some indexes and hence reduce the update costs. But, again, it all depends on your particular logical database schema and the prevalent data access/update patterns.|||

It is generally better to normalize your tables for better data integrity and to reduce duplicate data. After you have a good normalized foundation, then you can think about denormalizing with rollup tables for exceptional cases. For an OLTP workload, you may see some performance issues if you have frequently executed queries that have joins to more than four or five tables, since the query optimizer has to use heuristics to come up with a plan.

Your indexing strategy will have more effect on performance than anything else. You have to analyze how volatile your tables are and what kind of workload you have in order to determine what indexes to create. There are good DMV queries that will let you easily see which queries are being executed the most and which indexes are being used. Here is an example:

-- Get Top 200 executed SP's ordered by calls/minute
SELECT TOP 200 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',
qs.total_worker_time/ISNULL(qs.execution_count, 1) AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/ISNULL(qs.execution_count, 1) AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
qs.execution_count/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Calls/Minute',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = 5 -- Change this for the database you are interested in
ORDER BY qs.execution_count/DATEDIFF(SECOND, qs.creation_time, GetDate())DESC

|||

its a question of tradeoff..... for faster dmls u need a normalized structures...for faster (generaly) reports de normalized is better..... so u have to decide where actually u want to optimize the performance...

for me normalized database does the trick... as it may prove to be more helpful in longer run ... ie u always have a greater integrity of data....

for reports u may use views to avoid repeated use of joins...

so optimal for me = normalized + views

Performance cost of joins

We are in the stage of designing a database for our web application. We have two design options, one is to use normalized tables and using joins to get data from more than one table, the other is to duplicate fields in tables and avoid using joins.
The main purpose is to get high performance from the system. the system is supposed to produce reports that contain information from about 100,000 records from 5 or 6 related tables.
which of these approaches is better from the performance point of view? What is the performance cost of using joins over getting data from one table?Generally, one should not blindly assume that it's always 'normalized and slow' vs. 'de-normalized and fast’. Normalized databases when reading data typically perform better too provided that the optimizer is able to benefit from useful indexes for the frequent joins. Having said that, obviously there are corner cases where de-normalization does improve things for certain data access patterns especially when de-normalization allows you to get rid of some indexes and hence reduce the update costs. But, again, it all depends on your particular logical database schema and the prevalent data access/update patterns.|||

It is generally better to normalize your tables for better data integrity and to reduce duplicate data. After you have a good normalized foundation, then you can think about denormalizing with rollup tables for exceptional cases. For an OLTP workload, you may see some performance issues if you have frequently executed queries that have joins to more than four or five tables, since the query optimizer has to use heuristics to come up with a plan.

Your indexing strategy will have more effect on performance than anything else. You have to analyze how volatile your tables are and what kind of workload you have in order to determine what indexes to create. There are good DMV queries that will let you easily see which queries are being executed the most and which indexes are being used. Here is an example:

-- Get Top 200 executed SP's ordered by calls/minute
SELECT TOP 200 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',
qs.total_worker_time/ISNULL(qs.execution_count, 1) AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/ISNULL(qs.execution_count, 1) AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
qs.execution_count/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Calls/Minute',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = 5 -- Change this for the database you are interested in
ORDER BY qs.execution_count/DATEDIFF(SECOND, qs.creation_time, GetDate())DESC

|||

its a question of tradeoff..... for faster dmls u need a normalized structures...for faster (generaly) reports de normalized is better..... so u have to decide where actually u want to optimize the performance...

for me normalized database does the trick... as it may prove to be more helpful in longer run ... ie u always have a greater integrity of data....

for reports u may use views to avoid repeated use of joins...

so optimal for me = normalized + views

Perfomance Enhancement through proper database designing

Dear Reader

I am trying to design a database. How can I make best Judgement that Indexing (which I am trying to fix during Diagram Desingning process)is ok.

I am able to identify the best candidate for the indexing.

Below is the details I want to understand:

Area
ZIP
City
County
District
State/Province
Country

Now I want the data retrival optimization through Index. (you can suggest another idea, also)

Entities Area,..., Country have independent tables.
Example:

Area_Table
AreaID (PK)
Area
They have relationship- one to many- if you go from Country to Area.

There is one more table:

Location_Table (PK)
LocationID
AreaID
ZIPID
CityID
CountyID
DistrictID
State/ProvinceID
CountryID

(Location_ID is further related to the Address of the contact.)

GUI has a single form to enter these details.On a save command details in all the tables -Area to Country- (individually) being inserted.
& simultaniously Location_Table is also being inserted with the details.

Following is the situation of being queried these tables:

(1) GUI user can select an Area than the related details of ZIP .., ..., ...upto Country etc. should be loaded automatically (id it is previously stored by the user entry in the database.)

(2) Contacts have to be retrived on the basis of Area, ZIP, ....County. (Necessary Groupings are required )

Example:
If Contacts are queried Country Wise then the Display should be
Country1
State1
District1
County1
City1
ZIP1
Area1
Area2
ZIP2
City2

County2
District2
Country2

Please Guide.

SuryaPrakash

*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...0255a1765491f15
*****************************************SuryaPrakash Patel via SQLMonster.com (forum@.SQLMonster.com) writes:
> I am trying to design a database. How can I make best Judgement that
> Indexing (which I am trying to fix during Diagram Desingning process)is
> ok.

I was not really able to understand exactly what the queries would look
like. While a trained database designer certainly puts indexes already
in the early design stage, it's better to focus to get the data model
right to support the functional requirements first. Once we have the
queries and the tables, it may be eaesier to say what would be the
best indexes.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 09 Nov 2004 04:32:02 GMT, SuryaPrakash Patel via SQLMonster.com
wrote:

>Dear Reader
>I am trying to design a database.
(snip)

Hi SuryaPrakash,

Further to Erlands's comments: you should also not think about GUI or
report formats when designing a database. Database design should be driven
by the structure of the data only.

Only when you have a properly normalized database that will store all data
the application needs and that will reject all modifications that would
hurt data integrity comes the time to think about the user interface (both
for in- and output screens and for reports) and about adding extra indexes
for performance enhancement.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Dear Erland & Hugo

Thanks

I got your suggestions.

I have specific needs to get the certain ways of output. So it is obvious that I incorporate the needs while doing the database design.

But nowonwards I will try to stick the database structure, only. And leave the GUI part and Indexing part for later stages of development.

Point to be noted is that I will not have any chance to deploy any index after the software has been installed at user's end. Any comments.

Thanks again

SuryaPrakash

*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum...sql-server/5093
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...4931f7dbd3d74e1
*****************************************|||On Wed, 10 Nov 2004 00:59:23 GMT, SuryaPrakash Patel via SQLMonster.com
wrote:

>Point to be noted is that I will not have any chance to deploy any index after the software has been installed at user's end. Any comments.

Hi SuryaPrakash,

That's a logical and quite common scenario. That's why you should only
install the software when it's completely finished. I'd recommend to build
the software first, then test and debug until it works as desired, then
start doing performance test and tweaking things (adding indexes,
rewriting queries, etc) until the speed is as desired. Then do a final
test to check that performance tweaking didn't break functionality.

Installing at the user's end should be postponed until all these stages
are done and the product is completely finished.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||SuryaPrakash Patel via SQLMonster.com (forum@.SQLMonster.com) writes:
> Point to be noted is that I will not have any chance to deploy any index
> after the software has been installed at user's end. Any comments.

So you need to test carefully with real-world data before you ship.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp