I'm seeing a slow down when doing load testing on my app.
I have a query that takes about 350ms to run, but what I run it in parallel 8 times (not to mention 32 times), it goes up to 2.5 seconds.

I verified on a profiler that the execution is really what that is taking up the time.

the query:

SELECT SUM([_Facts_].[Sales]) [_measures___Sum Sales_], [_Date_].[year] [_Date___year_]
FROM [pp].[Facts] [_Facts_], [pp].[Date] [_Date_]
WHERE [_Facts_].[dateKey] = [_Date_].[dateKey]
GROUP BY [_Date_].[year]
ORDER BY [_Date_].[year] ASC

I'm running 8 parallel processes that make 10 calls in sequence.
For 1 parallel I get:

360, 350, 345, 360, 365, 360, 395, 786, 395, 370, avg:408

For 8 parallel:

515, 1571, 1471, 1326, 1862, 2478, 1922, 3098, 2413, 2032, 2773, 3048, 2453, 2092, 2077, 3359, 2898, 2733, 3018, 2483, 1887, 3023, 3088, 3724, 2317, 2753, 2643, 3284, 3299, 2418, 1907, 1862, 2498, 2838, 2518, 3203, 2613, 2207, 3434, 2613, 3198, 2257, 2593, 2448, 2518, 2968, 2828, 2122, 2963, 2212, 3299, 2988, 3153, 2803, 2157, 2543, 2758, 2998, 2538, 2257, 2788, 2443, 2082, 2613, 3173, 4205, 2603, 2387, 1747, 3854, 3068, 2788,  2603, 3103, 2703, 3198, 1832, 1421, 2217, 1326

avg:

2535, 2523, 2571, 2627, 2689, 2469, 2521, 2610

When going to 16 in parallel it went up to even more.

I tried testing on MySql and got the same jump (different test times, but at least 4 times slower when parallel).

Can't these DBs handle the load?!

This happens both in C# and Java:
C#:

class Program
{
    static void Main(string[] args)
    {
        Parallel.For(0, 8, i => run());
    }

    static void run()
    {
        using (var conn = new SqlConnection("Data Source=.;Initial Catalog=;User ID=;Password="))
        {
            conn.Open();

            var cnt = 10;

            long avg = 0;

            for (int i = 0; i < cnt; i++)
            {
                var sw = DateTime.Now.Ticks;

                using (var cmd = new SqlCommand("SET ARITHABORT ON", conn))
                {
                    cmd.CommandText =
                        "SELECT SUM([_Facts_].[Sales]) [_measures___Sum Sales_], [_Date_].[year] [_Date___year_]\n" +
                        "FROM [pp].[Facts] [_Facts_], [pp].[Date] [_Date_]\n" +
                        "WHERE [_Facts_].[dateKey] = [_Date_].[dateKey] \n" +
                        "GROUP BY [_Date_].[year]\n" +
                        "ORDER BY [_Date_].[year] ASC";

                    using (var reader = cmd.ExecuteReader())
                    {
                        var x = 0;
                        while (reader.Read())
                        {
                            x++;
                        }

                        reader.Close();
                    }

                }

                var dif = (DateTime.Now.Ticks - sw) / 1000;
                avg += dif;
                Console.WriteLine(dif);
            }

            conn.Close();

            Console.WriteLine("avg:" + avg / cnt);
        }
    }
}

Java:

@Test
public void asdf() throws SQLException {

   Runnable r = () -> {
      try (Connection conn = DriverManager.getConnection("jdbc:sqlserver://;databaseName=", "", "")) {

         long avg = 0;

         for (int i = 0; i < 10; i++) {

            try (Statement statement = conn.createStatement()) {
               StopWatch sw = new StopWatch();
               sw.start();

               ResultSet resultSet = statement.executeQuery("SELECT SUM([_Facts_].[Sales]) [_measures___Sum Sales_], [_Date_].[year] [_Date___year_]\n" +
                     "FROM [pp].[Facts] [_Facts_], [pp].[Date] [_Date_]\n" +
                     "WHERE [_Facts_].[dateKey] = [_Date_].[dateKey] \n" +
                     "GROUP BY [_Date_].[year]\n" +
                     "ORDER BY [_Date_].[year] ASC");

               int x = 0;
               while (resultSet.next()) {
                  x++;
               }

               sw.stop();
               avg+=sw.getTime();
               System.out.println(sw);
            }
         }

         System.out.println(avg/10);

      } catch (SQLException e) {
         e.printStackTrace();
      } finally {

      }
   };



   ExecutorService executor = Executors.newFixedThreadPool(8);
   for (int i = 0; i < 8; i++) {
      executor.execute(r);
   }


   try {
      executor.shutdown();
      executor.awaitTermination(12312313, TimeUnit.MINUTES);
   } catch (InterruptedException e) {
      e.printStackTrace();
   }
}

MORE INFO

The 2 tables are ~1,000 rows and ~420,000 rows.
The join is ~420,000 rows and the result it 3 rows.

There is a PK on date.dateKey and FK on facts.
These are tables, not views.

I checked with profiler to verify that the duration is the actual execution time of the query, and not the app's run-time.

The SqlServer has 16 cores, so I would hope for the 8 queries to run in parallel and not stack up.

On the server, CPU for 1 query goes up to 50%. for 8 it gets to 95%.

network/memory/IO doesn't seem to change dramatically.

UPDATE

So i tried playing with the limitations of the parallelism. The conclusion is that the DB uses all cores for each query and the cores are overwhelmed by multiple calls. if i turn down the parallelism to 1 then each query is slower but i can run multiple queries without taking a hit.

guessing that it's just the hardware limitations and there is not "magic" solution for the general case – only tweaking the queries.

btw, it seems that the biggest part of the hit is the joining.

Thanks everyone!