none
Indexes don't work in a query RRS feed

  • Question

  • Hi

    I have the query in which indexes don't work..

    select
    	c.orden_compra NroOrdenCompra,
    	a.tipo TipoDocumento,
    	a.documento NroFactura,
    	a.fecha_documento FechaEmision,
    	a.recorddate FechaRegistro,
    	a.RUBRO5_DOC as FechaPagoCadena,
    	a.proveedor Ruc,
    	p.nombre Proveedor,
    	a.moneda Moneda,
    	a.SUBTOTAL  Monto,
    	a.IMPUESTO1 Impuesto,
    	a.MONTO Total,
    	a.aprobado Estado,
    	'AJPSC' as Empresa
    	from dbo.documentos_cp a
    	inner join dbo.embarque_doc_cp b
    	on a.proveedor = b.proveedor
    	and a.tipo = b.tipo
    	and a.documento = b.documento
    	inner join dbo.embarque_linea c
    	on b.embarque = c.embarque
    	inner join dbo.proveedor p
    	on a.proveedor = p.proveedor

    My plan:

    https://www.brentozar.com/pastetheplan/?id=SJ9znEhOH

    My indexes

    Thursday, October 10, 2019 5:07 AM

Answers

  • Hi

    I solved the problem with dynamic sql.

    I see that Microsoft improves the performance of dynamic sql.

    With dynamic sql the queries are faster since I have little querys in which I don't have evaluate filters

    The logic is that little querys, little execution plan and less time to execution

    My tables only have 20000 rows so scan tables should be fast.

    My querys run in 1 or 2 seconds.

    • Edited by neonash Saturday, October 19, 2019 2:34 PM
    • Marked as answer by neonash Saturday, October 19, 2019 2:34 PM
    Saturday, October 19, 2019 2:33 PM

All replies

  • What would you expect by your indexes, since you don't have a WHERE clause? There's not much that can be done here. SQL does use an index on the embarque_doc_cp table, using it as the inner table in a nested loop join. As for the joins to the other tables, we haven't been provided any information about whatever indexes that exist on those table, hence what indexes *might* have been usedful on those tables to assist the for each join to those tables.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, October 10, 2019 6:46 AM
    Moderator
  • Hi neonash,

    Did you mean that in your table 'DOCUMENTOS_CP' you have index but in execution plan it use 'table scan' ?

    If so , please share us the result of following script.

    exec sp_help 'DOCUMENTOS_CP'

    By the way, in your original query you will SELECT many columns in table 'DOCUMENTOS_CP' and also there is no WHERE clause. I think it will make each table use SCAN. Please check it.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, October 10, 2019 6:51 AM
  • Hi

    All tables have indexes.

    This is the script of indexes..

    USE [at-jockey-prov-con-ajpsc]
    GO
    /****** Object:  Table [dbo].[DOCUMENTOS_CP]    Script Date: 12/10/2019 6:55:07 p. m. ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[DOCUMENTOS_CP](
    	[PROVEEDOR] [varchar](20) NOT NULL,
    	[DOCUMENTO] [varchar](50) NOT NULL,
    	[TIPO] [varchar](3) NOT NULL,
    	[FECHA_DOCUMENTO] [datetime] NOT NULL,
    	[FECHA] [datetime] NOT NULL,
    	[APLICACION] [varchar](249) NOT NULL,
    	[MONTO] [float] NOT NULL,
    	[SALDO] [float] NOT NULL,
    	[MONTO_LOCAL] [float] NOT NULL,
    	[SALDO_LOCAL] [float] NOT NULL,
    	[MONTO_DOLAR] [float] NOT NULL,
    	[SALDO_DOLAR] [float] NOT NULL,
    	[TIPO_CAMBIO_MONEDA] [float] NOT NULL,
    	[TIPO_CAMBIO_DOLAR] [float] NOT NULL,
    	[FECHA_ULT_CREDITO] [datetime] NOT NULL,
    	[CHEQUE_IMPRESO] [varchar](1) NOT NULL,
    	[APROBADO] [varchar](1) NOT NULL,
    	[SELECCIONADO] [varchar](1) NOT NULL,
    	[CONGELADO] [varchar](1) NOT NULL,
    	[MONTO_PROV] [float] NOT NULL,
    	[SALDO_PROV] [float] NOT NULL,
    	[TIPO_CAMBIO_PROV] [float] NOT NULL,
    	[SUBTOTAL] [float] NOT NULL,
    	[DESCUENTO] [float] NOT NULL,
    	[IMPUESTO1] [float] NOT NULL,
    	[IMPUESTO2] [float] NOT NULL,
    	[RUBRO1] [float] NOT NULL,
    	[RUBRO2] [float] NOT NULL,
    	[FECHA_ULT_MOD] [datetime] NOT NULL,
    	[MONTO_RETENCION] [float] NOT NULL,
    	[SALDO_RETENCION] [float] NOT NULL,
    	[DEPENDIENTE] [varchar](1) NOT NULL,
    	[ASIENTO_PENDIENTE] [varchar](1) NOT NULL,
    	[TIPO_CAMB_ACT_LOC] [float] NOT NULL,
    	[TIPO_CAMB_ACT_DOL] [float] NOT NULL,
    	[TIPO_CAMB_ACT_PROV] [float] NOT NULL,
    	[USUARIO_ULT_MOD] [varchar](25) NOT NULL,
    	[CONDICION_PAGO] [varchar](4) NOT NULL,
    	[MONEDA] [varchar](4) NOT NULL,
    	[FECHA_VENCE] [datetime] NOT NULL,
    	[CODIGO_IMPUESTO] [varchar](4) NULL,
    	[RECORDDATE] [datetime] NOT NULL,
    	[RUBRO5_DOC] [varchar](250) NULL,
     CONSTRAINT [DOCUMENTOS_CP_DOCUMENTOS_CPPK] PRIMARY KEY NONCLUSTERED 
    (
    	[PROVEEDOR] ASC,
    	[DOCUMENTO] ASC,
    	[TIPO] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    /****** Object:  Table [dbo].[EMBARQUE_DOC_CP]    Script Date: 12/10/2019 6:55:07 p. m. ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[EMBARQUE_DOC_CP](
    	[PROVEEDOR] [varchar](20) NOT NULL,
    	[DOCUMENTO] [varchar](50) NOT NULL,
    	[TIPO] [varchar](3) NOT NULL,
    	[EMBARQUE] [varchar](10) NOT NULL,
    	[RECORDDATE] [datetime] NOT NULL,
     CONSTRAINT [EMBARQUE_DOC_CP_XPKEMBARQUE_DOC_CP] PRIMARY KEY NONCLUSTERED 
    (
    	[PROVEEDOR] ASC,
    	[DOCUMENTO] ASC,
    	[TIPO] ASC,
    	[EMBARQUE] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    /****** Object:  Table [dbo].[EMBARQUE_LINEA]    Script Date: 12/10/2019 6:55:07 p. m. ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[EMBARQUE_LINEA](
    	[EMBARQUE] [varchar](10) NOT NULL,
    	[EMBARQUE_LINEA] [numeric](22, 0) NOT NULL,
    	[ORDEN_COMPRA] [varchar](10) NULL,
    	[ARTICULO] [varchar](20) NOT NULL,
    	[BODEGA] [varchar](4) NOT NULL,
    	[CANTIDAD_EMBARCADA] [float] NOT NULL,
    	[CANTIDAD_RECIBIDA] [float] NOT NULL,
    	[CANTIDAD_RECHAZADA] [float] NOT NULL,
    	[PRECIO_UNITARIO] [float] NOT NULL,
    	[COST_UN_FISC_LOCAL] [float] NOT NULL,
    	[COST_UN_FISC_DOLAR] [float] NOT NULL,
    	[COST_UN_ESTI_LOCAL] [float] NOT NULL,
    	[COST_UN_ESTI_DOLAR] [float] NOT NULL,
    	[COST_UN_REAL_LOCAL] [float] NOT NULL,
    	[COST_UN_REAL_DOLAR] [float] NOT NULL,
    	[PLAZO_REABAST] [numeric](22, 0) NOT NULL,
    	[PORC_AJUSTE_COSTO] [float] NOT NULL,
    	[EXISTENCIA_TOT_ING] [float] NOT NULL,
    	[RECIBIDO_DE_MAS] [varchar](1) NOT NULL,
    	[MONEDA_OC] [varchar](4) NOT NULL,
     CONSTRAINT [EMBARQUE_LINEA_XPKEMBARQUE_DETALL] PRIMARY KEY NONCLUSTERED 
    (
    	[EMBARQUE] ASC,
    	[EMBARQUE_LINEA] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    /****** Object:  Table [dbo].[PROVEEDOR]    Script Date: 12/10/2019 6:55:07 p. m. ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[PROVEEDOR](
    	[PROVEEDOR] [varchar](20) NOT NULL,
    	[NOMBRE] [varchar](150) NOT NULL,
    	[CONTACTO] [varchar](30) NOT NULL,
    	[CARGO] [varchar](30) NOT NULL,
    	[DIRECCION] [varchar](254) NOT NULL,
    	[FECHA_INGRESO] [datetime] NOT NULL,
    	[FECHA_ULT_MOV] [datetime] NOT NULL,
    	[TELEFONO1] [varchar](50) NOT NULL,
    	[TELEFONO2] [varchar](50) NOT NULL,
    	[FAX] [varchar](50) NOT NULL,
    	[ORDEN_MINIMA] [float] NOT NULL,
    	[DESCUENTO] [float] NOT NULL,
    	[LOCAL] [varchar](1) NOT NULL,
    	[CONGELADO] [varchar](1) NOT NULL,
    	[CONTRIBUYENTE] [varchar](20) NOT NULL,
    	[CONDICION_PAGO] [varchar](4) NOT NULL,
    	[MONEDA] [varchar](4) NOT NULL,
    	[PAIS] [varchar](4) NOT NULL,
    	[CATEGORIA_PROVEED] [varchar](8) NOT NULL,
    	[MULTIMONEDA] [varchar](1) NOT NULL,
    	[SALDO] [float] NOT NULL,
    	[SALDO_LOCAL] [float] NOT NULL,
    	[SALDO_DOLAR] [float] NOT NULL,
    	[ACTIVO] [varchar](1) NOT NULL,
     CONSTRAINT [PROVEEDOR_PROVEEDORPK] PRIMARY KEY NONCLUSTERED 
    (
    	[PROVEEDOR] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[DOCUMENTOS_CP] ADD  DEFAULT ('1753-01-01 00:00:00') FOR [RECORDDATE]
    GO
    ALTER TABLE [dbo].[EMBARQUE_DOC_CP] ADD  DEFAULT ('1753-01-01 00:00:00') FOR [RECORDDATE]
    GO
    ALTER TABLE [dbo].[DOCUMENTOS_CP]  WITH NOCHECK ADD  CONSTRAINT [DOCCPCODIMP] FOREIGN KEY([CODIGO_IMPUESTO])
    REFERENCES [dbo].[IMPUESTO] ([IMPUESTO])
    GO
    ALTER TABLE [dbo].[DOCUMENTOS_CP] NOCHECK CONSTRAINT [DOCCPCODIMP]
    GO
    ALTER TABLE [dbo].[DOCUMENTOS_CP]  WITH NOCHECK ADD  CONSTRAINT [DOCCPMON] FOREIGN KEY([MONEDA])
    REFERENCES [dbo].[MONEDA] ([MONEDA])
    GO
    ALTER TABLE [dbo].[DOCUMENTOS_CP] NOCHECK CONSTRAINT [DOCCPMON]
    GO
    ALTER TABLE [dbo].[DOCUMENTOS_CP]  WITH NOCHECK ADD  CONSTRAINT [DOCCPPRO] FOREIGN KEY([PROVEEDOR])
    REFERENCES [dbo].[PROVEEDOR] ([PROVEEDOR])
    GO
    ALTER TABLE [dbo].[DOCUMENTOS_CP] NOCHECK CONSTRAINT [DOCCPPRO]
    GO
    ALTER TABLE [dbo].[EMBARQUE_DOC_CP]  WITH NOCHECK ADD  CONSTRAINT [EMDOCDCP] FOREIGN KEY([PROVEEDOR], [DOCUMENTO], [TIPO])
    REFERENCES [dbo].[DOCUMENTOS_CP] ([PROVEEDOR], [DOCUMENTO], [TIPO])
    ON DELETE CASCADE
    GO
    ALTER TABLE [dbo].[EMBARQUE_DOC_CP] NOCHECK CONSTRAINT [EMDOCDCP]
    GO
    ALTER TABLE [dbo].[EMBARQUE_LINEA]  WITH NOCHECK ADD  CONSTRAINT [XFKEMBLINMONEDA] FOREIGN KEY([MONEDA_OC])
    REFERENCES [dbo].[MONEDA] ([MONEDA])
    GO
    ALTER TABLE [dbo].[EMBARQUE_LINEA] NOCHECK CONSTRAINT [XFKEMBLINMONEDA]
    GO
    ALTER TABLE [dbo].[PROVEEDOR]  WITH NOCHECK ADD  CONSTRAINT [PROVMON] FOREIGN KEY([MONEDA])
    REFERENCES [dbo].[MONEDA] ([MONEDA])
    GO
    ALTER TABLE [dbo].[PROVEEDOR] NOCHECK CONSTRAINT [PROVMON]
    GO
    

    The result of sp_help:

    

    Saturday, October 12, 2019 11:58 PM
  • The tables are heap type, with primary key using nonclustered index.

    The query optimizer probably found it simpler to read the table sequentially than to use the indexes and aggregate the RID LOOKUP operator to obtain the contents of the columns.

    Note that there are repeated indexes and also redundant indexes.


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    Sunday, October 13, 2019 8:39 PM
  • As José Diz said that in table 'DOCUMENTOS_CP' you have no clustered index. Also , you SELECT many columns in table 'DOCUMENTOS_CP' . So your table is heap table and execution plan will show ‘Table Scan’. Please check it. What do Clustered and Non clustered index actually mean?

    Best Regards,

    Rachel



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 14, 2019 8:28 AM
  • It is almost impossible to read that script code you posted. Not only is there lots of irrelevant parts, you have square brackets, all uppercase, etc. If you want a higher degree of engagement from us, a tip is to set yourself in our positions. All we want to know is what tables and indexes you have. In some readable form. :-)

    Anyhow, You do seem to have indexes on the join columns, but SQL Server prefer to use hash joins and scans instead. SQL Server is pragmatic, so it estimated that the cost for doing that is lower than nested loop joins. Possibly because the tables are heaps, compared to of those indexes were either clustered indexes or non-clustered indexes that includes all columns required from that table in your query. 


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, October 14, 2019 10:41 AM
    Moderator
  • It is almost impossible to read that script code you posted. Not only is there lots of irrelevant parts, you have square brackets, all uppercase, etc.
    Tibor, it seems to me that the scripts for creating the tables were generated by Management Studio.

    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    Monday, October 14, 2019 11:34 AM
  • I understand that. But that doesn't make the code any easier to read... 

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, October 14, 2019 11:39 AM
    Moderator
  • Hi neonash,

    Check the below suggestions-

    dbo.documentos_cp    
    	--> proveedor,tipo,documento
    		 PRIMARY KEY CLUSTERED 
    			([PROVEEDOR] ASC,[DOCUMENTO] ASC,[TIPO] ASC) --(try this since its unique)
    	--> proveedor 
    		 NONCLUSTERED --(already there) 
    
    dbo.embarque_doc_cp 
    	--> proveedor,tipo,documento
    		  PRIMARY KEY CLUSTERED ([PROVEEDOR] ASC,[DOCUMENTO] ASC,[TIPO] ASC,[EMBARQUE] ASC)
    		  --(try this since its unique and not already there)
    	--> embarque
    		NONCLUSTERED --(try this if not already there)
    
    dbo.embarque_linea 
    		PRIMARY KEY CLUSTERED (	[EMBARQUE] ASC,	[EMBARQUE_LINEA] ASC)
    		--(try this since its unique)
    	--> embarque
    		NONCLUSTERED --(try this if not already there)
    
    dbo.proveedor 
    	-->p.proveedor
    		PRIMARY KEY CLUSTERED (	[PROVEEDOR] ASC)
    		--(try this since its unique)

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Monday, October 14, 2019 4:47 PM
  • HI

    The tables have these clustered indexes..

    Wednesday, October 16, 2019 3:52 AM
  • Hi , 

    In this picture, we could know that you have primary key instead of clustered indexes. In following picture, it shows that the primary key is nonclustered indexes.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 16, 2019 9:22 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 17, 2019 9:34 AM
  • Hi

    I understand, the problem is that the primary keys of the tables are nonclustered index.

    But, How can I change a nonclustered index in a clustered index.

    Thursday, October 17, 2019 2:44 PM
  • But, How can I change a nonclustered index in a clustered index.

    -- code #2
    -- removes redundant index
    DROP INDEX idx_Documentos on dbo.DOCUMENTOS_CP;

    -- remove unnecessary index
    DROP INDEX DOCUMENTOS_CP_FKDOCCPPROVE on dbo.DOCUMENTOS_CP;

    -- delete nonclustered primary key and recreate clustered primary key
    ALTER TABLE dbo.DOCUMENTOS_CP
    drop constraint DOCUMENTOS_CP_DOCUMENTOS_CPPK;
    go ALTER TABLE dbo.DOCUMENTOS_CP add constraint DOCUMENTOS_CP_DOCUMENTOS_CPPK PRIMARY KEY CLUSTERED (PROVEEDOR, DOCUMENTO, TIPO);



    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Edited by José Diz Friday, October 18, 2019 12:12 AM
    Thursday, October 17, 2019 3:12 PM
  • Hi ,

    Please check it .

    IF OBJECT_ID('test') IS NOT NULL drop table  test   
    go 
    CREATE table test (id int not null,
    created_by varchar(20) )
    INSERT INTO test VALUES(1,'USER1')
    
     
     alter table test add  primary key nonclustered (id)
     exec sp_help 'test'
     /*
     index_name                       index_description                                        index_keys
    -------------------------------- -------------------------------------------------------- ---------------
    PK__test__3213E83E35CF92FF       nonclustered, unique, primary key located on PRIMARY     id
     */
    
     ----Firstly, please drop primary key nonclustered index 
     ALTER TABLE test DROP CONSTRAINT PK__test__3213E83E35CF92FF
    
    
     ----And then add primary key
     alter table test add constraint PK__test primary key (ID)
    
      exec sp_help 'test'
      /*
      index_name       index_description                                    index_keys
    ---------------- ---------------------------------------------------- -------------------
    PK__test         clustered, unique, primary key located on PRIMARY    id
      */
    

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 18, 2019 8:24 AM
  • Hi

    I solved the problem with dynamic sql.

    I see that Microsoft improves the performance of dynamic sql.

    With dynamic sql the queries are faster since I have little querys in which I don't have evaluate filters

    The logic is that little querys, little execution plan and less time to execution

    My tables only have 20000 rows so scan tables should be fast.

    My querys run in 1 or 2 seconds.

    • Edited by neonash Saturday, October 19, 2019 2:34 PM
    • Marked as answer by neonash Saturday, October 19, 2019 2:34 PM
    Saturday, October 19, 2019 2:33 PM
  • I solved the problem with dynamic sql.

    I see that Microsoft improves the performance of dynamic sql.

    With dynamic sql the queries are faster since I have little querys in which I don't have evaluate filters

    The logic is that little querys, little execution plan and less time to execution

    My tables only have 20000 rows so scan tables should be fast.

    I fail to see that dynamic SQL could help with the query you actually posted. Are you now talking about different queries that you asked about initially? Shame on you in such case, because you have really been wasting people's time.

    It would certainly be a courtsey to the people who tried to help you, if you posted one of the queries you actually problems with.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, October 19, 2019 4:58 PM