Very Computer

by **Brett Sheare** » Sat, 23 Mar 2002 08:42:18

Organisations and orders share a pivot table, that shows the organisations
that are aware of the order.

The following select statement results in table scans for Organisations and
OrgLink

select code, textname from organisations where pk_organisations in
(select fk_organisations from orglink where orglinktype = 'ESP' and
fk_orders in
(select pk_orders from orders where ordnumber like '%"%'))

Is there any way to make the query use index scans?
I have added the BrettTmp directory which gets rid of one table scan (on
Orders)
(I have chopped out the irelevant parts from the script)

CREATE TABLE [dbo].[Organisations] (
[PK_Organisations] [PRIMEKEY] NOT NULL ,
[OR_TSTAMP] [timestamp] NOT NULL ,
[espcode] [char] (8) NOT NULL ,
[code] [char] (10) NOT NULL ,
[cocode] [varchar] (20) NOT NULL ,
[textname] [varchar] (35) NOT NULL ,
) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [UniqueKey] ON [dbo].[Organisations]([espcode],
[code]) ON [PRIMARY]
GO

CREATE INDEX [ESPCode] ON [dbo].[Organisations]([espcode]) ON [PRIMARY]
GO

CREATE INDEX [PK_OrganisationsESPCode] ON
[dbo].[Organisations]([PK_Organisations], [espcode]) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Orders] (
[PK_Orders] [PRIMEKEY] NOT NULL ,
[OR_TSTAMP] [timestamp] NOT NULL ,
[ordnumber] [varchar] (20) NOT NULL ,
) ON [PRIMARY]
GO

CREATE INDEX [Shipment] ON [dbo].[Orders]([FK_Shipment]) ON [PRIMARY]
GO

CREATE INDEX [OrdNumber] ON [dbo].[Orders]([ordnumber]) ON [PRIMARY]
GO

CREATE INDEX [ATA] ON [dbo].[Orders]([ata]) ON [PRIMARY]
GO

CREATE INDEX [BrettTemp] ON [dbo].[Orders]([PK_Orders], [ordnumber]) ON
[PRIMARY]
GO

CREATE TABLE [dbo].[OrgLink] (
[PK_OrgLink] [PRIMEKEY] NOT NULL ,
[OL_TSTAMP] [timestamp] NOT NULL ,
[FK_Organisations] [FOREIGN_KEY] NULL ,
[OrgLinkType] [char] (3) NOT NULL ,
[FK_Shipment] [FOREIGN_KEY] NULL ,
[FK_Orders] [FOREIGN_KEY] NULL
) ON [PRIMARY]
GO

CREATE INDEX [Organisations] ON [dbo].[OrgLink]([FK_Organisations]) ON
[PRIMARY]
GO

CREATE INDEX [Shipment] ON [dbo].[OrgLink]([FK_Shipment]) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [UniqueKey] ON [dbo].[OrgLink]([FK_Organisations],
[OrgLinkType], [FK_Shipment], [FK_Orders]) ON [PRIMARY]
GO

CREATE INDEX [Orders] ON [dbo].[OrgLink]([FK_Orders]) ON [PRIMARY]
GO

by **Greg Linwoo** » Sat, 23 Mar 2002 09:12:03

Hi Brett..

The statement '%....' is not SARGable, meaning that it's not useful to any
index. Think about it - it's like saying give me any rows that <might> start
with anything. Therefore, ordered indexes are of no use. You need to be
careful when designing models where search criteria will be need left handed
wildcards like this.

btw - Table scans only appear when you don't have a clustered index in SQL
Server. I checked your code and you're not explicitly setting any indexes to
clustered, so I suggest that you change the DDL to set clustered /
nonclustered explicitly. You should also set PRIMARY KEY explicitly too..

HTH

Cheers,
Greg Linwood

Quote:> Organisations and orders share a pivot table, that shows the organisations
> that are aware of the order.

> The following select statement results in table scans for Organisations
and
> OrgLink

> select code, textname from organisations where pk_organisations in
> (select fk_organisations from orglink where orglinktype = 'ESP' and
> fk_orders in
> (select pk_orders from orders where ordnumber like '%"%'))

> Is there any way to make the query use index scans?
> I have added the BrettTmp directory which gets rid of one table scan (on
> Orders)
> (I have chopped out the irelevant parts from the script)

> CREATE TABLE [dbo].[Organisations] (
> [PK_Organisations] [PRIMEKEY] NOT NULL ,
> [OR_TSTAMP] [timestamp] NOT NULL ,
> [espcode] [char] (8) NOT NULL ,
> [code] [char] (10) NOT NULL ,
> [cocode] [varchar] (20) NOT NULL ,
> [textname] [varchar] (35) NOT NULL ,
> ) ON [PRIMARY]
> GO

> CREATE UNIQUE INDEX [UniqueKey] ON [dbo].[Organisations]([espcode],
> [code]) ON [PRIMARY]
> GO

> CREATE INDEX [ESPCode] ON [dbo].[Organisations]([espcode]) ON [PRIMARY]
> GO

> CREATE INDEX [PK_OrganisationsESPCode] ON
> [dbo].[Organisations]([PK_Organisations], [espcode]) ON [PRIMARY]
> GO

> CREATE TABLE [dbo].[Orders] (
> [PK_Orders] [PRIMEKEY] NOT NULL ,
> [OR_TSTAMP] [timestamp] NOT NULL ,
> [ordnumber] [varchar] (20) NOT NULL ,
> ) ON [PRIMARY]
> GO

> CREATE INDEX [Shipment] ON [dbo].[Orders]([FK_Shipment]) ON [PRIMARY]
> GO

> CREATE INDEX [OrdNumber] ON [dbo].[Orders]([ordnumber]) ON [PRIMARY]
> GO

> CREATE INDEX [ATA] ON [dbo].[Orders]([ata]) ON [PRIMARY]
> GO

> CREATE INDEX [BrettTemp] ON [dbo].[Orders]([PK_Orders], [ordnumber]) ON
> [PRIMARY]
> GO

> CREATE TABLE [dbo].[OrgLink] (
> [PK_OrgLink] [PRIMEKEY] NOT NULL ,
> [OL_TSTAMP] [timestamp] NOT NULL ,
> [FK_Organisations] [FOREIGN_KEY] NULL ,
> [OrgLinkType] [char] (3) NOT NULL ,
> [FK_Shipment] [FOREIGN_KEY] NULL ,
> [FK_Orders] [FOREIGN_KEY] NULL
> ) ON [PRIMARY]
> GO

> CREATE INDEX [Organisations] ON [dbo].[OrgLink]([FK_Organisations]) ON
> [PRIMARY]
> GO

> CREATE INDEX [Shipment] ON [dbo].[OrgLink]([FK_Shipment]) ON [PRIMARY]
> GO

> CREATE UNIQUE INDEX [UniqueKey] ON [dbo].[OrgLink]([FK_Organisations],
> [OrgLinkType], [FK_Shipment], [FK_Orders]) ON [PRIMARY]
> GO

> CREATE INDEX [Orders] ON [dbo].[OrgLink]([FK_Orders]) ON [PRIMARY]
> GO

by **Brett Sheare** » Sat, 23 Mar 2002 09:26:47

Thanks for the tips.

I understand that the like '%"%' will result in a scan.
I actually made a temp index to test, using OrdNumber + PK_ORDERS, and this
resulted in an index scan, instead of a table scan, which is obviously
better, but has the performance hit on insert, update of OrdNumber.

I'll implmenent your advice and check the results.

Thanks again,
Brett

> Hi Brett..

> The statement '%....' is not SARGable, meaning that it's not useful to any
> index. Think about it - it's like saying give me any rows that <might>
start
> with anything. Therefore, ordered indexes are of no use. You need to be
> careful when designing models where search criteria will be need left
handed
> wildcards like this.

> btw - Table scans only appear when you don't have a clustered index in SQL
> Server. I checked your code and you're not explicitly setting any indexes
to
> clustered, so I suggest that you change the DDL to set clustered /
> nonclustered explicitly. You should also set PRIMARY KEY explicitly too..

> HTH

> Cheers,
> Greg Linwood

> > Organisations and orders share a pivot table, that shows the
organisations
> > that are aware of the order.

> > The following select statement results in table scans for Organisations
> and
> > OrgLink

> > select code, textname from organisations where pk_organisations in
> > (select fk_organisations from orglink where orglinktype = 'ESP' and
> > fk_orders in
> > (select pk_orders from orders where ordnumber like '%"%'))

> > Is there any way to make the query use index scans?
> > I have added the BrettTmp directory which gets rid of one table scan (on
> > Orders)
> > (I have chopped out the irelevant parts from the script)

> > CREATE TABLE [dbo].[Organisations] (
> > [PK_Organisations] [PRIMEKEY] NOT NULL ,
> > [OR_TSTAMP] [timestamp] NOT NULL ,
> > [espcode] [char] (8) NOT NULL ,
> > [code] [char] (10) NOT NULL ,
> > [cocode] [varchar] (20) NOT NULL ,
> > [textname] [varchar] (35) NOT NULL ,
> > ) ON [PRIMARY]
> > GO

> > CREATE UNIQUE INDEX [UniqueKey] ON [dbo].[Organisations]([espcode],
> > [code]) ON [PRIMARY]
> > GO

> > CREATE INDEX [ESPCode] ON [dbo].[Organisations]([espcode]) ON
[PRIMARY]
> > GO

> > CREATE INDEX [PK_OrganisationsESPCode] ON
> > [dbo].[Organisations]([PK_Organisations], [espcode]) ON [PRIMARY]
> > GO

> > CREATE TABLE [dbo].[Orders] (
> > [PK_Orders] [PRIMEKEY] NOT NULL ,
> > [OR_TSTAMP] [timestamp] NOT NULL ,
> > [ordnumber] [varchar] (20) NOT NULL ,
> > ) ON [PRIMARY]
> > GO

> > CREATE INDEX [Shipment] ON [dbo].[Orders]([FK_Shipment]) ON [PRIMARY]
> > GO

> > CREATE INDEX [OrdNumber] ON [dbo].[Orders]([ordnumber]) ON [PRIMARY]
> > GO

> > CREATE INDEX [ATA] ON [dbo].[Orders]([ata]) ON [PRIMARY]
> > GO

> > CREATE INDEX [BrettTemp] ON [dbo].[Orders]([PK_Orders], [ordnumber])
ON
> > [PRIMARY]
> > GO

> > CREATE TABLE [dbo].[OrgLink] (
> > [PK_OrgLink] [PRIMEKEY] NOT NULL ,
> > [OL_TSTAMP] [timestamp] NOT NULL ,
> > [FK_Organisations] [FOREIGN_KEY] NULL ,
> > [OrgLinkType] [char] (3) NOT NULL ,
> > [FK_Shipment] [FOREIGN_KEY] NULL ,
> > [FK_Orders] [FOREIGN_KEY] NULL
> > ) ON [PRIMARY]
> > GO

> > CREATE INDEX [Organisations] ON [dbo].[OrgLink]([FK_Organisations]) ON
> > [PRIMARY]
> > GO

> > CREATE INDEX [Shipment] ON [dbo].[OrgLink]([FK_Shipment]) ON [PRIMARY]
> > GO

> > CREATE UNIQUE INDEX [UniqueKey] ON

[dbo].[OrgLink]([FK_Organisations],

- Show quoted text -

Quote:> > [OrgLinkType], [FK_Shipment], [FK_Orders]) ON [PRIMARY]
> > GO

> > CREATE INDEX [Orders] ON [dbo].[OrgLink]([FK_Orders]) ON [PRIMARY]
> > GO

by **Brett Sheare** » Sat, 23 Mar 2002 09:40:06

Sorry, forgot to click the script option 'Script PRIMARY keys, FOREIGN keys,
defaults, and check constraint'
All tables have primary keys, and foreign constraints etc.

See below

CREATE TABLE [dbo].[Orders] (
[PK_Orders] [PRIMEKEY] NOT NULL ,
[OR_TSTAMP] [timestamp] NOT NULL ,
[ordnumber] [varchar] (20) NOT NULL ,
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD
CONSTRAINT [DF__TEMP_Orde__PK_Or__4C413C06] DEFAULT (newid()) FOR
[PK_Orders],
CONSTRAINT [PK_Orders] PRIMARY KEY NONCLUSTERED
(
[PK_Orders]
) ON [PRIMARY]
GO

CREATE INDEX [Shipment] ON [dbo].[Orders]([FK_Shipment]) ON [PRIMARY]
GO

CREATE INDEX [OrdNumber] ON [dbo].[Orders]([ordnumber]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Orders] ADD
CONSTRAINT [Relation_207] FOREIGN KEY
(
[FK_Shipment]
) REFERENCES [dbo].[Shipment] (
[PK_Shipment]
)
GO

> Thanks for the tips.

> I understand that the like '%"%' will result in a scan.
> I actually made a temp index to test, using OrdNumber + PK_ORDERS, and
this
> resulted in an index scan, instead of a table scan, which is obviously
> better, but has the performance hit on insert, update of OrdNumber.

> I'll implmenent your advice and check the results.

> Thanks again,
> Brett

> > Hi Brett..

> > The statement '%....' is not SARGable, meaning that it's not useful to
any
> > index. Think about it - it's like saying give me any rows that <might>
> start
> > with anything. Therefore, ordered indexes are of no use. You need to be
> > careful when designing models where search criteria will be need left
> handed
> > wildcards like this.

> > btw - Table scans only appear when you don't have a clustered index in
SQL
> > Server. I checked your code and you're not explicitly setting any
indexes
> to
> > clustered, so I suggest that you change the DDL to set clustered /
> > nonclustered explicitly. You should also set PRIMARY KEY explicitly
too..

> > HTH

> > Cheers,
> > Greg Linwood

> > > Organisations and orders share a pivot table, that shows the
> organisations
> > > that are aware of the order.

> > > The following select statement results in table scans for
Organisations
> > and
> > > OrgLink

> > > select code, textname from organisations where pk_organisations in
> > > (select fk_organisations from orglink where orglinktype = 'ESP' and
> > > fk_orders in
> > > (select pk_orders from orders where ordnumber like '%"%'))

> > > Is there any way to make the query use index scans?
> > > I have added the BrettTmp directory which gets rid of one table scan
(on
> > > Orders)
> > > (I have chopped out the irelevant parts from the script)

> > > CREATE TABLE [dbo].[Organisations] (
> > > [PK_Organisations] [PRIMEKEY] NOT NULL ,
> > > [OR_TSTAMP] [timestamp] NOT NULL ,
> > > [espcode] [char] (8) NOT NULL ,
> > > [code] [char] (10) NOT NULL ,
> > > [cocode] [varchar] (20) NOT NULL ,
> > > [textname] [varchar] (35) NOT NULL ,
> > > ) ON [PRIMARY]
> > > GO

> > > CREATE UNIQUE INDEX [UniqueKey] ON [dbo].[Organisations]([espcode],
> > > [code]) ON [PRIMARY]
> > > GO

> > > CREATE INDEX [ESPCode] ON [dbo].[Organisations]([espcode]) ON
> [PRIMARY]
> > > GO

> > > CREATE INDEX [PK_OrganisationsESPCode] ON
> > > [dbo].[Organisations]([PK_Organisations], [espcode]) ON [PRIMARY]
> > > GO

> > > CREATE TABLE [dbo].[Orders] (
> > > [PK_Orders] [PRIMEKEY] NOT NULL ,
> > > [OR_TSTAMP] [timestamp] NOT NULL ,
> > > [ordnumber] [varchar] (20) NOT NULL ,
> > > ) ON [PRIMARY]
> > > GO

> > > CREATE INDEX [Shipment] ON [dbo].[Orders]([FK_Shipment]) ON
[PRIMARY]
> > > GO

> > > CREATE INDEX [OrdNumber] ON [dbo].[Orders]([ordnumber]) ON [PRIMARY]
> > > GO

> > > CREATE INDEX [ATA] ON [dbo].[Orders]([ata]) ON [PRIMARY]
> > > GO

> > > CREATE INDEX [BrettTemp] ON [dbo].[Orders]([PK_Orders], [ordnumber])
> ON
> > > [PRIMARY]
> > > GO

> > > CREATE TABLE [dbo].[OrgLink] (
> > > [PK_OrgLink] [PRIMEKEY] NOT NULL ,
> > > [OL_TSTAMP] [timestamp] NOT NULL ,
> > > [FK_Organisations] [FOREIGN_KEY] NULL ,
> > > [OrgLinkType] [char] (3) NOT NULL ,
> > > [FK_Shipment] [FOREIGN_KEY] NULL ,
> > > [FK_Orders] [FOREIGN_KEY] NULL
> > > ) ON [PRIMARY]
> > > GO

> > > CREATE INDEX [Organisations] ON [dbo].[OrgLink]([FK_Organisations])
ON
> > > [PRIMARY]
> > > GO

> > > CREATE INDEX [Shipment] ON [dbo].[OrgLink]([FK_Shipment]) ON
[PRIMARY]
> > > GO

> > > CREATE UNIQUE INDEX [UniqueKey] ON
> [dbo].[OrgLink]([FK_Organisations],
> > > [OrgLinkType], [FK_Shipment], [FK_Orders]) ON [PRIMARY]
> > > GO

> > > CREATE INDEX [Orders] ON [dbo].[OrgLink]([FK_Orders]) ON [PRIMARY]
> > > GO

Very Computer

Multiple table scans on 'simple' select statement

Multiple table scans on 'simple' select statement

Multiple table scans on 'simple' select statement

Multiple table scans on 'simple' select statement

Multiple table scans on 'simple' select statement