I have a large table with more than one million rows and give me a group of data to return this table In the shortest time you will have to ask.
So I have created a test environment with this table definition:
create table [dbo]. [Test] (NIM), [Dim1ID] [nvarchar] (20) No NULL, [Dim2ID] [nvarchar] (20) No NULL, [Dim3ID] [nvarchar] (4) No NULL, [Dim4ID] [smalldatetime] NULL, Dim5ID] [nvarchar] (20) No zero, [Dim6ID] [nvarchar] (4) No NULL, [Dim7ID] [nvarchar] (4) No NULL, [Dim8ID] [nvarchar] (4) No NULL, [Dim9ID] [Nvarchar] (4) No NULL, [Dim10ID] [nvarchar] (4) No NULL, [Dim11ID] [nvarchar] (20) No NULL, [value] [decimal] (21, 6) No NULL, obstacle [PK_Test ] Primary key cluster ([Dim1ID] ASC, [Dim2ID] ASC, [Dim3ID] ASC, [Dim4ID] ASC, [Dim5ID] ASC, [Dim6ID] ASC, [Dim7ID] ASC, [Dim8ID] ASC, [Dim9ID] ASC, [Dim10ID] with ASC, [Dim11ID] ASC (PAD_INDEX = closed, STATISTICS_NORECOMPUTE = closed, IGNORE_DUP_KEY = off, ALLOW_ROW_LO [primary]] [primary] This table is the actual table of Star Schema architecture (fact / dimension), as you can see that I have a compiled index on all the columns, besides "value" column.
I have almost filled up this data almost 10,000,000 lines for test purpose is currently at 0.01% fission
When I read a set of rows from this table, use this query to improve Want to: Announced @ Dim1ID nvarchar (20) = 'C1' declared @ Dim9ID nvarchar (4) = 'VRT1' declared @ Dim10ID nvarchar (4) = 'S1' declared @ Dim6ID nvarchar ( 4) = 'FRA' declaration @ Dim7ID nvarchar (4) = '' - - = empty = all declaration @ Dim8ID nvarchar (4) = '' - empty = (Dim2ID nvarchar (20) No NULL) All announcement @ Dim2 table @ Dim2 Enter into value ('A1'), ('A2'), ('A3'), ('A4'); DECLARE @ Dim3 Table (Dim3ID nvarchar (4)) No results @ Dim3 VALUES ('P1'); Announcement @ Dim4ID Table (Dim4ID smalldatetime not NULL) ('2009-01-01') ('2009-01-02') Enter @ Dim4ID value, ('2009-01-03'); Announcement @ Dim11 table (Dim11ID nvarchar (20) No NULL) Insert @ Dim11 values ('Var0001'), ('Var0040'), ('Var0060'), ('Var0099') Select RD.Dim2ID, RD.Dim3ID Check RD.Value from RD.Dim4ID, RD.Dim5ID, RD.Dim6ID, RD.Dim7ID, RD.Dim8ID, RD.Dim9ID, RD.Dim10ID, RD.Dim11ID, dbo.Test RD Include RD @ RD on RD2 Include Dim2ID = R.Dim2ID Insert @ Dim3 C at RD.Dim3ID = C.Dim3ID Insert @Dim4ID at RD.Dim4ID = P.Dim4ID Insert @DIM11V at RD.Dim11ID = V.Dim11ID Where RD .Dim1ID = @ Dim1ID and RD.Dim9ID = @ Dim9ID and ((@ Dim6ID I have tested this query and at that time 180 rows have been returned in these lines: First execution: 1 minute 32 seconds; Second execution: 1 minute . If I want to return the data in a few seconds
I suppose I indexed non-packages, but I'm not sure what is the best way to set up non-cluster indexes! If the order is sorted in this table, can performance improve? Or is there any other solution than indexes?
Thank you.
Think of your databases as a problem. This is
Another problem: PK is incorrect for your query, it must be Dim1ID, Dim9ID before (or vice versa on selectivity). Or some taste with the JOIN column. The third problem: The use of OR generally works, which also works for those who do not try to do it.
RD.Dim7ID = ISNULL (@ Dim7ID, RD.Dim7ID) It assumes that @ Dim7ID is NULL though . The optimizer will do short circuits in most cases.
Comments
Post a Comment