Questions tagged [query-performance]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
32 views

Is there a better way of displaying 'Count' of records beside other columns in a select query?

I have a table with below structure : Test1(c_num number ,c_first_name varchar2(50), c_last_name varchar2(50)) 1)There is a normal index on c_num column. 2)The table has nearly 5 million records. I ...
0
votes
1answer
62 views

Avoid duplicate WHERE clause on both sides of a LEFT JOIN, without changing semantics or impairing query optimization

I have a table recording the results of a web crawl. (Table definition at the end of this question.) The relevant part of the data stored in it looks like this: experiment_id | url_id | ...
0
votes
0answers
940 views

Query performance become slower after upgrade to MySQL 8.0.20

I have found bug/issue after update the MySQL Server from 5.7 to 8.0.20. When I run a query with the user-define function in 5.7(same session), the duration for each query is constant and stable. But ...
0
votes
0answers
17 views

High-performance query of two tables with count from second table?

I am searching for a high-performance query using two tables to output all locations (name) from country (adm0 = 'DE') in the geodb_locations table, sorted in descending order by the number of users ...
1
vote
0answers
30 views

Unexpected result by MySQL Optimizer for identical queries?

Following query is working like expected and uses index Query takes 0,0481 sec SELECT geodb_locations.name, geodb_locations.name_url, COUNT(user.uid) AS useranzahl FROM user LEFT ...
0
votes
1answer
24 views

Query specific key fields from x-number of tables?

I'm building an insurance database and I need some help with best practice on fast quering data. Let's say I have three insurance types ... Car, Home and Travel. Common for all three types, I have the ...
2
votes
1answer
97 views

Query customers attributes

I need to create a system where the user creates dynamic filters based on our customer's attributes. There are more or less 30 possible filters and 30 millions of customers, but the number of customer ...
-1
votes
1answer
38 views

Results are varying with same query

Hi I stuck in strange issue with one of my query. Formula : (28800/3600-(28382.6/3600-(1920/3600+1860/3600))) = So, Input Equation: = (28800/3600-(28382.6/3600-(1920/3600+1860/3600))) = (28800/3600-(...
0
votes
1answer
101 views

Postgres sometimes uses sequential scan instead of an index only scan for data that doesn't exist

We are using Postgres 12 and We have a simple table like this: > \d live_event_segmentation Table "public.live_event_segmentation" Column | Type ...
-1
votes
1answer
41 views

Oracle Select Query based on a column value

I have two tables and wanted to conditionally query one of the tables. For example below are the two tables with few matching columns in two tables Table A(SNO, Number,Name...) Table B ( SNO, Number,...
0
votes
0answers
52 views

Index usage for database: where are the sys tables located

I have seen a couple of scripts that query some sys.someName tables to find usage of indexes in a database. My guess is that these tables are tied to a database e.g. there is a sys.indexes table in ...
0
votes
1answer
46 views

how to replace with default value when querying a JSONB column with a key that is not present in a particular row giving empty result

I have a table that has a jsonb column as 'result' which has an array of objects. Looks like this for every row: [{"grade": "A", "subject": "MATH"}, {"...
1
vote
2answers
108 views

Why is index not being used? Can I replicate plan?

I have been tuning a query/ indexes in a dev environment, but cannot replicate the new query plan when applying the same changes to a UAT environment. Specifically, in the UAT environment the ...
0
votes
0answers
54 views

How to increase the query performance on my fts query?

I'm pretty new to database world and I have the following table: Table "public.so_rum" Column | Type | Collation | Nullable | Default ---------...
2
votes
1answer
33 views

Mysql self Join query with Limit never terminates

I'm running an experiment with the query Select Distinct table_1.id1, table_2.id1 FROM image as table_1, image as table_2 WHERE table_1.id2 = table_2.id2 LIMIT K; When I run the query ...
0
votes
1answer
66 views

MySQL query is very slow (15 minutes)

I was previously using SQLite for a personal project and due to a constraint of having it available online, I decided to make the switch to MySQL. I converted my database to the MySQL equivalent but I ...
0
votes
0answers
25 views

Smaller client results in a very slow query

I am testing a query on PostgreSQL 9.6 on a very small tenant and a big one (within the same database, schema, tables). The query is super slow on a small client, which is quite surprising as I would ...
3
votes
1answer
124 views

What could cause a sleeping session to have an open transaction?

For one of our SQL servers there has been a proc which creates a blocking mess: That stored proc completes under a sec most of the times but somehow is leaving transaction open. What i see from ...
0
votes
1answer
38 views

Optimising MySQL count() with INNER JOIN (550k rows)

I have the query below, which is currently taking approx 1 - 1.5 seconds to run in a MySQL 5.7 database. It's being called from a web application, and whilst I'll be implementing caching at the ...
0
votes
1answer
37 views

MySQL right join with tables with 100's of 1000's of rows

The following join is taking far too long in MySQL, almost half a day and it’s still not finished: INSERT INTO `aaaaa` SELECT * FROM `chosenLang` RIGHT JOIN `chosenLOCC_P` ON SUBSTRING_INDEX(...
2
votes
0answers
96 views

Sql Server 2019 Query Hint Placement confusion

I'm using SQL Server 2019 (CU4) and I'm encountering some strange issues when trying to use a query hint for the legacy cardinality estimation due to a poorly performing Table Value Function. I'm not ...
0
votes
0answers
117 views

Restart MySQL server while query is in Killed State

I need to reboot the server (not just MySQL instance) to perform some urgent server updates. Currently on the MySQL server when I run: select * from information_schema.processlist where info is not ...
0
votes
0answers
21 views

PostgreSQL - Simple deletes takes more time [duplicate]

Today I found a weird(may not be) thing in PostgreSQL 12. Im trying to delete rows from a table with where condition from another. Both are small tables. But the delete is going more than an hour but ...
2
votes
1answer
113 views

Index or query tuning for complex predicates

I'm struggling to identify effective indexes (or rewrite the query) to improve a query with the following confounding predicates: JOIN on a date from one table being in range - between two date ...
4
votes
3answers
616 views

Is a join faster with 5 tables joined together Or 5 separate queries? [duplicate]

I have confusion that I already have in my application 5 tables say a,b,c,d,e and have relationship with each other like a.b_id is primary key of b and chain goes on. Now my question is that which ...
0
votes
1answer
40 views

SQL query speedup/optimization

I am using SQL Server 2008 R2.I have tried to improve the following query in different methods. Method 1: DBCC DROPCLEANBUFFERS SET STATISTICS TIME ON SELECT count(*) FROM [prglog].dbo.[errorlog] ...
0
votes
0answers
17 views

Optimal selection of predefined non-materialised data

Take the following result set as an example: Store Category Item Cost Stock Sold Ordered ABC Accessories Lock 344.31 281 141 225 DEF ...
0
votes
3answers
41 views

Wrong index been peeked

I have an index that filters 99% of the table i.e. ix_magic_composite (for that query arguments). When I add another or filter it chooses the wrong index, i.e. fTS even if I create an index that ...
3
votes
1answer
118 views

Badly-formed histogram causes bad estimates on Nested Loop

We have a query on a SQL Server 2016 SP2 CU12 Enterprise where the Query Optimizer estimates that only 1 row would come out of the Nested Loops Join operator, in reality 108501 rows came back. This ...
3
votes
3answers
453 views

Need help to understand tuning up a slow SQL server query [closed]

For one of our databases the query like below is extremely slow. Due to security reasons I am not able to share the actual query or plan but just to give an idea how query is written is below SELECT [...
0
votes
1answer
37 views

The difference between using parallelism “/*+parallel(4)*/” in the subquery or the outer query in terms of performance in Oracle

I have the below query : select /*+parallel(4)*/ ---> First Location <Desc_1> Amount, <Desc_2> Amount, <Desc_3> Amount from (select /*+parallel(4)*/ ---> ...
0
votes
2answers
75 views

Why 2 Table Scans of Same Table Have Huge Differences in Elapsed Time SQL Server

This is driving me bananas. According to both the Execution Plan AND Statistics IO, both queries are doing a full table scan and nothing more, but in one case the query takes LESS THAN ONE SECOND and ...
0
votes
1answer
49 views

SQL Server Slowest Query is NULL

I am looking at both the SQL Server expensive queries report and the query below, but both are showing this mysterious NULL query as the slowest query on my server. Is there any way I can find out ...
1
vote
1answer
204 views

Query performance degraded after upgrading Postgres

I have a table with several million records in a PostgreSQL 12 database, and after an upgrade from 11 to 12, a few queries started performing horribly. They went from taking ~1 second to ~5 minutes. I ...
0
votes
2answers
70 views

How to search a table with 80 million records faster?

I have a table with about 80 million records, I want to find all the activities of lists and workspaces that a user has access to. So first, I get the ids of the lists and workspaces and then I run ...
0
votes
0answers
28 views

Index Spool (Eager Spool), Difference in number of row before and after the operator

I was optimizing one of the Stored Procedure for which I went through the execution plan of it. In the execution plan I see the operator Index Spool (Eager Spool). There I see it is taking 1759 rows ...
0
votes
0answers
21 views

Will a function which wraps a query criteria be a bottleneck?

I've been attempting to make the following query perform better lately : SELECT BIN_TO_UUID(album_id) AS e, firstdateofinterval AS p, BIN_TO_UUID(territory_id) AS t, SUM(playbacks) AS ...
0
votes
0answers
30 views

After upgrading from Postgres 9.2.4 to Postgres 10.5 query is 170x slower

We recently upgraded our Postgres database from 9.2 to 10.5. We noticed a somewhat odd behavior with a query that under the same circumstances became 170x slower. The query does a join between 2 ...
1
vote
0answers
34 views

How statistics play a major role in legacy CE vs new CE

We have quite some issues going on for one of our SQL server 2014 SP3 after compatibility change to 120 from 100. Yes i am aware of few hints to make use of legacy CE and have implemented as success. ...
1
vote
1answer
56 views

PostgreSQL incorrectly using primary key index for min/max query

I have a table with the structure similar to this: CREATE TABLE employees ( id bigserial NOT NULL, name_id uuid NOT NULL, department uuid NOT NULL, details text NULL, deleted bool ...
1
vote
1answer
30 views

Wrong estimated row count

PostgreSQL 11 Running EXPLAIN returns a row estimate that is way too low compared to the actual row count. Given the query SELECT s.offer_id, category_id, category_name_de, category_name_fr, ...
1
vote
0answers
25 views

MySQL - storing 162 million rows in 1 table as the result of an IFNULL query

I'm working on a legacy web application which uses a MySQL (5.5.65-MariaDB) database which is hosted on a server running CentOS Linux 7.7.1908. The application is a database of chemical substances and ...
0
votes
1answer
26 views

Bad estimates when sorting, good estimates without sort

I'm struggling with the tuning of one of the queries on our production SQL Server 2016 SP2. I needed to anonymize the query due to security policies. The query is like this: SELECT TOP 1000 Object1....
0
votes
0answers
49 views

Slow query using SQL Server difference (soundex) function

We've got a nasty query that is always doing a clustered index scan even if I add in a non-clustered index with the Forename and Surname columns. Query takes 2-3 seconds on a 2 million row table and ...
0
votes
0answers
9 views

Sql combination matching criteria - The icrecream kiosk issue

Let's create this case Icecream type | Available |Normally eats (person) | Price Small cup 2 1 1.50 Medium cup 0 1.5 2.00 ...
0
votes
1answer
435 views

Postgres group and aggregate(sum) JSONb arrays and non JSONb properties

Version PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc, a 224fe214a p 3971489d3e, 64-bit I am trying to build a query which (distinctly) groups values from JSONb columns, and also sums values ...
0
votes
1answer
37 views

PostgreSQL server running very slow at minimal work load

Postgres server running very slow. DB has two tables and when I run explain analyze for below query, taking more than 10min and query running forever. Tables DDL, db<>fiddle. User query: ...
0
votes
1answer
62 views

how to write queries properly inside of a postgres RLS policy

I'm trying to do a single implicit JOIN inside of an RLS expression. I get a syntax error and have a "hacky" solution using a subquery, but would love to know if there is a better way. So assuming ...
2
votes
1answer
128 views

Improve COUNT, JOIN and GROUP BY query performance

I am trying to get the count per group for a given user and wrote the query below. The execution plan looks fine. However, in some cases, the user has around 10k articles and with a bit of load the ...
1
vote
1answer
111 views

Query cycles between suspended and runnable without ever completing

I have an insert statement, extracting data through multiple inner joins from around 8 tables. It is getting into suspended state then runnable and vice versa forever; never coming back to running ...

1
3 4
5
6 7
50