October 24, 2013 at 12:53 am
You cannot vote on your own post
0
I have table called FACT_NLTRANS. Basically this is fact table. I am using this table for my SSRS table which has 60 lakh records
Could you please suggest right index for this table. Table definition as below.
CREATE TABLE [dbo].[FACT_NLTRANS](
[doc-id] [nvarchar](12) NULL,
[docnumber] [int] NULL,
[docdate] [datetime] NULL,
[sourceacc] [nvarchar](20) NULL,
[baseval] [numeric](17, 2) NULL,
[currvalue] [numeric](17, 2) NULL,
[qty] [numeric](18, 3) NULL,
[yearno] [numeric](15, 0) NULL,
[period] [numeric](15, 0) NULL,
[periodid] [int] NULL,
[trtyp] [nvarchar](12) NULL,
[inputdate] [datetime] NULL,
[stat] [nvarchar](2) NULL,
[costcentre] [nvarchar](30) NULL,
[expensecode] [nvarchar](30) NULL,
[expense_sk] [int] NULL,
[company_sk] [int] NULL,
[currency] [nvarchar](8) NULL,
[transdate] [datetime] NULL,
[CREATED_BY] [varchar](50) NULL,
[CREATED_DATE] [datetime] NULL,
[UPDATED_BY] [varchar](50) NULL,
[UPDATED_DATE] [datetime] NULL,
[upddate] [datetime] NULL,
[unqkey] [nvarchar](24) NULL
) ON [PRIMARY]
GO
We have to create non clustered index which column I need to go so that I can speed query.
looking for your answers
October 24, 2013 at 12:55 am
It might be useful if you also gave us the query...
(if you run the query and check the actual execution plan, it gives a hint about possible indexes)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 24, 2013 at 1:09 am
please find the query as below
DECLARE @DrillSelected VARCHAR(100)
if @DrillSelected is null
begin
select D.BUSINESSDIVISION,
(SUM((nL.baseval)*CASE WHEN CURR.RATE IS NULL THEN 1 ELSE CURR.RATE END)) OUTSTANDINGAMOUNT
FROM
vw_fact_nltrans nl
INNER JOIN
VW_DIM_COMPANY C
ON nl.COMPANY_sk=C.COMPANY_sk
INNER JOIN DIVISIONMAPPING D
ON D.COMPANYID=C.COMPANYID
INNER JOIN VW_DIM_EXPENSECODE EC
ON NL.COMPANY_SK=EC.COMPANY_SK AND NL.EXPENSE_SK=EC.EXPENSE_SK
LEFT OUTER JOIN VW_FACT_CURRENCY CURR
ON c.COMPANY_SK=CURR.COMPANY_SK
AND CURR.CURRENCY IN ('GBP','STER') where d.active in ('yes','no') and
nl.yearno in (select distinct fiscalyear from VW_DIM_FISCAL_CALENDAR_PERIOD where periodid='201302') and nl.periodid<='201302' and (EC.EXPENSEGROUP IN ('1-###-##','2-###-##','3-###-##') OR EC.EXPENSECODE='4-030-04') AND C.COMPANY_DESC NOT IN ('Advanced Health & Care NZ Ltd')
group by D.BUSINESSDIVISION
end
October 24, 2013 at 2:00 am
First I would optimize the query a bit.
select distinct fiscalyear from VW_DIM_FISCAL_CALENDAR_PERIOD where periodid='201302'
I assume this only returns one single value? If yes, I would execute this query first and store the result in a variable. Then use this variable in the where clause of the main query and use = instead of IN.
Why are there columns yearno and periodid in the fact table? Don't you have a date dimension?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 24, 2013 at 7:26 am
create your index on
yearno
periodid
October 24, 2013 at 7:28 am
T.Ashish (10/24/2013)
create your index onyearno
periodid
Sure that's the best index? A non-covering index that only supports the where clause.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 24, 2013 at 7:47 am
GilaMonster (10/24/2013)
T.Ashish (10/24/2013)
create your index onyearno
periodid
Sure that's the best index? A non-covering index that only supports the where clause.
Agree. Not the best.
covering index will be a better idea but I think covering index will be limited to this query only.
October 24, 2013 at 8:21 am
T.Ashish (10/24/2013)
covering index will be a better idea but I think covering index will be limited to this query only.
So you recommend an index on two columns but say that an index on those two columns plus a couple more will be limited only to this query? How does that work?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 24, 2013 at 8:24 am
Smash, it would be really useful if we could see the execution plan of that query, plus any other queries that run frequently against the table. Since we have little idea of data volumes or distribution it's really hard to suggest useful indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 24, 2013 at 1:45 pm
Just following a common practice in dimensional modelling, I would start with defining a unique index on your surrogate keys of the fact table.
(they should be unique, if not your design is incorrect)
And put all the date attributes in the date dimension.
I see way too many date and varchar columns in your fact table. A fact table should only have numeric data (foreign keys which are integers and your measures).
Normally you shouldn't filter on your fact table at all, you should filter on dimensions only.
If you have SQL Server Enterprise, the database engine will detect it's a star join query and it can apply very efficient optimizations.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 25, 2013 at 5:31 am
Thanks guys all your input. Tuning of query is given to me in the morning, i have no idea about the query and database involved. Thank fully i went for Database Tuning Adviser for right indexes. DTV suggested some NON CLUSTERED INDEXES and STATISTICS. I took these and applied. It worked for me. Now queries are bit faster .
October 25, 2013 at 5:52 am
Smash125 (10/25/2013)
Thanks guys all your input. Tuning of query is given to me in the morning, i have no idea about the query and database involved. Thank fully i went for Database Tuning Adviser for right indexes. DTV suggested some NON CLUSTERED INDEXES and STATISTICS. I took these and applied. It worked for me. Now queries are bit faster .
There are still quite some flaws in the design that might need to be addressed.
The Database Tuning Advisor is also not the most optimal tool there is to find. It's possible the indexes are useful for the one query, but they are useless for other queries.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply