Need help in deciding approach to resolve the isse

[varshan09]

Hi All,

We are developing an intranet based web application which deals with huge amount of data. To explain it better, the application has a calculation process which adds nearly 30 million rows in a table for a user. There can be multiple users accessing the application and performing similar calculations.

Currently, we are facing locking issues as the application maintains calculation data coming from all users in a single table. If same calculation is tried again, there is a logic of removing data and adding again. DELETE operation from this huge table is taking 40% of the time required to run the query.

We are planning to re-design the system, in order to provide different users the option of launching calculation without giving any error or waiting for the resources available.

Can somebody help me in suggesting a solution to resolve issue of huge data?

Thank you for your help,
Varsha

 

[Catadmin]

Have you tried implementing temporary tables? If the table you're using is going to have the data "deleted" after the calculations are done, you might as well just do it in a virtual table (look up Temporary Tables in Books Online) and make sure your TempDB is on its own physical drive to improve I/O.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

Oh, and DELETE is a logged operation. Use the TRUNCATE TABLE command (again see BOL for a definition) and it will save time because is it not logged.

Lastly, if you have to keep "summary data" or the final result, use the Temp Tables for your calculations then store the final result rather than using the real table object to do all the calcs and temporary storage.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[varshan09]

Thank you for your replies. Please find my comments below -
1. Cannot use Truncate as we are not deleting all records in the table
2. Cannot use temporary tables in tempDB as we need to retain data till the time user is executing another calculation process.

Please suggest.

Thank you,
Varsha

 

[SQLSister]

CAn you partition your database across several drives? with this number of records perhaps a partition by client or user would help?

Questions about posting. See faq183-874