0

One win service developed by C# and based on SQL Server.

I created a SqlConnection and then do ExecuteReader, but I face a problem when there is a high concurrent access.

On my local machine, code as below, average is 1.2s for one execution. MyObject table has 40 columns and 400'000 rows.

for (int i = 0; i < 500; i++)
{
    Task.Factory.StartNew(() =>
                {
                    Stopwatch sw = new Stopwatch();
                    sw.Start();

                    var serialNumber = "55292572";
                    var orderIdArr = ORM.GetObjecys<MyObject>(t =>t.PrimaryId== Id).ToList();//Only Open connection, do SqlCommandExecute, close connection here
                    //sw.Stop();
                    Console.WriteLine(sw.ElapsedMilliseconds);
                });
}

I deployed this service to 2 win server. You can guess I have 500 customers and they access this service very often.

Online environment, for one execution will cost 10s.

So I don't know why does this low performance happen? On my local machine, it only takes 1.2s.

Could anybody give me some answers or give my some improvable suggestions?

Many thanks!

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Raymond He
  • 127
  • 5
  • How accurate do the results need to be, can you cache the results for a short time (< 5 minutes)? Do they really need all 400k records in one hit, or can you do paginated results? – Zac Faragher Mar 26 '18 at 00:58
  • No. Online environment we can't catch data in local memory – Raymond He Mar 26 '18 at 01:00
  • Only get one or two records – Raymond He Mar 26 '18 at 01:01
  • Consider modifying this to use async/await. Blocking on I/O with 500 concurrent users is going to hurt. – Cory Nelson Mar 26 '18 at 01:02
  • If you're only retrieving a couple of results at a time and it's taking 1.2s to do that, it's probably more of a database issue than a program issue. Key Lookups on Primary Keys are **really** fast. If it's not a lookup on a primary key, you'll probably need to add an index to whatever column it is that you're looking up on, which will greatly speed up performance. You may want to ask this on [dba.stackexchange](https://dba.stackexchange.com) – Zac Faragher Mar 26 '18 at 01:05
  • What does `ORM.GetObjecys` do? How can we help if you don't show us the necessary parts of your code? Do you open and close your connection for every iteration in the loop? If yes then that can cause performance issues. – jegtugado Mar 26 '18 at 01:07
  • Are you using a hand-made ORM, or one of the common ones? If it's hand-made, it's possible that it's establishing a new connection each time, or using a whole lot of reflection each time (slow) to do the object mapping. Linq to Sql, Entity Framework, NHibernate etc. all reuse the object mapping each time it's needed, and reuse connections – Zac Faragher Mar 26 '18 at 01:11
  • @ZacFaragher yes. I added index for where column . And if i only execute once execution . Only cost 200ms – Raymond He Mar 26 '18 at 01:17
  • @JohnEphraimTugado yes . Connection is in using code。that orm only do open connection . Do execute reader . Convert to object . Close connection . I checked sql profile.its really cost 1.2s for one sql execution – Raymond He Mar 26 '18 at 01:20
  • @ZacFaragher yes.hand-made ORM. Here is almost 0time cost on create sql command and convert to object.Check the sql profile. Sql execution cost almost 1.2s – Raymond He Mar 26 '18 at 01:22
  • @RaymondHe then the problem is not in the query - the query itself is likely the fastest bit. The *real* problem is likely in your ORM. If possible, you should switch to a well established ORM, there's no need to reinvent the wheel, especially when they've already worked out all the kinks – Zac Faragher Mar 26 '18 at 01:23
  • 1
    @RaymondHe change your design. Sql connections should be reused as much as possible, it is not meant to be opened and closed for every execution of SQL query. You should get a noticeable difference. That design and inside a loop, no wonder you are getting poor performance. – jegtugado Mar 26 '18 at 01:25
  • @JohnEphraimTugado for ado.net. It will auto help to manage the connection pool ？I will try to analize that – Raymond He Mar 26 '18 at 01:27

0 Answers0