StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Without knowing the size of the tables and the sort of data stored in it them it is difficult to gauge.</p> <p>Assuming optin_channel_1 has a lot of data and profile_cons has a lot of data I would try the following:</p> <ul> <li>Clustered index on optin_channel_1(key_id) or key_type_id depending on which field has the most distinct values. (since you don't have a covering index)</li> <li>Clustered index on profile_conns (cons_key_id) or cons_key_type_id depending on what you have chosen in optin_channel_1</li> <li>etc...</li> </ul> <p>Basically, if your table profile_conns table has not much data, I would put the clustered index on the most fragmented "filter" field (I suspect profile_key_id). If the table has a lot of data I would aim for a hash/merge join and match the clustered index with the clustered index of the optin_channel_1 table.</p> <p>I would also rewrite the query as such:</p> <pre><code>SELECT @ret = MAX(o.optin_flag) AS optin_flag FROM optin_channel_1 o JOIN dbo.v_source_proc_id_by_group_id y ON o.source_proc_id = y.source_proc_id JOIN profile_conns z ON z.conn_key_type_id = o.key_type_id AND z.conn_key_id = o.[key_id] JOIN lu_channel_conns x ON z.conn_type_id = x.conn_type_id JOIN lu_conn_type ct ON ct.conn_type_id = x.conn_type_id AND ct.default_key_type_id=o.key_type_id WHERE y.source_proc_group_id = 5 AND z.profile_key_id = 1 AND x.channel_id = 1 AND z.valid_to = '01.01.3000' </code></pre> <p>The query changed this way because:</p> <ul> <li>Putting the filter conditions in the where clause shows you what are relevant fields to aim for a hash/merge join</li> <li>Putting join hints is rarely a good idea. It is very hard to beat the query governor to determine the best query plan. A bad plan usually indicates you have an issue with your indexes/statistics. </li> </ul> <p>So as summary:</p> <ul> <li>small table joined to big table ==> go for nested loops & focus your clustered index on the "filter" field in the small table & the join field in the big table. </li> <li>big table joined to big table => go for hash/merge join and put the clustered index on the matching field on both sides</li> <li>multi-field indexes usually only a good idea when they are "covering", this means all the fields you query are included in the index. (or are included with the include() clause)</li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload