April 27, 2011 at 8:54 pm
have anyone know have any white paper have discuss about Microsoft sql 2008 database schema intern of improve query performances ?
Will the database schema affected the query performances?
April 27, 2011 at 9:24 pm
The schema affects query performance only in terms of how many joins you might need to perform queries, but any balancing out to reduce query time can increase resources to update. schema decisions are usually based on normalization and integrity, not necessarily performance. You might denormalize for performance in places, but there isn't necessarily a white paper that explains this because it is such an open-ended discussion.
If you pick up Louis Davidson's database design book, that's a good start.
We also have a database design series here: http://www.sqlservercentral.com/stairway/72400/
April 28, 2011 at 4:54 am
It does depend on how you define "schema." I would include picking the clustered index as a fundamental part of database design and that certainly affects performance. Also, primary keys and foreign keys are used by the optimizer, so they are also a part of performance. It all adds up. I'm with Steve, Louis Davidson's book is the way to go.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 28, 2011 at 6:46 am
Hi
The Data base Schema will never decreases the Query Performance. If you are not mentioned the schema when you are executing the query check the execution time, and then again check with schema, You can easily identify the things in the execution plan...
Make Problem in to pieces and then think for the solution
Skase
April 28, 2011 at 7:41 am
spadavala82 (4/28/2011)
HiThe Data base Schema will never decreases the Query Performance. If you are not mentioned the schema when you are executing the query check the execution time, and then again check with schema, You can easily identify the things in the execution plan...
Make Problem in to pieces and then think for the solution
Skase
By "schema" we're not talking about the owner of the objects, but rather the objects themselves. Talking about a database schema is talking about the tables, primary keys and foreign keys.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 28, 2011 at 8:38 am
And the organization of those objects. It looks like schema is another area where we've "reused" a word too many times.
The schema has always meant the design as well. What tables you have, how are the entities broken out among one or more tables, what keys exist in and between them, the grouping of the objects into what used be called the owner and is now the "schema" in the SQL Server namespace.
It gets confusing, but I think if you are concerned, you should more closely define what you are concerned about. Do not use "schema", but give us a more detailed explanation of which objects, or parts thereof, that you are concerned about.
I do think that a poorly designed schema can impact query performance overall as simple queries might require many joins. However the individual performance of a particular query is optimized based on the schema (Design) of objects that are there. A different ordering of columns or vertical partitioning of entities is not inherently going to make the query slower.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply