rgds,
A simple way to test the performance of your application is to log the
time before and after blocks of code in your application. Once you can
identify the most time-consuming piece you can focus more on it and
eventually identify the best place to apply some optimization
techniques.
Until you know the which specific pieces are the cause for the delays
it will be hard to know what techniques to apply to resolve your
issues.
In general, even though the SQL and programming logic is not in a
code-behind, it sounds like you still have the code running either in a
class library or a console application which is not on the same machine
as the database server. If you are transferring the data from the
server to a remote client then you will find the time to transfer all
of that data is significant. A simple way to cut down on that is to
make sure you are not selecting all rows and instead trim it down to
just the columns you need.
Next you have to consider the costs of pulling in that data and parsing
it into a form you can use. The cost of casting a Integer from the
Object that the DataSet provides can add up. If at all possible, place
more code into your database as stored procedures. You can do quite a
bit with T/SQL, such as working with table variables and while loops to
look at the data to implement your business logic. Normally you do not
want a lot of business logic in your stored procedures, but when
performance is more important as it seems here, it can be the right
option.
I just moved a good deal of logic into a T/SQL script this week and it
went from running for 1 hour to 3 minutes. And that was likely due to
eliminating the data transfer and type casting costs.
Lastly, review each of your select statements and table indexes. Make
sure when you are joining tables you are doing so with indexed values.
And if you do join across many tables, consider a data warehousing
approach where you pre-fill a denormalized table with the contents of
the expensive query so that when you use it later it comes back very
quickly.
Brennan Stehling
http://brennan.offwhite.net/blog/ sk******@gmail.com wrote:
Mike
Thanks for you resonse.
1. "business logic is contained in individual exe" means that the
webapp does not contain the actual calculations. These are kicked off
from the web app on a button click by the user - we call each run a
process.
2. The business logic itself reads the data from files... validates
them pumps them into the DB (SQL Server) - this happens pretty quickly.
Then reads the data from the DB (selects) into in-memory tables.
Applies some rules on them, and makes inserts into another in-memory
tables and finally inserts and updates the SQL Server DB tables. The
part that takes long when multiple users kick off their own processes
is the actual calculation where there maybe in-memory tables from
multiple processes.
3. The reason I say that there is a performance issue is that the time
to do the actual calculation increases when there are multiple
processes running simultaeneously.
4. No we have not yet tries cacheing. We were concentrating on
optimizing the sql queries till now. Will look into that now.
5. Yes, I am looking into that as well. Are there some that you can
suggest? I have ANTS so far that I think will do the job and is
reasonably priced.
Thanks much for you help.
rgds
KS
Michael Nemtsev wrote:
Hello sk******@gmail.com,
I manage a web based VB .net application. This application has 3
components:
1. Webapp (this calls the executibles)
2. database
3. business logic is contained in individual exe application that get
called in a sequence to do some heavy calculations (mainly DB
operations with in memory datasets)
What does "business logic is contained in individual exe" mean?
Who starts them, where are they hosted?
I am having an issue with performance with this architecture.
How do u get this?
>the exes themselved are multi-threaded and can have multiple instances
(this means that there are these mutliple datasets on the app server).
Have u tried to use caching?
Are there any guidelines that can help me establish how I can improve
performance - increasing memory, adding an app server (clustering),
adding cpus, etc. Any advice from you will be helpful!
Firstly use some kind of performance tool to understand the bottleneck.
---
WBR,
Michael Nemtsev [C# MVP] :: blog: http://spaces.live.com/laflour
"At times one remains faithful to a cause only because its opponents do not
cease to be insipid." (c) Friedrich Nietzsche
