.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

Refcursor compared slow to sql query

Posted By:      Posted Date: September 15, 2010    Points: 0   Category :ASP.Net
Hi, I have created stored procedure with refcursor in Oracle 11g. The execution time of the procedure takes nearly 3 mins whereas when i execute only the select statements it takes nearly 35 mins.  How can tune my stored procedure when using refcursor.   Thanks, gayatri

View Complete Post

More Related Resource Links

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?   

FAST search query is slow...

Hi, we are using fast search for sharepoint 2010. We are crawling an external data source and this is used for search queries. we have around 30 metadata properties and all are full-text indexed. however, the search operation is slow for a database of 5k records in intranet (webserver and database server are in different geographic regions). typically a 100 item search takes 9-10 sec. it get worse over internet and the search query varies from 10-45 sec for 100 items. Any pointers will be helpful. thanks.  

Slow load time of custom assembly on x64 compared to x86.

I am developing an application that automatically generates assemblies using the CSharpCodeProvider (.Net Framework 3.5).  These assemblies contain a single class with a very large number of local variables and methods.  After compilation I create an instance of this class (using Activator.CreateInstance) to be used elsewhere in the application.  When the application is deployed on a 32-bit system (tested on Win 7 Prof 32-bit and on Win XP) the CreateInstance method returns after 1 or 2 seconds, i.e. it first JITs the assembly before creating the instance.  However, on a 64-bit system (tested on Win 7 Home 64-bit, Win Server 2003 64-bit and Win Server 2008 64-bit) CreateInstance takes up to 5 minutes to return. Is there any reason why it would be that much slower on a 64-bit machine? I have tried many different options when compiling, such as setting the platform to x64, using the /optimize flag, etc, but none of these have made a difference. I also tried calling PrepareMethod on all the method in the class.  When I do this in the 32-bit environment, it takes approximately 10 seconds.  In the 64-bit environment it takes more than 3 minutes. As a last resort I tried ngen before constructing the object, but this made no difference in the execution time.

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

BIDS Query Designer very slow in VS 2008

I am in the process of upgrading my Report Server projects from 2005 to 2008.  These reports use an Analysis Services 2005 data source and have queries that are anywhere from 20 to 100 lines of MDX.  After upgrading a report, when I open or save a dataset in the Query Designer, it takes up to 20 seconds.  In 2005, this would happen almost instantly. Running SQL Server Profiler, I see that there is an extensive number (600+) of Discover events going on with the Analysis Services server while the Query Designer loads. My version of Visual Studio is 9.0.30729.1 SP and I am running Windows XP SP2. Thanks, Mitch

BIDS Query Designer very slow

BIDS Query Designer seems to be very slow while saving the query or opening the query designer when you've got quite a lot of Query Parameters. When I remove all the query parameters, the query designer seems to be running smoothly. Editing a query with about 10 to 15 query parameters takes ages this way! Is there something I can do about this?!

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

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.

2005 query runs slow in 2008



I'm running some of our stored  procedures, written on 2005, on a test 2008 server

One of the stored procedures runs much slower on 2008, the execution plan is considerably different as well.

I've tried changing the compatability level, to no effect.

I'm wondering f there are any general actions I can take to mitigate this issue, or whether I'm just going to have to deal with these problems on a one by one basis.





Very Slow query



When doing the first initialization from a mobile device  using SQLCe i'm getting always a timeout.

After tracing the server i found the query that is taking more than 3 minutes.



@sync_last_received_anchor as

@sync_client_id_binary as


[T_DIST_Service].[ServiceID], [ServiceCode], [ClientID], [ServiceTypeID], [InvoicePlanID], [Notes], [ClientBarCode], [ReceiverCode],

[ReceiverName], [ReceiverAddress], [ReceiverPlace], [ReceiverPostalCode], [ReceiverPostalPlace], [ReceiverContact], [Weight], [VolumesQty], [Amount],

[ShipFromCode], [ShipFromName], [ShipFromAddress], [ShipFromPlace], [ShipFromPostalCode], [ShipFromPostalPlace], [ShipFromContact], [CollectScheduleDate],

[DeliverScheduleDate], [RelatedServiceCode]

slow query, proper db setup, indexes, tables

I have a large set (~10 million) of time series data (date, time, and value) stored in text files.   These dates and times may repeat.   I would like to move these to a database (or several databases) and access them from a program.  I have sort of done this, the problem is that retrieving the data takes too long (say 30s).  I'd like to reduce the time it takes to access the data.

The steps I've done are:

1) Create a new database using SSMS.
2) Read in all of the year 2010 data into a new table
3) Accessed the data with the LINQ query from c#:
var timeSeriesData =
                from point in context.2010s
                where (new DateTime(point.Date.Year, point.Date.Month, point.Date.Day, point.Time.Hour, point.Time.Minute, point.Time.Second, point.Time.Millisecond)).CompareTo(_MinimumDateTime) >= 0
                    && (new DateTime(point.Date.Year, point.Date.Month, point.Date.Day, point.Time.Hour, point.Time.Minute, point.Tim

Slow query performance after upgrading from SQL2000

Slow query performance after upgrading from SQL2000

We are having query performance issues with our data warehouse server after upgrading from SQL2000 to SQL2008R2.

Our problem is that *some* queries which ran in 5 seconds in SQL2000 now take 11 hours in SQL2008R2.

Worth noting that we get the same problem in SQL2005.

The queries which are stalling usually involve subselects. Some of those queries can be rewritten to remove the subselect but other queries are **dynamically** generated by an application which can not be changed.

We have tried altering the following server settings:

- Max worker thread (0 or 256)
- Degree of parallelism (0 , 1 or 4)
- Max memory (Reserve 1GB memory for system)
- Cost threshold (for parallelism) 1,5 or 20

Some of the other things We have tried:

- Adding new indexes
- Update statistics
- Rebuilt all indexes
- Migrating original data into new tables

SQL2000 and SQL2005 instances: 4CPU, 4GB RAM, Windows Server 2000 SP4
SQL2008R2: 4 vitual CPU cores, 6GB RAM, Window

performance issue - slow query


Hi  - I have a query which is running very slow under a certain condition. I have 2 tables, Employees and EmployeeGroups. Employee has employees personal information and employeegroups has a mapping of each employee to the group it belongs to. One employee can be a part of multiple groups. The problem is with the below query.

SELECT emp_GUID, emp_firstname,emp_LastName
FROM Employee E join EmployeeGroups ON Emp_Guid=EMG_Emp_GUID
where EMG_GUID In ('multiple group GUIDs') and emp_Lastname like 'abc%'

EMG_Emp_GUID - Employee id of the employee

EMG_GUID - ID of the group employee belongs to.

The query runs well with less number of groups but it slows down as the number of groups i.e data increases. The tables is well indexed.

I am running out of ideas on this. Need help.

Cheers!!! SqlFrenzy

Large IN query slow to run in SQL CE 4


I'm running a query along the lines of 'SELECT * FROM Table WHERE Field IN (a, b, c, ...)', where my IN list contains 1000 entries.  When I run this query through Visual Studio, it's instant, but when I run it using SqlCeCommand.ExcuteReader(), it takes minutes.  I copied the query from code into the query window, so it's identical.  The field for the IN is indexed, but the table is empty.  Anyone have any ideas on this one?




MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

How to Encrypt Query String Parameters in ASP.NET

Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt
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