.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

Large IN query slow to run in SQL CE 4

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :

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?




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

Form Display is very slow to display, (using large multi-select lists)

Hi, I am looking for some suggestions on how to speed up a few very slow forms. We have created some Leaver and Joiner type forms for our IT guys. They contain some lookup data , and we need to pick multiple values from a hidden list. Unfortunately this (Systems/Roles) list is large (3000 rows and growing). On a couple of forms there are 2 of these multi-select boxes, and things are getting pretty slow to display. Any suggestions? ---------- Background Info / things I have tried: The forms are based on Site content types and Site Columns. A set of 5 Combo boxes is slower than one Multi-select list. SPDesigner is available but I do not have Central Admin rights I am not alowed to write/install custom code on the server. (Visual Studio code / Codeplex code / extra Features etc) :-( I have had to resize the Multi-select list, so am using the SPServices JQuery call to make the multi-select box wide enough for the text. But this is extra overhead so I might have to back that out / replace with a fixed width for the multiselect box (if I can find it's id, so I can do that in Javascript). Filtering the Multi-select box is possible, but only after the page has initially loaded (it seems) (again I tried the SPServices Jquery call) The Company intend to add more Content types, so I am trying to avoid a custom Newform.aspx for each type (Leaver / New Joiner / Transfer/ C

Refcursor compared slow to sql query

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

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.





Inefficient Query Plans due to Large Amounts of RAM (KB2413549)


Does anyone have any more details on this KB?  MS states this happens when you set max memory to a "large value".  A hard cutoff here would be more helpful.  Also, on the versions list in the KB, SQL2008 Std is not listed, although SQL2005 Std edition is listed.  Any help here would be great.  Thanks.



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 processing due to large number of threads.


Hello Friends!

I am working on a project in which I am creating around 25 threads in my application. All the threads are communicating with each other. The problem is that due to large number of threads the application is executing very slow and my CPU utilization reaches around 100%.  How can I overcome this problem? Can I create multiple programs instead of threads, if yes then how will they communicate with each other?


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

Large document library is slow


We have a requirement to move our personnel files from a shared folder to Sharepoint and in the process attach some additional metadata fields. I have accomplished this via the Document Center template on SP 2010. In order to simplify the migration process, I have pre-created the folders (about 1,800) via the "Open with Explorer" option for the library - basically wrote a batch file to create the folders directly to the SP location. The 1,800 folders I have created are all blank - no files in them yet. 


When I try to upload a file to any of these folders, the Upload Document dialog freezes after I click on the "Choose Folder" button when specifying the Destination Folder. It eventually comes back after a few minutes with my folder listing - but its incredibly slow. There are no documents uploaded yet - all I have are the 1,800 folders. How can I fix this? Is 1,800 too close to the mystical 2,000 folder/item limit for doc libraries? Can't I fix this with indexing?


Any help would be greatly appreciated.



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