Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mrtweaver
Yak Posting Veteran
67 Posts |
 Posted - 2008-11-26 : 22:07:19
|
| Running MSSQL 2005, using interface Studio Manager GUI. My question comes about learning more about indexes and keys. I have a general understanding what they are but am unsure exactly how to use them.I am running an application that uses Python and Java. It uses standard queries such as SELECT this FROM this WHERE ..... and it also uses INSERTS and the other types of transact statement. It is stated that if you index your columns used in the where clause it will cause the transact to run faster and more efficient.Is this the case?Now my next question is can you have more than one index?If so does the query automatically know which one to use based on the part after the where clause? I ask this because I have three or four queries that come off of the same table but each accesses informaton differently based on the where clause.Do I have to used some sort of use index type of command so it knows which index to use?Sorry so many questions Like i said I am just learning and trying to better understand how things work in the SQL world. Hopefully my company will finally set aside the funding and send me to school on this type of information, Till then I have to use these forums and get as much information and provide as much information as possible.Thanks and have a great day and a good thanksgiving. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-26 : 22:25:55
|
quote:
Originally posted by mrtweaver
Running MSSQL 2005, using interface Studio Manager GUI. My question comes about learning more about indexes and keys. I have a general understanding what they are but am unsure exactly how to use them.I am running an application that uses Python and Java. It uses standard queries such as SELECT this FROM this WHERE ..... and it also uses INSERTS and the other types of transact statement. It is stated that if you index your columns used in the where clause it will cause the transact to run faster and more efficient.Is this the case?Yes. Now my next question is can you have more than one index?Yes.Only 1 clustered index and upto 249 Non-clustered index. Too bad to have too many indexes for OLTP tables.If so does the query automatically know which one to use based on the part after the where clause?It will choose whichever is efficient.I ask this because I have three or four queries that come off of the same table but each accesses informaton differently based on the where clause.See execution plan(CTRL + M) to analyze whether it is using index .Do I have to used some sort of use index type of command so it knows which index to use?You can use hints to force index if you want to.Sorry so many questions Like i said I am just learning and trying to better understand how things work in the SQL world. Hopefully my company will finally set aside the funding and send me to school on this type of information, Till then I have to use these forums and get as much information and provide as much information as possible.Thanks and have a great day and a good thanksgiving.
|
 |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-12-02 : 04:54:39
|
| An extra bit on indexes:Indexes will increase the speed of Reads, but they will slow the speed of Writes, because SQL Server has to maintain its indexing. If you think of them as similar to the index at the back of a book, if new info is added then the indexes need updated.So there is an overhead so you should not throw lots of indexes at a table if they are not needed, but do consider indexing columns which are frequently queried. |
|
|
|
mrtweaver
Yak Posting Veteran
67 Posts |
Posted - 2008-12-02 : 19:14:20
|
Here is a question concerning indexes. I have the following fields in a table: machine, start, stop, t_stamp, operator, assist1, assist2, and assist3. In the project which is quering this table these fields are used in a variety of way. In one query you have machine, operator, assist1, assist2 and assist3 in the where clause, in another query you have machine, t_stamp, and shift in the where clause, then in another query you have machine, start, stop, shift and t_stamp. And there is probably someother queries that I dont recall at this time that use these fields. So in a case like this where would you draw the indexes?Machine is a machine location and since all 99 machine dump into the same table this number can vary between 1 and 99. It is also an INT.start, stop and t_stamp are all date/time fieldsOperator, assist1, assist2, and assist3 are all INT values.And shift is an INT value that varies between 1 and 3.This table is currently over 1M records in length and it is bound to grow. But I know that the queries are sluggish and I can only assume that it is because there is no index. So I have to find the right way to index this to make it as fast as possible.I did ask at one point in time about creating a view that would limit the amount of data to a specific period but I was told that this would even be sluggish if there was no indexes because of how views operate. So everything points back to the fact that I need to create at least one index but with my limited knowledge I am not sure where and what I want to use to make this run as fast as possible.Thanks for any and all assistance. Have a great day. quote:
Originally posted by darkdusky
An extra bit on indexes:Indexes will increase the speed of Reads, but they will slow the speed of Writes, because SQL Server has to maintain its indexing. If you think of them as similar to the index at the back of a book, if new info is added then the indexes need updated.So there is an overhead so you should not throw lots of indexes at a table if they are not needed, but do consider indexing columns which are frequently queried.
|
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-02 : 20:43:27
|
| First of all ,Your table is breaking Ist Normal form.Regarding index choices, you can run server side trace for 15-30 during busy hours and put the workload in database tuning advisor for index recommendation. But you have to test with it before you apply. |
|
|
|
|
|
|
|
|
Index questions