SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


PLease suggest index for this table


PLease suggest index for this table

Author
Message
Smash125
Smash125
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 1381
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
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27133 Visits: 13268
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)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Smash125
Smash125
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 1381
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
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27133 Visits: 13268
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?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
T.Ashish
T.Ashish
SSC Eights!
SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)

Group: General Forum Members
Points: 907 Visits: 599
create your index on

yearno
periodid
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86444 Visits: 45235
T.Ashish (10/24/2013)
create your index on

yearno
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


T.Ashish
T.Ashish
SSC Eights!
SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)

Group: General Forum Members
Points: 907 Visits: 599
GilaMonster (10/24/2013)
T.Ashish (10/24/2013)
create your index on

yearno
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86444 Visits: 45235
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86444 Visits: 45235
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27133 Visits: 13268
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search