Perfomance Enhancement through proper database designing

[SuryaPrakash Patel via SQLMonster.com]

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....255a1765491f15
*************** *************** ***********

[Erland Sommarskog]

Re: Perfomance Enhancement through proper database designing

SuryaPrakash Patel via SQLMonster.com (forum@SQLMonst er.com) writes:[color=blue]
> 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.[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Hugo Kornelis]

Re: Perfomance Enhancement through proper database designing

On Tue, 09 Nov 2004 04:32:02 GMT, SuryaPrakash Patel via SQLMonster.com
wrote:
[color=blue]
>Dear Reader
>
>I am trying to design a database.[/color]
(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)

[SuryaPrakash Patel via SQLMonster.com]

Re: Perfomance Enhancement through proper database designing

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....ql-server/5093
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse....931f7dbd3d74e1
*************** *************** ***********

[Hugo Kornelis]

Re: Perfomance Enhancement through proper database designing

On Wed, 10 Nov 2004 00:59:23 GMT, SuryaPrakash Patel via SQLMonster.com
wrote:
[color=blue]
>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.[/color]

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)

[Erland Sommarskog]

Re: Perfomance Enhancement through proper database designing

SuryaPrakash Patel via SQLMonster.com (forum@SQLMonst er.com) writes:[color=blue]
> 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.[/color]

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


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp