0

I have a little deal. I have this table in a SQL Server 2012 Standard edition database. The table contains about 400 mil of rows.

CREATE TABLE [dbo].[prodeje]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [partner] [tinyint] NOT NULL,
    [id_paragon] [varchar](20) NOT NULL,
    [typ_pohybu] [varchar](2) NULL,
    [store] [varchar](20) NULL,
    [datetime] [datetime] NOT NULL,
    [date] [date] NULL,
    [id_prod] [varchar](50) NOT NULL,
    [customer] [varchar](50) NULL,
    [price_per_unit] [decimal](18, 2) NULL,
    [stock_price] [decimal](18, 2) NULL,
    [pc] [decimal](18, 2) NULL,
    [amount] [decimal](16, 4) NULL,
    [action] [varchar](50) NULL,
    [action_cupon] [varchar](50) NULL,
    [bonus_gratis] [varchar](50) NULL,
    [fin_sleva] [decimal](18, 2) NULL,
    [exclude] [tinyint] NULL
) ON [PRIMARY]

It's a table with sales. So most of the query will include column partner, store, date, id_prod, price per unit * amount and distinct count on column id_paragon for baskets. Column id_prod is foreign key for table with products. Column exclude is column with information if the row is good. All query will contains in where clause exclude=0.

And I am thinking about indexes. Because it's more usually use part of the year, so my first think was that i will do clustered index on column date. And in query i query first only data what i need. So I will query on few mil of rows and it will be quick. And index for id_prod. But I think that is not good way to do the indexes. So I have questions for you, what indexes will you do on this table.

If you need more info write a comment. I will add what you want for think about it.

Thanks

2
  • What are you optimizing for? Read speed? Write speed? Disk space used? Pick priorities. – usr Apr 19 '15 at 12:14
  • I want to optimizing that table for read speed. Write speed is good for now. Scripts insert about 80k rows per batch. (About 6 batch in one day). Disk space doesn't matter in this case. – JAttanonRadar Apr 19 '15 at 14:00
1

Since you are optimizing for reads you could simply add one optimal index per query. So if your query is

select price_per_unit from Sales where exclude = 0 and partner = 42

Create the index (exclude, partner) include (price_per_unit).

You say that writes and disk spaces are not of significant concern. So add the optimal indexes to support your reads.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.