StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOptimizing CLUSTERED INDEX for use with JOIN
text
Body
copied!<p>table <code>optin_channel_1</code> (for each 'channel' there's a dedicated table)</p> <pre><code>CREATE TABLE [dbo].[optin_channel_1]( [key_id] [bigint] NOT NULL, [valid_to] [datetime] NOT NULL, [valid_from] [datetime] NOT NULL, [key_type_id] [int] NOT NULL, [optin_flag] [tinyint] NOT NULL, [source_proc_id] [int] NOT NULL, [date_inserted] [datetime] NOT NULL ) ON [PRIMARY] CREATE CLUSTERED INDEX [ix_id] ON [dbo].[optin_channel_1] ( [key_type_id] ASC, [key_id] ASC, [valid_to] ASC, [valid_from] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] </code></pre> <p>table <code>profile_conns</code></p> <pre><code>CREATE TABLE [dbo].[profile_conns]( [profile_key_id] [bigint] NOT NULL, [valid_to] [datetime] NOT NULL, [valid_from] [datetime] NOT NULL, [conn_key_id] [bigint] NOT NULL, [conn_key_type_id] [int] NOT NULL, [conn_type_id] [int] NOT NULL, [source_proc_id] [int] NOT NULL, [date_inserted] [datetime] NOT NULL ) ON [PRIMARY] CREATE CLUSTERED INDEX [ix_id] ON [dbo].[profile_conns] ( [profile_key_id] ASC, [conn_key_type_id] ASC, [conn_key_id] ASC, [valid_to] ASC, [valid_from] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] </code></pre> <p>table <code>lu_channel_conns</code></p> <pre><code>CREATE TABLE [dbo].[lu_channel_conns]( [channel_id] [int] NOT NULL, [conn_type_id] [int] NOT NULL, CONSTRAINT [PK_lu_channel_conns] PRIMARY KEY CLUSTERED ( [channel_id] ASC, [conn_type_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] </code></pre> <p>table <code>lu_conn_type</code></p> <pre><code>CREATE TABLE [dbo].[lu_conn_type]( [conn_type_id] [int] NOT NULL, [default_key_type_id] [int] NOT NULL, [master_key_type_id] [int] NOT NULL, [date_inserted] [datetime] NOT NULL, CONSTRAINT [PK_lu_conns] PRIMARY KEY CLUSTERED ( [conn_type_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] </code></pre> <p>view <code>v_source_proc_id_by_group_id</code></p> <pre><code>SELECT DISTINCT x.source_proc_id, x.source_proc_group_id FROM lu_source_proc x INNER JOIN lu_source_proc_group y ON x.source_proc_group_id = y.group_id </code></pre> <p>There's a dynamic SQL statement going to be executed:</p> <pre><code>SET @sql_str='SELECT @ret=MAX(o.optin_flag) FROM optin_channel_'+CAST(@channel_id AS NVARCHAR(100))+' o INNER HASH JOIN dbo.v_source_proc_id_by_group_id y ON o.source_proc_id=y.source_proc_id AND y.source_proc_group_id=@source_proc_group_id INNER HASH JOIN profile_conns z ON z.profile_key_id=cast(@profile_key_id AS NVARCHAR(100)) AND z.conn_key_type_id=o.key_type_id AND z.conn_key_id=o.[key_id] AND z.valid_to=''01.01.3000'' INNER HASH JOIN lu_channel_conns x ON x.channel_id=@channel_id AND z.conn_type_id=x.conn_type_id INNER HASH JOIN lu_conn_type ct ON ct.conn_type_id=x.conn_type_id AND ct.default_key_type_id=o.key_type_id' SET @param='@channel_id INT, @profile_key_id INT, @source_proc_group_id INT, @ret NVARCHAR(400) OUTPUT' EXEC sp_executesql @sql_str,@param,@channel_id,@profile_key_id,@source_proc_group_id,@ret OUTPUT </code></pre> <p>I.e. this gives:</p> <pre><code>SELECT @ret=MAX(o.optin_flag) AS optin_flag FROM optin_channel_1 o INNER HASH JOIN dbo.v_source_proc_id_by_group_id y ON o.source_proc_id=y.source_proc_id AND y.source_proc_group_id=5 INNER HASH JOIN profile_conns z ON z.profile_key_id=1 AND z.conn_key_type_id=o.key_type_id AND z.conn_key_id=o.[key_id] AND z.valid_to='01.01.3000' INNER HASH JOIN lu_channel_conns x ON x.channel_id=1 AND z.conn_type_id=x.conn_type_id INNER HASH JOIN lu_conn_type ct ON ct.conn_type_id=x.conn_type_id AND ct.default_key_type_id=o.key_type_id </code></pre> <p>These tables are used for an optin database. <code>optin_flag</code> could be 0 or 1. With the last statement I want to get a 1 as <code>optin_flag</code> from <code>optin_channel_1</code> for the given <code>channel_id=1</code> for user with <code>profile_key_id=1</code>, when optin was inserted into database by process belonging to <code>source_proc_group_id=5</code>. I hope this is enough to comprehend what's going on.</p> <p>Is this the best way to use the <code>CLUSTERED INDEX</code>'es? Or would it be better to remove <code>profile_key_id</code> from index on <code>profile_conns</code> and put <code>z.profile_key_id=1</code> in a <code>WHERE</code> clause?</p> <p>May be there's a much better way for optimizing this select (changes in database schema is not possible, only changes on indexes and modifing statement).</p>

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