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!
