.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SSRS taking a long time to run an MDX query

Posted By:      Posted Date: October 07, 2010    Points: 0   Category :Sql Server


I have an MDX query which runs fine in the MDX Sample Application. It runs against an SSAS 2000 cube. In my query there is a recursion kind of a logic. It takes about 1-2 sec to run in the MDX Sample Application. My sample application is in the same server as my AS 2000 cube is. However when I run the same MDX from another server through BIDS (2008) it takes an unusually long amount of time to run. Most of the time it times out. :(

As a workaround I created 4 calculated members in my Cube itself (Initially these were used as members in my MDX). I thought that maybe the recursion logic is the culprit so I modified my MDX, removed the recursion part. But still from my SSRS it is taking a long time.

Any pointers to where I am missing the trick ?

Thanks in advance,


View Complete Post

More Related Resource Links

SQL Query is taking long time to run


Hi All,
SQL Query is taking long time to run in my report. It is using Clustered index seek when I am querying for 2 days on invoice_itemized table. It is returning 99 thousand records. It is returning  2524094 records and it is using clustered index scan when I am querying for one month. For one month It is taking 7 minutes.
Basically it is not using the index defined for the store_Id, status and datetime columns in the invoice_totals table. Can I force the sql query to use the specified index?

Please suggest me to improve the performance....

SQL Query

,Invoice_Totals.DateTime InvoiceDate
,Invoice_Totals.Discount InvDiscPercent
,Invoice_Itemized.LineDisc LineDiscPercent
WHEN Invoice_Totals.Total_Price <> 0 AND Inventory.ItemType=7 AND Invoice_Totals.Discount = 0 THEN ((Invoice_Itemized.PricePer * Invoice_Itemized.Quantity) * -1) / ((Invoice_Totals.Total_Price) + ((Invoice_Itemized.PricePer * Invoice_Itemized.Quantity) * -1))
WHEN Invoice_Totals.Total_Price <> 0 AND Inventory.ItemType=7 THEN ((Invoice_Itemized.PricePer * I

MDX Query in SSRS Dataset taking more time


Hi All,

I have  created report based on below mention MDX query ,but it will take more time say of 10 mins to execute.while other report having bulky data in their datset working fine.

Kindly suggest how do i optimise the Query so it will take less time



member [measures].[A]as

member [measures].[B]as
iif([Measures].[Avg After Call Work]=0,0,[Measures].[Avg After Call Work])

member [measures].[C]as
iif([Measures].[Staff Time]=0,0,[Measures].[CALLSHANDLED]/[Measures].[Staff Time])

member [measures].[AA] as ((STRTOMEMBER(@EmployeeEMPLOYEENAME)), [Measures].[A] )
member [measures].[BB] as ((STRTOMEMBER(@EmployeeEMPLOYEENAME)), [Measures].[B] )
member [measures].[CC] as ((STRTOMEMBER(@EmployeeEMPLOYEENAME)), [Measures].[C] )

 member [measures].[Rank_Avg_Handle]as
(filter ([Employee].[EMPLOYEE NAME].[EMPLOYEE NAME].members,

 member [measures].[Rank_Aft_Avg_Call]as
(filter ([Employee].[EMPLOYEE NAME].[EMPLOYEE NAME].members,
iif([Measures].[Avg After Call Work]=0,0,[Measures].[Avg After Call Work])>[measures].[

Why the Same sql query taking much more time in other server.

Hi All,   I have a sql query.. which is running absoulutely fine in the production environment. I restored the Prod DB to another server.. same hardware and software configuration as Prod server. But in that server the same query taking ____ lot of time.. What could be the reason ??SD

Restoring Database taking long time in SQL Server 2008 R2 -- some how urgent


Hi all

I have Database size 12GB in SQL Server 2005 and It is backed up.

Now I tring to restore Database in SQL Server 2008 R2(wizard)....it is not at complted after 2hours...still running.


any idea..or help appreciated..



WCF calling webservice - assigning return value taking a long time

0 down vote favorite

I have a wcf service (WCF_A ) which calls another wcf service (WCF_B) (currently I am hosting the WCF_B on my local machine with my credentials – as windows service), WCF_B internally makes a call to a webservice (WS01) that is hosted on the IIS. I have created a test client and call the WCF_A -> WCF_B -> WS01. Just before making the call to (WS01) I start a timer and I stop the timer when the webservice call comes back and the result is assigned to a variable, the flow is as below WCF_B

1)  Debug.WriteLine(“Call to webservice”) 
2)  Starttimer 
3)  Var result = WS01.Function(xxxx) 
4)  Stop

By any chance can we able to optimize the below query.. It is running for long time..


By any chance, can we optimize the below query..This is running for 2 hrs..As of now, i really don't have any execution plans. 

If i replace "IN" with "Exists", i am saving few minutes. Can someone please optimize the query little more?
SELECT pm.pm_no,
eam_od_pm_tb pm, 
eam_od_location_tb loc
pm.site_id in ('ALF','BRF', 'COF','CUF','GAF','JOF','JSF','KIF','SHF','WCF', 

ASP.NET upgrade seems to be taking a very long time to start


At work I'm tryhing to upgrade an ASP.NET 2.0 app (which we wrote using VB.NET 2005) to ASP.NET 4.0.  I had a few problems during the upgrade, but eventually worked them all out.  At the end of the day I decided to give it a quick compile and see if I'd get the start page, so I hit F5 in VS 2010.  I left it that way, and man was it acting weird.  VS 2010 wasn't responding to anything.  If I hovered the mouse over VS 2010's minimize or close buttons, in the upper right corner, then those buttons were flashing very rapidly.  I'm not sure what was going on.  After waiting 15 minutes to see if it would come up, I decided to try clicking on the close button.  Nothing happened.  Eventually, I had to leave (I take public transit, and I have to leave when the bus comes), but I thought I'd leave it going as is, in hopes that it might fix itself.

But what I want to know is this, is this pretty standard behavior?  Does it normally take this long to do this?  This website is of modest size (<100 pages).

SSIS Package Validation Taking Long Time



The package that I am workin on used to open in BIDS real quickly and also when running it through BIDS. Now, it is taking a long time to open in BIDS and also when I am executing it using DTEXEC it is taking more than 60 minutes just for validation.

I dont consider it as heavy as it has fewer tasks compared to other packages and they open really quickly. And also I can open the package to work offline, but have issue with it while executing the package using DTEXEC.

I have tried DelayValidation=TRUE, but it is not much of a help.



SSRS asking for credentials while reports running for long time.


We have a report which executes a stored procedure and it runs for 10 minutes.  After running for 5 minutes, the report keeps asking for username and password for 3 times and redirects to unauthorized page.  The website uses WIDE IP for crash and recovery handling, so it has two nodes.  Could some one please help me to figure out what problem it is ?  The SP data is not huge, which produces only 300 odd rows and 20+ columns.


Any help will be appreciated.  Thanks.

MDX Query parameter from SSRS


I've a MDX Query that has where clause as shown below.
I'm designing report using SSRS 2008. How can i pass date as parameter ? I tried to setup @from and @to as parameter but not working ?
any ideas....

WHERE ( {[Date Central].[Calendar Date].[2010-04-01 00:00:00]:[Date Central].[Calendar Date].[2010-08-30 00:00:00]} )

need it to work as
WHERE ( {[Date Central].[Calendar Date].[@From]:[Date Central].[Calendar Date].[@To]} )



Slow page load during a list query one time during the day


We have a monitoring tool set to check to see if the home pages for our 3 web apps load in under 60 seconds every 10 minutes.  All 3 web apps load in under 3 seconds on every 10 minute check except for one exception.  One check every day one of the web app's home page takes longer than 60 seconds to load.  This happens at 11:45PM when there is very little user usage on the environment.  The characteristics of this page are as follows: The only thing on the page is a list view web part which was added by the browser.  The page has not been customized with SPD or code at all.  The list that it pulls is a simple links list that has 281 items on it.  The view pulls all 281 items and displays them in sets of 100.  I cannot find any associated event in the server events and/or SharePoint ULS logs nor are there any daily sharepoint timer jobs running at that time.  Our full index happens at midnight with incremental happening hourly.  Our enviroment is 2 WFEs, 1 App/indexer and we have a separate SQL cluster backend.  

Could someone lead me possibly in the direction I should take next in my troubleshooting?   

WCF Service: takes very long time to start up

Hi, What could be the reasons, why my WCF service takes up to 40 seconds to get started? My service class is set to [ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)] I am running a thread inside this class. The WCF service gets initialized and opened within the context of this thread. The Initialization: private void InitializeWCFService() {      // initialize servcie base addrresses      this.SetupBaseAddresses();      //MetaData behaviour : allow HttpGet      ServiceMetadataBehavior metaDataBehavior = new ServiceMetadataBehavior();      metaDataBehavior.HttpGetEnabled = true;      // initialize service host instance      this.seviceHost = new ServiceHost(this, this.httpBaseAddress, this.tcpBaseAddress);      // add service endpoints and behavior.      this.seviceHost.AddServiceEndpoint(typeof(IUpdateServer), this.GetServerBinding(BindingType.Tcp), EndpointName);      this.seviceHost.AddServiceEndpoint(typeof(IUpdateServer), this.GetServerBinding(BindingType.Http), EndpointName);      this.seviceHost.Description.Behaviors.Add(metaDataBehavior);      // MEX endpoint      this.seviceHost.AddServic

Query Time out error?

Iam getting the querey time out error, when processing dimension in SSAS? The error Details is: OLE DB or ODBC error: Query timeout expired; HYT00; Unknown token received from SQL Server; HY000.\ can any one help me on this....how to resolve this?

SQLDataAdapter/SQLDataReader takes lot of time for executing a query

All, I have a webpage with 6 drop down lists. User can start with selecting any drop down list first, and then proceed in any order.  1. Assume when the 1st dropdown is selected first, rest 5 drop downs (except 1st) are filtered/updated based on the selected value in the 1st dropdown. 2. Assume user selects the 4th dropdown second, rest 5 drop downs (except 4th) will be filtered/updated but the condition would be based on both 1st drop down and 4th drop down. But while filtering the first drop down, only the 4th drop down is used as filter (self-filter will be ignored). The code works fine but I see a very bad performance hit in time when the user selects the fourth dropdown after selecting three drop downs. I tried using both SQLDataAdapter and SQLDataReader but neither used to solve the issue. The code takes a lot of time on one line in either of the code (which is highlighted below). When I run the query in SQLServer Mgmt Studio, the query completes in a few seconds.Using SQLDataReader: SqlConnection conn = new SqlConnection(CONNECTION); SqlCommand ObjCmd = new SqlCommand(query, conn); ObjCmd.CommandTimeout = 600; conn.Open(); SqlDataReader daSelTools = ObjCmd.ExecuteReader(); //This line takes a lot of time ddl.Items.Clear(); // Call Read before accessing data. while (daSelTools.Read()) { ddl.Items.Add(new ListItem(String.Format("{0}",daSelTools[0])));

SSRS 2008 R2 - Query builder freezes and needs to be shut down Report Builder 3 when attempting quie

Hi, In SSRS, using Report Builder 3.0 (SSRS R2), after choosing a Report Model as the report's embedded data source, launched "Query Deisinger…" from GUI to build data source in report.  First 1-2 attempts at querying underlying data source (Oracle back-end), are successful, but later attempts freeze application to the point where application freezes and overall Report Builder application needs to be forcibly shut-down. This system freeze has occurred when referencing tables containing a very small number of records as well as larger data sets.  Suspect that application freezing may be caused by timing-out of connection to back-end database. but I am not sure. Any suggestion to solve this issue? Thanks   Environment - Distributed deployment Server1(report_srv) --Windows server 2003R2 64bit, Installed sql server 2008R2 reporting service, configuration tools Server2 (report_db_srv) --windows server 2003r2 64bit, sql server 2005 for report server database

How can i show Transpose Query In SSRS ?

Hi, Any one help me for following Output. please tell immidiatly.  How I use this expression as follow: I have Database Table Like ----------------------------------------------------------- Date                 ShiftName                    Tons 7/21/2009               Day                          (Ans by Formula) 3 7/21/2009               Night                          (Ans by Formula) 4 7/22/2009               Day                           (Ans by Formula) 8 7/22/2009               Night       &

Get Query execution time

I need to find out the query execution time from the front end .Where should I insert the code for that.Please help.. I am using the bleow query: OracleConnection con = new OracleConnection(ConnStr); con.Open();  OracleCommand cmd = new OracleCommand("Stored_Proc",con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(); .................... ................ OracleDataAdapter oda = new OracleDataAdapter (cmd);  
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend