Questions tagged [parallelism]

Reducing run times by breaking a problem into parts that can be completed by separate execution paths simultaneously.

Filter by
Sorted by
Tagged with
0
votes
1answer
695 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 ...
2
votes
0answers
82 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 ...
4
votes
1answer
223 views

No parallelism in Express Edition of SQL Server

I have SQL Server 2019 Express Edition (CU8) on Windows Server 2019 (1809 version) and all of my queries go serial with NoParallelPlansInDesktopOrExpressEdition in NoparallelPlanReason property. Is it ...
0
votes
0answers
49 views

Update query performance improvement (MS-SQL)

UPDATE M SET ARCHIVE_FLAG = 'N' FROM MARKPOSTED_TMP M WHERE EXISTS (SELECT 1 FROM SHFMKPSTMM S INNER JOIN SHIFT_TMP S1 ON M.MARKID = S.MARKID AND S.SHIFTID = S1.SHIFTID AND S1.ARCHIVE_FLAG <> '...
0
votes
2answers
72 views

Cardinality , Parallel Hint alternative for MS-SQL

UPDATE MARK M SET ARCHIVE_FLAG = 'N' WHERE EXISTS (SELECT /*+ cardinality(S1, 10) parallel(S1,8)*/ 1 FROM SHFASG S, SHIFT S1 WHERE S.ID = S1.ID AND M.ID = S.MARKID AND ARCHIVE_FLAG <> 'Y'); ...
0
votes
0answers
41 views

Use Python to parallelize processing of rows, or is there a native way?

As mentioned in a recent question of mine, I'm new to Oracle: Running Oracle's sqlldr on VirtualBox VM from my MacBook (client) machine I'm back, this time with a question about the best way to ...
2
votes
3answers
116 views

Slow Parallel SQL Server query, almost instant in serial

I have a SQL Server query as follows (obfuscated): UPDATE [TABLE1] SET [COLUMN1] = CAST('N' AS CHAR(1)) FROM [TABLE1] WHERE (COLUMN1 = '2' AND COLUMN2 IN('VAL1', 'VAL2', 'VAL3')) OR ...
0
votes
1answer
201 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 ...
1
vote
3answers
2k 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 ...
1
vote
1answer
277 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 ...
0
votes
1answer
44 views

Does ' /*+parallel(<Specific Table >,4)*/ ' have better impact on query performance in comparison with simple parallel hint '/*+parallel(4)*/'?

I have three tables with below structure: create table customer_info (customer_num number, customer_firstName varchar2(100), customer_lastName varchar2(100), branch_code varchar2(...
1
vote
1answer
32 views

how to sum times in EXPLAIN for Postgres parallel query

I'm having trouble understanding this EXPLAIN on Postgres 12.3: EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT count(1) FROM mytable WHERE page ~ 'foo'; This is a 22GB table with 30 million rows, on a ...
0
votes
2answers
60 views

importing postgres tar file throws parallel not supported in postgres 12

I'm trying to import a db that someone sent me: testdb.tar (43GB size) First I've imported using: pg_restore --host localhost --port 5432 --username postgres --dbname testdb --role postgres --no-...
-3
votes
1answer
717 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 ...
3
votes
1answer
108 views

Save CPU cycles on SQL Server Query Plans (Better without Stats than with on wide tables)

Assume that a query in question has a very wide set of columns. The query looks like the following: set statistics io,time on; Select a.Col1, a.Col2, b.Col3, b.Col4, c.Col5 From FirstWideTable a ...
6
votes
2answers
7k views

MS SQL Server: Do multiple queries in a batch ever execute in parallel and if so what happens when the second query is dependent on the first?

If I have a batch of two different SELECT statements, is it possible for them to be executed in parallel (if the SQL Optimizer deems it to be the most efficient execution method)? If the first SELECT ...
3
votes
1answer
3k views

Query runs in parallel but it is showing as blocked by itself

I have a query which runs in parallel with a MAXDOP of 8. When i looked at sp_who2 i see that the same session ID is being repeated several times(>8) with different connection id. I used the below ...
0
votes
2answers
685 views

How to run INSERT INTO AS SELECT in parallel on Oracle?

I have a very large table AUD$ that I want to copy it to another empty one with the same structure AUD_BACKUP. INSERT INTO TEST_USER.AUD_BACKUP SELECT * FROM SYS.AUD$; COMMIT; How do I use ...
0
votes
0answers
80 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....
14
votes
2answers
4k 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 ...
0
votes
3answers
38 views

Is there performance loss in out of sequence inserted rows (MySQL InnoDB)

I am trying to migrate from a MySQL AWS RDS instance with a huge SSD and too much excess space down to a small one, and data migration is the only method. There are four tables in the range of 330GB-...
0
votes
1answer
116 views

How many parallel workers can I have on Postgres?

I working on a server with Ubuntu, 320 GB SSD, 6 cores and 16GB Ram, but Postgres is having issues some queries that take very long time to run, and with parallel queries, that are increasing the ...
5
votes
0answers
707 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 ...
0
votes
1answer
38 views

Should an update statement query plan ever go parallel?

If an update statement's query plan does go parallel, does it guarantee a communication buffer resource deadlock, or is that determined by whether the parallel plan is using all cores?
3
votes
1answer
205 views

How can INDEX rebuilds be going parallel when MAXDOP is set to 1

I am periodically running into a THREADPOOL wait issue with a SharePoint data store of hundreds of databases using a SQL Server 2008R2 Standard Edition instance (recently migrated to an Azure VM). It ...
0
votes
0answers
116 views

parallelism in sql server in user defined function

a four page long query (that creates two temporal tables, and then joins them) in sql server runs in a dozen of seconds (thanks to parallel execution). The same query packed into a user defined ...
3
votes
2answers
146 views

How many threads being used while query execution?

I have AdventureWorks2014 database installed on my test SQL Server 2014. I am planning to execute the following query: SELECT * FROM Sales.SalesOrderDetail sod INNER JOIN Production.Product p ON sod....
12
votes
4answers
3k views

Dealing with CXPACKET waits - setting cost threshold for parallelism

As a follow-up to my previous question on perf troubleshooting a Sharepoint site, I was wondering if I could do something about the CXPACKET waits. I know the knee-jerk solution is to turn off all ...
1
vote
0answers
13 views

Calculating forecast data in parallel

I'd like to ask about how to calculate forecast data in parallel in a relational DB like PostgreSQL, which seems like a very typical problem when doing forecasts. Let's say we're trying to get the ...
2
votes
2answers
2k views

Why might a parallel pg_restore command take hours longer to complete its non-parallel equivalent?

I used pg_restore to restore a 50 GB db from a directory backup using the following command which used 4 jobs: pg_restore -d analytics -U postgres -j 4 -v "D:\Program Files\PostgreSQL\10\backups\...
1
vote
1answer
402 views

Run stored procedures in other schemas from stored procedure [closed]

I have a current process: a large procedure in one schema. There is a plan to have A, B1, B2, B3 schemas (B-schemas are identical, could be B-n schemas) in Oracle 12c database. Schemas B1, B2, B3 have ...
2
votes
2answers
458 views

Is PostgreSQL function parallel safety intended for functions which update data?

I am having some trouble understanding function parallel safety. I can understand what it does - which is to specify whether the function will be executed by the parallel master node or whether the ...
0
votes
0answers
82 views

How to trigger Mysql parallel query function to start?

Please help,how to trigger Mysql parallel query function to start? MySQL version is 8.0.19. mysql> show variables like 'innodb_parallel_read_threads'; +------------------------------+-------+ | ...
0
votes
1answer
230 views

Is each query batch in a dynamic SQL statement executed sequentially or can it be parallelized, when using sp_ExecuteSql?

I have about 500 databases that are all structured the same but the data is unique in each database. Sometimes I write dynamic SQL statements to hit all (or a subset) of those databases and will ...
15
votes
1answer
2k views

sp_cursoropen and parallelism

I'm running into a performance problem with a query that I can't seem to get my head around. I pulled the query out of a cursor definition. This query takes seconds to execute SELECT A.JOBTYPE FROM ...
0
votes
1answer
389 views

Postgres parallel seq scan making high CPU Utilization

When I run a select query which runs on a view in Postgres, it is starting 3 processes for a single query, which is causing high CPU utilization when multiple job runs in parallel. How to avoid using ...
0
votes
1answer
71 views

which command to execute to run multiple OLAP queries?

I have 10 sql files that contains different OLAP queries. I want to run them as a batch. Meaning by batch, Postgres will consider each file as a batch and execute the queries at once (depends on its ...
1
vote
1answer
1k 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 ...
11
votes
2answers
683 views

Why/when does SQL Server evaluate the probe side of an inner hash join when the build side was empty?

Setup DROP TABLE IF EXISTS #EmptyTable, #BigTable CREATE TABLE #EmptyTable(A int); CREATE TABLE #BigTable(A int); INSERT INTO #BigTable SELECT TOP 10000000 CRYPT_GEN_RANDOM(3) FROM sys....
8
votes
2answers
1k 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 ...
8
votes
1answer
7k 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 ...
5
votes
1answer
241 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 ...
5
votes
3answers
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 ...
10
votes
2answers
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 ...
0
votes
1answer
159 views

Multiple SPIDs in sp_who2, Deadlocks and Parallelism

There were reports of slowness on our web application - screens hanging, some unable to log in. It's an OLTP system with approx 50 concurrent users. Upon investigation I found a few things: Number ...
2
votes
1answer
164 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 ...
2
votes
1answer
536 views

Is parallel execution enabled or disabled by default in Oracle Standard Edition database version 12.1?

Oracle Standard Edition 12.1 on Red Hat Linux 7 According to the documentation, parallel execution is enabled by default in Oracle SE 12.1. However, when looking at the database parameters, the ...
0
votes
1answer
1k views

Number of Worker threads available in PostgreSQL

In SQL Server we can have many worker threads per CPU core. Does anyone know if the same is true in PostgreSQL and can point me at a reference? (assume version 11). The background is that we have ...
4
votes
1answer
537 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 ...
0
votes
1answer
39 views

Is there a way to have SQL work in parallelism with two computers?

Amateur here. I have a couple of the same old 4 core computers that I am trying to use to beef up some automatic query scripts that run every evening for my hobby. I recently found MPICH to have the ...