The following query is taking 5 seconds to return 9692 rows:
These are the two indexes referencing Company_Id column, however this table have more than 15 indexes that don't reference it.
It is using the PK instead of my indexes. Why?
Extra question: Every JOIN is causing a Hash Match, is that ok? I mean considering that the final query will have more than 10 JOINs.
By Juliano 16 asked Oct 07, 2015 at 08:10 PM
SQL Server might be able to use the second index if you add CreateDate to the include list (and Process_Id to the key), but between the join conditions and the fact that your primary filter criteria is the 7th column in the key (why is the first column ProjectType_Id?), it's still going to perform a scan of that index, and it would only be chosen if the table (represented by the clustered index) is a lot wider than this (we can't tell how many other columns are in the table but not mentioned in the query). The first index is not suitable because, while it could be used to locate the rows for the where clause, it does not contain Unit_Id, which is necessary for the first outer join. SQL Server determines that it's cheaper to scan the clustered index than to seek on the skinny index and perform lookups for every single row to obtain the other necessary data to satisfy the query.
So, you might try dropping both indexes, and creating this index instead (this is just a first stab):
(Note that the second index may actually be useful for other queries, so check index_usage_stats, don't just drop it because I said so. The first index, on just Company_Id, is highly unlikely to be useful for any queries.)
A hash match is generally suboptimal; it means that the inputs are not sorted. Better indexes can usually eliminate those.
By Aaron Bertrand ♦ 1.7k answered Oct 07, 2015 at 08:44 PM
It shouldn't take that long. You must be using an underpowered (or cloud) server. If you had run the query from within Plan Explorer, it would have included I/O metrics and other useful performance information.
This is a red flag that your table may be too wide, and/or that your indexing strategy is not well-planned. Try to normalize your table design and give proper thought to your indexes. You should fix the spelling errors like
Because there is no index with
With the tiny number of rows in your tables at the moment, yes. It is probably optimal. The cause of the slow performance is almost certainly elsewhere - probably a hardware issue.
Writing a query with 10 joins is often a sign of trying to do too much in one query. Instead of writing one monster query (which you will have endless trouble optimizing), break the query into manageable blocks, and use temporary tables (indexed if necessary) to hold small intermediate results.
You will get better quality execution plans, and more predictable performance this way. Your final queries will likely be comprehensible by mere humans as an additional benefit. Maintaining queries usually represents more of the lifetime cost of a database than the initial implementation, so writing simpler queries usually pays off in the long run.
You appear to be using
Your tables contain very few rows. If you intend to scale this, you need to develop and test with a representative data volume and value distribution.
You should get into the habit of prefixing your table references with the schema prefix, perhaps it is
You should also get into the habit of using aliases for your object references e.g.
If you really want to test the performance of hash joins versus lookups, you may, for testing purposes, put a
Ultimately, I am suggesting you employ a decent DBA to assist you at this stage.
By SQLkiwi ♦ 6.6k answered Oct 08, 2015 at 01:39 PM