Skip to main content

Questions tagged [query-performance]

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

Filter by
Sorted by
Tagged with
2 votes
0 answers
37 views

Improve performance of TVF using system catalog view sys.schemas

We have a database where many tables have after update triggers that use the COLUMNS_UPDATED() function to track which column values were modified via a history table. I've been asked to expose the ...
Hannah Vernon's user avatar
  • 70.2k
-1 votes
0 answers
14 views

Summing values from different tables in access

I have 3 tables, student table stores information about students, Fee table stores information on fees paid by students and Collections table stores information on what a student has paid for which is ...
Zephaniah Nyamweya's user avatar
2 votes
0 answers
50 views

SQL Server Underestimating cardinality on a Filter operator in a Left Anti-Join

I am tuning a query which is slow, I have narrowed the root of the problem to be at the very beginning of the execution plan, where SQL Server makes a bad estimate on a WHERE IS NULL filter that ...
SE1986's user avatar
  • 1,990
7 votes
1 answer
552 views

Execution slow in stored procedure; fast when run ad hoc

I am dealing with an issue that I can't seem to solve. I was given this stored procedure that has speed issues. It takes 35 seconds or so. When run adhoc, it completes instantly. This made me think it ...
anakata's user avatar
  • 73
1 vote
1 answer
97 views

Why does MySQL choose such a poor query plan for "IN (SELECT NULL WHERE FALSE)"

We have some code that goes through a SQL generator to produce parameterized IN clauses of the form WHERE id IN (?, ?, ?, ...) AND .... If the list is empty, the generator cannot issue IN () since ...
ChaseMedallion's user avatar
0 votes
2 answers
31 views

Should I create a separate table for non null user_id values in my MySQL database schema for performance?

I have a MySQL database schema for storing uploads, where each configuration can be associated with a user account (user_id) or uploaded as a guest without an associated user account (null user_id). ...
user3075373's user avatar
0 votes
1 answer
39 views

Performance issues with time interval check

I'm running the following Postgres query where I join a consumer's usage with the service cost for that interval based on when they used the service from the service table. The query below is a ...
orchid's user avatar
  • 3
0 votes
2 answers
62 views

How to simplify this intersect query?

SELECT * from M left JOIN K on M.id = k.id left JOIN W on K.str = W.str where str = N'أخبار' INTERSECT SELECT * from M left JOIN K on m.id = k.id left JOIN W on K.str = W.str where str = N'فلسطين' ...
simpatico's user avatar
0 votes
1 answer
38 views

Postgres 15 column drop impact

Is the drop column operation scalable? Previously, we faced issues while updating a large number of records because it took more space. Is this the case with a drop column operation?
Slim's user avatar
  • 249
4 votes
2 answers
159 views

Index seeks and predicate push down

I have a query of the following form: IF EXISTS ( SELECT 1 FROM ( SELECT RowID, OETID FROM @InMemoryTableTypeTable i UNION ...
Dan Def's user avatar
  • 163
1 vote
2 answers
48 views

Oracle SQL query slow even with indexes

I have an Oracle 19c database (19.0.0.0.0), and two tables with lots of columns, I'll mention only the relevant ones: table1: createtime date contact_key char(96 byte) table2: contact_key char(96 ...
Gábor Major's user avatar
0 votes
0 answers
22 views

Mongo DB slow deletes due to indexes rebuild , same with TTL indexes

We are having huge collection which we can not partition ( we need to get the records by last updated time which can not be known ). To decrease the storage we have enabled TTL index based on last ...
Yulian Oifa's user avatar
2 votes
2 answers
57 views

How can I find a topmost row and count the rows with only one scan?

Suppose that I have a schema like the following: -- Many rows CREATE TABLE t1(i INTEGER PRIMARY KEY, c1 INTEGER, c2 INTEGER); -- t1's rows with c1 even CREATE VIEW t1_filtered(i, c1, c2) AS SELECT ...
user570286's user avatar
3 votes
1 answer
44 views

fetching data from 2 10M+ rows tables

I need to run the following query on my production database, using 2 tables that contain more than 10M rows each : SELECT rm.parent_id as processed_media_id FROM render_medias rm JOIN processed_medias ...
VaN's user avatar
  • 131
1 vote
0 answers
31 views

Very slow to work with table using PostgreSQL [closed]

I have a table labelled SALES. It has indexes like this list "sales_pkey" PRIMARY KEY, btree (id) "sales_order_date_idx" btree (order_date) "sales_product_id_idx&...
David Lazar's user avatar
0 votes
1 answer
31 views

Get the statistics_stream on SQL server using SSMS

I have generated statistics for a few columns of a table using the following SQL command: CREATE STATISTICS [STAT_NAME] on [TABLE_NAME] (COLUMN_NAME) WITH FULLSCAN The generated statistics are ...
Mohammad Mosiur's user avatar
0 votes
0 answers
47 views

Azure SQL Database :: table size query takes forever to execute

I was working with the awesome Azure SQL Database Diagnostic Information Queries created by Glenn Berry and I realized that the query number 40 takes forever to execute: -- Get Schema names, Table ...
Francesco Mantovani's user avatar
1 vote
0 answers
57 views

Poor Postgres Query Performance with a Partitioned Table - pg_hint_plan does better

Simplified, I have a table with this layout: CREATE TABLE IF NOT EXISTS public.scans ( id bigint NOT NULL DEFAULT nextval('new_scans_id_seq'::regclass), geo geometry(Point) NOT NULL, ...
Eric B's user avatar
  • 11
0 votes
0 answers
27 views

How to tune below query

Please help suggest how to tune below postgres query. select distinct c.aid, jsonb_array_elements (cvp.value::jsonb) as somejson from cont c join compara_values cvp on c.id=cvp.cont_instance_id join ...
gdba's user avatar
  • 13
0 votes
1 answer
40 views

Does long running query cause replication delay in PostgreSQL which is in 'idle in transaction' state?

In my system I found same query called more than 30 times 'idle in transaction' state. Is there any way to to identify the reason of 'idle in transaction'? Should I terminate those query manually ...
Sheikh Wasiu Al Hasib's user avatar
-1 votes
2 answers
44 views

LEFT JOIN LATERAL with a function result, how to prevent unnecessary function calls?

I have a costly function and it only provides relevant data when certain conditions are met. That data is inserted into the result through a LEFT JOIN LATERAL as shown below. As it works now, the ...
Björn Morén's user avatar
2 votes
1 answer
131 views

How to query for aggregated streaks?

My goal is to detect price trends for cryptocurrencies. For that I'm getting OHLC pricing data for multiple currencies vs. multiple quote symbols (e.g. candles for Bitcoin/USD, Bitcoin/EUR, Litecoin/...
Sascha Mayr's user avatar
0 votes
1 answer
19 views

Convert character encoding in big database

We have big database (~1TB) and we need to update character encoding for all tables. I am running following SQL: SET FOREIGN_KEY_CHECKS = 0; ALTER DATABASE wallmob_etail CHARACTER SET utf8mb4 COLLATE ...
Justinas's user avatar
  • 101
0 votes
1 answer
35 views

Why does Postgres give different EXPLAIN ANALYZE results when using now() vs current_timestamp?

At my job we've recently had to optimize a query and kind of stumbled into the answer to make the query faster but can't explain why it worked and it's bothering me. The situation is something like ...
Becuzz's user avatar
  • 213
0 votes
1 answer
33 views

How Can I Efficiently Structure a Data Model for Handling Both One-Time and Recurring Tasks in a Task Management app?

fellow developers and architects! I'm in the process of designing a webapp aimed at managing tasks and recurring tasks, which can be seen as habits. The unique challenge I'm facing revolves around how ...
nanobot's user avatar
0 votes
0 answers
37 views

Query performance degrade after upgrade from MySQLv5.6 to v5.7

I upgraded the MySQL Server from v5.6 to v5.7 and since then I see query performance degrade. For example, I have the following select query that selects some columns and has a single column in the ...
msbeast's user avatar
  • 11
1 vote
0 answers
44 views

Selecting joined column kills index and results in very slow query

I recently added a JOIN to a query look up one of three names based on id. Normally this has little to no effect on performance but for some reason in this case when I SELECT the column in question ...
Eaten by a Grue's user avatar
0 votes
0 answers
25 views

Performance difference in SQL queries using JOINs with function

I have two SQL queries that are structurally the same, but have a significant difference in execution time. The first query takes around 500ms to execute, while the second one takes only 50ms. Both ...
Timon's user avatar
  • 33
0 votes
0 answers
25 views

What is the proper way to index status history tables in Postgres

We are using PostgreSql and have multiple tables which are laid out approximately like this. A table for some entity in our system apple id | created_at | updated_at | user_id An enum table with ...
Ethan McCue's user avatar
1 vote
1 answer
35 views

Many items in `IN` clause causing full table scan earlier than expected

We have a search index table, provided by a CMS, that is of the form: CREATE TABLE `craft_searchindex` ( `elementId` int NOT NULL, `attribute` varchar(25) CHARACTER SET utf8mb3 COLLATE ...
msbit's user avatar
  • 121
0 votes
0 answers
30 views

Performance - where clause not using index due to null verification, how do i use the index seek in this code without doing code duplication? [duplicate]

question : how do i use the index seek in this code, without doing code duplication? I've got this code : CREATE procedure [dbo].[GetActiveEntitiesByRecord] ( @MainId bigint , @...
dexon's user avatar
  • 47
3 votes
5 answers
855 views

Is COMPACT a better format for fixed length rows than DYNAMIC?

In MySQL / MariaDB I have this table with fixed-length rows (no VARCHAR, TEXT, etc) CREATE TABLE trigram ( id BIGINT(20) NOT NULL, trigram CHAR(3) NOT NULL COLLATE 'utf8mb4_general_ci', ...
O. Jones's user avatar
  • 563
0 votes
1 answer
82 views

Why is this PostgreSQL query with LIMIT so slow?

I have a .NET WebApi with two entities (please forgive the silly names, it's just an example): public class Father { public int Id { get; set; } public string Name { get; set; } = null!; ...
Alessandro's user avatar
0 votes
0 answers
9 views

KILL QUERY Flooding in General Query log for MySQL 8.0.25

Problem : We have two MySQL servers in master master replication. We can see many KILL QUERY statements in MySQL general log specific to stored procedures as below : 2024-03-20T21:54:45.056981+05:30 ...
Sana Ansari's user avatar
0 votes
1 answer
80 views

Slow query when using OR clause

I have 4 tables and it takes quite a long time to query with OR clause (AND clause works fine). News articles Rows: ~7000 Schema: id | integer publication_date | timestamp with time ...
marylandmusclemachine's user avatar
1 vote
2 answers
50 views

Select unique rows based on condition stored in another table

I want to select rows based on conditions that is fetched from the database. The tables structure are mentioned below. # Questions Table: ╔════════════╦═══════════════╦════════╗ ║ id ║ ...
Rohit's user avatar
  • 11
0 votes
0 answers
39 views

Performance Differences in JOIN query Between MySQL 5.7 and MySQL 8.0

Hello MySQL Community, I wanted to share some observations regarding the performance differences between MySQL 5.7 and MySQL 8.0, specifically related to join algorithms. In a recent project, I ...
Amarjit Singh's user avatar
1 vote
2 answers
235 views

Why OFFSET / FETCH causing Performance issues with Queries in MS SQL 2019?

I have this select query: SELECT ITEM.ID ITEM_ID, ITEM.CODE ITEM_CODE, ITEM.DESCRIPTION ITEM_NAME, ITEM.CODE_CATEGORY_ID CATEGORY_ID, ITEM_CAT.CODE CATEGORY_CODE, ...
Subin Benny's user avatar
1 vote
3 answers
72 views

How to filter by one column, and sort by another, using an index, in a query with a limit clause

Suppose I have the following table: CREATE TABLE posts ( id INT, num_comments INT, created_at DATETIME, /* Irrelevant fields omitted. */ PRIMARY KEY (id), INDEX (created_at), ...
Jonathan Wilson's user avatar
0 votes
0 answers
32 views

MySQL 5.7 on Amazon Aurora - how to read the IO Cache vs. Disk Read metric?

I'm trying to determine how often InnoDB is hitting RAM vs. disk. Does db.Cache.innoDB_buffer_pool_hits.avg mean InnoDB went to the buffer pool? Does db.Cache.Innodb_buffer_pool_reads.avg mean that ...
mstrom's user avatar
  • 133
1 vote
2 answers
40 views

Finding files a user has access to is slow when ordering

I'm working on a system where users are sorted into groups, and then those groups are applied on files. At a high level, what I need to do is to be able to pull out the 10 most recently-updated files ...
chraume's user avatar
  • 11
0 votes
1 answer
96 views

How does different forms of EXISTS() affect performance in MS Sql Server?

I've been told that when checking for the existence of a row in a table, this will work: EXISTS(SELECT 1 FROM....) but that a "better" way in regards to speed performance of the query would ...
Gharbad The Weak's user avatar
0 votes
1 answer
43 views

Distinct based on window function (sort curiosity or wrong problem approach)

A lot of values are stored in values. The data is in a hierarchical order, so there could be a probe, on which some measurements have been performed. Many points have been archived with strain and ...
lrahlff's user avatar
0 votes
1 answer
55 views

Data servers: Do query optimisers re-write queries removing redundant columns during the plan creation?

Although I'm using SQL Server, as this is a question of whether an optimiser will re-write a query to remove redundant columns, I'm pitching this at all data servers: RDBMS, NoSQL, MPP, anything ...
Steve Martin's user avatar
2 votes
1 answer
184 views

MySql Aurora - how often to run ANALYZE TABLE?

I have a database that has existed for about 10 years, with hundreds of tables, many of which have several million rows. We don't have a DBA and we have an increasing number of slow queries. We're ...
mstrom's user avatar
  • 133
1 vote
1 answer
90 views

MySQL Amazon Aurora - a few slow queries causing extreme performance issues

I'm a dev, not a DBA, so pardon my ignorance. I'm running on MySQL Amazon Aurora, single-instance (with a replica for failover). A few days ago, my application had a handful of extremely slow queries (...
mstrom's user avatar
  • 133
-1 votes
1 answer
55 views

How to make this postgresql query to use composite index only ? Any Suggestion

Query: with firstData as (select rm.display_name as register, br.createdat, br.status, br.id as runid from bad_run br inner join bad_master bm on bm.id = br.badmasterid inner join regis_master rm on ...
ankush chauhan's user avatar
8 votes
0 answers
142 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
87 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
36 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

1
2 3 4 5
69