Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

PLease suggest index for this table Expand / Collapse
Author
Message
Posted Thursday, October 24, 2013 12:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 8:24 AM
Points: 194, Visits: 1,087




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
Post #1507893
Posted Thursday, October 24, 2013 12:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:09 AM
Points: 13,303, Visits: 10,168
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1507895
Posted Thursday, October 24, 2013 1:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 8:24 AM
Points: 194, Visits: 1,087
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
Post #1507900
Posted Thursday, October 24, 2013 2:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:09 AM
Points: 13,303, Visits: 10,168
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1507910
Posted Thursday, October 24, 2013 7:26 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 2:58 AM
Points: 718, Visits: 543
create your index on

yearno
periodid
Post #1508041
Posted Thursday, October 24, 2013 7:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 42,462, Visits: 35,525
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 2008, MVP
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

Post #1508042
Posted Thursday, October 24, 2013 7:47 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 2:58 AM
Points: 718, Visits: 543
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.
Post #1508051
Posted Thursday, October 24, 2013 8:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 42,462, Visits: 35,525
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 2008, MVP
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

Post #1508080
Posted Thursday, October 24, 2013 8:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 42,462, Visits: 35,525
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 2008, MVP
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

Post #1508082
Posted Thursday, October 24, 2013 1:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:09 AM
Points: 13,303, Visits: 10,168
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1508247
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse