'index for very large table sql

i have a large table with 4 miliion rows , the problem is my select from that table with special columns with a 3 month period is very slow and creating index,changing filegroup,changing datatypes does not help for performance. SO help me :D thx by the way

table structure :

CREATE TABLE [dbo].[table](
    [Datef] [nvarchar](10) NULL,
    [MasterT] [decimal](18, 5) NULL,
    [SlaveT] [decimal](18, 5) NULL,
    [AllT] [decimal](18, 5) NULL,
    [SlavePrice] [float] NULL,
    [Exp] [nvarchar](10) NULL,
    [Number] [numeric](18, 0) NULL,
    [StoreNo] [numeric](18, 0) NOT NULL,
    [Kind] [nvarchar](1) NULL,
    [Code] [numeric](18, 0) NOT NULL,
    [Name] [nvarchar](100) NULL,
    [TruePrice] [float] NULL,
    [StoreName] [nvarchar](35) NULL,
    [SumNo] [numeric](18, 0) NULL,
    [Price] [numeric](18, 0) NULL,
    [Qc] [nvarchar](15) NULL,
    [TF] [real] NULL,
    [MTF] [numeric](18, 0) NULL,
    [MGP] [numeric](18, 0) NULL,
    [GMK] [numeric](18, 0) NULL,
    [MSNO] [int] NULL,
    [inx] [numeric](18, 0) IDENTITY(1,1)  NOT NULL,
    [Vazn] [numeric](18, 0) NULL,
    [Hjm] [numeric](18, 0) NULL,
    [Masir] [nvarchar](8) NULL,
    [kindj] [tinyint] NULL,
    [Maliat] [numeric](18, 0) NULL,
    [Avarez] [numeric](18, 0) NULL,
    [IsMaliat] [bit] NULL,
    [PromotionNo] [nvarchar](300) NULL,
    [CashDis] [real] NULL,
    [TF1] [real] NULL,
    [MTF1] [numeric](18, 0) NULL,
    [TF2] [real] NULL,
    [MTF2] [numeric](18, 0) NULL,
    [TF3] [real] NULL,
    [MTF3] [numeric](18, 0) NULL,
    [TF4] [real] NULL,
    [MTF4] [numeric](18, 0) NULL,
    [kindj2] [tinyint] NULL,
    [kindj3] [tinyint] NULL,
    [kindj4] [tinyint] NULL,
    [PromotionInfo] [nvarchar](300) NULL,
    [CashDisO] [numeric](18, 0) NULL,
    [Dastmozd] [numeric](18, 0) NULL,
    [Sarbar] [numeric](18, 0) NULL,
 CONSTRAINT [PK_table] PRIMARY KEY CLUSTERED 
([inx] ASC)) ON [PRIMARY]

Query:

SELECT CASE WHEN dbo.t1.Kind = 'F' THEN N'SALE' ELSE N'returned' END AS Kind,
       t1.Code,
       t1.Name,
       t1.MasterT,
       t1.SlaveT,
       t1.AllT,
       t1.SlavePrice,
       t1.SlavePrice * t1.AllT AS Price,
       t1.TF,
       t1.MTF,
       t1.TF1,
       t1.MTF1,
       t1.TF2,
       t1.MTF2,
       t1.TF3,
       t1.MTF3,
       t1.TF4,
       t1.MTF4,
       ROUND(
                (((t1.AllT * t1.SlavePrice - t1.MTF) - t1.TruePrice * t1.AllT)
                 + (t1.Maliat + t1.Avarez)
                ) * -1,
                0
            ) AS AD,
       t1.Maliat + t1.Avarez AS Maliat,
       t1.TruePrice * t1.AllT AS TruePrice,
       t1.StoreNo,
       t1.PromotionInfo
FROM table1 AS t1
WHERE t1.kind = 'f'
      AND t1.DATE BETWEEN 'date1' AND 'date2'
sql-servertsqlquery-optimization


Solution 1:[1]

A BTREE index on these two columns will allow this particular query. It tests for equality on kind and for a range on `DATE’, so you need

ALTER TABLE table1 ADD INDEX kind_date (kind, [DATE]);

You could also INCLUDE some other columns, but unless your query usually fetches many rows, that won't be worth the trouble.

Others have said it, but it bears repeating: dates stored in NVARCHAR columns considered harmful.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 O. Jones

Related Questions