I've inherited an application that has a query that typically bombs out due to a deadlock in SQL Server 2005. I know where the resource contention is, and why the deadlock happens, and why SQL picks this query to fail, so that's all cool with me. My problem is that I'm in the process of modifying this query to be a little less resource intensive, and I'm getting two different result sets.
The original query is returning 1234 rows, and my first replacement query attempt is returning 930. My replacement query attempt is using a derived table (subquery) rather than copying into a temp table (as the original query is doing). I understand what the missing rows are and why they are not in the result set, so my question is What is it about my replacement query attempt that is so different from the original query?
I appreciate any advice anyone might have to offer.
Best,
B.
/* original query */
Code Block
select * into #response
from response
where MidEndFlag = 'End'
SELECT COUNT(#response.ResponseID) AS TotalCount, CourseInfo.Subject, CourseInfo.Course, CourseInfo.Sect,
CourseInfo.Term, CourseInfo.Enrollment, (COUNT(#response.ResponseID) / (CourseInfo.Enrollment * 1.00))* 100 AS Percentage,
#response.MidEndFlag
FROM #response FULL OUTER JOIN CourseInfo ON #response.Term = CourseInfo.Term AND #response.Subject = CourseInfo.Subject AND
#response.Course = CourseInfo.Course AND #response.Sect = CourseInfo.Sect
WHERE (courseinfo.InstructorSelectionEndFlag = 'Y') and CourseInfo.Enrollment > 0 and courseinfo.term = @term
GROUP BY CourseInfo.Subject, CourseInfo.Course, CourseInfo.Sect, CourseInfo.Term, CourseInfo.Enrollment, #response.MidEndFlag;
/* end original query */
/* my first replacement query attempt */
Code Block
SELECT COUNT(r.ResponseID) AS TotalCount, c.Subject, c.Course, c.Sect, c.Term, c.Enrollment,
(COUNT(r.ResponseID) / (c.Enrollment * 1.00))* 100 AS Percentage, r.MidEndFlag
FROM (select * from dbo.Response where MidEndFlag = 'End') r full outer join CourseInfo as c ON
r.Term = c.Term and r.Subject = c.Subject and r.Course = c.Course and r.Sect = c.Sect
WHERE c.InstructorSelectionEndFlag = 'Y' and c.Enrollment > 0 and c.term = @term and r.MidEndFlag = 'End'
GROUP BY c.Subject, c.Course, c.Sect, c.Term, c.Enrollment, r.MidEndFlag;
I'm trying to swap out old partitions and getting "An error occurred while processing 'AltFile' metadata for database id 12 file id 605" 605 is missing from sys.sysfiles. I've tried adding new file groups since it seemed to be assigning them in that range to allow the command to find a match. Once created and I issue the alter command the file id of the target file changes to something else in the missing range.
The file id values seem to be managed solely by sqlserver so I'm not sure what to try. There are hundreds of files with millions of rows and the method has been used problem free for years. I do occasionally get "unable to remove file because it's not empty" once in a while which may be related. I wind up having to shrink those and leave them in existence.
The target file group has an existing file id value when you join sys.sysfiles using the filegroup name.
I partition data in 2 tables on one filegroup per day. I swap out parition 1 each day which makes the new earliest day's partition the new partition 1. Different databases have different day ranges depending on requirements.
I have an application which works with SQL Server. When the application attempts to load a page it needs to run certain queries. When it attempts to run a particular query it fails but I catch the exception and then log it. The following is logged:
System.Data.Ole.Db.OleDbException: Login failed for user 'CWAMB01DWH01ASPENT'.
Now what I don't understand is that this is the work group that ASPNET is run under, and I am running other queries to the database via SQL Server authentication. Why am I getting a failed login for this workgroup? Do I need to create a new Login for the Server in SQL Server, and then create a new user for the database with the same username as the Workgroup name? If so, then how does the password work for the SQL Server as the workgroup (CWAMB01DWH01ASPENT) obviously doesn't have a password.
Thanks, and I hope I have explain my problem clear enough.
I write a Sql Query for select UPdateTime between 2007/09/01~ 2007/09/30 select * from ZT_Master where TDateTime < DateAdd(Month,-1,GetDate()) the result return all the column.. why?
I have the following query that produces this: 1 0 Arts & Entertainment6 1 Arts & EntertainmentDance2 1 Arts & EntertainmentMovies9 0 Automotive10 0 Business11 0 Cancer12 0 Communications3 0 Computers13 3 ComputersE-Learning4 3 ComputersHardware14 3 ComputersJava16 3 ComputersLink Popularity17 3 ComputersMicrosoft.net15 3 ComputersRSS5 3 ComputersSoftware7 0 Real Estate8 7 Real EstateFinance Here is the query: SELECT tblArticleCategory.ACategoryID, tblArticleCategory.AParentID, ISNULL(Parent.ParentCategory + '', '') + ISNULL(tblArticleCategory.ACategoryname, '') AS ACategory FROM tblArticleCategory FULL OUTER JOIN(SELECT ACategoryName AS ParentCategory, ACategoryID FROM tblArticleCategory AS tblArticleCategory_1 WHERE (AParentID =0) AND (AActive = 1)) AS Parent ON Parent.ACategoryID = tblArticleCategory.AParentID WHERE (tblArticleCategory.AActive = 1) ORDER BY ACategory How do I modify this so that just the category name is returned and not the parent cetegory. In other words, everything shows up just once. Arts and Entertainment, once, then on the next line Dance, then Music then the next parent category which is automotive. Here is the table: ACategoryID int UncheckedACategoryName nvarchar(150) CheckedADescription nvarchar(300) CheckedAParentID int CheckedAActive bit Checked Unchecked
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
In Enterprise Manager, I would right click on the table, choose Open Table and Query where I could select specific records and (most importantly) could alter data in a record by deleting the text, adding or over-typing.
In 2005 Server Management Studio I just cannot figure how to do this. I'm guessing that I need the 'Script Table as' option but then what?
I have managed to open selected data using the New Query and then Design Query in Editor, but the results only appear in a kind of view form and I cannot seem to alter any of the data entries, I get dotted lines around the selected field.
I Everytimes I run a software I wrote on the server I get the following error:
Process ID %d attempting to unlock unowned resource %.*ls.
Does anybody experienced that type of problems before?
have a copy of the database and of the sfotwqre locally on my win 98 machine where it runs without problem. Once I try on the server it crashes miserably.
In an attempt to 're-cluster' SQL Server 7.0 after re-installing SQL to change Sort Order, I am recieving the following error when I run the Failover Wizard. "Could not update remote nodes in Cluster". And here is the error from the sqlsp.log
13:50:05 Begin Setup 13:50:05 7.00.623 13:50:05 Mode=Silent 13:50:06 ModeType=SecNode 13:50:06 LoadLibrary failed on supporting DLL. 13:50:06 An internal error occurred during install (failed to load package id). Contact Microsoft Technical Support. 13:50:06 Action CleanUpInstall: 13:50:06 Installation Failed.
I am getting the error 'Incorrect syntax near the keyword set' with the following code snippet -
declare @dteTo Date set @dteTo = CONVERT(date, GETDATE())
My intention is to set the variable @dteTo to the current date (no time component). If I run a SELECT CONVERT(date, GETDATE()) command I do get what I want, it is just assigning the value to the variable that isn't working as intended.
I've just installed SQL Server Express and attempted to create my first database and received this error message :-(
Maybe this is a long shot, but can someone tell me what I've done wrong? Do I have a bad install or what?
Essentially I followed the steps below to create the database:
1. Started MS SQL Server Management studio Express 2. Right clicked on the Database folder 3. Selected "New Database..." 4. New Database panel displayed 5. Entered name of database - pip 6. Clicked "OK" 7. Receieved error dialog with followig message:
TITLE: Microsoft SQL Server Management Studio Express ------------------------------
Create failed for Database 'pip'. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
Could not obtain exclusive lock on database 'model'. Retry the operation later. CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 1807)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=1807&LinkId=20476
Exception has been thrown by the target of an invocation. (mscorlib)
------------------------------
The connection type "EXCEL" specified for connection manager "{55E5636D-F2A9-48D6-9723-867C9F241F5B}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name. ({F390DBA3-1B52-43F4-A624-9E71D273B4D7})
I really need to get this data into SQL server... why do I have to go through SQL 2000?
I have a log table with no indexes, triggers, or keys. During the course of development, I will clean out the entries by selecting all the rows (in Database Explorer) and hitting the delete key.
I downloaded VisualBasic 2005 Express Edition recently, and the installation went flawlessly, but when I tried to download the optional component SQL server express, the installation failed. When two subsequent installations failed, I decided I'd get along without it, until such time as I discovered I *needed* it. Well, I'm here, and the installation is still failing. I've tried both installing it though the VBstudio setup, and downloading it separately, but both fail at roughly the same point. The error from the standalone installation is more descriptive:
TITLE: Microsoft SQL Server 2005 Setup ------------------------------
SQL Server Setup Failed to compile the Managed Object Format (MOF) file c:Program FilesMicrosoft SQL Server90Sharedsqlmgmproviderxpsp2up.mof. To proceed, see "Troubleshooting an Installation of SQL Server 2005" or "How to: View SQL Server 2005 Setup Log Files" in SQL Server 2005 Setup Help documentation.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=setup.rll&EvtID=29513&EvtType=sqlca%5csqlmofca.cpp%40Do_sqlMofcomp%40Do_sqlMofcomp%40x80041002
The link leads to an apology - they have no information on this subject. Of the help files, the troubleshooting page contains nothing (noticeably) relevant, and the log files have nothing obvious either, at least as far as I could tell... but then, I have no idea, which is why I'm here
I get the following exception when attemption to create a connection from my i5 to our SQL Server:
Nov 5, 2007 10:09:33 AM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL WARNING: TDSChannel ( ConnectionID:1 TransactionID:0x0000000000000000) SSL handshake failed: null com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: null. at java.lang.Throwable.<init>(Throwable.java:195) at java.lang.Exception.<init>(Exception.java:41) at java.sql.SQLException.<init>(SQLException.java:40) at com.microsoft.sqlserver.jdbc.SQLServerException.<init>(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSChannel.throwSSLConnectionFailed(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.loginWithoutFailover(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
I am using sqljdbc.jar version 1.2 and the classes load properly during the launch of the JVM.
I put 'encrypt=false' in the connection properties and everything I've looked at on the SQL Server side shows that SSL isn't being used on that end. Is there any diagnostic method I can use to determine why the driver is attempting to use SSL?
Is there any specific place where I can find which SQL Query is more efficient?.
Is INNER JOIN is faster or Select ... Where ID in (SELECT ...) is faster?
I have two tables: 1.FLEET (The number of rows is not so much) Attributes : Company_Id (PK) Fleet_Id (PK) Fleet_Name Fleet_Description
2.USER_PRIVILEGE (The number of rows can reach up to 3 times the number of row in fleet table) Attributes : Company_Id (PK) Fleet_Id (PK) User_Id (PK) Privilege_Id(PK) Comment Category
I want to select Fleet_Id and Fleet_Name from fleet table Where the current user has privilege_id=1
I have two possible select statement :
1.Option 1
SELECT Fleet_Name, Fleet_Id FROM FLEET WHERE (Company_Id = 2) AND (Fleet_Id IN (SELECT fleet_id FROM user_privilege WHERE user_id = 11 AND company_id = 2 AND privilege_id = 1)) ORDER BY Fleet_Name
2.Option 2
SELECT F.Fleet_Name, F.Fleet_Id FROM USER_PRIVILEGE U INNER JOIN FLEET F ON U.Fleet_Id = F.Fleet_Id WHERE (F.Company_Id = 2) AND (U.Privilege_Id = 1) AND (U.User_Id = 11) ORDER BY F.Fleet_Name
Actually which one is faster. Is SQL Statement with INNER JOIN (Option 2) can be executed faster than the one with double Select Statement(Option 1)?
Hi: I have the following query, can somebody help me? SELECT s.Id, s.NameFROM Switch s INNER JOIN SwitchTelephoneRange r ON s.Id = r.IdWHERE '1526858' BETWEEN FromTelephone AND ToTelephone Where the '1526858' is a phone number. My problem is, I want to run the above query for each record in : select Telephone from PhoneDirectory So, each telephone number in the second phone, would macth the ' ' in the first query. How can I do so? Do I need a loop? a cursor? Can you help please? Thanks
Hi, I want to know query execution time as output. I want execution time only. this is for tuning purpose... Time displayed in the status bar is not helpful for me. thanks.
Hi there, I'm having a big performance problems with sql query. What i have done is changes physical layout (rearanged the columns) in one of the tables in the database. I used bcp to get the data out and then in. There are about quarter million rows in the this table. I have created the same indexes but know the same query takes a long time to execute. I have noticed that the showplan is somehow different than it used to be. This query uses the table i have changed and another one that i haven't touched. I have updated the stats to no help. Here are the show plans. this one is slow STEP 1 The type of query is INSERT The update mode is direct Worktable created for ORDER BY FROM TABLE SW_PERSON Nested iteration Index : swiPERSON10 FROM TABLE SW_CASE Nested iteration Table Scan TO TABLE Worktable 1 STEP 2 The type of query is SELECT This step involves sorting FROM TABLE Worktable 1 Using GETSORTED Table Scan
this one used to be fast STEP 1 The type of query is INSERT The update mode is direct Worktable created for ORDER BY FROM TABLE SW_CASE Nested iteration Table Scan FROM TABLE SW_PERSON Nested iteration Index : PK_SW_PERSON_1__27 TO TABLE Worktable 1 STEP 2 The type of query is SELECT This step involves sorting FROM TABLE Worktable 1 Using GETSORTED Table Scan
I think the problem is with the fact that the fist one doesn't use the PK key which is the one the links both tables. My question is how to force the query to use this index. PS. One thing i haven't done is to recreate indexes on the other table. But i don't think that would have made a differnece. Thanks
CREATE FUNCTION dbo.fnProductsRetrieveBySupplierID ( @SupplierID int ) RETURNS TABLE AS RETURN ( SELECT * FROM Products WHERE SupplierID = @SupplierID )
CREATE FUNCTION dbo.fnSuppliersRetrieveBySupplierID ( @SupplierID int ) RETURNS TABLE AS RETURN ( SELECT * FROM Suppliers WHERE SupplierID = @SupplierID )
I have been testing the performance of the following SQL statements:
Code:
1. SELECT * FROM Products INNER JOIN Suppliers ON (Products.SupplierID = Suppliers.SupplierID) WHERE Products.SupplierID = 3
2. SELECT * FROM dbo.fnProductsRetrieveBySupplierID (3), dbo.fnSuppliersRetrieveBySupplierID (3)
I have built a loop to execute each statement multiple times and then compare the execution times. Although both queries produce the same result, the 2nd one (which uses the functions) is about twice as slow, does anyone know why?
I am joining three tables each table has got about 1.5 million rows,selecting data from these three tables and inserting into a table,to avoid transaction log issues I am running the query in a batch of size 50,000 rows,it is taking about 5hrs to insert all the 1.5 millions rows.
All the columns in the where clause have proper indexes,I ran show plan for the query and it is using indexes properly and not doing any table scan.I updated the statistics for all the indexes also.
query looks some thing like this.
insert into table d (col1,col2,col3,.............. ) values (a.col1,b.col2,c.col3 .....................) from a,b,c where a.id = b.id and a.id = c.id and a.id in between @minid and @currid
The @minid starts from 1 and @currid starts from 50000 ,I am running this in a loop, in next iteration @minid will become 50001 and currid 100,000 and so on.
I have two tables. Employee EmployeeCode int Primary Key
Employee_Stock EmployeeCode int StockCode varchar(10) Primay key on (Employeecode, StockCode)..
There is no foreign key relation between these 2 tables. Now my question is which query give more performance. and why? 1. Select * from Employee INNER JOIN Employee_Stock on Employee.Employeecode = Employee_Stock.EmployeeCode
2. Create a foreign Key between Employee and Employee_Stock for EmployeeCode. and run the same query.
Actually we forgot to put the foreign key relationship between these 2 tables and we have lot of queries joining them.. Now if we add foreignkey, is it going to improve the performance or not?
I wrote a query and I use cursor in the query. When I run the query on dev box it takes 3 mins. I moved the query to EPM database box and it takes forever to run. Usually EPM database query performance is much better. How do I start debugging the poor performance?
How can I check if the query is creating any table locks?
Purpose of query: I get all the Companies (20000) and loop thru each company in the cursor and do calculations.
I have a query like below and it takes a aouple of seconds to run
select a.Registration_Key, ag.Agreement_Type_Name,p.ServiceProvider from dbo.Assessment a INNER JOIN ( select distinct Registration_Key, p.ServiceProvider, max(CSDS_Object_Key) as [Sequence] from dbo.Assessment a INNER JOIN dbo.CD_Provider_Xref p ON a.Provider_CD = p.Provider_CD where Creation_DT >= '07/01/2007' and Reason_CD = 1 group by Registration_Key, p.ServiceProvider ) as s1 ON a.CSDS_Object_Key = s1.Sequence INNER JOIN dbo.CD_Provider_XREF p ON a.Provider_CD = p.Provider_CD INNER JOIN dbo.CD_Agreement_Type ag ON ag.Agreement_Type_CD = a.Agreement_Type_CD LEFT OUTER JOIN ( select distinct Registration_Key, p.ServiceProvider , 1 as served from dbo.Encounters e INNER JOIN dbo.CD_Provider_Xref p ON e.Provider_CD = p.Provider_CD where Encounter_Begin_DT between '08/01/2007' and '08/31/2007' and Procedure_CD is not null and Encounter_Units > 0
) as s2 ON a.Registration_Key = s2.Registration_Key and p.ServiceProvider = s2.ServiceProvider
group by a.Registration_Key, ag.Agreement_Type_Name, p.ServiceProvider
However, if i add a served field( stamped with 1) it takes forever to run.. All of join columns have indexes.. cluster and non-clustered.. and i don;t see any index fregmentaitons...
select a.Registration_Key, ag.Agreement_Type_Name,p.ServiceProvider, served from dbo.Assessment a INNER JOIN ( select distinct Registration_Key, p.ServiceProvider, max(CSDS_Object_Key) as [Sequence] from dbo.Assessment a INNER JOIN dbo.CD_Provider_Xref p ON a.Provider_CD = p.Provider_CD where Creation_DT >= '07/01/2007' and Reason_CD = 1 group by Registration_Key, p.ServiceProvider ) as s1 ON a.CSDS_Object_Key = s1.Sequence INNER JOIN dbo.CD_Provider_XREF p ON a.Provider_CD = p.Provider_CD INNER JOIN dbo.CD_Agreement_Type ag ON ag.Agreement_Type_CD = a.Agreement_Type_CD LEFT OUTER JOIN ( select distinct Registration_Key, p.ServiceProvider , 1 as served from dbo.Encounters e INNER JOIN dbo.CD_Provider_Xref p ON e.Provider_CD = p.Provider_CD where Encounter_Begin_DT between '08/01/2007' and '08/31/2007' and Procedure_CD is not null and Encounter_Units > 0
) as s2 ON a.Registration_Key = s2.Registration_Key and p.ServiceProvider = s2.ServiceProvider
group by a.Registration_Key, ag.Agreement_Type_Name, p.ServiceProvider, served
Hello SQL Gurus, From the query below, I am using 2 TOP functions to return the desired row. I am wondering if someone can shed some light on how to AVOID using 2 TOP statements and combine into just one select query?
select TOP 1 * from (select TOP 2 Num from A order by Num) X order by Num desc
Truly Appreciate your help as this performance issue has been bugging in my head for quite some time...
I usually am all over answering these kinds of questions, but while I continue to work on this issue, maybe someone here can lend me a hand. A vendor application we run, stores metadata about backup blobs stored on a NAS device. The app basically backs up select folders on 1400 remote computers in the back office of our stores, and stores this on a NAS, while maintaining metadata about the BLOBs in SQL Server so that they can push recovery of the data back to the original store it came from. The database is roughly 80GB in size and has a single file group and is on its own dedicated LUN. It uses TempDB heavily, and this is not something that I can change, but TempDB is on a different disk array.
Today I spent hours on a conference call with them looking at a specific stored procedure that is used to clean up the records in the database after a BLOB file is deleted. A single BLOB file can have millions of related records in the database. There is a LEFT JOIN in the code that is against a table with 150 million + rows of data in it. The table size is fairly small, only 5 GB of data, but the LEFT JOIN spools 2.4GB of data to a Hash Match. It seems to me like the left join can't be removed, but I don't get how all of this works, because I didn't write the application. It is an INDEX SCAN. I can't seem to eliminate it. Is there anything I can do to help this thing out?
I am attempting to get a better understanding of why my SQL 2005 setup when running a simple select statement on a large table is displaying very low IO in performance monitor. If i run a single Select * From testtable i see 4mbsec transfer and Disk readssec is around 8-9. This particular table is sitting on a single U320 10k drive so i expecting to see far more substantial IO. Does anyone have any information on how IO is consumed using different SQL operations so i can obtain a better understanding?
So I am experimenting with upgrading a Windows Mobile application from .NETCF 2.0 to .NETCF3.5, along with moving my SQL 2005 Compact to SQL Compact 3.5. I have a database that I upgraded using the recommended methods (creating a datasource in VS2008, opening the SQL 2005 Compact .sdf file and allowing the tool to upgrade to SQL Compact 3.5). On the device (Dell Axim x51), with the .SDF files on an SD Card, the query, when executed against the SQL 2005 Compact database file, takes 1.5 seconds, but takes 1min41sec to execute on the SQL Compact 3.5 database.
This is a fairly simple query, with an inner join (using about 4 inner join constraints), a where clause (over about 3 things), and an order by clause. The execution plan for the SQL Compact 3.5 query shows index seeks (one consuming 2% and the other consuming 0%, with the inner join using 98%). The database files are on the order of 90MB.
Can anyone offer any suggestion why the SQL Compact 3.5 query performance would be so much worse than the SQL 2005 Compact performance?