none
optimizar consulta RRS feed

  • Pregunta

  • En una bd, tengo una tabla con 5500000 registros, y una consulta que se repite contantemente, un ejemplo de la consulta es:

     

    Select isnull (sum(value),0)
     from valuationdetail
    where begtime >='1/1/2012 12:00:00 AM'
    and endtime <='1/7/2012 12:00:00 AM'
    and valuation = '120998'
    and trade ='126984'
    and counterparty = 'Sequent Energy'
    and position IN (170386)
    and quantitytype <> 'LOSS'
    and credittype IS NOT NULL

     

    Y la estructura de la tabla, os paso el script:

     

    CREATE TABLE [dbo].[valuationdetail](
        [valuationdetail] [decimal](16, 0) NOT NULL,
        [valuation] [varchar](32) NOT NULL,
        [position] [varchar](8) NOT NULL,
        [valuationperiod] [varchar](32) NULL,
        [valuationexposure] [varchar](32) NULL,
        [valuationproduct] [varchar](32) NULL,
        [quantitystatus] [varchar](8) NULL,
        [description] [varchar](1024) NULL,
        [quantitytype] [varchar](8) NULL DEFAULT ('RECEIPT'),
        [positionstatus] [varchar](16) NULL,
        [settlementstatus] [bit] NULL,
        [evergreenstatus] [varchar](16) NULL,
        [pricedate] [datetime] NULL,
        [quantity] [decimal](24, 6) NULL DEFAULT ((0)),
        [pricequantity] [decimal](24, 6) NULL DEFAULT ((0)),
        [exposurequantity] [decimal](24, 6) NULL DEFAULT ((0)),
        [unit] [varchar](8) NULL,
        [value] [decimal](24, 6) NULL DEFAULT ((0)),
        [price] [decimal](38, 6) NULL,
        [marketvalue] [decimal](24, 6) NULL DEFAULT ((0)),
        [marketprice] [decimal](16, 6) NULL,
        [currency] [varchar](8) NULL,
        [priceunit] [varchar](8) NULL,
        [npvfactor] [decimal](16, 8) NULL DEFAULT ((0)),
        [validation] [varchar](1024) NULL,
        [strikeprice] [decimal](16, 6) NULL DEFAULT ((0)),
        [optrefprice] [decimal](16, 6) NULL DEFAULT ((0)),
        [intrate] [decimal](16, 6) NULL DEFAULT ((0)),
        [optionvalue] [decimal](24, 6) NULL DEFAULT ((0)),
        [delta] [decimal](16, 6) NULL DEFAULT ((0)),
        [gamma] [decimal](16, 6) NULL DEFAULT ((0)),
        [theta] [decimal](16, 6) NULL DEFAULT ((0)),
        [vega] [decimal](16, 6) NULL DEFAULT ((0)),
        [rho] [decimal](16, 6) NULL DEFAULT ((0)),
        [volatility] [decimal](16, 6) NULL DEFAULT ((0)),
        [promptvolatility] [decimal](16, 6) NULL DEFAULT ((0)),
        [var] [decimal](24, 2) NULL DEFAULT ((0)),
        [creditexposure] [decimal](16, 2) NULL,
        [creditvar] [decimal](16, 2) NULL,
        [creationname] [varchar](64) NOT NULL,
        [creationdate] [datetime] NOT NULL,
        [revisionname] [varchar](64) NULL,
        [revisiondate] [datetime] NULL,
        [quantitycontribution] [decimal](16, 2) NULL,
        [deltacontribution] [decimal](16, 2) NULL,
        [gammacontribution] [decimal](16, 2) NULL,
        [thetacontribution] [decimal](16, 2) NULL,
        [vegacontribution] [decimal](16, 2) NULL,
        [rhocontribution] [decimal](16, 2) NULL,
        [interactioncontribution] [decimal](16, 2) NULL,
        [netcontribution] [decimal](16, 2) NULL,
        [movement] [bit] NULL DEFAULT ((0)),
        [posdetail] [varchar](8) NOT NULL,
        [begtime] [datetime] NULL,
        [endtime] [datetime] NULL,
        [exposure] [varchar](8) NOT NULL DEFAULT ('POSITION'),
        [pricestatus] [varchar](8) NULL DEFAULT ('FLOAT'),
        [exposuretype] [varchar](16) NULL,
        [priceindex] [varchar](32) NULL,
        [trade] [varchar](8) NULL,
        [timezone] [varchar](4) NULL,
        [product] [varchar](24) NULL,
        [fee] [varchar](8) NULL,
        [feetype] [varchar](32) NULL,
        [shipment] [varchar](8) NULL,
        [measure] [varchar](8) NULL,
        [credittype] [varchar](16) NULL,
        [tsperiod] [varchar](16) NULL,
        [futuremonth] [datetime] NULL,
        [expirationdate] [datetime] NULL,
        [settlementdate] [datetime] NULL,
        [daylightsaving] [bit] NOT NULL DEFAULT ((0)),
        [marketdayhour] [decimal](4, 0) NOT NULL DEFAULT ((0)),
        [cycle] [varchar](16) NULL,
        [taxlocation] [varchar](16) NULL DEFAULT ('0'),
        [optionstatus] [varchar](16) NULL,
        [contract] [varchar](16) NULL,
        [compositeindex] [varchar](32) NULL,
        [company] [varchar](32) NULL,
        [counterparty] [varchar](32) NULL,
        [trader] [varchar](64) NULL,
        [tradebook] [varchar](32) NULL,
        [currencyfactor] [decimal](4, 0) NULL DEFAULT ((1)),
        [paymentterms] [varchar](32) NULL,
        [transactiontype] [varchar](4) NULL DEFAULT ('JE'),
        [subledger] [varchar](16) NULL,
        [component] [varchar](24) NULL,
        [block] [varchar](16) NULL,
        [marketarea] [varchar](32) NULL,
        [carrier] [varchar](32) NULL,
        [location] [varchar](64) NULL,
        [property] [varchar](32) NULL,
        [pile] [varchar](16) NULL,
        [quality] [varchar](16) NULL,
        [tradetype] [varchar](32) NULL,
        [tier] [decimal](4, 0) NULL,
        [strategy] [varchar](16) NULL,
        [strategydetail] [decimal](16, 0) NULL,
        [hedge] [varchar](8) NULL,
        [hypothetical] [bit] NULL DEFAULT ((0)),
        [producttype] [varchar](16) NULL,
        [pricebegtime] [datetime] NULL,
        [priceendtime] [datetime] NULL,
     CONSTRAINT [pk_valuationdetail] PRIMARY KEY CLUSTERED
    (
        [valuationdetail] 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
    SET ANSI_PADDING OFF
    GO
    ALTER TABLE [dbo].[valuationdetail]  WITH CHECK ADD FOREIGN KEY([company])
    REFERENCES [dbo].[company] ([company])
    GO
    ALTER TABLE [dbo].[valuationdetail]  WITH CHECK ADD FOREIGN KEY([contract])
    REFERENCES [dbo].[contract] ([contract])
    GO
    ALTER TABLE [dbo].[valuationdetail]  WITH CHECK ADD FOREIGN KEY([counterparty])
    REFERENCES [dbo].[counterparty] ([counterparty])
    GO
    ALTER TABLE [dbo].[valuationdetail]  WITH CHECK ADD FOREIGN KEY([hedge])
    REFERENCES [dbo].[hedge] ([hedge])
    GO
    ALTER TABLE [dbo].[valuationdetail]  WITH CHECK ADD FOREIGN KEY([marketarea])
    REFERENCES [dbo].[location] ([location])
    GO
    ALTER TABLE [dbo].[valuationdetail]  WITH CHECK ADD FOREIGN KEY([paymentterms])
    REFERENCES [dbo].[paymentterms] ([paymentterms])
    GO
    ALTER TABLE [dbo].[valuationdetail]  WITH CHECK ADD FOREIGN KEY([product])
    REFERENCES [dbo].[product] ([product])
    GO
    ALTER TABLE [dbo].[valuationdetail]  WITH CHECK ADD FOREIGN KEY([producttype])
    REFERENCES [dbo].[producttype] ([producttype])
    GO
    ALTER TABLE [dbo].[valuationdetail]  WITH CHECK ADD FOREIGN KEY([strategy])
    REFERENCES [dbo].[strategy] ([strategy])
    GO
    ALTER TABLE [dbo].[valuationdetail]  WITH CHECK ADD FOREIGN KEY([timezone])
    REFERENCES [dbo].[timezone] ([timezone])
    GO
    ALTER TABLE [dbo].[valuationdetail]  WITH CHECK ADD FOREIGN KEY([trade])
    REFERENCES [dbo].[trade] ([trade])
    GO
    ALTER TABLE [dbo].[valuationdetail]  WITH CHECK ADD FOREIGN KEY([tradebook])
    REFERENCES [dbo].[tradebook] ([tradebook])
    GO
    ALTER TABLE [dbo].[valuationdetail]  WITH CHECK ADD FOREIGN KEY([tradetype])
    REFERENCES [dbo].[tradetype] ([tradetype])
    GO
    ALTER TABLE [dbo].[valuationdetail]  WITH CHECK ADD FOREIGN KEY([valuation])
    REFERENCES [dbo].[valuation] ([valuation])
    GO
    ALTER TABLE [dbo].[valuationdetail]  WITH CHECK ADD  CONSTRAINT [ck_valuationdetail_credittype] CHECK  (([credittype]='OPEN' OR [credittype]='MARGIN' OR [credittype]='LIMIT' OR [credittype]='FORWARD' OR [credittype]='DELIVERED' OR [credittype]='CURRENT' OR [credittype]='COLLATERAL'))
    GO
    ALTER TABLE [dbo].[valuationdetail] CHECK CONSTRAINT [ck_valuationdetail_credittype]
    GO
    ALTER TABLE [dbo].[valuationdetail]  WITH CHECK ADD  CONSTRAINT [ck_valuationdetail_exposure] CHECK  (([exposure]='POSITION' OR [exposure]='MARKET'))
    GO
    ALTER TABLE [dbo].[valuationdetail] CHECK CONSTRAINT [ck_valuationdetail_exposure]
    GO
    ALTER TABLE [dbo].[valuationdetail]  WITH CHECK ADD  CONSTRAINT [ck_valuationdetail_option679] CHECK  (([optionstatus]='TRIGGER' OR [optionstatus]='TERMINATE' OR [optionstatus]='EXPIRED' OR [optionstatus]='EXERCISE'))
    GO
    ALTER TABLE [dbo].[valuationdetail] CHECK CONSTRAINT [ck_valuationdetail_option679]
    GO
    ALTER TABLE [dbo].[valuationdetail]  WITH CHECK ADD  CONSTRAINT [ck_valuationdetail_pricestatus] CHECK  (([pricestatus]='FLOAT' OR [pricestatus]='FIXED'))
    GO
    ALTER TABLE [dbo].[valuationdetail] CHECK CONSTRAINT [ck_valuationdetail_pricestatus]
    GO
    ALTER TABLE [dbo].[valuationdetail]  WITH CHECK ADD  CONSTRAINT [ck_valuationdetail_quanti689] CHECK  (([quantitytype]='RECEIPT' OR [quantitytype]='LOSS' OR [quantitytype]='DELIVERY'))
    GO
    ALTER TABLE [dbo].[valuationdetail] CHECK CONSTRAINT [ck_valuationdetail_quanti689]
    GO
    ALTER TABLE [dbo].[valuationdetail]  WITH CHECK ADD  CONSTRAINT [ck_valuationdetail_quanti915] CHECK  (([quantitystatus]='TRADE' OR [quantitystatus]='TAG' OR [quantitystatus]='SCHEDULE' OR [quantitystatus]='PLAN' OR [quantitystatus]='NOM' OR [quantitystatus]='FORECAST' OR [quantitystatus]='ESTIMATE' OR [quantitystatus]='BOOKOUT' OR [quantitystatus]='ACTUAL'))
    GO
    ALTER TABLE [dbo].[valuationdetail] CHECK CONSTRAINT [ck_valuationdetail_quanti915]
    GO
    ALTER TABLE [dbo].[valuationdetail]  WITH CHECK ADD  CONSTRAINT [ck_valuationdetail_transa999] CHECK  (([transactiontype]='JE' OR [transactiontype]='CR' OR [transactiontype]='CD' OR [transactiontype]='AR' OR [transactiontype]='AP'))
    GO
    ALTER TABLE [dbo].[valuationdetail] CHECK CONSTRAINT [ck_valuationdetail_transa999]

     

    martes, 10 de enero de 2012 13:59

Respuestas

  • Crea este índice en la tabla:

    CREATE NONCLUSTERED INDEX [index_valuationdetail_A] ON [dbo].[valuationdetail]
    (
                    [trade] ASC,
                    [valuation] ASC,
                    [valuationdetail] ASC,
                    [position] ASC,
                    [begtime] ASC,
                    [endtime] ASC,
                    [counterparty] ASC,
                    [quantitytype] ASC
    )
    INCLUDE ( [value],
    [credittype]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

    O en su defecto la creación de estadísticas en estos campos:

    CREATE STATISTICS [stat_ valuationdetail_A] ON [dbo].[valuationdetail]([valuation], [position], [begtime], [endtime], [trade], [counterparty], [quantitytype], [valuationdetail])

     Norman M. Pardell 

    ||Microsoft Certified IT Professional|| Database Administrator. Database Developer. SQL Server 2008


    • Editado Normannp martes, 10 de enero de 2012 14:02
    • Marcado como respuesta SergioAlto miércoles, 11 de enero de 2012 8:34
    martes, 10 de enero de 2012 14:01

Todas las respuestas

  • Crea este índice en la tabla:

    CREATE NONCLUSTERED INDEX [index_valuationdetail_A] ON [dbo].[valuationdetail]
    (
                    [trade] ASC,
                    [valuation] ASC,
                    [valuationdetail] ASC,
                    [position] ASC,
                    [begtime] ASC,
                    [endtime] ASC,
                    [counterparty] ASC,
                    [quantitytype] ASC
    )
    INCLUDE ( [value],
    [credittype]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

    O en su defecto la creación de estadísticas en estos campos:

    CREATE STATISTICS [stat_ valuationdetail_A] ON [dbo].[valuationdetail]([valuation], [position], [begtime], [endtime], [trade], [counterparty], [quantitytype], [valuationdetail])

     Norman M. Pardell 

    ||Microsoft Certified IT Professional|| Database Administrator. Database Developer. SQL Server 2008


    • Editado Normannp martes, 10 de enero de 2012 14:02
    • Marcado como respuesta SergioAlto miércoles, 11 de enero de 2012 8:34
    martes, 10 de enero de 2012 14:01
  • ¿Has probado a crear una vista indexada? http://msdn.microsoft.com/es-es/library/ms187864.aspx
    martes, 10 de enero de 2012 14:58
  • Hola.

    Es un poco complicado sin conocer la distribución de datos, ni si será siempre la misma consulta o sólo parecidas (si nos facilitas 3 ejemplos más, mejor). La creación de un índice, como sugiere Norman, por supuesto que ayudaría, pero claro, habría que considerar seriamente el gran tamaño que tendría el índice, porque los campos son de cadena larga en varios de los casos, y porque estamos hablando de un gran número de registros. Por ejemplo, yo primero vería qué tal funciona un índice por el campo "valuation" (sin más).

    Sería también muy útil que nos pasaras los índices que ya tiene la tabla (seguramente tendrá ya alguno), así como el número de lecturas lógicas que se realizan sobre la tabla al ejecutar la consulta.

    Otro detalle, la estructura de la tabla y los tipos de datos, sería importante revisarla.


    Alberto López Grande
    SQL Server MVP
    Visita mi blog en http://qwalgrande.blogspot.es/ Sígueme en twitter en http://twitter.com/qwalgrande

    martes, 10 de enero de 2012 20:18
    Moderador