Hi,
I need an advice from some SQL Guru. I have a Cursor (SQL Server 2005) which calculates the monthly employee wages. The outer query selects all the employees and the inner query computes the monthly salary day by day in a loop. For each day in a month, i insert a record in a temporary table. So for 2 employees, there can be 2 x 31 records (31 are the maximum days in a month). Later i rotate the temporary table to convert the 31 rows for each employee into one record so at the end there are only 2 employee records but with 31 columns. The problem is that the rotation is taking a huge performance hit and almost takes 5 minutes to convert rows to columns. I have even tried the PIVOT statement but that too almost takes the same time.
Could anyone suggest me how can i nail down a performance efficient query for a monthly report. As far as i know, even on Mainframes, monthly/annual reports takes a long time to compile. So if any GURU has a better idea, i would really appreciate it.
I am only asking about how would any Guru/Expert handle monthly/annual reports with huge amount of data in the tables efficiently.
Regards.......
I suggest that you also post on http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1, the MSDN forum on TSQL.|||Thanks for the link, i think its a better place to post my question.
Regards......
|||Hi,
Cursor has a very poor performance and you should avoid it.
Visit http://www.sql-server-performance.com/default.asp. In lower portion you will get many tips to improve your query performance.
I will suggest you to post this question in sql-server-performance.com forum.
