Multiple table scans on 'simple' select statement

Multiple table scans on 'simple' select statement

Post 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

 
 
 

Multiple table scans on 'simple' select statement

Post 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


 
 
 

Multiple table scans on 'simple' select statement

Post 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

 
 
 

Multiple table scans on 'simple' select statement

Post 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

 
 
 

1. IF- statements in a rule's 'DO INSTEAD SELECT ...'- statement

Hello, all,

Can I insert IF- statements into a rule's DO INSTEAD- clause ? I have the
problem in a situation like :

create rule vprinsert as on insert to vpr_status
        do instead (DDDDD);

if I insert as DDDDD a genuine SELECT- statement (or, Alvaro, several select
statements), everything is OK; but if I start inserting IF- statements, it's
not OK any more (syntax errors). Don't I just know the exact syntax, or is
this simply impossible ?

Anyone any idea on how to resolve this ?

Regads,

Philippe Bertin.

P.S. To have an idea why I want to insert IF- statements into the rule,
please refer to my newsgroup posting of last Friday ...

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

2. import and update of databases

3. Need Help on alter column

4. Why doesn't simple SELECT statement work?

5. Problems: PDOX 4.0 with Win 98

6. IF- statements in a rule's 'DO INSTEAD SELECT ...'-

7. Help: SQR

8. 'simple' select returns unexpected result

9. multiple 'NOT LIKE' statements

10. Multiple OR's or AND's in SQL Statement

11. Simple, simple SELECT doesn't work

12. simple, simple SELECT doesn't work with me in SQL7