Hi All
I am running this query on top of SharePoint linked table (Votes) and its taking much time to return resutls when doing Analyze performance then getting message of creating indexes on few fields of linked table
But we can not create indexes on sharepoint linked table.. Table is linked to SharePoint to get latest data from site.
Pls let me know what should be my approach and any better way of writing below query?
Code:
SELECT t.*, avgs.avg_vote, avgs.W_Avg
FROM MonthlyTrendContent AS t, (SELECT d.[Vote Received], avg(a.[Vote Value]) AS avg_vote, sum(a.[Vote Value] * a.[BlueSky]) / SUM(a.[BlueSky]) AS W_Avg FROM MonthlyTrendContent AS a, (SELECT DISTINCT [Vote Received] FROM MonthlyTrendContent) AS d WHERE a.[Vote Received] <= DateSerial(Year(d.[Vote Received]), Month(d.[Vote Received]) + 1, 0) and not exists (select null from MonthlyTrendContent b where b.[Client ID] = a.[Client ID] and b.[Product] = a.[Product] and b.[Office] = a.[Office] and b.[Vote Received] > a.[Vote Received] and b.[Vote Received] <= DateSerial(Year(d.[Vote Received]), Month(d.[Vote Received]) + 1, 0)) GROUP BY d.[Vote Received]) AS avgs
WHERE avgs.[Vote Received] = t.[Vote Received];