Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
2,745
questions
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, ...
- 1
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 101
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 ...
- 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?
...
- 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 ...
- 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 ...
- 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 ...
- 231
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 ...
- 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
...
- 101
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&...
- 163
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 (!!) ...
- 123
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 ...
-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='...
- 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 ...
- 23
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-...
- 17
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
...
- 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 ...
- 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 ...
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 ...
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 ...
- 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,
...
- 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 ...
- 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
-----------------+---------------+-----------+----------+----...
- 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
-----------------+---------------+-----------+----------+----...
- 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
...
- 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 ...
- 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:
>>&...
- 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)
...
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 ...
- 442
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 ...
- 14.8k
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 ...
- 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 ...
- 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 ...
- 701
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 ...
- 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 ('(...
- 207
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,...
- 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.
- 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*...
- 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 ...
- 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 ...
- 23
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 ...
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 ...
- 1
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 ...
- 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 ...
- 125
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 ...
- 207