Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
2,328
questions
0
votes
0answers
18 views
Using a trigger to check if a row exists in mysql — why so slow?
I am using a trigger to check if a row exists. The code below works, but it is very very slow. I suspect that it has to do with the way I am handling NULLs.
Any suggestion to optimize it?
DELIMITER $$
...
0
votes
1answer
16 views
Why does the query plan start with a not-filtered-for table when the filtered-for table's entries exceed a certain threshold?
We process data for our customers. Each customerrequest and is associated with one or more items (less than two on average) and falls into one of <100 categories, foreign keys to which are also ...
0
votes
0answers
15 views
Complex rights structure for users/members and keeping it fast
We are working on a database that holds various information about 15.000 members in a complex structure. These members are managed/viewed by about 1.000 and we need a system to handled rights for ...
13
votes
3answers
2k views
Colleague says to never use an OR statement in SQL, is this true?
My colleague now responsible for SQL development says that I should never use an OR statement because it will mess up the query optimizer and ignore table indexes producing slow queries. I couldn't ...
0
votes
2answers
35 views
Optimize duplicated but negated subquery
I have what I call a 'Hamlet' query in the form:
select * from stuff a
left join otherStuff b on (a.x=b.x) -- lots of joins ommited
where
(b.ToBe = 1 and a.Being in (select Being from stuffThatIs ...
0
votes
0answers
30 views
Improve the speed of this PostgreSQL query [closed]
I want to improve the execution time of the below query. Can you suggest a better way?
select id
from cp_contactcompany
where cp_contactcompany.name ilike '%A%'
and cp_contactcompany.company_id = ...
0
votes
0answers
39 views
Decrease Data IO operation on query to lower the DTU cost on Azure
We have a FunctionApp in Azure that triggers on a timer and does some DB operations every 5 minutes. These operation are taking a very long time to complete. When I run the same query on my computer ...
0
votes
2answers
75 views
Select arbitrary single value for GROUP BY: What's the fastest option?
I have a query that I use to indicate locations in a map where there are overlapping points:
select
min(objectid) as min_objectid,
longitudex,
latitudey,
count(1) as count,
min(...
0
votes
0answers
24 views
Will the query be quicker if adding more WHERE conditions?
Assume I have a table t1 and two queries q1 and q2 on t1:
SELECT xxx FROM t1 WHERE <condition-1>
and
SELECT xxx FROM t1 WHERE <condition-2>,
where <condition-1> logically ...
0
votes
0answers
9 views
Can Redshift process CTE / with chains without making interim tables?
If you are careful about structuring a CTE chain, is Redshift capable of analyzing it and determining that interim results are being used in a linear fashion? If so, it could drop any interim tables ...
0
votes
2answers
30 views
postgresql - declaring variables to be used for start_time - end_time range
I'm relatively new to postgresql, so forgive me if this is a simple problem.
I'm trying to implement a query with start_time and end_time arguments, which the user can input their own dates into for ...
1
vote
2answers
41 views
Postgres lag() function has huge impact on query
As reported to my previous question (that for completeness is reported here), I've solved my problem using the window function LAG to get the time between the previous and next row, sum them and then ...
0
votes
2answers
39 views
Are there better alternatives rathet than joining a table twice for having one record for a specific column in the output?
I have two tables with below structures:
create table TABLE_LC
( lc_num NUMBER,
brn_code NUMBER,
brn_role NUMBER)
create table BRN
( brn_code NUMBER,
brn_name VARCHAR2(50))
Some example data ...
0
votes
1answer
47 views
PostgreSQL LWLock: lock_manager issue
In our application we need to insert a lot of historical data that will be consumed later by some mobile devices.
These data is organized by a numeric incremental attribute that the device will use to ...
3
votes
1answer
56 views
Query optimization - unread messages in all channels of a conversation
I have a slow query which I'm trying to tune. Tables in DB (mssql):
Users, ChatGroups, ChatMessages, ChatGroupChannels, ChatMessageSeenFast
The query:
WITH messages_ranked AS( SELECT p.*, ROW_NUMBER(...
1
vote
1answer
74 views
How to speed up SQL Server 2014 Spatial functions
I have some normalized tables with about 1,2M records that I collect in a view in a SQL Server 2014 database.
Note that I have simplified the names of the tables and columns.
To illustrate I have ...
0
votes
2answers
56 views
Postgres create Index
im running out with ideas for creating postgres index for a better performance.
This example below is a selection of a big table with many entries for a diary to person relation.
usually, i create an ...
0
votes
1answer
62 views
Why does our MySQL server become non-responsive with the processlist showing tons of processes waiting in STATE=init, INFO=commit?
I work for a company that's been around for a while and has a large MySQL (5.6.48) monolith running in RDS. Recently, the database has starting going unresponsive for 10-30 minutes at a time during ...
1
vote
1answer
26 views
Strategies for long term data growth optimization for a query with an index on a joined table
Given two simple, contrived example tables
create schema test;
create table test.exchange (
id serial primary key,
created_at date not null
);
create index on test.exchange(created_at);
create ...
0
votes
1answer
32 views
order by slowing down query with multiple joins and limit/offset on larger result sets
I am having trouble with the following query taking quite a long time to process when results are large. The limit and offset can change as this is used with pagination. The range on ...
0
votes
1answer
39 views
Query optimizer triggering statistics update seems to be slowing down queries
We are running SQL Server 2016 and have a very simple stored procedure that does a select using a clustered index seek of a single row in a table with 140 million rows. In that table, we keep around ...
1
vote
1answer
36 views
PostgreSQL - trying to speed up query responses from view that has join and group by
I have these two tables:
precipitaion: store precipitation data at daily (freq = 'daily') and hourly (freq = 'hourly') frequencies for 7 months so far (I receive regular updates).
Column | ...
1
vote
1answer
45 views
PostgreSQL Sorting an output of a fulltext search query takes very long time
I have a table with a few millions of rows where I perform a SELECT with a complex fulltext search query in the WHERE clause (over a fulltext search index column) and with ORDER BY id DESC at the end. ...
0
votes
0answers
19 views
Group by with clause in postgresql taking lots of time
I am new to sql and trying to write a query in postgresql which is taking lot of time during execution (~8 mins), total number of data in the table is around 298650 rows. and i am using below stated ...
0
votes
1answer
25 views
Why does my Query use a Bitmap Heap Scan
my simple query is not really fast and I don't know why my query is using Bitmap Heap Scan.
explain analyze verbose
SELECT connections, epoch_time
FROM connections
WHERE host_name = 'xyz.z'
ORDER ...
1
vote
1answer
31 views
How to run my query on a one-to-many relation to run efficiently
I have two tables in one-to-many rel, I want to run a query efficiently returning data from the base table and some aggregates from the joined subtable. Adding in some indices, I still did not manage ...
0
votes
0answers
15 views
SELECT statement optimization MySQL
I am looking for a way how to make my SELECT query even faster than it is now, because I have a feeling it should be possible to make it faster.
Here is the query
SELECT r.id_customer, ROUND(AVG(tp....
0
votes
0answers
58 views
Postgres SQL CPU and RAM optimisation to improve decryption performance with pg_crypto
I am currently working on an application that requires column-based decryption for a few thousand of rows on a regular basis. The queries are decrypted using pgp_sym_decrypt, where several columns are ...
0
votes
1answer
45 views
Why is this postgres query ignoring the index in the subquery?
I have a couple of tables, companies and datapoints. If I query for datapoints from a given company, it uses the index:
sophia=> explain analyze select count(distinct(source_doc)) from public....
-2
votes
1answer
49 views
Indexes not used for join
I join listings_tail table and materialised view cals_reviewed_wide_scaled on listing_id column of type integer.
The table has around 1,5 billion rows and 2 mln unique listing ids. The MV has been ...
0
votes
1answer
69 views
SQL CTE Expression with Cross Join - Can it be simplified?
I am looking to simplify the below SQL Query. Currently it's taking 44 mins to run.
The main issue seems to be the WHERE [Versions] = (SELECT max([Versions]) from BPC_CTE) statement.
What I'd like to ...
0
votes
2answers
80 views
Slow planning time on PostgreSQL 12.4
I have a database with Openstreetmap location data, and in particular there is a polygon table with around 500 million rows.
I created indexes on all of my queries consisting of using gist(way) where ...
1
vote
2answers
103 views
Postgres NOT NULL optimization
I am trying to optimize this SQL query:
select topics.id from "topics"
left join "articles_topics" on "topics"."id" = "articles_topics"."...
0
votes
1answer
23 views
Query with big IN clause
I'm having an performance issue with a query, the query is something like:
Select *
from requests
where start_date > {start_date}
and end_date < {end_date}
and employee in {employee_id_list}
...
2
votes
1answer
29 views
How does mysql optimiser chooses a composite Index
I have a table with the following structure.
-id (bigint|pk)
-funnel_id (bigint|fk with funnels table)
-affiliate_id (bigint|fk with affiliates table)
-created_timestamp (bigint)
Observations
...
2
votes
1answer
39 views
Read-only database: Normalize or not for best query performace
I have a pandas DataFrame that looks a bit like this:
id name date col1 col2 total
0 123456748 EXAMPLENAME 2020-09-01 SOMEDATA MOREDATA 5.99
1 123456748 EXAMPLENAME ...
2
votes
1answer
45 views
What can cause delay in SELECT statement execution and high ASYNC_NETWORK_IO even with “disregard results after execution”
We are facing performance problems on our SQL Servers.
7 servers, all running the same application, all have the same problem.
SELECT @@VERSION
Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0....
0
votes
1answer
75 views
Query tables with 400 million records fast [closed]
Table: Customer Data
Rows: 400 million
Indexes: Non-clustered (as per queries)
Issue: Index Scan happening with most queries, 400 million scan take too long to complete
Need advise on how to address ...
0
votes
1answer
23 views
How to make MySQL query performant WHERE one column uses greater than and one column uses less than?
I'd like to make this query perfomant--
SELECT *
FROM my_table
WHERE foo_id = 8
AND enabled = true
AND start_time < '2020-09-12T06:59:59.999Z'
AND end_time > '2020-09-07T07:00:00.000Z'
This is ...
2
votes
1answer
54 views
Should I duplicate columns between tables to speed-up aggregations like SUM? [closed]
I have two tables in PostgreSQL 10.12 database:
waste_card (id (PK), user_id, manufacture_date, transfer_date, address_id, card_type, .. (and other card_specific_firelds)
wastes (user_id, ...
2
votes
1answer
26 views
composit index and possible keys specific query
i have query like this
select
date_format(referencedate, '%m/%d/%Y') as PostingDate,
reference_code,
"" as DocNo,
Account_Number,
detail_desc as Description,
case
...
0
votes
1answer
46 views
MongoDB not using wildcard nested array index
I have the following collection:
{
_id: 12345,
quizzes: [
{
_id: 111111,
done: true
}
]
},
{
_id: 78910,
quizzes: [
{
_id: 22222,
done: ...
0
votes
0answers
5 views
Can you link tables based off data in a cell/field?
I have 4 tables that I want to link. The main table is Contributors and then I have 3 tables for the events, Golf, Breakfast, and Dinner. I have a field that has a value list with 3 selections in ...
2
votes
1answer
53 views
MariaDB wants to use bizarre and extremely inefficient query plan on Aria, but not on InnoDB (5 seconds vs 1+ hour query)
I'm having some troubles getting a query to preform as expected on my MariaDB 10.4.14 system.
We recently migrated the database from InnoDB to Aria so we could store the INDEX separately on a fast PCI-...
7
votes
1answer
373 views
Improving performance when ordering by a column of a joined table
I have a parent table that contains a foreign key to a lookup table (simplified example):
CREATE TABLE [dbo].[Parent] (
[Id] [uniqueidentifier] NOT NULL,
[LookupId] [uniqueidentifier] NULL
)
...
-1
votes
1answer
37 views
MySQL Query Fetch Time increases when LEFT JOIN with big table [closed]
I am fetching the same amount of data with 2 different queries. However, one of them have a fetching time of ~x130 compared to the other. Being the only difference between the two queries a LEFT JOIN ...
0
votes
1answer
30 views
Get top 5 students for each class in many-to-many relationship [duplicate]
So I'm doing this in MySQL.
I have the table students and classes. There is a many-to-many relationship between which I track through the student_class_rel table. Below are the tables and their ...
3
votes
1answer
54 views
Row vs Columnar DB for queries with multiple OR statements
I understand columnar databases are great for speed queries where you don't use many fields to lookup against, but what if you only used OR statements?
For instance, I want all the records that have (...
1
vote
1answer
36 views
How to prevent a column from repeating in the final result when using the LISTAGG function
I have a table like this:
CUST_NUMBER | CUST_NAME | CUST_PROFESSION
------------------------------------------
1 | Mike | Teacher
1 | Mike | Engineer
2 |...
3
votes
0answers
51 views
Why is PostgreSQL not using composite Index on JSONB Fields?
Two of a PostgreSQL DB table's columns are JSONB field (j1, j2). My queries are on (j1->>'f1')::int and (j2->>'f2'), for example:
SELECT id from table where (j1->>'f1')::int = 1 AND (...
