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


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

Report performance slow

Posted By:      Posted Date: September 23, 2010    Points: 0   Category :Sql Server
 

The below query is taking about 30 sec to execute in the management studio. I tried to visualize the Estimated execution plan and everything is a clustered index scan. The number of rows in the fact_dailycost table are 384962 and all the dimension tables have less than 1000 rows.

Please let me know how should I start tuning this query. This I'm creating a view and in the SSRS Report, I have a parameter Contract Number that is applied on the top of this view. For the report to display the results it is approximately taking 21/2 minutes.

SELECT

 

* FROM
dbo.Dim_Employee


View Complete Post


More Related Resource Links

Very Slow Performance on 64 bit SQL System

  
Hello, we have a Windows Server 2008 64 bit and a SQL Server 2008 64 bit. The server work and the speed with a quad CPU and 8 GB RAM is nice. But, we use a SQL script with xml (we make a XML paket for replication) is the speed very slow. The script need 40 - 60 min to finish. But the same Database and the same script on a Server 2008 32 bit and SQL Server 2008 32 bit on the same hardware need 22 seconds. We have all updates installed. Have anywere a Idea? Thank

Change performance point report color

  
This should be a simple question. I would like to change the color of the bars in the bar graph charts, and the color of the line in the line graph charts. I would imagine there is a way to do this. 

Slow performance when searching in "any part of field" of a SQL Server database using a Microsoft Ac

  
We have just migrated our back end database from Access to MS SQL Server 2008 R2. We have noticed better performance on our searches - unless we select to search in "any part of field" in Access (with Access' built-in search function - we use a mix of Access 2003 and 2007). This takes nearly 20 seconds to find the result, whereas before the migration it was taking 5-7 seconds (compared to instant results we now experience when searching for whole fields). The main fields we search are not large - usually just two words. Obviously it is better to search for a whole or start of a field, but this is not always possible. The contractor that assisted us in this project has told us that this "is just the way SQL works with Access". Is this really true? I find it hard to believe two MS products would have such a big issue between them. I presumed using SQL with an Access front end would be a common setup, which is why I thought this must be a problem with our setup. Is there any way to fix this speed issue?

SSRS Report Performance & Optimization

  
Can  any explain how would i Optimize a report performance.. I have few reports that are taking longer time to run. Thank youFM

Very slow performance with a query involving JOINS

  
I am experiencing extremely slow performance on a particular SQL query using SQL compact.  This same query completes in less than one second on my regular SQL server.  With SQL compact, I am actually not even sure if the query ever completes.  As I type this I am testing it from SQL management studio, and it's up to 17 minutes and 40 seconds still executing so far. Since I am not experiencing this on most of my other queries, I have little doubt the slowness is due to the volume of data in these tables, coupled with the fact that I am doing the joins.  I made sure that foreign key constraints are in place for the columns that the joins are based on.  From what I've read, this also ensures that appropriate indexes are created, although I am starting to wonder about that. This is the query: SELECT TOP (200) t.BusinessEntityID, t.BusinessName, t.IsTowTruck, t.IsHospital, t.TowTruckLastUsed, t.BusinessLocked, cn.Address1, cn.City, cn.State, cn.PostalCode, cnp.PhoneNumber as TopPhoneNo, GetDate() as LastUpdatedDate, GetDate() as CreatedDate FROM tblBusinessEntity t LEFT JOIN cnBusinessEntityLocation cn ON cn.cnID in (SELECT TOP (1) cnID FROM cnBusinessEntityLocation where cnBusinessEntityLocation.Active = 1 AND cnBusinessEntityLocation.BusinessEntityID = t.BusinessEntityID

MDX query performance is slow for complex logic implementation in Calculated Members.

  
We have implemented a calculated members which involves multiple calculated members  called in a nested e.q. Cal1 calls Cal2 and Cal2 calls  Cal3  and Cal3 calls Cal4 .  And when browing Cal1 through Excel it takes around 30 mins for getting result from a Partition having row count around 5 million. Follwing are the information on the scenario I am having: We are having partitioning in the cube. one partition contains around 4-6 million records Processers on Query Server: 4 quad core RAM on the Query Server: 64 GB Calculated members having simple calculation logics are giving results in no time. There is a Calculated members which calls 6 intermediate calculated in nested way and with small Data Set like 5-6 k rows it is running fine.   Any help will be much appreciated.   Regards, Sandeep

Slow Replication Performance

  
I have a fairly large table and I have updates 1.2 million rows on the Primary. The replication performance is very slow. There is a text column on this table. I am using profiler to see the performance on the target end. It says CPU time is @200ms and Duration is @210ms. The number of locks acquired on this table is very high. There is not much going on the target end. I cloned this table and performed an update on one row and it takes just 12ms. I am not sure what is going on. Any pointers to improve performance will help!  

CAST in delete query-Performance is so slow

  
Hi all, I've below join in the delete statementt, but it's taking so long the query run, the performance is really bad because of cast conversion, is there a better way, especially performace wise to fasten this below join, A.PHONE in nvarchar data type and DNC.PHONE_NUMBER  is in bigint data type.   A.PHONE=CAST(DNC.PHONE_NUMBER as varchar (15)) Thank you.

Slow performance of WCF service calling singleton (which calls COM+!)

  

Scenario:
WCF Service call routing to COM+ application.
netTCPBinding, throttling set pretty high.

[ServiceBehavior(InstanceContextMode = InstanceContextMode.Single, ConcurrencyMode=ConcurrencyMode.Multiple)] 

Service has two methods, one simply returns the string was input, the other calls the COM+ component and FOR TESTING returns the string "success" for every call. Therefore, both methods return a simple string.

Client starts 20 threads, each calling the WCF Service twice. (NOTE: A "first call" is made to prime the singleton service and also the COM+ component)

Using log4net and a Stopwatch to log the timings of the clien

Report exported to Excel 2007 is EXTREMELY slow once a filter is applied.

  

I am exporting a 12,000 row, 20 column report to Excel from SSRS 2008.  Once opened in Excel, everything is fine.  If I further filter the data through Excel (no external data connections) performance degrades.  If I filter down to say 20 rows, it nearly completely bogs down my entire PC.  I've emailed this spreadsheet to several others, who then experience the same issue.

If I export this same set of data from a different BI tool, such as Microstrategy, there are no performance issues what so ever.


Save a Personal View of Performance Point Services Report

  

Hi I am changing the view of my Performance Point Services report and want to save the same like it was possible in proclarity. I have been told that all features of Proclarity are there in Sharepoint Performance Point Services.

I  was able to find the decomposition chart but not the option of saving the modified view. Has this been removed,If yes can i use Object Model and write code for it.

Thanks,

Gautam


SSRS report runs slow

  

I developed a report with BIDS  which uses a stored procedure on MS SQL 2008 Database to populate its dataset used as Datasource. The report runs very slow (10 mins) and if the stored procedure is executed separately on MS Server Management Studio (SMS) it takes only a few seconds with the same parameters. Appreciate if anyone could help on this issue.

Thanks. VisaAde


SSRS 2000 reports when upgraded to 2005 report with subreport really really slow

  

We recently upgraded a report server and corresponding reports from SQL Server 2000 (reports 2003) to SQL Server 2005. After upgrade one of our reports which use two subreport is very very slow.

The sp's used for these all these reports are fast. All the sp's together takes about 40 secs.

Both in the BIDS environment as well as the report site the report is very slow.

We are using a database in which data is inserted through replicatoin. In all our sp's we have given 'with nolock' along with the table names.

Can anyone please help me here?


Slow performance with SQL Server 2005 executing parameterized queries with LIKE and an aggregate com

  

We have a slow-performing query and, after some analysis, have narrowed the cause down to using parametrized queries in conjunction with LIKE and aggregate comparisons used in the WHERE clause.  If we use a non-parametrized query (but keep the WHERE clause the same) the query performs much faster and, in fact, executes a different plan.  Using OPTIMIZE FOR UNKNOWN is not an option for us as we are using SQL Server 2005.  Obviously, we'd like to use parametrized queries to prevent SQL injection but the slow perf is unacceptable.  We need the LIKE to support wildcard scenarios.  If we need to we'll use dynamic SQL (scrubbed as much as we can) in lieu of parametrized queries.  We're wondering, however, is there another option that would give us the perf without sacrificing safety?  I can provide a sample database, query, plans, etc. if necessary...I'm assuming SQL Server generates a less efficient plan with the parametrized query as it can't make any assumptions about the parameter values...

Mike Bouck


Slow performance with SQL Server 2005 executing parameterized queries with LIKE and an aggregate com

  

We have a slow-performing query and, after some analysis, have narrowed the cause down to using parametrized queries in conjunction with LIKE and aggregate comparisons used in the WHERE clause.  If we use a non-parametrized query (but keep the WHERE clause the same) the query performs much faster and, in fact, executes a different plan.  Using OPTIMIZE FOR UNKNOWN is not an option for us as we are using SQL Server 2005.  Obviously, we'd like to use parametrized queries to prevent SQL injection but the slow perf is unacceptable.  We need the LIKE to support wildcard scenarios.  If we need to we'll use dynamic SQL (scrubbed as much as we can) in lieu of parametrized queries.  We're wondering, however, is there another option that would give us the perf without sacrificing safety?  I can provide a sample database, query, plans, etc. if necessary...I'm assuming SQL Server generates a less efficient plan with the parametrized query as it can't make any assumptions about the parameter values...

Mike Bouck


Viewing Video's in Sharepoint 2010--Will it slow down the performance for the end users?

  

I understand how to upload files to the media web part and creating an asset library, however; i'm trying to figure out where these files are being stored and the performance of the users.  I read the digital asset management library information on technet and couldn't find the exact answer i'm looking for. 

So, here's my question:  If I want to upload 10 videos to the asset library, they each may have around 50mb or less for the file size, and we want 200 plus users to access it, will this affect the performance of when these users hit the site?  We want to display the video's without having to buy a media server.  And the main question that will be asked is, who will host the content?  And, where is it residing?  My guess is that tt will be hosted in SP 2010.

I'm trying to incorporate a new intranet portal site for my company that will have a video to be played on the home page of the intranet site and the links will be located on the left and right side of the page with the video webpart in the center.  Will using the Blob cache and bit throttling be sufficient for this request?  I think it will be, I just want to make sure before we role this out.  Any help will be greatly appreciated.  Thanks.

 

 


Model3DGroup slow performance on adding items

  

Performance on our program struck on Model3DCollection.Add method

We often adds and removs GeometryModel3D from screen, and adding models to Model3DGroup.Children -> Model3DCollection takes 20% of performance.

Reduce the number of models addid to Model3DCollections is impossible. All objects and materials are freezed.

 Can someone helps?

It would be very useful to have a method like Model3DCollection.AddModels for adding a set of models without having to fire changed events.


Categories: 
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