My view defination is :-
CREATE VIEW VIEW-NAME
a.batch AS [bid], -- column1 of a
a.studentid AS [a_sid], -- column2 of a
b.stuid AS [b_sid], -- column1 of b
b.column2 AS [newcolumn2],
b.column3 AS [newcolumn3],
b.column4 AS [newcolumn4],
...... 164 columns in total for WHOLE VIEW (TableA + TableB)
LEFT OUTER JOIN
a.studentid = b.stuid
We have clustured-index on a.studentid & non-clustured-index on a.batch and in the same way clustured-index on b.stuid.
Now when I'm writing below query:-
SELECT * -- 164 columns & we want all the columns
WHERE [bid] = 10
It is taking almost 25 to 30 mins to execute it successfully but we want to reduced the time slot to 10-12 mins can any body give me their ideas and options?
I'm aware that architecture isn't good but....... any good options were we can optimize the query I execute in DTA it recommeded to create indexes which we did but still it is taking same time as before. Also the above query will fetch 100,000 records with 164 columns.
View Complete Post