Questions tagged [parallelism]
Reducing run times by breaking a problem into parts that can be completed by separate execution paths simultaneously.
197
questions
1
vote
1
answer
575
views
Parallel insert and delete into staging tables
I work on a small system that receives quite a lot of data from multiple sources (hundreds of them). Depending on few factors I can get anywhere from few rows to a couple of thousands (in fact ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
2
votes
1
answer
60
views
How to make a Postgresql function parallelly running?
I have thousands tables with same structure, and I need to add a index on each.
In order to simplify my work, I code a function like this:
CREATE OR REPLACE FUNCTION createIndexTD(
IN tbl_name ...
- 16.6k
3
votes
1
answer
67
views
Is this function PARALLEL SAFE?
Looking at the function f_jsonb_select_keys from this answer:
How to select sub-object with given keys from JSONB?
https://www.postgresql.org/docs/current/functions-aggregate.html
Aggregate functions ...
- 167k
4
votes
1
answer
142
views
SQL Server generates a query plan with parallelism and TOP statement
I have two simple queries:
SELECT TOP(20) * FROM Clients ORDER BY City
SELECT TOP(20) Id, Name, City FROM Clients ORDER BY City
In the first case I get a result like this
Id
Name
City
many more ...
- 3,327
14
votes
3
answers
6k
views
Parallel Statistics Update
In SQL Server 2008 or later, is UPDATE STATISTICS WITH FULLSCAN a single threaded operation or it can use parallelism? How about update statistics with default sampling - can it use parallelism? I don'...
- 80.3k
1
vote
1
answer
143
views
Why does parallelism cause lock escalation and where is the tipping point?
I use the customized Stack Overflow database (180GB) and run a simple update query: (there is only a clustered index on the Users table)
Begin Tran
Update U set U.Reputation=100000
from StackOverflow....
- 80.3k
0
votes
1
answer
847
views
PostgreSQL: running SQL statements in parallel in a SQL script
I use a for loop in a script to compute geometries according to values in a series, and insert the results into a table (t1). Is there a way to rewrite the loop below using parallelism? What approach ...
CommunityBot
- 1
4
votes
2
answers
1k
views
Is SQL Server able to use internal parallelism for an update statement?
I am struggling to find the best way to migrate some "varchar" columns to "nvarchar". One of the options i am using is to add new nvarchar column(s) then update the values from the ...
- 31.7k
1
vote
1
answer
1k
views
Does CREATE/REFRESH MATERIALIZED VIEW in PostgreSQL use the parallel planner?
I am wondering whether CREATE (with data) or REFRESH MATERIALIZED VIEW uses the parallel planner. When I EXPLAIN ANALYZE the query by itself (any query with parallel potential works here) without ...
- 80.3k
2
votes
1
answer
160
views
How to reap benefits of parallelism for PostgreSQL DELETE?
I have a fairly niche use case of a (AWS Aurora) PostgreSQL database and we are running into performance issues that I have discovered a "clever hack" (not actually clever) to work around ...
- 36.3k
3
votes
0
answers
273
views
What is the cause of persistent SQL Server deadlocks with a chosen victim, where the victim is never stopped, and no transactions are rolled back?
We are experiencing an issue with Microsoft SQL Server where every few days of running our application against the database, it goes into a state where it keeps deadlocking. It logs a deadlock about ...
- 81.1k
11
votes
2
answers
3k
views
Is support for Parallel Scalar UDF a reasonable feature request?
It is fairly well documented that scalar UDF's force an overall serial plan.
Running functions in parallel
Given a large number of rows coming into a point in the pipeline where a UDF must be ...
- 2,181
0
votes
0
answers
90
views
Forced Serialization on SQL Server 2019
I ran Brent Ozar's BlitzCache and it identified a query that is executing with Forced Serialization. I'm assuming it is because of the Calendar functions contained with the query. I was looking for ...
- 159
7
votes
1
answer
2k
views
When to mark functions as PARALLEL RESTRICTED vs PARALLEL SAFE?
The manual for Postgres 12 says:
The following operations are always parallel restricted.
Scans of common table expressions (CTEs).
Scans of temporary tables.
...
Further down on ...
- 167k
6
votes
1
answer
1k
views
In SQL Server, what is "Partitioned Table Parallelism" that is only available in Enterprise Edition?
I am reviewing the differences between Standard and Enterprise editions of SQL Server and can't reproduce the differences advertised in this demo that explains the differences - the performance I ...
- 33
0
votes
1
answer
92
views
How to update multiple databases in parallel
As part of performance regression testing of a backup product, one stage of the workflow requires updating a single table that exists in multiple databases on a single SQL Server instance and then ...
- 80.3k
-3
votes
1
answer
69
views
0
votes
1
answer
168
views
exclusiveness of multiple table locks for a single transaction
Consider an api that performs writes to multiple tables:
BEGIN TRANSACTION;
UPDATE ... TABLE1 ... VALUES ...;
UPDATE ... TABLE2 ... VALUES ...;
UPDATE ... TABLE3 ... VALUES ...;
UPDATE ... ...
- 1,477
0
votes
1
answer
136
views
Parallel and faster evaluation of SELECTing boolean comparing count
I have a table:
CREATE TABLE IF NOT EXISTS blacklist
(
id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('blacklist_sequence') PRIMARY KEY,
device_id ...
- 2,632
0
votes
1
answer
49
views
Parallel Insert is not showing in MSSQL Stored Procedure execution Plan
As you can see in the above screenshot, this query provides a parallel insert:
INSERT INTO #StateAllocationData WITH (TABLOCK)
(ProjectID,StateId,StateLineDescriptionId,PartnerID,Value)
SELECT @...
- 69.1k
3
votes
0
answers
80
views
Evaluating Success of CTFP Value Change
We have recently made a change to the CTFP setting on our test server with a view to rolling this out to live.
Our current CTFP is 15 so we tried setting it to 50 which is often seen as a better value....
- 1,741
0
votes
1
answer
1k
views
How to get a parallel nested loop in postgres 12?
I have a lateral join, and I would like each subselect to execute in parallel. According to the docs (https://www.postgresql.org/docs/12/parallel-plans.html), nested loops can be parallelized:
Just ...
CommunityBot
- 1
6
votes
3
answers
1k
views
Why is cost threshold for parallelism ignored?
SQL Server 2012 SP2 Enterprise Edition. Users complaining of slowness. Monitoring tool shows highest wait event is CXPACKET.
Instance settings
MAXDOP: 8
Cost Threshold for Parallelism: 175
Ran ...
- 80.3k
6
votes
2
answers
771
views
Non parallel plan only in Azure when SESSION_CONTEXT is used
I'm observing strange difference between query plans that I'm getting on my local machine and on Azure SQL. I'm trying to implement row level security, where I read user identifier from ...
- 80.3k
1
vote
1
answer
55
views
Potentially Improper Row Count Read in Parallel Bitmap Hash Match Plan
I am trying to determine whether or not this is an execution plan reporting bug or an actual feature that is working as intended.
In my execution plan, when using MAXDOP 1, I am seeing the entire ...
- 80.3k
-1
votes
2
answers
296
views
Why does MAXDOP = 0 result in high CXPACKET wait time?
During a parallel query execution (whether or not to go parallel is decided by the cost threshold for parallelism) MAXDOP limits the number of tasks per request.
Assuming cost threshold for ...
- 16.6k
2
votes
2
answers
73
views
Find Which Queries will be affected by Proposed Change to CTFP
The Cost Threshold for Parallelism Setting on one of our servers is set at what is generally considered too low (15) and we are considering increasing to 50 in hope to reduce CPU as it is becoming ...
- 9,825
1
vote
3
answers
116
views
Optimizing a PostgreSQL query with many small lookups
I have a database about books and book sales.
The tables look like this:
CREATE TABLE purchase (
person_id integer,
book_id integer,
CONSTRAINT purchase_pk PRIMARY KEY (person_id, book_id)
...
- 167k
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 ...
- 80.3k
0
votes
0
answers
50
views
What is the difference between statement level and object level parallel hint for a select Statement
Does object level parallel hint affect the table settings for parallelism?
Will statement level parallel hint apply to only that particular query and won't affect any default settings?
1
vote
2
answers
2k
views
How can a cheap function in a SELECT make the whole query slow?
I'm using Postgres 13.3 with inner and outer queries that both only produce a single row (just some stats about row counts).
I can't figure out why Query2 below is so much slower than Query1. They ...
- 167k
12
votes
1
answer
860
views
Why is a parallel top N sort apparently much more CPU efficient than a serial top N sort?
I'm testing against SQL Server 2019 CU14. I have a pure row-mode query that selects the top 50 rows from a complicated view. The full query takes 25426 ms of CPU time at MAXDOP 1 and 19068 ms of CPU ...
- 80.3k
2
votes
1
answer
734
views
Running parallel functions with union all
I'm trying to run 3 functions at the same time using parallelism and union all. I've read that union queries are candidates for parallelism but i can't get it to work.
The query looks like this (the ...
- 42.5k
6
votes
1
answer
350
views
SQL Server Scalar UDF Parallelism Mystery!
Referencing this blog post by Erik Darling on the website of my favorite SQL Server guru - Brent Ozar:
When you select from that table alone, it shows "CouldNotGenerateValidParallelPlan".
...
- 80.3k
1
vote
0
answers
410
views
Bottleneck when inserting many rows into different tables
TLDR: I want to bulk insert into many initially empty tables at once, without the performance of each insert being affected too badly. I seem to be hitting a performance bottleneck I can't yet explain....
- 1
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 ...
- 1
3
votes
1
answer
282
views
CPU vs Elapsed & Parallelism
I have read threads here and here and I get that elapsed time is the total duration of the task - and if the elapsed time is less than CPU time, the query went parallel.
After typing that, I was ...
- 69.1k
1
vote
1
answer
79
views
SSIS: Can't save "parallel load" option in Oracle destination with Attunity driver, how to solve this?
I am working on an existing SSIS package that has proven to run successfully, however, due to data volume issues, I would like to try enabling the "parallel load" option by selecting the ...
- 15.7k
0
votes
0
answers
141
views
Are mutliple client-based queries multithreaded internally in PostgreSQL?
This seems to suggest, that if i connect to the same instance of Postgres running on my machine, multiple connections from clients are handled in parallel. Is that true?
Can I start 4 shells running ...
- 113
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 ...
- 1
0
votes
0
answers
74
views
SQL Server query tuning strategy
I'm new to query tuning and have been using Brent Ozar's tools to identify costly queries on the server.
One query that is executed numerous times per minute is triggering warnings for:
Forced ...
- 16.6k
1
vote
3
answers
770
views
SQL Server not creating parallel plan with OPTION(MAXDOP 20)
We have a UAT3 server hosted on VM with 8 sockets and 20 processer, we have similar UAT2 server hosted on the same VM with same configuration.
We are running a below query on both the server
select ...
- 80.3k
70
votes
4
answers
87k
views
Can a single PostgreSQL query use multiple cores?
In recent versions of PostgreSQL (as of Dec 2013), can we share a query between two or more cores to get a performance boost? Or should we get faster cores?
- 24.3k
6
votes
2
answers
619
views
Can a server wide setting of 'Max Degree of Parallelism' = 1 cause Brent Ozar's sp_BlitzCache to flag execution plan as 'forced-serialization'?
I am using Brent Ozar's sp_BlitzCache store procedure and I'm attempting to nail down why it is reporting:
"Something in your plan is forcing a serial query. Further
investigation is needed if ...
- 37.4k
0
votes
1
answer
143
views
Should I change parallelism (MaxDop and Threshold)?
Seems like I have problems with parallelism because largest wait types are CXCONSUMER and CXPACKET.
Server has 8 cores.
So I am planning to bump up
Cost Threshold to 50 and
MaxDop to 4
Currently I ...
- 80.3k
0
votes
2
answers
227
views
multiple tables INSERT and DELETE in parallel?
I have 25 tables I refresh in one transaction. Few of them refresh much longer than others. I wonder would it possible to refresh "short-timers" in parallel of "long-timers" in ...
- 2,234
0
votes
1
answer
170
views
How to confirm the positive impact of changing Cost Threshold for parallelism to a value higher than default value of 5
We have a DB Server where we found a simple update query going parallel concurrently and causing blockings and deadlock. The cost of the query was around 7. CTFP on the server was set to default value ...
- 37.4k
0
votes
1
answer
227
views
How to reduce the maximum degree of parallelism at session level?
I have inherited a legacy application that includes a job that creates performance issue at instance level. The DBA told us about this and our team needs to fix the stored procedure that is the most ...
- 1,261
2
votes
0
answers
189
views
Unhandled Deadlocks with SQL Server 2017 [closed]
We’re running into issues with unresolved deadlocks on the most recent version of SQL-Server 2017, has anybody seen a problem like this before?
Detailed explanation: We migrated our database from ...
- 21