Questions tagged [query-performance]

For questions about improving the performance and/or efficiency of database queries.

Filter by
Sorted by
Tagged with
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 (...

1
2 3 4 5
47