Dear All,
I have developed a multi-tier windows application. I have few transactions where it would take more than 2~3 minutes on saving of data, I am using stored procedure where I am trying to create multiple rows on single transaction.
Earlier I was getting SQL client time out error, so I have increased timeout in connection string (app.config file).
Then I was getting CSLA transaction timeout error, so I have increased data context’s command timeout in all the places where I am calling Fetch, Insert or Update. (My problem was not solved at this point)
Then I have increased transaction timeout in app.config file. So my transaction timeout problem had been sorted out.
But my application became too slow. Even fetching, inserting or updating of small amount of data (very simple data, like fetching single row) also taking too much of time.
Development environment:
Framework: Microsoft .NET 4.0
CSLA.NET: 4.1.0
Language: C#
Reporting Tool: Crystal Reports
Controls: Infragistics 2010 Vol 3
Client Machine Hardware and Software information:
Laptop: HP Mini
Processor: Intel® Atom™ CPU N450 @ 1.6GHz 1.67 GHz
Memory: 2.00 GB
System Type: 32- bit
OS: Windows 7
DB: SQL Server 2008 R2 Express with Advanced Services.
Connection String:
<add name="MyDb" connectionString="Data Source=.;Initial Catalog=MyDb;Integrated Security=SSPI;Connect Timeout = 600;" providerName="System.Data.SqlClient" />
Command Timeout:
ctx.DataContext.CommandTimeout = 600;
Transaction Timeout:
<system.transactions>
<defaultSettings timeout="00:30:00" />
</system.transactions>
I have 30 forms at present in my application, it would increase later on.
Could anyone please help me out to increase the performance of my application. It is too slow now, it takes too much time to fetch, insert and update even small amount of data.
Thanks in advance
Raghav
A fetch would not normally have a transaction attribute on it, and since you've mentioned this is a multi-tier application I assume you are using some form of application server, assuming that any time-outs that you are getting would be better traced back to that.
Can you advise if you are running this using an application server, and if so what sort of server (IIS, WCF, etc)?
What sort of object are giving you the most problem, we've had some problems with really massive lists causing timeout issues (e.g >= 10,000 children items) but these are rare and other than the odd database performance issue or a really massive object with MANY children haven't ran into many transaction issues. Is this a list object causing you grief or a root level object with any or many children?
Before we can assist any further, need a bit more information about the objects themselves rather than system specs (though they may come in handy later)
Currently i am not using any application server, both db server and application installed on client system. (SQL server 2008 R2 Express)
I am using 4 databases in my application. Its working fine with other 3 databases, like fetching, inserting, updating are too fast in other databases (db size is around 150 MB)
But, i am getting problem in 4th db (db size is approx 650 MB), like fetching, inserting, and updating are too slow. like fetching would take 2~3 minutes or more, even if it is small amount of data.
Actually the db server and application both run on client machine, because client will work stand by (individually, without network, or offline) later on they replicate with main server, and later on this application will act as multi tier application where db sever, and application server will be configured and stand by client remain same.
As i know this may not be the problem of application, this could be the DB issue.
I am using SQL Server 2008 R2 Standard 64bit edition on Server and SQL Server 2008 R2 Express 32bit edition on client machine, and i am using merge replication
Client mahcine OS : Windows 7, XP
Server OS : Windows Server 2008 R2
Thanks
Raghav
try 2 things :-
1. Reduce the connection time out to something like 120 or less.. Do you need 600 for all queries, i doubt.. if it is for selective queries/updations then change ur code accordingly.
2. For all fetch operations wherever you are sure you don't need to update db. use
Enlist=false
Have you done any profiling to see where the application is slowest? That would give you the best answer.
However, you indicate that data portal related operations are slow. This may be because your application is in 2-tier mode and database communications are often "chatty." One thing to try would be setting up an application server (IIS on the database server) where all of your data portal code will run (as opposed to the client). This would reduce the network use down to two steps; on to serialize the Csla objects to send the the server, and one back. The DB may be doing much more back and forth. Be sure to enable compression to minimize the amount of data transmitted as well.
On the server side things can improve too; since the code actually talking to the database is on the same physical machine, all the database chatter will be done via a direct memory connection (i.e., the connection can use Shared Memory instead of tcp/ip).
Also, I'd certainly profile your stored procedure as it shouldn't be taking that long to generate a few rows. Profile all interactions with the db actually; your updates in a multi-user environment maybe commonly be getting stuck into deadlocks.
Copyright (c) Marimer LLC