SQL Server Performance

Are Table Scans always avoidable?

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by EMoscosoCam, May 22, 2007.

  1. EMoscosoCam Member

    Hello

    I believe that I have created the necessary indexes in order to avoid a Table Scan in the following query; however, a table scan is still performed. Can this be avoided?

    Thanks a lot.

    A Table scan is performed through the table VENTAS_M_SALHIS, and it has indexes on Prod_ID, Doc_Tipo and (Año, Mes):


    select
    Prod_ID,
    Año,
    Mes,
    cast(Año + '-' + Mes + '-01' as datetime) as Fecha,
    (sum(dolares)/sum(cantidad)) as PrecioUnitario
    From VENTAS_DB..VENTAS_M_SALHIS as Ventas
    inner join
    (
    select distinct
    Cod_Producto
    from DPO_H_VentasMensuales
    where Fec_UltimaActualizacion>cast('2007-04-01' as datetime)
    ) as T on T.Cod_Producto=Ventas.Prod_ID
    where
    doc_tipo = '1' and
    Año>(year(GetDate())-1)
    group by prod_ID, Año, Mes
    having sum(cantidad)>0


  2. condeba92 New Member

    Hi!, can you give to me both tables structures and indexes structures too ?
    Because, i have been trying to probe what you say and i did not table scan.

  3. EMoscosoCam Member


    Thanks for your reply. Here is a script that creates the tables:





    CREATE TABLE [dbo].[VENTAS_M_SALHIS](
    [AÑO] [char](4) NULL,
    [MES] [char](2) NULL,
    [DIA] [char](2) NULL,
    [NORDEN] [char](7) NULL,
    [DOC_TIPO] [char](1) NULL,
    [DOC_NUMERO] [char](7) NULL,
    [MONEDA] [char](2) NULL,
    [CUST_ID] [char](6) NULL,
    [CMTY_ID] [char](4) NULL,
    [PROD_ID] [char](10) NULL,
    [CANTIDAD] [decimal](9, 0) NULL,
    [DOLARES] [decimal](9, 2) NULL,
    [SOLES] [decimal](11, 2) NULL,
    [DOL_ORIG] [decimal](9, 2) NULL,
    [SOL_ORIG] [decimal](11, 2) NULL,
    [DIVISION] [char](1) NULL,
    [DOL_IGV] [decimal](9, 2) NULL,
    [SOL_IGV] [decimal](11, 2) NULL,
    [IGV] [char](2) NULL,
    [NLINEA] [decimal](3, 0) NULL,
    [CORR] [decimal](18, 0) IDENTITY(1,1) NOT NULL
    )

    go

    CREATE NONCLUSTERED INDEX [IDX_AñoMes] ON [dbo].[VENTAS_M_SALHIS]
    (
    [AÑO] ASC,
    [MES] ASC
    )
    GO

    CREATE NONCLUSTERED INDEX [IDX_DocTipo] ON [dbo].[VENTAS_M_SALHIS]
    (
    [DOC_TIPO] ASC
    )
    GO

    CREATE NONCLUSTERED INDEX [IDX_ProdId] ON [dbo].[VENTAS_M_SALHIS]
    (
    [PROD_ID] ASC
    )

    GO

    CREATE TABLE [dbo].[DPO_H_VentasMensuales](
    [Cod_Producto] [char](10) NOT NULL,
    [Num_Ano] [char](4) NOT NULL,
    [Num_Mes] [char](2) NOT NULL,
    [Imp_VentasUM] [money] NULL,
    [Qty_VentasReales] [numeric](18, 0) NULL,
    [Qty_VentasPronosticadas] [numeric](18, 0) NULL,
    [Qty_AdicionalMarketero] [numeric](18, 0) NULL,
    [Des_Nota] [varchar](8000) NULL,
    [Fec_Registro] [datetime] NOT NULL,
    [Fec_UltimaActualizacion] [datetime] NULL,
    [Des_Workstation] [varchar](250) NULL,
    [Des_UsuarioWindows] [varchar](250) NULL,
    CONSTRAINT [PK_DPO_H_VentasMensuales] PRIMARY KEY CLUSTERED
    (
    [Cod_Producto] ASC,
    [Num_Ano] ASC,
    [Num_Mes] ASC
    )
    )


    GO

    CREATE NONCLUSTERED INDEX [IDX_DPO_FechaActualizacion] ON [dbo].[DPO_H_VentasMensuales]
    (
    [Fec_UltimaActualizacion] ASC
    )
    GO

    CREATE UNIQUE NONCLUSTERED INDEX [IDX_DPO_VentasMensuales] ON [dbo].[DPO_H_VentasMensuales]
    (
    [Cod_Producto] ASC,
    [Fec_Registro] ASC
    )

  4. FrankKalis Moderator

    How many rows are in the tables?
    How many matching rows are to be returned?

    Btw, a scan is not evil in and of itself. Consider the following examples:


    SELECT *
    FROM Northwind.db:confused:rders

    SELECT *
    FROM Northwind.db:confused:rders
    WHERE OrderID > 0

    The work done is the same in both queries. However the first shows a scan, while the second shows a seek. Probably an extreme example, but still...

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  5. condeba92 New Member

    It's a good example by FrankKalis and perhaps it's the solution.
    It's true what you say, when you make a group by for VENTAS_M_SALHIS table, the query analizer show me a scan table on this table.
    I have been seeing the posibility to change query called 'T' into a temporal table, this way the I/O cost of both of tables in the query plan will be improved.
    Then, you can just drop temporal table.
  6. joechang New Member

    there are probably too many rows involved for the indexes you created
    ie, to use the index intersection technique

    try the compound index

    CREATE NONCLUSTERED INDEX [IDX_ProdId] ON [dbo].[VENTAS_M_SALHIS] ([PROD_ID] ASC, [ANO], [MES], [DOC_TIPO], [DOLARES], [CANTIDAD])


    you should also understand that the decimal type is horribly expensive
    learn to use int, bigint, and money over decimal where ever possible
  7. andrians2009 New Member

    i will try to answer your question, why sql server do scan table in your query because your query don't use index that you have already defined,your query in "where" clause don't use index, try to create index where first index column use in "where" clause,in your case u must create index where doc_tipo is the first index column

Share This Page