MSSQL indexes and keys and FPMI

Archived Topics
1

I am looking for a way to better my databases. I have been reading a lot but some knowledge still eludes me. I understand the basics of indexes, to overly simplify an index is similar in nature to a what an index does in a book, it help sort things and puts them in order.

Would this be correct so far?

Now comes the parts that I am unsure about. All the pages I have read talk about setting indexes and calling them using a method I am not familar with. The only thing I have used so far is Studio Manager and that is very limited usage. So here are my list of questions:

  1. If you use the database/table in a number of ways, example one query would use machine and start; another query would use machine and stop, and a final one might use machine, operator, assist1, assist2 and assist3. WOuld I need to set an index for each type of query?

  2. How does MS SQL know which index grouping to use? Do you have to specify it somewhere in the query?

  3. I have a easy chart, it runs off of a view, the view gets its information from the main historcal table and is relatively small usually less than 50K record. But yet when the query is run to get the data for the easy chart it seems to really put a taxing on the server. If you use task manager and watch the application under normal circumstances it is usually around 8 to 10 percent but when the page is opened with the easy chart everytime the query is executed it racks the usage upto 100 holds for like a second then drops to about 50 then slowly starts going down again until the next query. I know it is this because I have taken everything else off of the page. So when I read about how the one integrator on here got much better results when he indexed it got my curiousity peaked. And then when I was chatting with Nathan he also mentioned indexes and using proper key fields so I thought if I could get a better understanding of how I can use them within PMI like the other integrator did then my application would be much more efficient.

Sorry so many questions in one posting but I hope it is all realted and can easily be answered.

Thanks and have a great day and a wonderful thanksgiving.

2

Martin - you may be overthinking this one.

An index stores “copies of” or “references to” a subset of the data in your table - usually a column. It stores it in an efficient (quickly searchable/sortable) manner.

Consider the following query:

SELECT * FROM employees WHERE name LIKE '%joe%'

If the name column wasn’t indexed the database might have to inspect every record.
If we have the name column indexed then the database will search that sorted index (fast), determine all the rows where the condition is true, then SELECT only those columns.

To answer your question, you don’t need an index per query. As a rule of thumb, index the columns that you might use in the WHERE clause of any query. For “historical” tables you want to index the timestamp.

As an aside - your tables had a lot of JOINs. It’s pretty much the same to the system if they’re coming from your queries or simple queries on views that contain them. In that case you want to set up foreign keys between the referencing column of one table and the primary key of the other.

3

With regards to the view - a view is simply a stored query. So when you say the view isn’t very big, it doesn’t make sense. Every time you access the view it has to access the underlying table, which is very big! Views don’t save the database any work. Make sure your view is fast. i.e. make sure that doing a select * from myview is fast before you try to figure out why the easy chart is bogging down the server.