|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-07-08 : 08:45:27
|
| Bass Man writes "I have a fairly simple SQL Statement. My problem is that it is far too slow!Here is my Statement:select p.Product_ID, SeriesIssue_ID = '', p.Priority, p.Title as NewTitle, p.Author, p.Subject, Issue = '', p.Series, p.Price, p.InDate, p.ProductType_ID, p.DVDRegion, p.ExtraInfo, pt.Description as Type, pt.Description2 as Type2, p.Image from Product p, Department d, ProductDepartment pd, ProductType pt where p.productType_ID is not null and (p.Series != 'Y' or p.Series is null) and p.Product_ID = pd.Product_ID and pd.Department_ID = d.Department_ID and p.ProductType_ID = pt.ProductType_ID and pd.Department_ID = d.Department_ID and d.area like 'dvdvideo' and p.Price > 0 and p.Reviewed = 1 and (p.Indate > GetDate() or p.Indate is null) UNION select p.Product_ID, s.SeriesIssue_ID, p.Priority, p.Title + ' (#' + s.Issue + ')' as NewTitle, s.Note as Author, p.Subject, s.Issue, p.Series, s.Price, s.InDate, p.ProductType_ID, DVDRegion = '', p.ExtraInfo, pt.Description as Type, pt.Description2 as Type2, s.Image from Product p, SeriesIssue s, Department d, ProductDepartment pd, ProductType pt where p.productType_ID is not null and p.Product_ID = pd.Product_ID and pd.Department_ID = d.Department_ID and p.Product_ID = s.Product_ID and p.ProductType_ID = pt.ProductType_ID and d.Area like 'dvdvideo' and s.Price > 0 and p.Reviewed = 1 and (s.Indate > GetDate() or s.Indate is null) order by Priority desc This search returns 5,500 records, and takes 4 seconds to complete the search! If I change the search criteria from 'dvdvideo' to '%' it takes less than a second, and returns 6000 records. I have indexes on all the fields in both the 'Where' clauses... Maybe I am completely on the wrong track, but is there any way I can speed this statement up?" |
|
Enhance Speed 