By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

Performance based on design

P: 2
I was reading an earlier post and came across the following message:

In any case, discussing the size of a database or the hardware it runs
on usually isn't as important as how well it has been designed. If you
have a well designed database which is properly indexed and accessed
using well-written code, then it will perform and scale well up to
very large amounts of data. If you don't, then you can have
performance problems with even small amounts of data.


Which brought up a few questions, and perhaps there isn't a good answer but what are some "quidelines" when someone builds a database? Such as what would reason I would want to index a field or table? (btw..I do know what indexing is) But there are pros and cons?

Thanks
Apr 28 '08 #1
Share this Question
Share on Google+
5 Replies


Delerna
Expert 100+
P: 1,134
Difficult to come up with absolutes here because each database system is different and you need to weigh up the pro's and cons accordingly.

1) Indexes speed up table scans because they reduce the number of rows that a query has to search through.
Indexes slow down table inserts and deletes because every time rows are added or deleted the indexes also needs to be updated.

So the more often inserts and deletes occur on a table the less you want to impede that with indexes.

2) Query speed is relative to table size.
So the larger a table becomes the more you will want to speed queries up with indexes.

3)Tables can have more that 1 index.
So the more variation in query slection criteria there is the more you will want to increase the number of indexes to suit.

4) With point 1 in mind how can we balance 2 and 3 for best overall performance

Is that the sort of thing you have in mind?
Apr 29 '08 #2

P: 2
1) Indexes speed up table scans because they reduce the number of rows that a query has to search through.
Indexes slow down table inserts and deletes because every time rows are added or deleted the indexes also needs to be updated.

So the more often inserts and deletes occur on a table the less you want to impede that with indexes.

2) Query speed is relative to table size.
So the larger a table becomes the more you will want to speed queries up with indexes.

3)Tables can have more that 1 index.
So the more variation in query slection criteria there is the more you will want to increase the number of indexes to suit.

4) With point 1 in mind how can we balance 2 and 3 for best overall performance

Is that the sort of thing you have in mind?[/quote]

Yes it was. But what would be the cutoff point when the index is actually slowing down the system becuase of inserts?

I have a home build Classic ASP (yes I know CASAP is also slower) user directory that searches the home address, phone, major, and degree. Overall, there is 8 tables that are linked together for this search, howerver none of them are indexed. The largest tables is around 200K rows. If I wanted to try to increase the speed with indexing, what columns would I index.. the ones it searches by or the ones it displays?

Thanks
Apr 29 '08 #3

ck9663
Expert 2.5K+
P: 2,878
Hands down, the one you always searched for.

-- CK
Apr 29 '08 #4

Delerna
Expert 100+
P: 1,134
Yes,what ck said. Indexes don't speed displaying data, only searching data.
So if you find many queries are seaching a table and retrieving records between a date range, then the date field would be an obvious candidate for an index.
Apr 29 '08 #5

Delerna
Expert 100+
P: 1,134
Yes,what ck said. Indexes don't speed displaying data, only searching data.
So if you find many queries are seaching a table and retrieving records between a date range, then the date field would be an obvious candidate for an index.
I just re-read that and I didn't mean to imply that dates are the only thing that can be indexed
Apr 30 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.