Questions tagged [query-optimization]
For questions about making database queries run faster. PLEASE INCLUDE QUERY PLANS, AND TABLE AND INDEX DEFINITIONS in your question. Please use additional tags to specify your query language and server maker. Some of these questions may belong on https://DBA.stackexchange.com, especially if they involve reindexing, query plans, etc.
query-optimization
8,107
questions
1
vote
0
answers
10
views
Which time unit does graphdb's onto:measure uses?
The GraphDB docs states that if i want collect the execution time of a query a should use onto:measure like in the example bellow. However it doesn´t states which time unit it returns neither if the ...
- 11
0
votes
0
answers
10
views
MongoDB text search with text indexes slower than using $regex
I have a shoes collection with about 1.4 millions records. I tried to get list of shoes containing "Adidas Men's Clinch-X M Running Shoe" phrase. At first, I did not index the "name&...
0
votes
1
answer
23
views
How to avoid duplicate computation in Cypher
Let's consider a Cypher query with a, b, the two ends of a complex path :
MATCH (a:Label1)-[a quite complex path]-(b:Label2)
WHERE <a set of complex constraints>
RETURN DISTINCT a, b
At this ...
- 85
0
votes
0
answers
43
views
Understanding multiple CTE filtering when used in a view
This is the same question as Understanding multiple CTE filtering when used in a view. inPostgreSQL, but for SQL Server and with reproduction code.
An ex colleague of mine has written many highly ...
- 633
0
votes
1
answer
58
views
Hash and merge join never finishes, loops does
I ran into issues with one query that used to work fine in the past, but now it is so slow so it basically never finishes (perhaps in some hours, beyond any timeouts).
The query is like
select ky....
- 924
0
votes
1
answer
110
views
Optimizing SQL query - joining views of views is extremely slow [closed]
Following question pertains to Microsoft SQL Azure (RTM) - 12.0.2000.8. The XML I got from Azure Data Studio does not work on Paste The Plan website, so I am sharing the raw XML instead.
I have a ...
- 116
-3
votes
0
answers
55
views
Optimise this query [closed]
Recently in a interview, i came across a question where i was asked what would be steps to optimise following query
select * from users
where id=1 or name='xyz'
Conversation:
Me:Check if we need all ...
- 556
0
votes
0
answers
58
views
Understanding multiple CTE filtering when used in a view. inPostgreSQL
An ex colleague of mine has written many highly complex nested-view views using multiple CTEs.
The end views are very fast.
It seems to me that his end views filter the initial CTEs, making the rest ...
- 633
3
votes
2
answers
84
views
Improve processing time of applying a function over a vector and grouping by columns
I am trying to apply a function over data.table columns, and grouping by columns value.
I am using the lapply fuction, but my script is quite slow.
To give some context, I am working of probability ...
- 33
0
votes
0
answers
82
views
Joining 2 huge tables timing out
I have 2 tables with large data. The first one has 580 million rows and the second one has 1200 Million rows. I need to run a query where I join on 3 columns.
The 2nd table has multiple rows for the ...
- 11
0
votes
2
answers
42
views
Mariadb / mysql EXPLAIN to optimize data query
I am using mariadb to store a large number of measurements. A common query I do selects for a data range. It takes a little long, something like 10 to 20 seconds, and it gets worse and worse, so I was ...
- 13
1
vote
2
answers
77
views
How to optimize a query?
I have a T-SQL query that run during 6 minutes.
There is several sub-query on the same table in it. I think it is the cause of the problem.
But I have no idea for optimizing it.
SELECT dateheure, bac,...
-1
votes
1
answer
87
views
Optimizing SQL join query with date comparison
I have a query that takes around 2 seconds to fetch 16,900 rows:
SELECT x.lid
FROM schema1.view1 x
INNER JOIN schema1.view2 y
ON x.cid = y.cid
and datediff(day, x.indt, y.linvcy)=0 -- ...
- 116
0
votes
0
answers
66
views
Joining 10 Collections in MongoDB
I encountered the following scenario: I’m doing an aggregation for the MasterCollection collection. I’m “joining” this collection with other 9 collections in the aggregation.
In the end, I’m merging ...
1
vote
0
answers
33
views
Significant performance differences for 'ORDER BY x LIMIT 1' and 'ORDER BY x LIMIT 2'
I've got the following table:
name
type
id
PrimaryKey, UUID, Unique
test_id
ForeignKey, UUID, Indexed
created_at
DateTime+TZ, Indexed
2 418 139 Distinct rows (test_id)
283 036 392 Distinct rows ...
- 374