December 29, 2010 at 12:22 pm
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
December 29, 2010 at 1:24 pm
Please post table definitions, index definitions and execution plan, as per http://qa.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
December 29, 2010 at 5:34 pm
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
December 30, 2010 at 7:41 am
just checked the execution plan and it seems like "clusterd index scan cost = 0%" and "table scan is 94%"
December 30, 2010 at 7:43 am
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
December 30, 2010 at 7:51 am
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
December 30, 2010 at 7:54 am
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
December 30, 2010 at 12:23 pm
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%
December 30, 2010 at 12:25 pm
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
December 30, 2010 at 12:27 pm
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
December 30, 2010 at 12:31 pm
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 ?
December 31, 2010 at 8:22 am
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 ... ?
December 31, 2010 at 8:29 am
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.
December 31, 2010 at 8:55 am
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
December 31, 2010 at 8:59 am
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_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
postdatedimkey int no 4 10 0 yes (n/a) (n/a) NULL
currentbalance money no 8 19 4 yes (n/a) (n/a) NULL
accountbranchnumber int no 4 10 0 yes (n/a) (n/a) NULL
accountnumber bigint no 8 19 0 yes (n/a) (n/a) NULL
accounttype varchar no 1 yes no yes SQL_Latin1_General_CP1_CI_AS
accounttypedesc varchar no 8 yes no yes SQL_Latin1_General_CP1_CI_AS
productcode varchar no 8 yes no yes SQL_Latin1_General_CP1_CI_AS
monthname varchar no 8 yes no yes SQL_Latin1_General_CP1_CI_AS
departmentcodekey int no 4 10 0 yes (n/a) (n/a) NULL
condensecodedesc varchar no 40 yes no yes SQL_Latin1_General_CP1_CI_AS
statementdesc varchar no 30 yes no yes SQL_Latin1_General_CP1_CI_AS
branchname varchar no 100 yes no yes SQL_Latin1_General_CP1_CI_AS
groupname varchar no 100 yes no yes SQL_Latin1_General_CP1_CI_AS
division varchar no 2 yes no yes SQL_Latin1_General_CP1_CI_AS
index_name index_description index_keys
MyTableIndex nonclustered located on PRIMARY accountbranchnumber, accounttype, division, groupname, postdatedimkey, branchname, accounttypedesc
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply