473,287 Members | 1,827 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Keys in a table: Is there a point when all columns are important?


Folks,

I have a table that has five columns in it - It collates references to
records in five other tables. I understand that an index/key can help
improve performance when searching - however I also believe too many
keys can hamper performance since table updates also mean updateing the
indexes.

Thus - Since all five columns in my table can be used individually for
specific searches, would I be better off not having keys - Since the
keys/indexes would not have to be updated, I stand a performance
improvement in this situation,true?

Comments via the newsgroup are greatly appreciated, thanks,

Randell D.
Jul 20 '05 #1
2 1201
Randell D. wrote:
Thus - Since all five columns in my table can be used individually for
specific searches, would I be better off not having keys - Since the
keys/indexes would not have to be updated, I stand a performance
improvement in this situation,true?


It's often the case that an application reads the data more than it
updates data. So the cost of updating the keys will likely give you
performance benefit on read operations many times. And depending on the
size of your dataset, the difference between an indexed search and a
non-indexed search can be huge. If the keys are actually going to get
used, it's almost certainly a net benefit to keep them.

I recommend that indexes should be removed when they are on columns that
are *never* used in search criteria, sorting, or join conditions. Even
if a column is only used very seldom, keeping an index maintained can be
justified if it's important that those rare searches run as quickly as
possible.

Regards,
Bill K.
Jul 20 '05 #2
Bill Karwin wrote:
Randell D. wrote:
Thus - Since all five columns in my table can be used individually for
specific searches, would I be better off not having keys - Since the
keys/indexes would not have to be updated, I stand a performance
improvement in this situation,true?

It's often the case that an application reads the data more than it
updates data. So the cost of updating the keys will likely give you
performance benefit on read operations many times. And depending on the
size of your dataset, the difference between an indexed search and a
non-indexed search can be huge. If the keys are actually going to get
used, it's almost certainly a net benefit to keep them.

I recommend that indexes should be removed when they are on columns that
are *never* used in search criteria, sorting, or join conditions. Even
if a column is only used very seldom, keeping an index maintained can be
justified if it's important that those rare searches run as quickly as
possible.

Regards,
Bill K.


Thanks for that...
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

61
by: Toby Austin | last post by:
I'm trying to replace <table>s with <div>s as much as possible. However, I can't figure out how to do the following… <table> <tr> <td valign="top" width="100%">some data that will...
4
by: Robin Tucker | last post by:
Hi, I'm trying to determine with my program whether or not a given database supports a given feature set. To do this I'm querying for certain stored procedures in the sysobjects table and if...
3
by: Gaz | last post by:
I have a table which has 10 columns which make up the secondary key. 1 or more of these columns can be set but the remaining columns in the secondary key will be null. For example : id k1 k2...
3
by: Randell D. | last post by:
Folks, My internet access is intermitent until I get my own connection inside the next ten days - I say this so that I can thank in advance who ever gives a few seconds to read/answer my...
3
by: heroe | last post by:
Hello *, i write you with an urgent problem. I would like to create forign keys to datetime field, but it doesn't seem to work. I get Can't create table '.\sampleDB\student_module.frm' (errno:...
5
by: Ross A. Finlayson | last post by:
Hi, I'm scratching together an Access database. The development box is Office 95, the deployment box Office 2003. So anyways I am griping about forms and global variables. Say for example...
117
by: phil-news-nospam | last post by:
Is there really any advantage to using DIV elements with float style properies, vs. the old method of TABLE and TR and TD? I'm finding that by using DIV, it still involves the same number of...
6
by: polocar | last post by:
Hi, I'm writing a program in Visual C# 2005 Professional Edition. This program connects to a SQL Server 2005 database called "Generations" (in which there is only one table, called...
9
by: PeteCresswell | last post by:
I've got something called "Reference Rates". The idea is that on a given day, we have various rates of return for various entities. e.g. Libor 3-month return, Libor 6-month return, US Treasury...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.