All Questions
Tagged with recursive performance
18 questions
0
votes
1
answer
62
views
PostgreSQL CTE Recursion - How to Improve Performance for Parent->Child Tree (8 levels deep, 890 roots)
I am working on a query that performs PostgreSQL CTE Recursion in PostgreSQL 13. This query is intended to recurse from the root nodes to the deepest leaf node with certain properties (labeled 'begat'...
- 50
1
vote
1
answer
114
views
PostgreSQL recursive query performance improvement
I'm building a database which is used to derive hierarchical relations for which I chose a PostgreSQL recursive CTE .
my table structure is
table_name |column_name |data_type ...
- 13
0
votes
0
answers
31
views
Fastest way of updating a spider web of data
I have a large table with over 60 million rows, which has relational data inside of it. I need to update each of these rows to the newest date, of the newest member, including members that that ...
- 1
2
votes
1
answer
2k
views
CTE Query to CONCAT text (over 10,000 rows)
I am trying to construct a CTE query to loop through rows of data and concatenate text into one line for each SampleUserNumber.
Below is what I am working with:
CREATE TABLE #SampleTable
(
...
0
votes
0
answers
76
views
Cleaner approach for Recursive CTE query
I have a table called entry_changes defined as follows:
Table "public.entry_changes"
Column | Type | ...
- 436
2
votes
1
answer
3k
views
How to query all entities associated with all children (recursive) of a node in SQL?
This question is about SQL in general. Answering specifically for MySQL would be helpful but not necessary.
Ok, I’m having trouble putting this into words… so bear with me.
Say I have a tree of ...
- 143
5
votes
1
answer
324
views
Why does this CTE return the bulk of its results immediately, but then take several minutes to finish?
I've got a schema roughly like this (this is a simplification of my actual schema):
CREATE TABLE foo (
key1 NUMERIC(6) NOT NULL,
key2 VARCHAR(32) NOT NULL,
val VARCHAR(255) NULL,
...
- 163
4
votes
1
answer
4k
views
PostgresSQL recursive query running slow with low load and almost no data
I'm building a simple ACL system and the datastore is Postgres 9.6.
Permissions are assigned to groups, which can have subgroups, and people are assigned to groups.
I have a poor performing query ...
- 143
4
votes
1
answer
11k
views
Recursive cte avoiding loops
I'm trying to write a recursive CTE to explore a workflow system. Unfortunately, I'm getting maximum recursion errors due to loops:
with cteActs as
(
select a.id as [id], aa.TASKNEXTID as [...
- 1,463
4
votes
3
answers
2k
views
Best way to design / query multi-level relationship table
My company is going to develop a mobile app selling services across different regions. We are expecting users are going to hugely depend on the Search function. Thus, I need to make sure the database ...
- 41
7
votes
2
answers
4k
views
Looking for a simpler alternative to a recursive query
The actual query is more involved, but the problem I'm facing can be distilled to this:
A query to filter a rowset of monotonically increasing integers so that - in the final result set, row(n+1)....
- 71
3
votes
2
answers
151
views
Can performance be improved by breaking databases up? [closed]
Performance is my over-riding concern, as I have a heavily recursive CTE that is the engine room of my application. This CTE may have to work on 4 columns of up to 7 million rows. Queries can take ...
- 31
6
votes
2
answers
4k
views
Improve performance for order by with columns from many tables
Using PostgreSQL 8.4, I'm trying to consult two tables with 1 million records using order by with indexed columns of the two tables, and I'm losing performance (with 1 column takes 30 ms and with two ...
2
votes
1
answer
3k
views
Finding the end of a Relationship chain optimally
I have a table that contains an id column, a parent column, and it's child column. So for every record, I know what record comes before it and what will come after it in a chain relationship, but from ...
- 503
1
vote
1
answer
108
views
Finding the current prices for n Fuelstations at a certain point in time
I have a Table where price information is stored in with approx 13 million rows stored in a PostgreSQL 9.5 database.
CREATE TABLE public.de_tt_priceinfo (
id integer NOT NULL DEFAULT nextval('...
- 298
3
votes
0
answers
52
views
Why does retrieving fewer rows take longer? [duplicate]
I have a view that (left outer) joins multiple recursive CTEs. There are about 10k rows. The results of running SELECT * FROM MyView ORDER BY MyPrimaryKey, with statistics time and io on:
SQL Server ...
- 131
1
vote
0
answers
268
views
How can I reduce recursion in Couchbase?
Sometimes, you need to access a certain document (A), but you do not know the key for that document (A), as that data is stored in a different document (B). So now, if you want to fetch A, it looks ...
- 121
8
votes
1
answer
6k
views
Recursive CTE performance
Need help with recursive CTE performance. Below CTE is running very slow as it is trying to pull heirarchical
data recusively. Table is big with every root id having upto 3 recursive itemid. There ...
- 101