I have a C# Program that creats PowerPoint reports filled with statistics of a chosen (by the project manager who operates with the tool) company (each one has a companyID).

The statistics are coming from a SQL Server Database but they are calculated over many different tables some based on surveys(like average working hours, department count, emplyee satisfaction). All together I will have around 40 values. These values I want to 1. use in my C# Tool for the creation and 2. store into a new Table ("ReportX Values") and provide it with a timestamp.

The point where I stuck is what is a efficient way of doing this?

All I can come up with is creating stored procedures for each statistic (around 40) with an imput paramter (companyID) and trigger these from C# each time a report got created. But this looks a bit infefficent. I am aware of SSRS and SSIS and I am not sure if there are better and more efficient ways to accomplish this. Anyone who can help me get a good stategy so I can start?

The tables are quite simple. Each survey has one table with companyId, personID and fields according to the questions in a survey like question1, question2... And I have a table with meta data with columns like e.g. companyID, PersonID, Department, CompanyName and some fields to cover possible hierachical structures.

A query could be:

Select sum(v1) from survey1 where companyid ='123'

Select avg(v1) from survey3 where v1 In (1,2) and companyid = 5

The goal would be to have a stable workflow and an easy interface from c# to the database.

  • Are these 40 tables well normalized? Editing the question to include some table definitions and typical queries would help a lot. – Michael Green Dec 4 '19 at 0:48
  • 1
    Being efficient means maximizing output for a given input. Often something else has to be relinquished in order to achieve that improvement. What do you want improved - run-time latency, design-time query writing, on-disk storage or ... ? What are you prepared to give up to achieve this? – Michael Green Dec 4 '19 at 0:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.