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,
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
View Complete Post