Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
2,728
questions
0
votes
0
answers
13
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
22
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
13
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
26
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
27
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
229
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
96
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
31
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
791
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
46
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
7
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
17
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
51
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
43
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
293
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
47
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
votes
0
answers
39
views
Table Statistics are out of date quickly
We have a table in our dev environment that has 2 million rows. Recently we dropped a few unused indexes from this table. A week after that, we noticed that the stored procedure that uses this table ...
1
vote
5
answers
84
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
39
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 ...
- 703
1
vote
1
answer
45
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 ...
- 113
-3
votes
0
answers
28
views
Total hours spent on working on each project using SQL Server (Azure Data Studio)
Question:: Create a new view called “Project Hours” which would show for every project how many total hours were spent working on it, and the number of employees working on the project. Query the view ...
0
votes
0
answers
21
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
29
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
47
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
36
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
144
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
86
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
31
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
59
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 ...
- 325
0
votes
0
answers
53
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
24
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 ...
- 197
0
votes
1
answer
28
views
Query to update one column based on the amount of another column
I have a table with below structure :
create table Z_TEST_ONE
(
col_id NUMBER,
bed_amnt NUMBER,
bes_amnt NUMBER,
bop NUMBER
)
Sample data is like this :
col_id bed_amnt ...
- 1,294
0
votes
1
answer
42
views
Optimize join without denormalization
I have two tables:
orders(order_id, scan_timestamp) and
order_products(order_product_id, order_id, pick_number, quantity)
and I want to know how many of a particular product (identified by ...
- 415
0
votes
1
answer
23
views
Does Greg Rahn's "join order benchmark" reflect real world OLAP join scenarios?
Greg Rahn's "join order benchmark" has dozens of inner join queries on the IMDB database.
Does this benchmark and the database itself reflect common real world OLAP join scenarios? How does ...
- 1
2
votes
1
answer
61
views
Avoiding Hash match to improve CPU usage for a specific query
We are trying to resolve or figure out change in plan for one of the top sql queries by CPU.
This query we have found (it's a vendor-based product) which unfortunately I do not have access to extract ...
- 703
0
votes
1
answer
25
views
Mysql takes longer when using php or python compared to worbench queries
Versions:
MySQL Community Server: 8.0.16 in Windows server (Configuration file: unknow)
Workbench: 8.0
Problem:
When I make a query through workbench, it takes 1 second but when I make a query through ...
- 1
5
votes
2
answers
244
views
dateadd in the where clause - how can I avoid a full scan in this particular situation?
I have the following query:
SELECT u.userId,
app.applicationId
FROM app.application AS app
INNER JOIN app.applicant AS ap
ON ...
- 14.8k
2
votes
2
answers
168
views
Writing a query that will quit if there's a lock on a target row
Is it possible to write an UPDATE query that will simply quit if the record it tries to change is locked by another process (rather than waiting for the lock to be released)?
I have a process that ...
- 811
0
votes
1
answer
25
views
Inhibiting results to pane in SSMS (TSQL language) via script
I found that one could use a menu to "discard" results in SSMS (TSQL language). Please see: Original Question about inhibiting results and its answer.
However, I was wondering if there is a ...
- 125
1
vote
1
answer
35
views
mysql query takes too long for a million row table using count() and group by
I want to retrieve phone_num and their count of unread message for each phone number from the table user_message.
Below mentioned query takes 10 seconds to give result which is super duper slow for me....
10
votes
1
answer
729
views
Why is a parallel top N sort apparently much more CPU efficient than a serial top N sort?
I'm testing against SQL Server 2019 CU14. I have a pure row-mode query that selects the top 50 rows from a complicated view. The full query takes 25426 ms of CPU time at MAXDOP 1 and 19068 ms of CPU ...
- 30.3k
0
votes
0
answers
25
views
Optimizing a view query that uses pivot aggregation - Oracle
I have a table with columns as -
| ID | cat_1 | cat_2 | type | value |
|----|-------|-------|------|-------|
|1 | a |b | t1 | 4 |
|2 | b |d | t2 | 5 |
|3 | b |e ...
2
votes
2
answers
74
views
Dealing with suboptimal postgres query plans related to nested loops
I'm developing an application that dynamically builds up SQL queries and executes them against postgres 13.
Some of the queries are very slow because they use nested-loops but the query planner ...
- 121
0
votes
0
answers
27
views
IN clause with large value slower on mariadb compare to mysql 5.5
I'm searching for a reason that explain why a request is slower on mariadb compare to mysql
The request is pretty simple :
select id, firstname, lastname from users where id in (1,2,...20000);
Yes it'...
0
votes
0
answers
46
views
PostgreSQL Database Performance issue
I have performance issue with PostgreSQL Database. These are excerpted related tables:
CREATE TABLE public.patient (
patient_id serial4 NOT NULL,
user_id int4 NULL,
cid varchar(13) NULL,
...
1
vote
0
answers
48
views
Potential causes for unusually long queries (postgres)
Recently we've noticed some SELECT queries taking an unusually long time to complete and are a bit stumped on what the reason could be.
Looking at DB (and server) CPU and IO during the time the ...
- 11
1
vote
2
answers
49
views
Slow query for multiple data at same timestamp
Hi everyone,
I'm currently facing a problem to query efficiently my database to retrieve all rows at same timestamp.
I will explain,
I have two table of data using TimescaleDb Hypertable with ...
- 11
0
votes
2
answers
31
views
Fastest query to process data in small batches without repetition
I have java app that is using MySQL in the backend. I have the following table:
A = int, B = varchar, C = timestamp
A | B | C
1 | 100 | 2022-03-01 12:00:00
2 | 200 | 2022-03-01 12:00:01
3 | 100 | ...
- 101
0
votes
0
answers
36
views
Will a query which uses IS NULL always perform bad on large tables?
I've got the following table :
CREATE TABLE `file` (
`id` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`track_id` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`...
- 113