سلام.
کوئری زیر دقیقا 6 ثانیه زمان می بره و فقط 1230 رکورد برمی گردونه که برای این مقدار رکورد زمان خیلی زیادیه.
ایندکس گذاری هم فایده ای نداره چون تمامی فیلدها باید بازیابی بشن.
از دوستان و اساتید محترم تقاضا دارم راهنماییم کنید.
متشکرم.
SELECT Movies.Id AS id, COUNT(*) AS CountBuy, Movies.SubtitleText as subtext,
Movies.Fname, Movies.CreateDate, Movies.Director, Movies.Abstract, Movies.IMDB, Movies.Parts,
Movies.Price, Movies.Photo, Movies.AdddateEn, Categories.CatName,
CONVERT(nvarchar, Movies.Subtitle) AS sub ,
Categories.CatName + ' (' +MainCategories.CatName+')' AS MainCatName
FROM BagMovie AS BagMovie_1 INNER JOIN Movies ON
BagMovie_1.MovieId = Movies.Id INNER JOIN Categories ON
Movies.CatId = Categories.Id INNER JOIN MainCategories ON
Categories.MCatId = MainCategories.Id GROUP BY
Movies.Id, Movies.Fname,Movies.SubtitleText, Movies.CreateDate, Movies.Director, Movies.Abstract,
Movies.IMDB, Movies.Parts, Movies.Price, Movies.Photo, Movies.AdddateEn, Categories.CatName,
Convert(nvarchar, Movies.Subtitle),Convert(nvarchar, Movies.ShowInFirstPage),
MainCategories.CatName Having Convert(nvarchar, Movies.ShowInFirstPage)='1' UNION SELECT '~/bag.jpg' as img,
Movies_1.Id AS id, 0 AS CountBuy, Movies_1.SubtitleText as subtext,Movies_1.Fname, Movies_1.CreateDate, Movies_1.Director,
Movies_1.Abstract, Movies_1.IMDB, Movies_1.Parts, Movies_1.Price, Movies_1.Photo, Movies_1.AdddateEn, Categories_1.CatName,
CONVERT(nvarchar, Movies_1.Subtitle) AS sub , Categories_1.CatName + ' (' +MainCategories_1.CatName+')' AS MainCatName
FROM Movies AS Movies_1 INNER JOIN Categories AS Categories_1 ON Movies_1.CatId = Categories_1.Id INNER JOIN
MainCategories AS MainCategories_1 ON Categories_1.MCatId = MainCategories_1.Id
WHERE (Movies_1.Id NOT IN ( SELECT MovieId FROM BagMovie)) and
Convert(nvarchar, Movies_1.ShowInFirstPage)='1' order by Movies.Id DESC
