Performance Improvment

  

We have about 6 schedule timers to run daily overnight. Each timer roughly takes 30 minutes per timer in which all timers have a lot of advance sql to run.

I want to find a way to increase the performance cut the time the 30 minute run time per timer.

Beside the way to optimized the code, what else can be done? Have better server? put more ram to it??

Load balancing help? or Load balancing just helping the front end?


Hi David,


It all depends on what is taking time. (and you didn't answer me on your other thread where I tried to help on the same topic).

It depends on where the time is being taken. Is it processing the data between each advanced queries, is it communication time, is it all database time?

In the worse case it's all database time ..and in that case only improving the queries/code and improving the database server will help. Also in one of your other threads you talked about multiple databases, where would that enter in play here?

But this is really hard just with guess work. You need to understand where the time is being taken and what can be parallelized.


Regards,
João Rosado

I believe it is database time. There are a lot of Insert/Update/Group By/For Each etc...

So adding more ram to the server wouldn't help increase the SQL processing ?

How our database is setup for a table for example.

Customer Order DB:

We have columns: CompanyId,FacilityId,Area,Item Number,etc..

Sample data 5,1,Dry,IM1252,etc...

So all of our clients' Customer Order file have been inserted into the same DB, but in my insert or update or select queries it always filter the company and facility and area.

So I was wondering if its possible to have 2 database server mirroring each other, 1 client can insert the data in Mirror 1 and the other client goes Mirror2


João Rosado wrote:

Hi David,


It all depends on what is taking time. (and you didn't answer me on your other thread where I tried to help on the same topic).

It depends on where the time is being taken. Is it processing the data between each advanced queries, is it communication time, is it all database time?

In the worse case it's all database time ..and in that case only improving the queries/code and improving the database server will help. Also in one of your other threads you talked about multiple databases, where would that enter in play here?

But this is really hard just with guess work. You need to understand where the time is being taken and what can be parallelized.


Regards,
João Rosado



Before trying anything you need to understand exactly where the time is being used.

 - Get all your slow query reports

 - Get the stats for processor and memory of your servers (both front ends and database)

 - Determine where are the slowest points in your processing.

 - ....

Stuff like that. If you have to start by saying "I believe it is database time" it means you have not enough information to act on it. Having a talk about throwing ram/cpu at machines or applying complex architecture changes like active-active mirroring really feels strange at this point.


Regards,
João Rosado