"Uri Dimant" <urid@iscar.co.il> wrote in message
news:e#FSJPPWEHA.3664@TK2MSFTNGP12.phx.gbl...
> N
> What are the columns participate with WHERE condition?
>
>
>
> "N" <raasen@hotmail.com> wrote in message
> news:eL3i$OKWEHA.1760@TK2MSFTNGP10.phx.gbl...
> > Hi
> >
> > I have a table with the structure as shown below. This table consists of
> > about +-10 000 000 rows.
> > I then have a query that goes through most of this table using joins
from
> > other tables on cli_code, grp_code, sub_code, sub_levl, ben_Code,
> portcode,
> > investment_date, bus_run_num
> > Now, my clustered index is a partial match for this condition. Would i
> > benefit from defining a non clustered index on the exact search
condition
> or
> > would a partial match on the clustered index be faster?
> >
> > Many thanks
> > N
> >
> > create table investment_history (
> > cli_code varchar(10),
> > grp_code varchar(5),
> > sub_code smallint
> > sub_levl smallint
> > ben_code smallint
> > portcode smallint,
> > investment_date datetime,
> > cdt_cli_contr decimal(15,2),
> > cdt_emr_contr decimal(15,2),
> > cdt_int_div decimal(15,2),
> > cdt_inv_switch decimal(15,2),
> > cdt_other decimal(15,2),
> > dbt_admin decimal(15,2),
> > dbt_comm decimal(15,2),
> > dbt_underwriting decimal(15,2),
> > dbt_statutory decimal(15,2),
> > dbt_investment decimal(15,2),
> > dbt_other decimal(15,2),
> > dbt_risk_premium decimal(15,2),
> > dbt_inv_claim decimal(15,2),
> > investment_amount decimal(15,2),
> > bus_run_num int,
> > investment_index decimal(15,2),
> > units decimal(15,2),
> > static_index smallint,
> > inv_code varchar,
> > updated_inv_code smallint,
> > TIMESTAMP timestamp,
> > pk_id int,
> > dbt_initial_ut_fee decimal(15,2),
> > dbt_bss_fee decimal(15,2)
> > )
> > and the following indexes
> > CREATE CLUSTERED INDEX [xkeyfields] ON
> > [dbo].[investment_history]([cli_code], [grp_code], [sub_code],
[sub_levl],
> > [ben_code], [portcode], [investment_date]) WITH FILLFACTOR = 90 ON
> > [PRIMARY]
> > GO
> >
> > CREATE INDEX [xbusrunnum] ON [dbo].[investment_history]([bus_run_num])
> ON
> > [PRIMARY]
> > GO
> >
> > CREATE INDEX [XTimeStamp] ON [dbo].[investment_history]([TIMESTAMP])
ON
> > [PRIMARY]
> > GO
> >
> > CREATE INDEX [xunits] ON [dbo].[investment_history]([portcode],
> > [investment_date], [static_index]) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [MissingPrices] ON [dbo].[investment_history]([portcode],
> > [investment_index]) ON [PRIMARY]
> > GO
> >
> >
> >
>
>

"N" <raasen@hotmail.com> wrote in message
news:eL3i$OKWEHA.1760@TK2MSFTNGP10.phx.gbl...
> Hi
>
> I have a table with the structure as shown below. This table consists of
> about +-10 000 000 rows.
> I then have a query that goes through most of this table using joins from
> other tables on cli_code, grp_code, sub_code, sub_levl, ben_Code,
portcode,
> investment_date, bus_run_num
> Now, my clustered index is a partial match for this condition. Would i
> benefit from defining a non clustered index on the exact search condition
or
> would a partial match on the clustered index be faster?
>
> Many thanks
> N
>
> create table investment_history (
> cli_code varchar(10),
> grp_code varchar(5),
> sub_code smallint
> sub_levl smallint
> ben_code smallint
> portcode smallint,
> investment_date datetime,
> cdt_cli_contr decimal(15,2),
> cdt_emr_contr decimal(15,2),
> cdt_int_div decimal(15,2),
> cdt_inv_switch decimal(15,2),
> cdt_other decimal(15,2),
> dbt_admin decimal(15,2),
> dbt_comm decimal(15,2),
> dbt_underwriting decimal(15,2),
> dbt_statutory decimal(15,2),
> dbt_investment decimal(15,2),
> dbt_other decimal(15,2),
> dbt_risk_premium decimal(15,2),
> dbt_inv_claim decimal(15,2),
> investment_amount decimal(15,2),
> bus_run_num int,
> investment_index decimal(15,2),
> units decimal(15,2),
> static_index smallint,
> inv_code varchar,
> updated_inv_code smallint,
> TIMESTAMP timestamp,
> pk_id int,
> dbt_initial_ut_fee decimal(15,2),
> dbt_bss_fee decimal(15,2)
> )
> and the following indexes
> CREATE CLUSTERED INDEX [xkeyfields] ON
> [dbo].[investment_history]([cli_code], [grp_code], [sub_code], [sub_levl],
> [ben_code], [portcode], [investment_date]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
>
> CREATE INDEX [xbusrunnum] ON [dbo].[investment_history]([bus_run_num])
ON
> [PRIMARY]
> GO
>
> CREATE INDEX [XTimeStamp] ON [dbo].[investment_history]([TIMESTAMP]) ON
> [PRIMARY]
> GO
>
> CREATE INDEX [xunits] ON [dbo].[investment_history]([portcode],
> [investment_date], [static_index]) ON [PRIMARY]
> GO
>
> CREATE INDEX [MissingPrices] ON [dbo].[investment_history]([portcode],
> [investment_index]) ON [PRIMARY]
> GO
>
>
>

"N" <raasen@hotmail.com> wrote in message
news:%23ODA9XPWEHA.2340@TK2MSFTNGP09.phx.gbl...
> select attached and ddl for the other table
>
> SELECT
> cth.cli_code, cth.grp_code, cth.sub_code, cth.sub_levl , cth.ben_code,
> cth.portcode, cth.investment_date, cth.bus_run_num, cth.trancode,
> SUM(cth.transaction_amount) AS 'transaction_amount'
> FROM OMOASYSX.dbo.cdt_tran_history cth (NOLOCK)
> GROUP BY
> cth.cli_code, cth.grp_code, cth.sub_code, cth.sub_levl , cth.ben_code,
> cth.portcode, cth.investment_date, cth.bus_run_num, cth.trancode
> )cth ON (wcl.ClientID = cth.cli_code AND mg.grp_code = cth.grp_code)
> INNER JOIN (
> SELECT DISTINCT
> ih.cli_code,
> ih.grp_code,
> ih.sub_code,
> ih.sub_levl,
> ih.ben_code,
> ih.portcode,
> ih.investment_date,
> ih.bus_run_num,
> ih.Investment_index
> FROM OMOASYSX.dbo.investment_history ih (nolock)
> WHERE NOT (ih.cdt_cli_contr = 0
> AND ih.cdt_emr_contr = 0
> AND ih.cdt_int_div = 0
> AND ih.cdt_inv_switch = 0
> AND ih.cdt_other = 0)
> ) ih ON (
> cth.cli_code = ih.cli_code AND
> cth.grp_code = ih.grp_code AND
> cth.sub_code = ih.sub_code AND
> cth.sub_levl = ih.sub_levl AND
> cth.ben_code = ih.ben_code AND
> cth.portcode = ih.portcode AND
> cth.investment_date = ih.investment_date AND
> cth.bus_run_num = ih.bus_run_num
> )
>
> CREATE TABLE [dbo].[cdt_tran_history] (
> [cli_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [grp_code] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [sub_code] [smallint] NOT NULL ,
> [sub_levl] [smallint] NOT NULL ,
> [ben_code] [smallint] NOT NULL ,
> [portcode] [smallint] NOT NULL ,
> [acc_levl] [smallint] NOT NULL ,
> [creator] [smallint] NOT NULL ,
> [auth_1] [smallint] NOT NULL ,
> [auth_2] [smallint] NOT NULL ,
> [business_date] [datetime] NOT NULL ,
> [investment_date] [datetime] NOT NULL ,
> [transaction_amount] [decimal](15, 2) NOT NULL ,
> [trancode] [smallint] NOT NULL ,
> [trandesc] [smallint] NOT NULL ,
> [tax_code] [smallint] NOT NULL ,
> [bank_date] [datetime] NULL ,
> [bank_batch] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [tax_year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [inv_code] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [tax_cert_printed] [datetime] NULL ,
> [bus_run_num] [int] NOT NULL ,
> [creation_date] [datetime] NOT NULL ,
> [TIMESTAMP] [timestamp] NOT NULL ,
> [sale_centre_code] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> ,
> [bank_brch_int_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [bulk_bus_ind] [smallint] NULL ,
> [rf_tax] [decimal](10, 3) NULL ,
> [cmc_levy] [decimal](10, 3) NULL ,
> [withholding_tax] [decimal](10, 3) NULL ,
> [portcode_ma] [smallint] NULL ,
> [pk_id] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE INDEX [portcodeindex] ON [dbo].[cdt_tran_history]([portcode])
WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [xbusrunnum] ON [dbo].[cdt_tran_history]([bus_run_num])
WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [xkey_timestamp] ON [dbo].[cdt_tran_history]([TIMESTAMP])
> WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [xkeyfields] ON [dbo].[cdt_tran_history]([cli_code],
> [grp_code], [sub_code], [sub_levl], [ben_code], [portcode], [acc_levl])
WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [xInternetSelects] ON [dbo].[cdt_tran_history]([trancode])
> WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [x_idx] ON [dbo].[cdt_tran_history]([trancode]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
>
>
>
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message
> news:e#FSJPPWEHA.3664@TK2MSFTNGP12.phx.gbl...
> > N
> > What are the columns participate with WHERE condition?
> >
> >
> >
> > "N" <raasen@hotmail.com> wrote in message
> > news:eL3i$OKWEHA.1760@TK2MSFTNGP10.phx.gbl...
> > > Hi
> > >
> > > I have a table with the structure as shown below. This table consists
of
> > > about +-10 000 000 rows.
> > > I then have a query that goes through most of this table using joins
> from
> > > other tables on cli_code, grp_code, sub_code, sub_levl, ben_Code,
> > portcode,
> > > investment_date, bus_run_num
> > > Now, my clustered index is a partial match for this condition. Would i
> > > benefit from defining a non clustered index on the exact search
> condition
> > or
> > > would a partial match on the clustered index be faster?
> > >
> > > Many thanks
> > > N
> > >
> > > create table investment_history (
> > > cli_code varchar(10),
> > > grp_code varchar(5),
> > > sub_code smallint
> > > sub_levl smallint
> > > ben_code smallint
> > > portcode smallint,
> > > investment_date datetime,
> > > cdt_cli_contr decimal(15,2),
> > > cdt_emr_contr decimal(15,2),
> > > cdt_int_div decimal(15,2),
> > > cdt_inv_switch decimal(15,2),
> > > cdt_other decimal(15,2),
> > > dbt_admin decimal(15,2),
> > > dbt_comm decimal(15,2),
> > > dbt_underwriting decimal(15,2),
> > > dbt_statutory decimal(15,2),
> > > dbt_investment decimal(15,2),
> > > dbt_other decimal(15,2),
> > > dbt_risk_premium decimal(15,2),
> > > dbt_inv_claim decimal(15,2),
> > > investment_amount decimal(15,2),
> > > bus_run_num int,
> > > investment_index decimal(15,2),
> > > units decimal(15,2),
> > > static_index smallint,
> > > inv_code varchar,
> > > updated_inv_code smallint,
> > > TIMESTAMP timestamp,
> > > pk_id int,
> > > dbt_initial_ut_fee decimal(15,2),
> > > dbt_bss_fee decimal(15,2)
> > > )
> > > and the following indexes
> > > CREATE CLUSTERED INDEX [xkeyfields] ON
> > > [dbo].[investment_history]([cli_code], [grp_code], [sub_code],
> [sub_levl],
> > > [ben_code], [portcode], [investment_date]) WITH FILLFACTOR = 90 ON
> > > [PRIMARY]
> > > GO
> > >
> > > CREATE INDEX [xbusrunnum] ON
[dbo].[investment_history]([bus_run_num])
> > ON
> > > [PRIMARY]
> > > GO
> > >
> > > CREATE INDEX [XTimeStamp] ON [dbo].[investment_history]([TIMESTAMP])
> ON
> > > [PRIMARY]
> > > GO
> > >
> > > CREATE INDEX [xunits] ON [dbo].[investment_history]([portcode],
> > > [investment_date], [static_index]) ON [PRIMARY]
> > > GO
> > >
> > > CREATE INDEX [MissingPrices] ON
[dbo].[investment_history]([portcode],
> > > [investment_index]) ON [PRIMARY]
> > > GO
> > >
> > >
> > >
> >
> >
>
>