Richard Santos

I was given a certain module where in it performs a simple computation (the SUM function). What I’m tryin’ to do is add all the data from a specific fields and group by a certain field from another table. but the problem is the table containing the data to be calculated is three tables apart from the table containing the field needed for the group by function and four fields are needed for groupings.

to make it clear, here’s the query sample:

SELECT  a.field1, a.field2, a.field3, SUM(d.field2+d.field3) as totalFld
FROM    table1 a
        LEFT OUTER JOIN table2 b
        ON a.field4= b.field1
        LEFT OUTER JOIN table3 c
        ON b.field2= c.field1
        LEFT OUTER JOIN table4 d
        ON c.field2= d.field1
GROUP BY a.field1, a.field2, a.field3

By running the query above, it took me more than five minutes before it returns the data.

And so i started investigating and found out that the reason for slow performance is because I am joining the tables on the field that is not an index. So what I did is I added an indexes to the columns in each table used to join the tables. I’ve learned that for maximum performance, the indexes on the columns to be joined should have the same data type and it should be a clustered key for optimum performance.

Another thing is the group by function. I’ve found out that this also affects the performance. As the number of groupings increases the performance decreases.

to create an index using t-sql:

CREATE CLUSTERED INDEX indexName on tableName(fieldName1, fieldName2,….)

to delete an index using t-sql:

DROP INDEX tableName.indexName