report optimization

Question

report optimization

koolme_85

Ten Centuries

Points: 1153
More actions
December 29, 2010 at 12:22 pm

#231513

i have a parameterized report it has like 4 parameters , so according to the requirements i choose the paramaeter values to look into the report, the report pulls the data from the fact tables ,
but every time i pull the reports , its taking long time ,
can anybody help me with this issue

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Have you captured the query being run by the report and run it in SSMS to view the execution plan to determine if you can optimize the query? If the query is optimized then the issue is not the query returning the data, but the rendering of the report.

Oh and if you provide what Gail has asked then it would be easier to offer a real answer.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

koolme_85 Ten Centuries Points: 1153 More actions · Answer 3

just checked the execution plan and it seems like "clusterd index scan cost = 0%" and "table scan is 94%"

koolme_85 Ten Centuries Points: 1153 More actions · Answer 4

so when you talk of rendering the report .....do you think i should work on report caching ...and the data is not updated in the tables like till the end of every month

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

koolme_85 (12/30/2010)
just checked the execution plan and it seems like "clusterd index scan cost = 0%" and "table scan is 94%"

Can you post the plan please, along with the table and index definitions? Hard to do anything other than guess without.

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

Without knowing the structure of the tables and the query it is hard to offer accurate advice.

How long does it take for the query to run in SSMS in relation to the time it takes the report to run?

Based on what you've posted thus far it looks like, for this report, you may need to add an index on the table that has the table scan. Before you do that you need to understand how the table is used elsewhere and any other indexes on the table because having too many indexes can cause performance issues just like having too few or incorrect indexes can.

I don't think report caching is th issue here, I think it is the query and database itself.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

koolme_85 Ten Centuries Points: 1153 More actions · Answer 7

Thanks for the suggestions guys , i actually ran the query in database engine tuning advisor

and it gave me some recomandations , i have to create some indexes and stastics on the columns and

the estimated improvement is about 99.00%

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Don't just go and implement them. DTA's a pile of junk most of the time.

Take each recommendation. Test it. See if it improves the report and does not degrade anything else. If so, implement that recommendation. Repeat until you've evaluated all of them. Don't implement anything that doesn't help.

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 9

GilaMonster (12/30/2010)
Don't just go and implement them. DTA's a pile of junk most of the time.
Take each recommendation. Test it. See if it improves the report and does not degrade anything else. If so, implement that recommendation. Repeat until you've evaluated all of them. Don't implement anything that doesn't help.

Ditto and also from my last post:

Before you do that you need to understand how the table is used elsewhere and any other indexes on the table because having too many indexes can cause performance issues just like having too few or incorrect indexes can.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

koolme_85 Ten Centuries Points: 1153 More actions · Answer 10

OK will do thanks , i have another question , its in SSIS , i have actually posted the issue ,

i dont know if you had a chance of looking at it ?

koolme_85 Ten Centuries Points: 1153 More actions · Answer 11

hi ,

well i have created nonclustered indexes on the the colums thats used in where and group by claus

but the seems to be having no improvements in reports

is there any other way ... ?

LutzM SSC Guru Points: 107049 More actions · Answer 12

koolme_85 (12/31/2010)
hi ,
well i have created nonclustered indexes on the the colums thats used in where and group by claus
but the seems to be having no improvements in reports
is there any other way ... ?

Yes: post table def incl. index def together with the actual execution plan you have right now.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 13

We need the tables structures (including indexes), the query, and the execution plan in order to give any more advice.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

koolme_85 Ten Centuries Points: 1153 More actions · Answer 14

the indexes i have created is like this

CREATE NONCLUSTERED INDEX [MyTableIndex] ON [dbo].[MM_EOMRetail]

(

[accountbranchnumber ] ASC,

[accounttype ] ASC,

[division] ASC,

[groupname] ASC,

[postdatedimkey] ASC,

[branchname],

[accounttypedesc]

)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

the execution plan showes this

select cost = 0% , nestedloops(innerjoin) cost = 0% , indexseek(nonclustered) [MM_EOMretail].[mytableindex] cost = 50% , RID lookup(heap) [MM_EOMRetail] cost = 50%

the table definition is

Column_nameTypeComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation

postdatedimkeyintno410 0 yes(n/a)(n/a)NULL

currentbalancemoneyno819 4 yes(n/a)(n/a)NULL

accountbranchnumberintno410 0 yes(n/a)(n/a)NULL

accountnumberbigintno819 0 yes(n/a)(n/a)NULL

accounttypevarcharno1 yesnoyesSQL_Latin1_General_CP1_CI_AS

accounttypedescvarcharno8 yesnoyesSQL_Latin1_General_CP1_CI_AS

productcodevarcharno8 yesnoyesSQL_Latin1_General_CP1_CI_AS

monthnamevarcharno8 yesnoyesSQL_Latin1_General_CP1_CI_AS

departmentcodekeyintno410 0 yes(n/a)(n/a)NULL

condensecodedescvarcharno40 yesnoyesSQL_Latin1_General_CP1_CI_AS

statementdescvarcharno30 yesnoyesSQL_Latin1_General_CP1_CI_AS

branchnamevarcharno100 yesnoyesSQL_Latin1_General_CP1_CI_AS

groupnamevarcharno100 yesnoyesSQL_Latin1_General_CP1_CI_AS

divisionvarcharno2 yesnoyesSQL_Latin1_General_CP1_CI_AS

index_nameindex_description index_keys

MyTableIndexnonclustered located on PRIMARYaccountbranchnumber, accounttype, division, groupname, postdatedimkey, branchname, accounttypedesc