Questions tagged [parallelism]
Reducing run times by breaking a problem into parts that can be completed by separate execution paths simultaneously.
197
questions
2
votes
1
answer
977
views
How do I synchronize parallel imports with COPY? [closed]
I have ~20 tables that get built at the same time but using different psql processes (via bash script) at the first stage with something like:
create table foo_1 as (select ....); -- via psql process ...
- 1,153
8
votes
1
answer
707
views
Did Microsoft change query optimizer regarding number of files and parallelism
Did Microsoft change query optimizer regarding number of files and parallelism? Does the optimizer no longer factor in the number of files to determine the degree of parallelism for a query? If so, ...
- 166
0
votes
1
answer
1k
views
Parallel Sequential Scan on SELECT statement, but not on SELECT ... INTO [duplicate]
The following query
EXPLAIN
SELECT c.global_id
FROM centers_res17 AS c
LEFT JOIN regions_res17 AS r ON ST_Contains(r.wkb_geometry, c.wkb_geometry)
WHERE r.global_id IS NULL;
says that a Parallel ...
- 263
15
votes
2
answers
6k
views
If a Parallelism Exchange Event deadlock is victim-less, is it a problem?
We're seeing a lot of these Intra-Query Parallel Thread Deadlocks in our Production environment (SQL Server 2012 SP2 - yes...I know...), however when looking at the Deadlock XML that has been captured ...
- 10.4k
-1
votes
1
answer
221
views
SQL Server Parallelism Disabled
I came up with a strange issue where the performance of select queries was degraded after creating missing indexes on tables. Initially, it looks like all the queries are affected but when looked into ...
- 101
3
votes
1
answer
4k
views
SQL Parallel Stored Procedure execution using CLR - Performance
We have a main analytics SP that calls 100 other sub SPs, all of which act upon the same set of data (Claims), check it for some business rules, and output discrepancies.
The SPs need not all be run ...
- 1,114
2
votes
2
answers
511
views
Insert result of a parallellized select query into a table, in Postgresql
According to https://www.postgresql.org/docs/current/static/when-can-parallel-query-be-used.html,
"Even when it is in general possible for parallel query plans to be
generated, the planner will ...
- 173
2
votes
1
answer
4k
views
UNION Queries run in Parallel form or not
I have three tables and I want to select data from these three tables in parallel form to increase the performance.
I'm running a query like this.
SELECT * FROM table1
UNION ALL
SELECT * FROM table2
...
- 73
31
votes
2
answers
3k
views
Is there a way to prevent Scalar UDFs in computed columns from inhibiting parallelism?
Much has been written about the perils of Scalar UDFs in SQL Server. A casual search will return oodles of results.
There are some places where a Scalar UDF is the only option, though.
As an ...
- 37.4k
-3
votes
1
answer
883
views
Script to setup MAXDOP [duplicate]
I need to script toi set MAXDOP.
----I have this script----
select cpu_count,
case
when cpu_count/hyperthread_ratio >8 then 8
else cpu_count/hyperthread_ratio
end as optimal_maxdop_setting
from ...
- 11
1
vote
1
answer
2k
views
Oracle data pump import parallel configuration
I am trying to track down a cause for a difference in oracle impdp processing, and I am not finding anything, so I am wondering if anyone here can explain the cause of the differences.
At times I ...
- 676
8
votes
2
answers
2k
views
Round robin vs Demand Parallelism partitioning
This is related to having a common key driver on top of the query with a nested loops, and the parallelism for the rows coming out of driver being either demand type or round robin type. I would have ...
- 304
4
votes
1
answer
743
views
Can multiple cores be used by PostgreSQL to run code in sections (cursors) of a table effecting parallelism?
Let's say I have a 1 million record table. Each record is uniquely identifiable by id. If the table was divided into 10 cursors and 10 requests were made by a web client for each cursor to say change ...
- 37
1
vote
1
answer
382
views
Cost threshold for parallelism and wait stats
We have an Stored Proc on our prod server (SQL Server 2014) that takes 3hr 40m (on a Sunday: low server activity) to run (collection job costs on 220 jobs with each requiring 6 SP calls, so 13200 SP ...
- 181
3
votes
1
answer
9k
views
SQL Server Threads and Degree Of Parallelism
My understanding about SQL Server threads is that SQL Server breaks down an execution request within a session into several tasks and associates each task with a worker thread. So SQL Server always ...
user99201
2
votes
1
answer
195
views
SQL Server using Parallel Plan for Cost less than 5
I have a pivot query that uses a parallel plan even though the cost is less than 5.
The Cost Threshold for Parallelism is set to default of 5, and MAXDOP is 0.
SELECT Column1, Column2 Column3, ...
- 591
1
vote
0
answers
324
views
Parallel LOAD DATA to different memory tables very slow compared to single one
I'm using a fairly simple bash script to execute mysql load data infile to 11 different MEMORY engine tables.
Example:
load.sh
echo "start $@ $(date)"
mysql -p"mypass" -e "use db; set ...
- 11
12
votes
6
answers
51k
views
Run stored procedures in parallel
I'm looking to try and run the same stored procedure multiple times with different parameters but at the same time.
I'm using SQL 2014
The reason for this is that the procedure takes around 7 hours ...
- 127
3
votes
1
answer
936
views
Why does my parallel query sometimes process billions of extra rows?
I was running some data prep queries when I noticed that one of them sometimes ran much longer than expected. The query should have finished in seconds but sometimes would take minutes to run. Here is ...
- 31.7k
12
votes
2
answers
918
views
Can I refactor this query to get it to run in parallel?
I have a query that takes about 3 hours to run on our server--and it doesn't take advantage of parallel processing. (about 1.15 million records in dbo.Deidentified, 300 records in dbo.NamesMultiWord). ...
- 383
1
vote
4
answers
2k
views
Load testing: The performance of a query is decreasing as the number of parallel executions is increasing
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 ...
- 119
-1
votes
1
answer
351
views
Why parallel seq scan is not chosen by planner for jsonb type of fields? [closed]
I have a table with jsonb type of column (filter_data) and I set
set force_parallel_mode to true
set max_parallel_workers_per_gather to 8
Then I execute
explain analyze
select
stream_packet_id
from ...
- 49
1
vote
1
answer
244
views
When I have 6 cores and they do a table scan and merge afterwards, does every core scan the whole table?
I remember that I once had a missing index on a table and one query was taking ages. In the execution plan I saw that a table scan and a merge was done. Statistics from IO showed me that every core ...
- 1,353
0
votes
1
answer
308
views
The relations between sessions, DOP and sets of parallel execution servers
From How Parallel Execution Works:
The number of parallel execution servers in one set is the degree of parallelism (DOP)
What is the meaning of "one set" in the document? Does it have a ...
- 201
0
votes
1
answer
909
views
PARALLEL_MIN_TIME_THRESHOLD parameter when AUTO or MANUAL mode
From How Parallel Execution Works:
The following is a summary of parallel statement processing when parallel degree policy is set to automatic.
A SQL statement is issued.
The statement is parsed and ...
- 201
0
votes
2
answers
1k
views
How does PARALLEL_MIN_PERCENT affect parallel execution?
From the reference for PARALLEL_MIN_PERCENT:
PARALLEL_MIN_PERCENT lets you specify the minimum percentage of the requested number of parallel execution processes required for parallel execution. ...
- 201
1
vote
1
answer
2k
views
What do PCWP, PCWC, and P->S mean?
Oracle parallel explain plans have a column named IN-OUT, and it often contains values P->S, PCWP, and PCWC. What do these values mean?
Here is an example explain plan with an IN-OUT column:
--------...
- 201
1
vote
2
answers
1k
views
Parallel PostgreSQL Queries with R
I'm running a large query that for various reasons, I've broken into a series of smaller queries. The example below is just to show how the query is broken up by id; the actual query is much more ...
- 251
2
votes
0
answers
917
views
Intra-Query Parallelism Deadlock Resolution
I want to know how SQL Server handles intra-query parallelism deadlocks, and whether it kills one of the threads involved in the deadlock?
Will there be a change in the result set when this deadlock ...
- 21
0
votes
1
answer
377
views
execution sequence of parallel execution plan
I am reading an article about Understanding Parallel Execution
There is a paragraph which is as follows:
But for my understanding of how to read an execution plan, this parallel execution plan ...
- 201
4
votes
2
answers
3k
views
Bloom Filter about filtering in oracle
I read a great blog about bloom filter in Oracle, and I have a few questions about the Bloom filter use case - Filtering in this blog.
SQL> CREATE TABLE DIMENSION
(
col1,
...
- 201
-1
votes
1
answer
414
views
MAXDOP and Physical Cores
I've got an instance built with 4 cores. We have some queries that are going parallel. I know changing the MAXDOP to 0 will single thread the queries. My question is whether the 4 cores will no ...
- 1
3
votes
1
answer
3k
views
Can SQL Server execute two BULK INSERTs in parallel?
I have two heavy queries to run, and both of them take about 1 minute to run. Both queries are using the Bulk Insert command to insert data from text files to tables in the database.
For the two ...
- 693
7
votes
1
answer
479
views
STATISTICS IO for parallel index scan
Assume that there is a table with clustered index
create table [a_table] ([key] binary(900) unique clustered);
and some data
insert into [a_table] ([key])
select top (1000000) row_number() over (...
- 2,334
1
vote
3
answers
3k
views
MYSQL parallel query performance issue
Im working on a project for my company which sells huge amounts of weather data to customers through a Web API. As part of that API, there is a backend service which needs to fetch data from MYSQL ...
2
votes
1
answer
633
views
Is forcing T-SQL query to use parallelism OK?
It can be done by calling the trace flag 8649 in a query option.
OPTION (QUERYTRACEON 8649)
This trace flag causes the cost of the query to be 0, which will always be below the cost threshold for ...
- 45
5
votes
1
answer
4k
views
SQL SERVER 2016 high CPU
I have a brand new server with SQL Server 2016.
It has 24 CPU, about 80go of RAM.
The thing is, sometimes, the CPU gets pretty high (> 70%) with no specific reason at all.
If I look at the 'execute ...
4
votes
1
answer
2k
views
Parallelism with temp table but not table variable?
The first query (inserts into table variable) takes twice as long as the second one. It does not use parallelism in the execution plan.
The second query (inserts into temp table) uses parallelism in ...
- 143
5
votes
1
answer
266
views
sp_who returns more rows than the value of "max degree of parallelism"
sp_configure returns the following values on a server.
name minimum maximum config_value run_value
max degree of parallelism 0 32767 8 8
However, ...
- 1,535
7
votes
1
answer
647
views
Why would MySQL request a shared-lock after setting transaction level to READ UNCOMMITTED
Running a processing job in multiple parallel batches which basically reads big chunks of rows to update a few - it's OK here to use a lower transaction level since I know the relevant values won't be ...
- 171
11
votes
1
answer
9k
views
SQL not engaging parallelism for extremely large query
I have a very large query (~630 lines) that involves a lot of nested SELECT statements and pulls from multiple views. Our SQL server has parallelism set to 2 with a threshold of 95 (set that way ...
- 211
2
votes
1
answer
9k
views
Long running SELECT query fails as deadlock victim to a DELETE on SQL SERVER - Why, and how to avoid this?
Firstly 1 thread selecting a huge amount of data from a table.
Then, before the SELECT query is not yet finished, another thread deletes data from that table.
It causes SELECT query to failed with ...
- 207
0
votes
1
answer
131
views
Why is my query using parallelism, even with MAXDOP = 0?
I'm having some headaches with a procedure.
Long story short, I'm using now a hint to force an index ( now, the query runs in 0.1 secs, after this, it was running in 2min )
but the question is, why ...
- 7,274
2
votes
1
answer
308
views
Max Degree of Parallelism for SQL Server 2008 [duplicate]
Microsoft have recommendations around setting MAXDOP on various versions of SQL Server, but this (old) article raises some issues and recommends setting MAXDOP = 1 generally for SQL Server 2005 and ...
- 41
5
votes
3
answers
15k
views
How to reduce huge CXPACKET & LATCH_EX (ACCESS_METHODS_DATASET_PARENT) wait times?
Problem
We have been experiencing high levels of user disruption due to SQL timeouts accross our systems since the beginning of the year.
The SQL-Server instance in question has very high CPU usage (...
- 53
0
votes
1
answer
1k
views
Turn on multi-threading in Oracle for ETL purposes
As you can see from this question, I was able to configure multi-threading in SQL Server and build a workflow in Informatica to multi-thread a job to move data faster into SQL Server.
Multi-thread ...
- 2,072
5
votes
1
answer
674
views
Are there practical scenarios where inserts + deletes are faster than updates? (SQL Server)
I'm just wondering if there are common scenarios where an insert/ delete combination is faster than an update else insert function.
Here is my specific example.
I have to update a database with ...
- 1,603
4
votes
1
answer
7k
views
"Lock request time out period exceeded" when publishing SqlServer databases in parallel
I am working on a continuous integration environment using SqlServer2014. There I need to publish many SqlServer databases simultaneously on the same instance. Our publish process uses a .sqlproj file ...
3
votes
1
answer
6k
views
What's the biggest bottleneck (time) in writing data to a SQL database?
I'm running an ETL process that is writing about 2 million rows to a SQL Server database.
I'm trying to optimize the time it takes for pure inserts (I guess updates is a different story).
I'm ...
- 1,603
7
votes
2
answers
225
views
Parallel option performing worse on cluster
I'm trying out oracles parallel option on a cluster and surprisingly I am getting worse results with the parallel option. I was expecting some improvement with the parallel option but certainly not ...
- 273