All Questions
Tagged with subquery performance
88 questions
0
votes
0
answers
38
views
Performance difference in SQL queries using JOINs with function
I have two SQL queries that are structurally the same, but have a significant difference in execution time.
The first query takes around 500ms to execute, while the second one takes only 50ms.
Both ...
- 33
0
votes
1
answer
65
views
Why the following first of two queries in MSSQL is faster as I want the second one?
1st Query: result 32 seconds (1,758,220 rows)
SELECT
CAST(ID.PYYY AS varchar(8))+','+
CAST(ID.SYYY AS varchar(8))+','+
(
SELECT TOP 1 (
CAST(IMM.INVOICE_DATE1 AS varchar(8))+','+
CAST(...
- 101
2
votes
4
answers
2k
views
Why is my big Postgres query faster if I separate it into 3 smaller queries?
I have a big/complex query (with an OR clause and 2 subqueries that have multiple joins and an IN condition). Executing the big query is slow.
However, if I break my big query down into 3 smaller ...
- 121
0
votes
2
answers
3k
views
Query with CASE in subquery join clause is very slow on PostgreSQL but fast on Oracle and MariaDB
We have an attribute-oriented data management system with bitemporality and flexible tenant priorization logic. Deletion of records is done by inserting the same row in DELETED status. Tenants may ...
- 11
1
vote
1
answer
404
views
basic subquery evaluation
I am new at subqueries. Is there a general strategy on how basic subqueries are evaluated and executed (besides from sql engines automatically optimizing them). For example, consider this select query:...
1
vote
1
answer
93
views
Is there anything I can do to optimise this complex query?
I have a rather complex query that spans several layers of subqueries, the two primary ones fetching data from the exact same fields, only one takes the values themselves, the other takes aggregates.
...
- 151
1
vote
2
answers
128
views
Index isn't used when value is a result of a subquery or from a wrapper query
I have a simple one-to-many relationship: accounts and events where an account may have many events.
accounts
id: uuid
...more irrelevant fields
events
id: serial
type: enum ['activated', '...
- 111
0
votes
1
answer
383
views
SQL (Postgres): Subquery in WHERE clause with IN operator is slow
I have the following SQL Statement:
select count(*)
from person p
cross join task t
where t.id in (
select tp.task_id
from task_participant tp
where tp.person_id = p.id )
and p.id = 1234
...
- 111
0
votes
1
answer
571
views
How to perform query operation on another query result
How to get approved , unapproved , deleted count from the below result using sql query.
SELECT approved,deleted_at,count(id)AS count FROM `payment_voucher`
WHERE company_id = 1 GROUP BY approved,...
2
votes
2
answers
1k
views
Postgresql expanding view multiple times instead of executing it once
I have a rahter long subquery which I store as a view
CREATE VIEW scans_minmax AS
SELECT tmp.source,
value ->> 'Ok' AS "Ok",
...
- 123
0
votes
1
answer
44
views
Query with a few subqueries and appropriate indicies taking, on average, 613ms to execute
I have a ton of queries that do a similar thing as to what I'm doing here, but none of them have the performance issues this one does:
SELECT
(SELECT lead_id FROM leads WHERE to_id = lead_id OR ...
- 147
1
vote
1
answer
48
views
selecting data after filtering out
I am using RDBMS MySQL 5.7. I have 3 tables with the following structures:
Table A - primary table - products:
+----+-----------+-------------+------------+--------+
| id | title | description |...
- 65
0
votes
2
answers
940
views
MySQL Function work very slow
I have writed this mysql function, it's worked perfekt but very slowly, what can I do for better performance, have you any idea?
note:i need all created virtual columns
DROP FUNCTION IF EXISTS ...
6
votes
1
answer
433
views
Why an incorrect JOIN using correlated sub-query is so much slower
I'm doing some fairly lightweight data massaging/cleaning and ran into a problem where one version of JOIN using a correlated sub-query (probably an erroneous one) ran much much slower than what I ...
- 163
1
vote
3
answers
4k
views
How to apply outer limit offset and filters in the subquery to avoid grouping over the complete table used in subquery in Postgresql
I have legacy tables similar to the following:
employee
------------------------------------
| employee_id | name
------------------------------------
| 1 | David
| 2 ...
- 111
1
vote
1
answer
516
views
Update "oldest" row
I need to update the "oldest" row in the database (for now, its limit 1, but I need to also be able to set it to n).
I'm essentially doing a constant stream of "update the oldest rows and retrieve ...
- 223
-1
votes
2
answers
19k
views
Group by in subquery
So my problem is the following. Consider this query:
SELECT
P.id,
(SELECT barcode FROM product WHERE publisher_id = P.id LIMIT 1) AS barcode_sample
FROM
publisher P
WHERE
P.name LIKE '...
- 3
3
votes
0
answers
2k
views
Why is Postgres choosing a Hash-Join instead of a Nested Join that is 200x faster?
I run the following query against Postgres 9.5 which returns article information along with the currently valid price:
WITH selected_article_rm(id) AS (
VALUES
('80512e52-a406-4a9b-a8df-...
- 256
0
votes
1
answer
236
views
How to make subtraction without repeating the subqueries? [closed]
I need to efficiently obtain:
players who play more than one world cup and who have never been champion.
primary-key | foreign-key | pk-and-fk
PLAYER<player_id, player_name, country_player, .....
- 109
1
vote
2
answers
1k
views
How to use GROUP BY in an efficient way without losing attributes? [closed]
I am working with PostgreSQL.
My idea is to group players who appear more than 3 times in table-a.
table-a = <player_id, year_world, c, d, e>
With the first subquery I get player_id and the ...
- 109
0
votes
3
answers
80
views
Optimize query to filter active IDs for a given day from big table
I have a PostgreSQL table called users with 4 columns:
user_id integer, -- we can have a lot of the same user_id
name text, -- name of the user
value text NOT NULL, -- ...
- 37
-1
votes
1
answer
4k
views
Replace Subquery with JOIN - MYSQL
Can you please suggest how i can replace subquery in fetching select columns to improve the performance of query :
SELECT sa.id AS patAppIdSA,
sa.rte_id AS patRTEId,
sa.case_type_id AS ...
- 109
1
vote
1
answer
265
views
conversion of a non correlated sub query to a join statement
Is it possible to get rid of the sub query and convert the entire query into a JOIN query?
SELECT student_id,
COUNT(attendance)
FROM student_attendance
WHERE attendance = 'ABSENT'
GROUP BY ...
- 257
2
votes
2
answers
22k
views
how to add loop in date column in sql through query? [duplicate]
If ID = 01234 and this ID has done transaction in 3 time in a month like this:
***DAY_no Balance***
1/1/2018 5000
10/1/2018 10000
15/1/2018 ...
- 21
0
votes
1
answer
576
views
Count total number of nested one to many relationships
Tables: Users, Costs, Payments
Users can have many costs. Costs can have many payments
I want to get information about the user AND count of all payments that they made
This query returns result ...
- 141
1
vote
3
answers
12k
views
Joining two tables by the nearest match
General problem: I want to do a left join on two huge tables, but there is no matching key - I want to join the left table with the "nearest" row in the right table. In this example I want to join ...
- 211
1
vote
3
answers
174
views
Slow query with sub SELECTs
Unsure how to optimize this query but it and about 15 variations of it need to run for a dashboard page. Any and all suggestions are welcomed! Thanks.
Time: 0.466s
Time without sub-selects: 0.058s
...
- 11
5
votes
2
answers
238
views
A better way to write this query?
I have the following schema for my database (Postgresql 10):
CREATE TABLE "PulledTexts" (
"Id" serial PRIMARY KEY,
"BaseText" TEXT,
"CleanText" TEXT
);
CREATE TABLE "UniqueWords" (
"Id" serial ...
- 153
1
vote
1
answer
54
views
Implementing a query, two types of strategies
Tables have not been created yet, but to simplify there is a groups and an items tables. A group contains an item id (and a group id). And several groups may contain the same item id.
For instance
...
- 513
2
votes
1
answer
1k
views
Best way to move CTE into query so planner can better optimize and I can add indexes speed it up? [closed]
I'm trying to incorporate the CTE into my final query. I can do it, but it slows it down a lot because I have to do the same sub query for CTE d over and over again (I think):
with d AS(
SELECT
...
2
votes
1
answer
40
views
selecting next todo item from child table
I've a table with millions of records, relationship is one (object) to many (object_items).
object_items:
CREATE TABLE `object_items` (
`item_name` varchar(50) NOT NULL DEFAULT '',
`object_id` ...
- 123
3
votes
3
answers
5k
views
How to avoid a subquery in FILTER clause?
Schema:
CREATE TABLE "applications" (
"id" SERIAL NOT NULL PRIMARY KEY,
"country" VARCHAR(2) NOT NULL,
"created" TIMESTAMP WITH TIME ...
- 475
3
votes
2
answers
3k
views
Optimize a query with two range conditions
I have a structure similar to this one:
CREATE TABLE `author` (
`id` int(11) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8 ...
- 131
15
votes
1
answer
474
views
Poor performing subquery with date comparisons
When using a subquery to find the total count of all prior records with a matching field, the performance is terrible on a table with as little as 50k records. Without the subquery, the query executes ...
- 255
8
votes
1
answer
10k
views
Should i use subquery to limit table before a join?
In the case of a join followed by a where clause, would it be better to use a subquery to limit the results, and then do the join? Example:
SELECT *
FROM Customers
NATURAL JOIN Orders
WHERE ...
- 415
8
votes
3
answers
13k
views
Performance of count(*) in subquery
Suppose we have the following queries:
1.
SELECT COUNT(*) FROM some_big_table WHERE some_col = 'some_val'
2.
SELECT COUNT(*) FROM ( SELECT * FROM some_big_table WHERE some_col = 'some_val' ...
- 195
1
vote
2
answers
398
views
Can some redundancy in this nested SELECT COUNT query be eliminated?
For the record, this is not a homework question. I am a professional brushing up on my SQL skills.
Given a table called world that looks like this,
I want to find the continents where all countries ...
- 175
1
vote
1
answer
98
views
SQL multiple select with where conditions performs really poorly
Data structure is something like this:
Number| ID | DateTime | Status
50000001, 101, 1/1/09 0:10, PO
50000001, 110, 1/1/09 0:11, PO
50000001, 102, 1/1/09 0:15, PO
50000001, 101, 1/1/09 0:10, PCK
...
- 65
2
votes
2
answers
3k
views
Postgres optimizing nested exists
I have a table of events that each take place during a time range, and I'm trying to create a template for a query which can find events that take place within a specified time period of correlated ...
- 185
2
votes
1
answer
148
views
Slow subquery in select join
I have a slow subquery as below (taking 2 min 35.03 seconds)
SELECT *
FROM erp_gl_batch_item i
LEFT JOIN erp_gl_batch b
ON i.gl_batch_id = b.gl_batch_id
LEFT JOIN erp_customer c
ON i....
- 369
5
votes
1
answer
5k
views
Why does adding ROWNUM to a query improve performance?
I have two queries:
1) This query has a ROWNUM column (takes 20 seconds to execute):
SELECT
ROWNUM
,ROAD_ID
,VERTEX_INDEX
,SDE.ST_X(ST_POINT) AS X
,SDE.ST_Y(ST_POINT) AS Y
...
- 1,497
1
vote
1
answer
1k
views
MySQL subquery with IN operator optimization
I have a MySQL query in which I try to get articles with keywords matching those of a given page and that are not already posted to it.
The db tables are :
items : Articles table.
features : ...
- 35
1
vote
1
answer
60
views
How to improve my mysql query execution performance
I have a main table named prog as below:
CREATE TABLE `prog` (
`prog_id` int(11) NOT NULL AUTO_INCREMENT,
`prog_insert_date` varchar(16) COLLATE utf8_persian_ci NOT NULL,
`prog_edit_date` varchar(...
6
votes
2
answers
124
views
Retrieving data of vehicle drivers that have not been assigned to a job
Struggling with something for the past 24 hours. Needed some genius to shine some light please. I am trying to display a weekly job rota/log for drivers, which will display:
Jobs that have not been ...
- 95
2
votes
1
answer
169
views
JOIN with the last id
I want to select a record from one table (customers table) and join only with the last from other related table (sales table). I know two ways of doing it, but I don't know which one have better ...
- 161
0
votes
2
answers
181
views
Optimizing sql server MIN in the select blah where blah IN (SELECT MIN(blah)
is there any effective way to tune the query below?
I have a filtered index on OrganisationID <> 0
Primary key is on another field, not relevant for this query ItemCode varchar(20).
I have tagged ...
- 16.6k
0
votes
1
answer
250
views
A multi-table JOIN query as subquery to a query that JOINS the same tables. Good or bad?
I have the following tables in my postgres database
- power_relations - (id, properties)
- power_relation_members - (id, power_relation_id, member_id, member_type)
- power_lines - (...
3
votes
2
answers
190
views
Create index for multiple WHERE EXISTS subqueries
I have to find an efficient way to write a very specific query for an application that I'm developing.
The idea is the following: Find all the genetic markers for which there exists at least one ...
- 165
7
votes
2
answers
11k
views
best way to avoid redundant aggregate functions and/or group by columns
Suppose I have two tables:
Foo:
id
baz
Bar:
id
foo_id
boom
So a Foo has many Bars. I frequently find myself in situations where I need to compute an aggregate across the Bars for a given set of ...
- 183
1
vote
0
answers
227
views
Moving slow sub-query to With clause
I am trying to optimize this query :
SELECT sum(a-alias.s_count) AS count
FROM a-table a-alias
WHERE
(
a-alias.created_at_minute_id >=
(floor(extract(epoch from(
select ...
- 111