.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

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

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

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?

View Complete Post

More Related Resource Links

SQL Server 2008 query execution time is different if i execute it in management studio or from code

When I execute query from software which i wrote in c# it required lots of time to complete but if I execute exactly the same query from management studio it will be done in seconds. In both cases execution plan is the same. 

Oracle Linked serve Query performance in 2000 vs 2008 R2 64 Bit

Hi everyone We have started to migrate one of our reporting systems from Sql 2000 to Sql 2008 R2.  One of the steps has been to test the perforance of certain Oracle linked server queries between each server.  We are finding on average 3 fold better perforance stats (in terms of query completion time) on the old server.  This should obviously not be the case.  The new server has signifantly more CPU/Memory/IO resources to play with, and it is 64 bit (not to mention its new!).  Here's what I got so far: Old server: SQL 2000 on W2000 both fully patched.  Old Dell Dual core with 3 GB of Ram running on two soft IDE Mirrored drives (yes I know... it sucks).  It connecting via the MS OLE DB provider for Oracle (9i client) New server: ESX VMware Server with 2 CPU's assigned, 8 GB of ram connected to large HP SAN.  CPU, ram and IO's have all been ruled out as the problem.  We've tried varying network cards with different results so we havent ruled that out yet.  Its connecting via the Oracle provider for OLE DB (11G 64 bit client) The linked server is an Oracle 9i fully patched server.  All three are on the same network backbone. Running a simple select * query on both servers returns the same number of rows (~76 000) .  It takes ~1:20 on the new server and ~0:20 on the old server. In looking at the wait stati

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

Decrease Query execution time

Hello friends,
I am facing problem since last 5-6 month in sql server in query executing it takes 10-12 mins to execute even if database is on local pc.
I want to decrease it and want to make it execte withing 30-40 sec..
It is getting data of whole month with subquery of 1-30 date and each joining 3-5 tables for 300-500 persons.
so any ideas....
Confused D'Oh! 
One thing that i can do some process in background and insert fetched data to the some other table and at the time of executing above query fetch data from that table.
if its possible then how can i do it?Is there any other way to solve this problem?

Thanks in advance.. Smile 

Query execution failed using SQL Server 2008 R2 (RTM)

I have a reporting services application that is returning an error running a report which uses an MDX query. This same report is running successfully on other systems using Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64). But this system returning the error is using this version: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64). I'm inclined to think that R2 is using different rules for the parameter because the position indicated below (4,33) is date parameter (@Date), and the same report is running fine in SP1.

When I run the report via the report manager, this syntax below returns the error shown at position (4,33) which is the position of @Date.


linkmember(StrToMember(@Date).parent.lag(4).firstChild,[Origination Date].[Date])


ApplicationException: Error processing alert report ...: System.Web.Services.Protocols.SoapException: An error has occurred durning report processing. --> Microsoft .ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. --> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset '...'. --> Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseExceition: Query(4,33) Parser: The syntax for 'Date' is incorrect.

At Microsoft.ReportingServi

Remote insert speeds query time from 5.5 mins to 3 seconds?



I have a query running in SQL Server 2005 sp2 that takes just over 5½ minutes to run despite my best efforts to speed it up.

It is an INSERT INTO local table SELECT FROM sql statement.  If I just run the SELECT part, it still takes over 5½ minutes.  The query is currently returning 2 rows of data.

However, If I change the table it inserts into to be an identical table on a remote server using a linked server, then it runs in 3 seconds????

I like the time it now takes, but I do not understand why it is so much faster inserting into a table on a remote server.  If anything, I expected it to take a little longer - anyone have any ideas?

These are examples of the query:
--Takes approximately 05:26 to complete
SELECT Field1, Field2, Field3

SQL Query execution time difference



I am running a query in which there is a primary key Mail_Id

SELECT  TOP 1000 Mail_Id, MailUID,FromAddress,ToAddress,CcAddress,Subject,SentDate FROM MailIdSystem

where Subject LIKE '%hi%'


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?   

Count Query Execution


I have a class that being used to connect with the DB. Now I want to count how many times each web request executes the queries, but I've no idea where to store the counted value. I mean, Session wont, ViewState wont work as site also have webservices. What else I can use ?

can anyone tell me why setInterval is not working properly i want to display the time every seconds,

setInterval is suppose to execute the script every second there getting the most uptodate time of day , but it does not update it stays the same
      <script type="text/JavaScript">

          function interval() {
              setInterval("time()", 1000);
          function time() 
              var d = new Date();
              var time = d.getHours();
              var time2 = d.getMinutes();
              var time3 = d.getSeconds();

              document.write(" : ");
              document.write(" : ");
  // -->
      <input type="button" OnClick="interval()" value="Click Me!"/>


Slow query execution

Hello, I have a strange problem that suddenly happened. I have a Windows 7 Ultimate OS running SSMS 2008. When I run a query or an stored proc against a server (the server is remote and passes through a VPN tunnel) it can take me 40 seconds to get the query to complete. But when I run a similar query against another server located at the same location (go through the same tunnel) I get the results in 1 second.  I select about 400 rows on both servers. The slow response comes from a server that runs sql 2005 and the fast response is sql 2008. I asked a friend that works in the same office with me and uses the same connection as I do to run the same queries and he gets the result in 1 second. Does anyone has any ideas what can cause such problems?  I tried to remove and reinstall all the sql server components from my computer but nothing helped? Thanks, Oded Tal

SQL Server 2008 Powershel trying to access SQL Server 2000

Below is a script from my SQL server 2008 PowerShell trying to connect a SQL Server 2000 instance. PS SQLSERVER:\SQL\SS2K8\DEFAULT\databases> cd \ PS SQLSERVER:\> cd sql PS SQLSERVER:\sql> cd MySS2KMachine PS SQLSERVER:\sql\MySS2KMachine> cd default PS SQLSERVER:\sql\MySS2KMachine\default> cd Databases PS SQLSERVER:\sql\MySS2KMachine\default\Databases> dir and I see a list of all databases on the default (SS2K) instance. I also see al instances on the machine: PS SQLSERVER:\sql\MySS2KMachine\default> cd .. PS SQLSERVER:\sql\MySS2KMachine> dir Instance Name ------------- DEFAULT MSSQL2005 As you can see from the above, there is also a SQL Server 2005 instance installed on the MySS2KMachine. Now, if I try the same thing for a Sql Server 2000 instance on another server (Windows 2003 R2 with SP1) with no SQL Server 2005 or higher installed on the machine, I get the error below: PS SQLSERVER:\> cd sql PS SQLSERVER:\sql> cd MyPureSS2KMachine WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'MyPureSS2KMachine' failed with the following error: SQL Server WMI provider is not available on MyPureSS2KMachine. --> Invalid namespace Set-Location : Cannot find path 'SQLSERVER:\sql\MyPureSS2KMachine' because it does not exist. At line:1 char:3 + cd <<<< MyPureSS2KMachine PS SQLSERVER:\sql> So it see

Query on XML datatype in SQL Server 2008

Hi All, In table I have column with XML datatype. I can able to read the below XML tag. SET   @Title = CAST(@XMLContent.query('data(root/chemicalName)') AS VARCHAR(50)) <root><chemicalName>Sulphuric acid</chemicalName></root> but how can I read (in SQL Server 2008) with multiple rows in it, and how to know the list of child nodes and data of it dynamically? <Root> <Row> < CurrencyCode>GBP</CurrencyCode> < CurrencyName>POUND (STERLING)</CurrencyName> </ Row> <Row> < CurrencyCode>INR</CurrencyCode> < CurrencyName>Rupee</CurrencyName> </ Row></

Why upgrade is required from SQL Server 2000 to SQl Server 2008

Hi all Can some body share with information about "Why upgrade is required from SQL Server 2000 to SQl Server 2008?"   This question was raised by my Boss why upgrade is required.   I stared saying   My: there is no more   support for SQL Server 2000 from Microsoft   Boss: what you will do  as DBA, Why Required Microsoft support   My: 2008 having very good features where 2000 not have...like Compression, resource governing. .Etc..   Boss: do you mean Database backup compression take full backup and compresse it using ZIP   Finally He (Boss) told me that give document proof why I have go for SQL Server 2008 and what down time you required?   Some body help me why really SQLServer 2008 is required and what are benefits   I have a link; please provide your greatest ideas so that I can convince my Boss.   http://www.mssqltips.com/tip.asp?tip=1462   share your greate Ideas please....   Thanks in Advance     SNIVAS

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?

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