asked
How To Remove Hash Match/Inner Join From Execution Plan?
Hi there, How best to remove a hash join from the execution plan? The current query is: SELECT CLIENTNUM, ACTLINENUM, ACTIONNUM, INFO_1, INFO_2, INFO_3, INFO_4, INFO_5, INFO_6, INFO_7, INFO_8, INFO_9, INFO_10, INFO_11, INFO_12, INFO_13, INFO_14, INFO_15, INFO_16, INFO_17, INFO_18, INFO_19, INFO_20, INFO_21, INFO_22, INFO_23, INFO_24, INFO_25, INFO_1001, STAFF_NAME FROM CONTACT_LOG WITH (INDEX(IX_CONTACT_LOG_XXX,IX_CONTACT_LOG_XX2,IX_CONTACT_LOG_XX3)) WHERE (INFO_1 = 'Y' OR INFO_2 = 'Y' OR INFO_3 = 'Y' OR INFO_4 = 'Y' OR INFO_5 = 'Y' OR INFO_6 = 'Y' OR INFO_7 = 'Y' OR INFO_8 = 'Y' OR INFO_9 = 'Y' OR INFO_10 = 'Y' OR INFO_11 = 'Y' OR INFO_12 = 'Y' OR INFO_13 = 'Y' OR INFO_14 = 'Y' OR INFO_15 = 'Y' OR INFO_16 = 'Y' OR INFO_17 = 'Y' OR INFO_18 = 'Y' OR INFO_19 = 'Y' OR INFO_20 = 'Y' OR INFO_21 = 'Y' OR INFO_22 = 'Y' OR INFO_23 = 'Y' OR INFO_24 = 'Y' OR INFO_25 = 'Y' OR INFO_1001 = 'Y') AND INFO_DATE IS NULL Indexes are: CREATE NONCLUSTERED INDEX IX_CONTACT_LOG_XXX ON CONTACT_LOG(CLIENTNUM, ACTLINENUM, ACTIONNUM, INFO_1, INFO_2, INFO_3, INFO_4, INFO_5, INFO_6, INFO_7, INFO_8, INFO_9, INFO_10, INFO_11, INFO_12, INFO_13) CREATE NONCLUSTERED INDEX IX_CONTACT_LOG_XX2 ON CONTACT_LOG(CLIENTNUM, ACTLINENUM, ACTIONNUM, INFO_14, INFO_15, INFO_16, INFO_17, INFO_18, INFO_19, INFO_20, INFO_21, INFO_22, INFO_23, INFO_24, INFO_25, INFO_1001) CREATE NONCLUSTERED INDEX IX_CONTACT_LOG_XX3 ON CONTACT_LOG(CLIENTNUM, ACTLINENUM, ACTIONNUM, STAFF_NAME, INFO_DATE) At present the above query performs 3 index scans (at 3% cost each) and 2 hash match/inner joins of the results (at cost 39% and 37%). If I remove the index hints the query performs a full table scan which due to the size of the table is not ideal. How can I speed up the query and/or indexing to perform a merge or nested loop join? TIA
How many rows does that query produce, and how many rows are in the table?
I'd be interested to see the full DDL of the table, and what other indexes are there?
Just rereading the question, I hadn't realised you where using multiple indexes in the index hint - I've never seen that in the wild! To be honest I don't think that you are going to get a great performance from that query with any index given that huge list of OR clauses - have you thought about precalculating another column (computed or not) that could be used - i.e set a single bit field that is the logical equivalent or all those ORs? The overhead of doing this might be less than the cost of these indexes and this query.
The general rule is to not use index hints at all. The optimizer is very good at choosing a good execution plan, and if you have to resort to index hints then it suggests that maybe an issue with statistics or database design or query design. The optimizer has the ability to change the execution plan over time (as data changes, or even SQL version, etc) - by overriding the optimizer you lose that ability. I'd love to see the execution plan if you could post it here - the xml version of it that is (.sqlplan file)
AHH! SQL 2000 - it's been that long since I used it, I forgot XML was invented after that!! :) Table scanning 4 million rows shouldn't take that long - what locking issues do you get?
The reason for using the index hints is that when running the query with the hints it performs all index scans so doesn't lock the table to others. Without the hints the optimizer wants to do a table scan however when doing this its causing locking issues. Is it possible to generate an XML plan for SQL 2000?
Unfortunately SQL 2000. :/ Basically when the report runs the select query above minus the hints and full table scan is performed and this takes over a minute to complete, during which the application that users use to query the CONTACT_LOG table hangs. By creating the indexes and adding the hint, the report executes without interrupting the application or users.
14 People are following this question.
