Questions tagged [optimization]

In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.

Filter by
Sorted by
Tagged with
4 votes
0 answers
41 views

SQL Server: how Row Goal affects the same index operation?

I am tuning some SQL performance in the SQL Server 2019, and I ran into issues with one query that involves the issue with the row goals. The simplified query is: select TOP 1 ISNULL(RUNID,-1) from ...
Jan Drozen's user avatar
1 vote
1 answer
47 views

How to improve performance for this query ? Any Suggestions

Query: with firstData as ( select r.display_name as counter, r.createdat, r.status, r.id as runid from run r inner join customer c on c.id = r.badmasterid ...
ankush chauhan's user avatar
0 votes
2 answers
27 views

Optimizing Reads on versioned table with multiple ANDed "IN" clauses

I am trying to find an optimized way to query for some data given some very particular tables. Frankly, I think some of the optimization might involve a table refactor, but I am trying to stave that ...
Mrpebbles's user avatar
  • 101
0 votes
3 answers
27 views

PostgreSQL enforcing order dependent constraints within a query

Is there a best practice for enforcing the order in which search conditions are applied? We have seen a few queries where the optimizer has reordered the operations and passed rows through a search ...
Justin Lowen's user avatar
0 votes
0 answers
18 views

Local SQL to Managed Cloud SQL via ADF. Copy Data throughput fell off a cliff

I have an on-site mini PC acting as a data extraction and replication SQL server that copies data nightly from the ERP production database that's running some legacy linux architecture. The copying is ...
funkyman50's user avatar
1 vote
2 answers
41 views

Optimized conception for score table

I have a simple question yet I couldn't find a direct answer/explanation. Sorry if duplicate I want to build a score table for a small quiz game where users get questions and they can either be right ...
1000k's user avatar
  • 13
0 votes
1 answer
33 views

Strategy for Postgres updates with very high write load, but low consistency requirements

I need to collect statistics on profile thumbnail views for a social media app, backed by Postgres. Every time the thumbnail of a user appears anywhere on a page (in a list of profiles, or next to ...
Luke Hutchison's user avatar
1 vote
2 answers
39 views

Table partitioning for data pruning purposes

We are using MySQL database. The total space it can accommodate is 250 GB. It is currently at 225 GB. With this, there had been this initiative within our team to create a daily cron job for daily ...
Xmus Jackson Flaxon Waxon's user avatar
0 votes
0 answers
15 views

Distributions when only one physical node

Working on Azure Synapse, we have for now around 30 tables on a dev environment. I want to optimize the tables before replicating them in qal and prod env. As far as I understand, we only have one ...
Gregoire_M's user avatar
1 vote
3 answers
98 views

The mariadbd process is at over 140% CPU utilisation despite a high innodb_buffer_pool_size value

I have a Plesk Ubuntu 22.04 server with 120GB RAM and mariadb 10.6.16 that I use to run the database for a single website. The database alone is about 8GB but despite all the optimisations I've made ...
Nelson Nokimi's user avatar
1 vote
2 answers
37 views

How to optimally do Postgres `IN (...set...)` query with a large set of values

I have a table with millions of rows, and I want to find all rows that have any one of a supplied list of a few thousand values in a specific column. Bascially I want to run an IN(...set...) query, ...
Luke Hutchison's user avatar
0 votes
1 answer
32 views

Sharding a Materialized View

I am a Postgres 15.3 AWS RDS instance and have a database that centers around one table, called posts that has 20 other relations to build the full object. The full query is about a second altogether ...
Frank Conry's user avatar
1 vote
2 answers
56 views

MySQL optimize query against table containing 13M records

I recently started working with a legacy Rails application. Trying to go through with some of the queries used to get analytics related data. Some table contains around 13M records. Running count ...
Aparichith's user avatar
0 votes
1 answer
29 views

Evaluation order (commutativity/associativity) of ANDed terms in Postgres WHERE clause

Is Postgres' query planner able to rearrange terms in a WHERE clause according to the rules of commutativity and associativity? For example, do the following WHERE clauses all result in a query plan ...
Luke Hutchison's user avatar
0 votes
1 answer
60 views

Optimized count for large table using triggers, views, or external cache

I have a public API method that calls a Postgres (14) database and returns a paginated list of rows belonging to a user along with a total count and page index. The count is very costly to perform (...
JackMahoney's user avatar
1 vote
1 answer
54 views

Optimizing type alignment for table with many of variable-length columns in Postgres

I have the following DDL for a Postgres table that was generated by JPA: postgres=# \d+ mytable; Table "public.mytable" Column ...
user991710's user avatar
2 votes
1 answer
95 views

optimize SELECT MAX() with GROUP BY in Postgres?

I'm trying to speed up the following query in postgres: select MAX(msg."timestamp") AS latestDate, msg.channel_id from message msg group by msg.channel_id the explain is as such: Finalize ...
tuskiomi's user avatar
  • 123
0 votes
0 answers
30 views

Speedup wordcloud select with group by in mysql

I am currently working on a project for my studies where a wordcloud is created for the NYT. Therefore I am scraping all the articles from the NYT from their API, and then store dates of the article, ...
Ynfiniti's user avatar
2 votes
2 answers
143 views

Improve query performance of filtered left outer join of big tables

I am trying to optimize a query that joins two large tables (40MM+ rows) in PostgreSQL 15.4. SELECT files.id, ARRAY_AGG(b.status) FROM files LEFT OUTER JOIN processing_tasks b ON (files.id = b....
Jeff G's user avatar
  • 123
0 votes
0 answers
25 views

Duplicating (but not using) a materialized CTE makes the query perform as it should

I have a large query with many CTE's and joins, and once I changed the amount of data in the database (from 20k rows to 10k), it started performing horribly: https://explain.depesz.com/s/TSu3 ...
localhost's user avatar
  • 101
0 votes
2 answers
81 views

formating date colums causing performance issues

I have a query(written by my previous co-worker) with date columns returned as output but formatted, which s causing performance degradation (for below query returning 10k records, performance ...
Madhusudan's user avatar
1 vote
1 answer
103 views

Using table valued function as a table in sql server join

I have a scenario where SQL server using table valued function with joins to many other tables. The function returns many columns, which are not always desirable output for every report, causing lots ...
Madhusudan's user avatar
0 votes
0 answers
24 views

resume search with full text index

I have a database structure where resume stored in document column in WHDocument table in database for each person. During skilled person search in application the persons skill is also searched for ...
Madhusudan's user avatar
1 vote
2 answers
56 views

Is there a "correct" way for an optimizer to process a subquery if the outer query has additional where filters?

This topic came up for me as the result of investigating an error in Redshift. We have a table containing a column of numbers represented as text, but for rows where no value is present a single ...
MattB's user avatar
  • 111
1 vote
1 answer
48 views

Optimizing the SQLite database as source data for R scripts

I have an application written in R Shiny that supports a large number of csv's (and 3 new ones are added every day). These files have a structure that is quite unpleasant to handle, so I decided to ...
tomsu's user avatar
  • 111
1 vote
1 answer
56 views

MySQL - Slow Performing Subquery when DISTINCT Is Used

I have this query that runs very slowly when DISTINCT is used; SELECT COUNT(*) FROM (SELECT DISTINCT query_text FROM search_query WHERE (store_id IN (1)) AND (num_results > 0) ORDER BY popularity ...
Marisa's user avatar
  • 23
0 votes
1 answer
92 views

SQL Server - Update Statistics FULL scan vs Auto Update [duplicate]

Something I have been trying to figure out.. I take a table with 2 billion + rows and I run a manual stats update with FULLSCAN Sometime in the future, an automatic update stats runs against the table,...
Sicilian-Najdorf's user avatar
1 vote
1 answer
70 views

Wrong execution plan in MySQL 8.0 when using JOIN in WHERE NOT IN clause

After a migration from MySQL 5.7 to MySQL 8.0.34, we have a very strange behavior with a query when semijoin is on in the optimizer_switch. 1. The problematic query: SELECT COUNT(s0_.id) FROM ...
Arie S.'s user avatar
  • 13
1 vote
2 answers
630 views

How to optimize a query on a large table, besides using index?

I need suggestions/ideas of optimizing the following query: SELECT COUNT(*) FROM (SELECT 1 FROM f WHERE parent_sha256 = $1 AND parent_sha256 <> sha256 LIMIT 1) AS ...
KaliTheGreat's user avatar
1 vote
1 answer
129 views

SQL 2019 - BatchModeOnRowStoreUsed - Legacy Cardinality - IQP

Have recently migrated from SQL 2016 where the DB's compatibility level was all set to 2008 to SQL 2019 where the compatibility level for all DB's are now 150. Upon running workload for a while, I ...
Sicilian-Najdorf's user avatar
0 votes
0 answers
39 views

Optimizing SELECT COUNT(DISTINCT) on a table increasing daily

Let's say we have a table Daily_users which has the columns student_id, school_id, grade, timestamp. We collect usage data of students daily and so the table grows daily (note that there could be ...
dezdichado's user avatar
0 votes
2 answers
83 views

How to choose index to optimize this MySQL query?

SELECT * FROM ehd_historical_data WHERE exchange = 'NYSE ARCA' AND symbol = 'ZROZ' AND blacklisted = 0 ORDER BY high DESC LIMIT 1; SELECT * FROM ehd_historical_data WHERE exchange = 'NYSE ARCA' ...
user avatar
1 vote
2 answers
114 views

Why can SQL Server not perform a TOP N SORT between an Index Seek and the Key Lookup?

I am looking into a minor performance issue, where an optimizer tool is basically saying, "hey just go ahead and include all the columns on this table in this index" which is a horrible ...
Morgeth888's user avatar
1 vote
1 answer
41 views

Finding the latest status

I have 2 tables events and event_status. The event_status table is the history table, and latest being the current one. The events and event_status have a one-to-many mapping. I need a optimized query ...
Anwesh Budhathoki's user avatar
0 votes
1 answer
59 views

How to optimize a query in postgres?

I have three tables: company, employments and entries. A company has many employments and a single employment has many entries. The entries table has the most items in the entire database (currently ...
Gabriel Michelassi's user avatar
0 votes
0 answers
84 views

How can I optimize the view in MariaDB for Order By?

I have a list of products. Each has many tags. Those tags have normal tables, but they also have OLAP tables that present CSV for each product. For example, a chair can have 'furniture', 'wood', and '...
Saeed Neamati's user avatar
0 votes
0 answers
26 views

MongoDB indexing for dynamic query

I'm trying to build a query optimizer for survey response data, all questions are stored in a collection and have some fields are number_of_selection, option_marked etc. Now I'm trying to build a ...
Deepak Kumar's user avatar
0 votes
0 answers
36 views

Faster running for this query sql server

i used database engine tunning advisor for creating some index and statistics but still run slow SELECT C.code_commande AS 'Order', C.code_client AS 'Cust&#46', ...
octavian darie's user avatar
3 votes
1 answer
316 views

PostgreSQL Query Planner overestimating Row Count leads to slow Hash Join

I have a table with two columns and about 10M rows. About half the records are NULL in the first field, and about half the records are NULL in the second field, although there are a few hundred ...
LennartF22's user avatar
0 votes
0 answers
36 views

PostgreSQL Optimizer does not find best solution when specifying subset of keys in query

PostgreSQL (Version 15) does not find a (in my opinion) obvious optimization for the query plan, is there a way to help the optimizer with this problem? I have a specific kind of query where I have to ...
MrSunday's user avatar
5 votes
1 answer
180 views

Partitioned view aggregation query not optimized

Given the following two tables: CREATE TABLE SalesLedger ( Id int PRIMARY KEY IDENTITY, Date date NOT NULL, Total decimal(38,18), INDEX IX (Date, Total) ); CREATE TABLE Purchases ( Id int ...
Charlieface's user avatar
  • 12.5k
0 votes
1 answer
86 views

Help with SQL Query: Is there a cost-efficient way to join multiple tables to one main table?

I have a main table set up like this: id table_to_join table_item_id 1 products 123 2 products 577 3 products 344 4 products 1230 5 images 14 6 images 42 7 video 555 8 products 400 9 video 9 ...
peppy's user avatar
  • 33
0 votes
1 answer
215 views

How to optimise a query with inner join and order by

I'm struggling with optimising my query. I've tried to solve the issue with various indexes by none of them seems to be helpful. There are 3 tables: transfers(id, amount, type, timestamp, file_id), ...
Farad's user avatar
  • 101
1 vote
1 answer
33 views

Slower MySQL queries involving both wp and custom table

Below are less efficient MySQL queries involving wp posts, taxonomies, and a custom movie table search. The custom movies table contains around 30k rows, and wp posts have been generated with the ...
Rahul K's user avatar
  • 141
-2 votes
1 answer
29 views

Database for video from the internet [closed]

I am an direction student from India working in advertising. For making treatment note and pitch deck I require a lot of photos for referencing from different videos from the internet readily ...
Sumit Rawat's user avatar
5 votes
1 answer
199 views

Single Predicate Check Constraint Gives Constant Scan but Two Predicate Constraint does not

I can create the following constraint on the AdventureWorks table Person.Person: ALTER TABLE Person.Person ADD CONSTRAINT ConstantScan CHECK (LastName <> N'Doesn''t Exist') This tells SQL Server ...
SE1986's user avatar
  • 1,950
0 votes
2 answers
43 views

Optimizie MySQL query: col_name IN (val1, ..., valN)

I had posted a question about mysql equality range optimization here. At this post, I want to discuss about why mysql optimizer is too bad for this type of query: col_name IN (val1, ..., valN). My ...
kietheros's user avatar
  • 145
2 votes
3 answers
407 views

How to manage mysql database table index

I am not aware of how to manage an mysql database table index and I attempted to implement it, but I am uncertain about managing the index for this extensive MySQL database table (Will do it at my end ...
Rahul K's user avatar
  • 141
0 votes
1 answer
18 views

Xamarin.Forms Working with Data

I'm looking for the best option to work with a database for a mobile app. The data that I'm looking to store is articles, posts , sounds (podcasts) and pictures. I have 0 knowledge (as to 2023) ...
user22228863's user avatar
0 votes
2 answers
75 views

Moving tables to different database within same sql server

There is a SQL server, there are around 100 databases in it. I have to query a few tables from one of the databases. when I query, it's very slow and I think CPU utilization is very high at that time. ...
Vivek Nuna's user avatar

1
2 3 4 5
40