Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jch04
Starting Member
9 Posts |
 Posted - 2009-02-10 : 04:57:46
|
| Hello to all team,I need to optimize the following query which takes 12-13 secs to complete and returns 7581 rows:select distinct * from stud_progress where psem>=20 and student=71367The stud_progress is a view:ALTER view [dbo].[stud_progress] asselect distinct sc.student,sc.semester psem,sc.course pcourse,rel.family as crsfamily,sc.hours,sc.state,sc.alt ,lr.semester rsem,lr.course rcourse,lr.grade,isnull(lr.result,0) as result,course=isnull(lr.course,sc.course),semester=isnull(lr.semester,sc.semester)from studcrs sc--inner join student stu on (sc.student=stu.person) inner join course_relatives rel on (sc.course=rel.course)--inner join pr_spoudon p on (rel.course=p.course and sc.alt & p.alt>0 and p.semester>=stu.insem)left join exp_lastresult lr on (rel.family=lr.crsfamily and sc.student=lr.student and lr.semester<=sc.semester)thanks in advance. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-10 : 05:02:08
|
Add proper indexes on base tables.Add indexes on the columns used for JOINing and filtering in WHERE clause.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-10 : 05:58:43
|
| you could start with running the definition query with show execution plan option on to identify the costly steps and then trying to optimize those |
|
|
|
jch04
Starting Member
9 Posts |
Posted - 2009-02-10 : 07:05:12
|
| Hello,about execution plans:Almost all steps are taking 3-4% of time, based on real execution plan. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-10 : 09:32:50
|
| what are steps involved? |
|
|
|
jch04
Starting Member
9 Posts |
Posted - 2009-02-11 : 04:00:47
|
| Because I could not find a way to capture the graphical plan, I tried to write down the steps that take >= 1%Sort Distinct Sort 6%Hash Match (Right Outer Join) 4%Sort Distinct Sort 1%Cluster Index Seek 1% Index:IDX_TOTAL Table:_totalgradeSort Cost: 1%Nested Loops (Inner Join) 1%Index Scan 5% Index:PK_Crsgroup TablecrsgroupCluster Index Seek 4% Index:IDX_COURSE Table:COURSETable Spool:2%Merge Join (Union) 1%Hash Match (Inner Join) 2%Index Scan 2% Index:PK_Crsgroup Table:crsgroupSort Cost: 2%Hash Match (Inner Join) 4%===>Nested Loops (Inner Join) 14% ===>Index Scan 10% Index:_dta_index Table:PR_SPOUDON Cluster Index Scan 5% Index:PK_ALT Table:ALTCluster Index Scan 5% Index:PK_ALT Table:ALTCluster Index Scan 5% Index:PK_ALT Table:ALT |
|
|
|
|
|
|
|
|
Optimizing a View in SQL Server 2000