Code performance: SQL Server Query vs C#.Net web application

Question

I am writing a complex logic to calculate sales and customer bonuses. I have million of records to calculate bonuses.

I want expert opinions so that mathematical operation don't take much time to display result on web page.

So where I need to write calculation part? In SQL Server Queries (Using Stored Procedure and functions) or ASP.NET & C#.Net (Business Logic layer)?

Which one is the best practice? Processing on Database server or Processing on Application server?

Regards

Mohsin JK

if those millions of rows are already in a SQL Server database - process the data there! Have a stored procedure generate the results, put it in a table, and then just show the results from that table in your ASP.NET webpage. — marc_s
– marc_s, Commented Apr 18, 2012 at 7:27
If your calculation requires extensive use of aggregations, you can take a look on materialized views. en.wikipedia.org/wiki/Materialized_view — edze
– edze, Commented Apr 18, 2012 at 8:08

Parag Meshram · Accepted Answer · 2012-04-18 07:31:31Z

5

If you have millions of records then I would suggest you to write calculation part in SQL Server as calcuations in Business Layer will significantly take more time.

Following are suggestions to improve performance -

Write Stored Procedures
Create Indexes to fetch records faster
Use of temp tables if manupulation is for huge records

You can search on net to find out T-SQL other performance optimization techniques.

answered Apr 18, 2012 at 7:31

Parag Meshram

8,52110 gold badges57 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

VladV Over a year ago

Agree. Additionaly, I'd suggest considering CLR functions. For complex calculations CLR might be significantly faster than plain T-SQL. Or it might not be in this particular case, so testing is necessary.

Artem Koshelev · Accepted Answer · 2012-04-18 07:27:41Z

2

I have million of records to calculate bonuses

I think it is better to calculate it in database to avoid passing large amounts of data from the database to application.

answered Apr 18, 2012 at 7:27

Artem Koshelev

10.6k4 gold badges39 silver badges69 bronze badges

Collectives™ on Stack Overflow

Code performance: SQL Server Query vs C#.Net web application

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related