November 16, 2006 at 7:32 am
I have a query that returns ~3k records, I'm fairly novice at SQL Server so I'm sure there is stuff wrong with this query. It was running fast (<5 seconds) a day ago, then I updated the data in the database and now I can't seem to get it to execute in less than 50 seconds. Anyway, here it is and if anyone can help me I'd appreciate it.
The Doba_Product table has an index on "Product", Doba_Item has an index on "Product" and "Item". I've tried using a paging stored procedure and that lowers the time to about 35 seconds (ugh!)
SELECT a.Warehouse, a.Product, a.SKU, a.Title, a.Status, a.Brand, a.Description, a.Details, a.Weight,
a.Dimensions, a.[Manufacturer Site], a.Logo, a.Thumb, a.Image, a.Flags, a.[Expected Ship Cost], a.Category
FROM Doba_Product a WHERE Len(a.Image) > 0 AND a.Flags = 0 AND a.Status <> 'discontinued'
AND a.Weight <> '0' AND a.Product NOT IN (SELECT Distinct(Product) FROM Cart_Categories_Index)
AND (SELECT Count(Item) FROM Doba_Item WHERE Product = a.Product) > 0
November 16, 2006 at 8:01 am
NM, the Count(Item) part was taking up all the resources so I took that out and I do a check on individual records I get back instead of the entire recordset.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply