Questions tagged [query-performance]

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

Filter by
Sorted by
Tagged with
0 votes
1 answer
22 views

How can I optimise this MySQL query?

I'm trying to make a filter which matches every record in the table to find ones which are within the range @min and @max. They're unxi timestamps if anyone was wondering. It does work right now, but, ...
user avatar
2 votes
1 answer
34 views

MySQL Query slows down on concurrent requests to different subpartitions of a partitioned table

We have partitioned a large table (with over 100 million records) into multiple partitions and subpartitions in our Serverless Aurora to query different subpartitions in parallel. But we find that ...
user avatar
  • 121
2 votes
1 answer
37 views

How to improve simple = condition on a large table?

Check out the following simple query: SELECT * FROM "teammgr_team" WHERE ("teammgr_team"."real" = true AND "teammgr_team"."name" = 'abc'); But it ...
user avatar
  • 133
1 vote
0 answers
14 views

How to efficiently run range based queries in Cassandra

I wanted to use Cassandra in a project, but it's important that I'm able to do a few ranged queries (for example, 12345 <= time < 67890 ). Unfortunately, Cassandra's design seems to preclude ...
user avatar
  • 135
2 votes
0 answers
50 views

Service Broker performance issues after upgrading from 2012 to 2017 - tons of LCK_M_IX

Last night we upgraded a SQL Server 2012 server to SQL Server 2017 + CU 29. This is one of a matched set - we send the same message to both and each processes it. No, this is not AlwaysOn; we're ...
user avatar
  • 433
0 votes
1 answer
18 views

Best approach for basic PHP DB session data storage

I've decided to switch from PHP's native session management based on $_SESSION to use my own, due to many different issues I've had so far with PHP's possibilities, and also because I'm building an ...
user avatar
  • 147
0 votes
0 answers
46 views

MySQL reading speed is much slower on a more powerful server

I have recently provisioned two new servers: Server 1: AMD EPYC 7320P, 128GB DDR4, 7.68TB Micron 5300 SSD Server 2: Intel E2388G, 128GB DDR4, 2x 6TB SATA HDD in software RAID1 Both are running ...
user avatar
1 vote
0 answers
21 views

Slow Query Log Only Logs Few Queries

Slow query is enabled but only logs few queries when you retrieve the slow log. It’s been working ok all last week, suddenly this week, it only logs just 2 or 3 queries. Long query time = 0.01 and I ...
user avatar
  • 11
0 votes
1 answer
68 views

MySQL - Optimizing a large table's join

Im using the following query. This eat my complete CPU in Aurora Cluster. Also too much of wait. I tried by tunning index, but still no luck. Is there any recommendation to make it perform better? ...
user avatar
  • 1,698
0 votes
1 answer
27 views

Slow query optimize help

Db is on Aurora cluster. Added indexes. removed functions from where clause. rewrote some logic. Got number of records to measurable limit, but still the query is performing slow. Attaching query plan ...
user avatar
  • 1
-2 votes
0 answers
14 views

MySQL select script is slow

Good day! I have a problem in MySQL and PHP. I have a script with only 16 data in the table. and I have a simple script. Sometimes querying is slow and it takes to 2minutes to display. I have 128 RAM ...
user avatar
  • 1
1 vote
1 answer
40 views

Best performance to get distinct values for a given key from a big table

Given a table like so: CREATE TABLE attr (user_id uuid, key text, value text); with indexes on key and value (but not a combined index) what are my options for getting a list of distinct values by ...
user avatar
0 votes
3 answers
38 views

How to optimize table for arbitrary user-made queries?

Let's say you have have a large-ish database table, and you'd like to allow users to query it with an API, using several different filter and sort options, and of course supporting pagination. What ...
user avatar
  • 1
0 votes
1 answer
62 views

How to optimize the simplest SQL query of a very simple table?

Query SELECT * FROM public.data Table id → PK Integer data → json timestamp → timestamp version → integer The data column can have "large" values (e.g ~250kb) Result of query Time: 4m 52s ...
user avatar
0 votes
0 answers
46 views

Large table taking forever to return query results

I'm working on a [Rails+PostgreSQL] project where we have a table called attempts that has 13,578,350 rows. Getting that count (Attempt.count, which converts to SELECT COUNT(*) FROM "attempts&...
user avatar
1 vote
2 answers
39 views

Complicated query running orders of magnitude faster than simple query

Query A takes 3 seconds or more to produce 294 results: SELECT user_id, id FROM web_useradvisory WHERE emailed IS NULL ORDER BY user_id ASC, id ASC; However, Query B takes about 10 milliseconds (!!) ...
user avatar
0 votes
0 answers
13 views

What issue will i run into for real time data writing and reading with lots of table using RDS PSQL

I have a solution that writes sensor data to a database every minute but I do not think the way we have our database is scalable. We are using one database and each sensor has its own table. We have ...
user avatar
-1 votes
1 answer
27 views

LIKE query with leading wildcards in large MySQL production database

According to insights in Amazon RDS the following query in a large MySQL production database is causing high loads ( ~ 50.000.000 entries): SELECT * FROM entities WHERE status='ready' AND user_id='...
user avatar
  • 163
0 votes
0 answers
13 views

Slow Database queries when on Secondary Node of Availability Group

Failover happens almost immediately, but an application connecting to several databases on the server runs WAY slower (Queries return in 4 minutes compared to 30 seconds). The hardware and software on ...
user avatar
0 votes
1 answer
44 views

Searching against a large list of people at the same time in Postgres

I have a list of people that varies between 2-5k entries that has this general format: firstname1 lastname1, firstname2 lastname2, firstname3 lastname3 ... I have a user_table which contains about 250-...
user avatar
0 votes
1 answer
45 views

Improving a MySQL query that result in a Lock wait timeout exceeded; try restarting transaction

I have a table that tracks the action of my users (used for logging and debugging for their purpose). From time to time, I need to mark the data as treated by doing the following call : UPDATE actions ...
user avatar
  • 101
0 votes
0 answers
33 views

Why is my time range query slow even when I have BRIN index on the timestamp?

I have a table that stores chat messages, like so; id (int8), timestamp (timestamp), author (text), channel(text), content(text) I have BTREE indexes on all of the fields, except for timestamp, which ...
user avatar
  • 101
-1 votes
1 answer
16 views

Should we create new tables instead of filtering 1 large table?

In an application with groups and posts where each post can be in exactly 1 group, we can have the table POSTS(post_id, group_id, post_content). However, there's gonna be a lot of posts in a lot of ...
user avatar
0 votes
0 answers
28 views

Two views yielding the same results switch places performance-wise when called remotly

An SQL Server 2000 has a view "A" that produces around 700 rows of data in about 30 seconds. I was tasked with optimizing "A" and created a view "B" that produces the ...
user avatar
1 vote
2 answers
36 views

Could MySQL perform better on a 10GB network storage with raid vs local HDD?

Compared to using a HDD on local computer and do many MySQL read / writes on it, could it have more queries per second if I install 10GBe network cards and I store the MySQL data on a raid network ...
user avatar
  • 131
2 votes
1 answer
240 views

SQL Server: Is it worth checking for change in a field before the update?

I'm investigating this statement performance on SQL Server 2019 (I've extracted the important part): UPDATE m SET A1 = src.A1, A2 = src.A2, A3 = src.A3, ...
user avatar
  • 185
4 votes
1 answer
101 views

Execution plan with a lot of ComputeScalar operations

I executed the same query with and without OPTION (RECOMPILE). When I compare the 2 plans, visually the one main difference I see is that the plan without option recompile shows a lot of ComputeScalar ...
user avatar
  • 368
1 vote
1 answer
37 views

Why is the optimizer not using the clustering index on my table?

I have this table Table "public.lineitem" Column | Type | Collation | Nullable | Default -----------------+---------------+-----------+----------+----...
user avatar
  • 95
6 votes
1 answer
832 views

Do datatypes of columns affect query performance?

Let's suppose I have this table: Table "public.orders" Column | Type | Collation | Nullable | Default -----------------+---------------+-----------+----------+----...
user avatar
  • 95
0 votes
1 answer
48 views

How to speed up subquery

Let's suppose we have: select o_orderpriority, count(*) as order_count from orders where o_orderdate>='01/07/1993'and o_orderdate<'01/10/1993' and exists (select * from ...
user avatar
  • 95
0 votes
0 answers
8 views

How to optimise a Redshift table with a SUPER data type column?

We currently have a small table in Redshift with 3 columns col1 (id): varchar(36) col2: varchar(36) col3: SUPER Where the SUPER column contains a dictionary which can vary in length. The total size ...
user avatar
  • 1
0 votes
1 answer
19 views

Query with EXISTS-subquery taking about 280 ms in 80 % of cases, and less than 1 ms in 20 % of cases

I noticed a query that seemed to take way too long most of the time, so I investigated it with the Laravel query logger (to get the time and the actual SQL from the query builder). Excerpt: >>&...
user avatar
  • 103
1 vote
3 answers
57 views

Optimizing a PostgreSQL query with many small lookups

I have a database about books and book sales. The tables look like this: CREATE TABLE purchase ( person_id integer, book_id integer, CONSTRAINT purchase_pk PRIMARY KEY (person_id, book_id) ...
user avatar
0 votes
0 answers
57 views

Refreshing Materialized Views Taking Longer After PostgreSQL Upgrade

I have upgraded a PostgreSQL instance from 10 to 13 using pg_upgrade. Though I analyzed the databases but the Materialized Views I have are taking longer to refresh than before. For some of these ...
user avatar
2 votes
2 answers
307 views

how to find out the `Key Lookups` in the current query plan?

I am putting a query to list the key lookups that are present in the current requests being executed, I basically would like to tackle then and see if I could eliminate these key lookup from the ...
user avatar
1 vote
1 answer
91 views

Moving from Amazon RDS to own server made queries slow (maybe because heap fetches increased?)

Hope you're doing good. I've recently moved from Amazon RDS to my own PostgreSQL instance in Hetzner (in order to colocate it with the server and reduce the roundtrip). However I think that some ...
user avatar
  • 11
1 vote
5 answers
118 views

Optimising a query for matching multiple values on a related table

I am trying to optimise a query for fetching all game IDs where certain units (Archer, Cavalry, etc.) were present. I am using the players table, which contains the game_id, and the units table, which ...
user avatar
  • 113
0 votes
0 answers
41 views

How can i store execution plans for certain stored procs

Unfortunately we cannot use query store as we are on SQL 2014 and need to find a way to store execution plans of certain stored procedures. I saw XE like sp statement completed but unfortunately it ...
user avatar
1 vote
1 answer
56 views

Optimizing simple query in PostgreSQL (two table join and sorting)

I have a simple query that should return data about booking, but it misbehaves and sometimes does not return data within 1 minute. So I have main booking table: table booking_data - bid bigint primary ...
user avatar
  • 115
0 votes
0 answers
63 views

Improve MySQL REGEXP query with any of multiple words MATCHING

I noticed that REGEXP query works well with few words but lose a lot of performance by adding more words. Is there any way to improve a query like this? SELECT * FROM table WHERE textfield REGEXP ('(...
user avatar
0 votes
1 answer
32 views

Using SELECT by key value, the smaller the result count, the slower the query performance

In my MariaDB database I store combined metadata from multiple other sources (face databases). There is a 'main' table containing great majority of data, and each row is identified by both it's own ID,...
user avatar
  • 1
0 votes
1 answer
54 views

How a batch can get how much CPU it used in SQL Server 2019

Is there a statement I can put at the end of a batch to find out how many milliseconds of CPU it used? Something like SET STATISTICS TIME but for the whole batch, not each statement.
user avatar
  • 39
0 votes
1 answer
45 views

Optimize GROUP BY, ORDER BY and many SUM and AVG operation query

I have this query, from TPCH-H benchmark: explain analyze select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice*...
user avatar
  • 95
0 votes
0 answers
15 views

Deleting rows from a tag_map if parent tag exists

I have a simple tag_map CREATE TABLE tag_map ( article_id int(11) unsigned NOT NULL, tag_id int(11) unsigned NOT NULL, INDEX(tag_id), PRIMARY KEY(article_id,tag_id) ) ENGINE=InnoDB There is a table ...
user avatar
  • 4,179
2 votes
3 answers
169 views

Efficient unique Inner Join

Context I have a query that is something like where I want to query all of the days that each product was on sale at at least 1 store. This query runs as part of a larger query and runs whenever a ...
user avatar
2 votes
0 answers
88 views

Why does SQL Server 2019 stored proc get Adaptive Join on Development Server, but not on Production Server?

I have a stored proc with one select statement that gets a plan w/ Adaptive Joins on my development server, but not on my production server. What could explain that? I'm not much interested in ...
user avatar
0 votes
0 answers
61 views

Unique query to multiple tables with array. Is it possible?

I'm using PostgreSQL 14. Today, I have the following tables, with more than 4 million lines each table: tbx_00, tbx_01, tbx_02, tbx_03, tbx_04 and tby_00, tby_01, tby_02, tby_03, tby_04 About the ...
user avatar
0 votes
2 answers
62 views

Why use both universal identifiers and separately incremented primary keys for strong entities?

There is a good public example for this: The relational model of ChEMBL (A bioactive molecule database). Here all strong entities (such as molecules) have a database-wide unique identifier which also ...
user avatar
  • 335
0 votes
0 answers
129 views

Clear database cache in SQL Server and also Postgresql

My current tasks involve optimizing queries for both SQL Server (using TSQL language) and PostgreSQL. There are ways to run large batches of the same query to get timing results. However, it appears ...
user avatar
1 vote
0 answers
43 views

Query Planner has bad row estimate for second-degree joined link table and uses inefficient nested join

Note: This is a toy database example I came up with and a simplification of a real design, so please disregard from commenting about the way the database is structured. Each Group has precisely one ...
user avatar
  • 207

1
2 3 4 5
55