SQL Server Performance Forum – Threads Archive

Are Table Scans always avoidable?

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

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.

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
)

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

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.

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

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

]]>