Questions tagged [query-performance]

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

Filter by
Sorted by
Tagged with
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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, ...
user avatar
  • 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 ...
user avatar
  • 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 -----------------+---------------+-----------+----------+----...
user avatar
  • 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 -----------------+---------------+-----------+----------+----...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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: >>&...
user avatar
  • 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) ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ('(...
user avatar
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,...
user avatar
  • 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.
user avatar
  • 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*...
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
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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....
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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'...
user avatar
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, ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 | ...
user avatar
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, `...
user avatar
  • 113

1
2 3 4 5
55