.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

Nested Views execution time, how long does it take to "unpack" the SQL?

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

We have the case where in a reporting solution for an application it was decided to build a series of reporting views, fair enough. These views are layered, nested several levels deep at times and, not unexpectedly, as our customer's data grows so these views slow...

To complicate matters further, the report SQL may then join several of these top level reporting views to produce the data it needs.

In a recent support request for a slow performing report we ripped the sql out of the report, pasted it into SSMS and while investigating found a join error in the reports SQL that took execution time from ~ 2 minutes down to about 6 seconds...  Regardless of what we did after that we were unable to reduce the time below 6 seconds using our reporting views. Rewriting the SQL against our base tables takes a fraction of a second to return the needed information.

What I need to do is demonstrate where the extra time in getting the results from the views is spent.  My guess is that it is in untangling all the views and then ending up with a less than optimal query plan. Is there anyway to measure how long it takes the server to "unpack" the nested views into a single SQL statement before it can be executed? Knowing how much of the 6 seconds this is accounting for would be helpful to us in our decision making process.



View Complete Post

More Related Resource Links

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

improve sql execution time

i need to select one of the record out of 10 thourands record,but when the program execute the sql , it takes a long time to extract data and my PC indicate low memory alert.   how can i improve the execution time to select the data from sybase

Long execution times for Search pages even after 4th or 5th user has accessed the same page.

Hi Guys We have a Sharepoint 2010 farm with two NLB web fronts ends, a Index server and a two box SQL cluster. The same problem also occurs on a dev box which has Sharepoint 2010 and SQl server running on the same single box. When a user access the search or advanced search page we get long execution times of between 15 and 30 seconds.  The strange thing is that the user will have a slow response the first time, then they are fine for a period of an hour or two and then they hit the slow response again.  Below i have copied info from the developer dashboard.  Does anyone have a suggestion of where to start tackiling this issue. Thanks 1st Run.  (Note this site was accessed by 3 other users first) BeginRequestHandler (0.05 ms) PostAuthenticateRequestHandler (0.06 ms) PostResolveRequestCacheHandler (18.48 ms) GetWebPartPageContent (16.87 ms) GetFileAndMetaInfo (15.57 ms) GetWebPartPageContent (35.25 ms) GetFileAndMetaInfo (34.99 ms) GetWebPartPageContent#1 (89.27 ms) GetFileAndMetaInfo (89.06 ms) Add WebParts (3975.17 ms) Search Box (3974.54 ms) SearchBoxEx.OnLoad (15426.56 ms) SearchBoxEx.HandleContextualScoping (0.01 ms) UserPreference.GetUserPreference (15418.52 ms) UserPreference.GetFromCache (0.04 ms) SearchServiceApplicationProxy.GetUse

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);  

How to do Time bound workflow execution

The requirement is that the workflow should complete in a time specified as a workflow parameter, if it is not able to execute all the activities , it should terminate itself. Can anybody give me an idea how i can do that. regards

SharePoint 2007 + PowerPoint gives "Contacting the server for information" for a long time

I've never had this issue for but today all of a sudden I tried to open a PowerPoint document posted to a document library on our SharePoint 2007 site and it gave a message saying "Contacting the server for information" for a long time.  It eventually opened but not until after 5-10 minutes.  Does anyone know what might have caused this?  The file itself is pretty small, only 70KB, and as I said we haven't had this problem before.

Why does it takes such a long time to load a parameter?

Hi all,   I have a parameter which is filled with a set of about 90.000 rows. When running the report, it takes a long time to view the report. While it loads the report the CPU on the client is getting up with 50% and the memory stacks 300 MB. I think that I am doing something wrong as when I run the query directly at the cube, it takes just 9 seconds. Does anybody have coped with this problem? If so, what are the best practices in this? Your help will be greatly appreciated! Daniel

When not open MOSS 2007 site for long time...


Hi all,

I have a web site under constructing using MOSS 2007, when site is not opened for long time, and come some one to open it, MOSS 2007 return error: Error occured, if you have a permission go to webpart editing page to solve the problem...

I don't know why this happen, and how to solve it?



Performance of XBAP in .NET 4 degrades after a long idle time.


A few of my clients are using XBAP application running over .net 4 client profile in IE8. The operating system they use is windows XP (service pack 2 or 3) on all their machines. We've had no issues during install of the .net framework or our XBAP application but many of them are complaining that after keeping the xbap open for a long time their computer begins to slow down considerably. This specially happens when program is left open without using it for some time. On my own development system which is a windows 7 machine with full .net framework installed i haven't experienced such issue.

I checked for resource usage of the application but there is no significant increase in memory usage. The CPU consumption however becomes quite high, which I suspect is the reason for degradation of performance during these episodes. If this was a single case i would have ignored it for some glitch on their system but this is happening on many machines and I suspect this has got something to do with .net framework on xp machines.

I don't see any reason for why this should be happening. Is there any documented bug, report related to this ? Are there any compatibility issues of .net 4 with other common programs running in xp?  Do you have any idea on why this could be happening ? What kind of data should i provide you s

Execution Time changes in a fashion


Hi All,

Here is my problem,

I have an sp to retrieve max 1000 records(from joining 6 diff tables & conditions too). 

suddenly a bulk amount (millions)of records inserted to the tables.

Here is the statistics of the sp executed in an order :[any way result will be top 1000]

0 seconds for querying a dept of 10500 records without extra parameters.

0 seconds for querying a dept of 10500 records with extra parameters.

4 seconds for querying a dept of 700500 records without extra parameters.

8 seconds for querying a dept of 700500 records with extra parameters.

now the problem starts

3 minutes for querying a dept of 700500 records without extra parameters.[which previously executed in 4 seconds.]

Thanks for looking into this,

Any solution/suggestion will help me to recover from mental stress :(


Let the users know that something is being processed in the background as they wait for a long time



I have a listbox that contains a list of items. The user is required to pick the items from this listbox and add them to another which is called selected items listbox. When doing this he needs to check for any duplicate entries as teh available listbox can contain duplicates but not the selected items list. I have 2 buttons "Add" and "Add All" to add items from available to selected list. The code for Add All button is:




Delivering Replicated Transaction take very long time


Hi All

I have pull transactional replication under SQL 2000 to SQL2005, SQL 2000 as publisher and SQL2005 as Subscriber.

I have setup replication with 28 distribution job agent,i used pull transactional replication. every agent contains 1 or more table to be replicate. i have problem with 1 agent replication with 1 table to be replicated, it call ARAccount_Receivable. since initial snapshot distribution agent show message "Delivering Replicated Transaction". the distribution agent was running for 11days, but the data have lastupdate since 3 days ago, means replication was running but very long time delay. this table size have only 32 million data, if compared to the other table with have size more than 100 milion data was running normal.

Below the agent distribution profile 

-BCPBatchSize : 100000

-CommitBatchSize : 1000

-CommitBatchTreshold : 1000

-HistoryVerboseLevel : 1

-KeepAliveMessageInternal : 300

-LoginTimeOut : 1000

-MaxBcpThreads : 10


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..



Query execution time from 1 second in SQL 2000 to 20 Seconds in SQL 2008 R2


When I ran this query from SQL 2000 it takes 1 second. now in SQL 2008 R2  the same query is 20 times slower.

This is the query and the numer of records and the time in SQL 2008 R2:

SELECT * FROM dbo.CxP_Saldos 
0 seconds, 923 Rows
SELECT * FROM S_Conta.Cuentas
0 seconds, 662 Rows
SELECT * FROM S_Conta.Cuentas c INNER JOIN dbo.CxP_Saldos s ON c.id = s.CtaProv
18 seconds, 923 Rows

how can I submit the actual execution Plan?

Dealing with timeout on long SP execution


I am executing a stored procedure that takes about 75 seconds to execute. My page times out while it tries to wait for the resultset. I tried adding Connect Timeout=200; pooling='true'; Max Pool Size=200" but it still times out.

I guess I do not necessarily need to wait for the result set. Is it possible to just send the SQL command with something like (making it up:) command.ExecuteOnServerAndDoNotWait  ?


Here is my code:


Using connection As New SqlConnection("Data Source=Rosebud;Initial Catalog=lovebug;Persist Security Info=True;User ID=notME;Password=L0v3ly;Connect Timeout=200; pooling='true'; Max Pool Size=200")
            Dim command As New SqlCommand("exec CalculateDataUsage @Customer,@CNumber,@Month,@Year,@TypeOfEntry,@Usage", connection)
            'CalculateDataUsage] @Customer varchar(50),@CNumber varchar(20), @Month varchar(5), @Year varchar(5), @TypeOfEntry varchar(20), @Usage numeric OUTPUT
            command.Parameters.Add("@Customer", Data.SqlDbType.VarChar)
            command.Parameters.Add("@CNumber", Data.SqlDbType.VarChar)
            command.Parameters.Add("@Month", Data.SqlDbType.VarChar)
            command.Parameters.Add("@Year", Data.SqlDbType.VarChar)

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

Closing a WCF connection takes a long time when using SSL


I have a very puzzling problem closing WCF connections. We utilize several different protocols for different customers. The most common is net.tcp. We now have a customer that requires SSL encryption of their WCF calls. It does seem to work, but exhibits some strange behavior when we close the connection.

Closing the connection takes a long time. This could be caused by any number of problems, but the strange thing is that the more data that was transmitted on the connection, the longer it takes to close. Connections that get a small amount of data close reasonably fast, but if a large amount of data was transmitted, closing the connection can take as long as actually retrieving the data. It almost seems as if the data is being retransmitted back to the server for verification before the connection is closed.

We are using reliable sessions and Binary over HTTP protocol.

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