Subtitles of the Movie

In talking about optimizing our indexes, I'd like to start with the couple of assumptions that are definitely a safe bet. First and foremost, people do not like to wait. Second, in terms of your computer and the running of your queries, 8 seconds is an eternity. Finally, programs need to be fast. Now, keep in mind that Normalization helps this, so too does good design. Part of good design is the use of indexes and properly Normalizing our database, our two key ingredients in optimizing. So what about optimization and indexes? First, carefully chosen indexes will speed up your queries. Now, almost all tables will benefit from an index. Remember, it's those secondary indexes that we want to be cautious about. We have to constantly think in terms of what is the value on performance. Also, remember the smaller the index, the better the response time. It's better to have an index for instance just on the zip code. You do not need to do that index on the zip code and State since the zip code is already defining the State. Whenever you start having multiple columns on an index, you're always slowing you down. Remember too that many database programs do not store the full character for the index. This is why the best one to use is your integer. Many databases will only store the first few bytes. It will actually truncate the rest of the information as a result. Make sure you have the best choice for your index. Also, put your most unique data element in the main index. What we want to do is bring up the least amount of information possible with the index itself. Now, some specific steps that you can actually take; first, you'll want to be able to query only what you need. You don't need a query running. Consider the filter. Remember in any query in any report in any table, we could use the filter. Also, the where clause is the most important consideration for optimization. It will allow us to only select the fields that we need. So keeping this in mind, never use Select All and also remember that when you join tables, they will be expensive on resources. Make sure that you're joining on an index field. Also, make sure you're using the right type of join and always define the join as an inner or an outer. Do not just simply use the word join. Now, when you're working with your indexes, remember that they can be added and modified. The first thing to keep in mind is that a full table scan will hinder performance. If you're finding that from an execution plan, change your index. Indexes and proper filtering can solve these problems. Also remember that primary keys need indexes. That's what will make a join faster and remember those indexes must be maintained. Check on them from time to time. Run your execution plans and always remember to only add them if you need them. When a table is Read-Only, indexes can be added much more frequently than in dynamic data since there's not going to be a need for maintenance. Your data is not changing, so the indexing space a bit more static. It is important to remove unneeded views also. When you're working with our indexes and we're running them against queries, we're also running them against the views for the users. As a result, don't use them if you do not need to. Views are not tables. They are virtual tables. They are queries that have been designed to give us information in a tabular format. Since they are logical and not physical, every time you run the Selected Statement from the name of the views such as TheView, you will run the query that makes TheView and it will run that query on TheView again. This is exceptionally heavy on resources. If you always need the same information over and over again, views can be good, but if you're filtering the view that you made, keep in mind that it's like running a query on your query and it's going to be slower. We want to keep in mind that the way to access our data is in the most performance-based manner. We want to minimize the way it comes up and we want to pull it up in the most efficient manner. We really need to think through should it be a table, should it be a view, should it be a query. If it's a query, should it be index. These are only decisions that you can make and it's going to take practice to get them correct.