Report in SQL Server 2005 - Suggestion needed

Hi,

I want to develop a monthly salary report for our employees. The salary is calculated on daily basis for individiaul employees. Thus the report is supposed to display Employee Name, ID etc + 31 columns (representing maximum days in a month). The report will display the employee basic information along with their salary calculation for each day. So its like 35 columns report. I already have used a cursor in my stored procedure with SQL Server 2005 to get this report (displayed in .NET Page) but the performance is more than worst and takes even 3-4 minutes to compile (which ofcourse is unacceptable). There are around 300 employees and they belong to different departments. When we select a given department, all the respective employees report has to be displayed. If i select All Departments, then the entire 300 employees report has to be displayed. I dont want to cache the data as it will have a big performance issue for the server.

I want some good suggestion on how to develop a monthly report in SQL Server 2005 which can display the data with optimized performance. Kindly help of any guru will be highly appreciated.

Regards...

Hello my friend,

I assume that your procedure for the calculations works but it is just slow. My advise would be to create a new table that stores this calculated information. After all, it will not change once calculated. What your employee earned last month each day will not change because it is in the past. Your calculated table, which you will populate from your stored procedure that does all of the current calculations would look something like this: -

EmployeeID DayDate Amount
101 2007/08/01 200
101 2007/08/02 250
101 2007/08/03 200
102 2007/08/01 180
...

Each record will be unique by the combination of the EmployeeID and DayDate.
At the end of each day, you run your stored procedure for all employees for the amounts that they earned just for that day, which will be inserted into the table above.

Then when you need to query this data, it will be much quicker and simpler query. This is the most effective way of speeding things up but if you have any concerns about this approach, please let me know.

Kind regards

Scotty

|||

Hi Scotty,

Thanks for the prompt reply and sorry for the late reply from my side. Well the problem is that i cannot create any table and thus have to use the actual values in the tables, calculate them and figure them out. I am using my cursor in the following manner:

Declare Temporary Table - Temp

Cursor -- Get All employees based on some condition

For each employee, Loop = 1 - 31 times (days in a month)

For each day (adding Loop to 1st Day of Month Selected), calculate salary and insert in Temp

//// In this temporary table, we have 31 rows inserted for each employee

End Loop

End Cursor

Pivot table (rotate table such that 31 rows for each employee are converted into a single row with 31 columns) to get the monthly report...

Kindly, could you give your expert opinion on how to improve this procedure...