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

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

Query takes too long to run

Posted By:      Posted Date: September 27, 2010    Points: 0   Category :Sql Server
Every time I'm trying to run a query it is taking too long to run the simplest query, in order to run the queries, I have to go to the Activity monitor and kill off processes that have been sitting even though they are not running. Is there a way the processes can be killed off when they have finished executing?

Thank you

View Complete Post

More Related Resource Links

Query takes too long to run

I have the following query, which does an update statement but everytime I run it it took about 1hr and 20 mins to run so I created Indexes because that would solve the problem. After creating the Indexes I re-run the query but it was the same so I checked to see if it was blocked by running the following query and under the spid it had the same spid in under the blocked column, the lastwaittype is PAGEIOLATCH_SH. UPDATE table SET column1 = column2 FROM table1 JOIN table2 ON column1= column2 Thank you

Query takes too long to run

I have the following query, which does an update statement but everytime I run it it took about 1hr and 20 mins to run so I created Indexes because that would solve the problem. After creating the Indexes I re-run the query but it was the same so I checked to see if it was blocked by running the following query and under the spid it had the same spid in under the blocked column, the lastwaittype is PAGEIOLATCH_SH. UPDATE table SET column1 = column2 FROM table1 JOIN table2 ON column1= column2 Thank you

STSADM ActivateFeature takes too long



I have a solution installed to a web application, this Web App has a lot of Site Collections, these site collections were created with different versions of the wsp package. What I need is a way of activating the new features on the already provisioned site collections.

At first we made a Web Application scoped feature that went through all site collections that were using our template and then activated the features on each site collection but it was giving us a whole bunch of trouble were the code would skip entire site collections or enter the site collection, changing a field that we used for control and not activating anything... Now we have a Web scoped feature that activates all the features within the site collection at either Web or Site scope that we specify through an XML. This works like a charm for every site collection.

The problem is that we need a way of activating that feature on every site collection so I did a batch file that runs the STSADM ActivateFeature to do this. This also works properly but seing the many site collections that we have it is taking more than an hour and a half to finish the batch; is there a way of optimizing this so it runs quicker?

I think that the batch is actually waiting untill the feature is done activating to start to activate the feature on the next site collection, is there a way of a

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

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

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

Full database backup takes so long

Today several full database backups took so long. Normally it took 2 hours to backup. But today the 5 backups didn't finish by the end of the work day. The backups went well until yesterday. I don't know what happened overnight. Is there a performance tool embedded in SQL Server? How can I find out why the backups take so long? What could be the possible reasons? Thank you,  

Stored Procedure takes longer than the query.



I'm stuck up in a situation where my sp takes longer time but the query takes lesser time. I went through all the posts mentioned in the T-sql Forum and i tried all the possible resolutions mentioned there but it did not help me.

I have a sp which has around 20 parameters and i tried assigning them to local variables as mentioned in various posts but that did not help me.

Could you please help me out with this problem?







How to build long query string



I know that it is basic questions, but I am stuck here.

I have TableOne have fields: USERNAME, PID, GID, TID, SID, NEWSID. I need to write query string to check if those value is already in the table. If not, insert those value to the table.

string AddUserName = UsernameToken(GetUsername);
string InsertString = "insert into TableOne(USERNAME, PID, GID, TID, SID, NEWSID) values (?, ?, ?, ?, ?, ? )" + "  WHERE NOT " + " ( "  [USERNAME] = '" + AddUserName + "' + " PID = '" + DropDownBoxPID.Text.Trim() +
 '" + " AND " + " GID = '" + DropDownBoxGID.Text.Trim()  +
 '"  + " AND " + "  TID = '" + DropDownBoxtid.Text.Trim()  +  
 '" + " AND " + " SID = '" + Textboxsid.Text.Trim() +  
 '" + " AND " + " NEWSID = '" + DropDownssid.Text.Trim() +
 '" + ")" ;

However, I got the many errors:

Error    1    Too many characters in character literal  

Error    3    Newline in constant   

Is any way to debug this? As always, your help is highly a

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.

ALTER DATABASE AdventureWorks SET ENABLE_BROKER takes too long to finish, how long does it run

try to run ALTER DATABASE AdventureWorks SET ENABLE_BROKER ;
but still running after 8mins, is this normal, what is approximate time? can it be cancelled?

Succeeding loop of the same query takes longer than the previous one


Hi, I'm having trouble with an itermittent problem I'm facing with our site. See I'm running an sql query that accepts date range as variable. When I run the query in sql manager it runs in less than a second no matter what date range I put in. I have a function that loops a dozen times to get records for the past days getting a whole day's record for every loop. It would usually work fine but there are times that the succeeding loops slows down. I made it print out the duration for each loop and this is what I got in seconds.

start: 10/5/2010 12:00:00 AM|End: 10/5/2010 11:59:59 PM|0.5460009 <-- should be more or less the normal speed of each loop
start: 10/4/2010 12:00:00 AM|End: 10/4/2010 11:59:59 PM|14.3208252
start: 10/3/2010 12:00:00 AM|End: 10/3/2010 11:59:59 PM|14.5860256
start: 10/2/2010 12:00:00 AM|End: 10/2/2010 11:59:59 PM|14.1024248
start: 10/1/2010 12:00:00 AM|End: 10/1/2010 11:59:59 PM|4.6956082
start: 9/30/2010 12:00:00 AM|End: 9/30/2010 11:59:59 PM|5.4444096
start: 9/29/2010 12:00:00 AM|End: 9/29/2010 11:59:59 PM|8.3304146
start: 9/28/2010 12:00:00 AM|End: 9/28/2010 11:59:59 PM|11.0760195
start: 9/27/2010 12:00:00 AM|End: 9/27/2010 11:59:59 PM|20.1708354
start: 9/26/2010 12:00:00 AM|End: 9/26/2010 11:59:59 PM|20.8884367
start: 9/25/2010 12:00:00 AM|End: 9/25/2010 11:59:59 PM|15.444027

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', 

SSRS taking a long time to run an MDX query



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,


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

Breaking long query into small one using SQLDataSource (using Temp table)


Currently, I have long script which does major processing and then puts data into temp table.

Finally I read data from this temp table to show on the chart, based on user's selection of parameter. So I can divide my query into two parts and while I am researching this, I will appreciate any pointers/guidance.


Is it possible to do processing in one Datasource and read table from second datasource?

Is it even possible?

If not, what is recommended method for this (breaking large processing into small one)?

SSIS - OLE DB from VIEW takes long time



I have a question regarding SSIS 2008 and how it handles views. I hope the answer for this question will be a quick "Get a hotfix" heh.


I am experiencing a very odd behavior with setting up a View as a data-source in OLE DB. I have set up a a very simple package (for experimenting) with a Data Flow Task that has only two components, Data-Source and Data-Destination. I have set the data-source to a view and the destination to a table in the same database.

Note: I am developing the package on the server itself.


  • While trying to preview data-source I get an error, "Query Timeout expired (Microsoft SQL Server Native Client 10.0)
  • When executing the package Validation, Pre-Execute phases are complete but no rows are passing through the buffer. I have waited over 12 hours before stop the job. Checking the monitoring verified that IO was on 1. And that no object was locked , which may have caused the package to run so long or not to retrieve data.
  • When connecting to SSMS and doing export of data from the view - it also retrieves no data and the process seems to be stuck.


Additional data:

  • T
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