Questions tagged [query-performance]

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

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

usage percentage of Mongodb internal cache

I've ran two performance test on a MongoDB server with identical environment/settings AFAIK. I found that the throughput was 10% apart. When I inspected the mongostat logs, I found that Mongodb in ...
79
votes
3answers
115k views

What is faster, one big query or many small queries?

I have been working for different companies, and I have noticed that some of them prefer to have views that will join a table with all its "relatives". But then in the application sometimes, ...
0
votes
1answer
212 views

GROUP BY very slow when returning many rows

I have a query to aggregate data per day. When I run the query for a single day it is very fast. However, when I run the query for multiple days the time cost increases much faster than O(...
1
vote
1answer
26 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
23 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 ...
1
vote
1answer
820 views

Query taking more than 5 minutes to execute

I've a query that runs in 2 identical environments: Production and QA. In QA server, it takes around 6 seconds and in production it takes 5 minutes or more! Query is as follows: mysql> SELECT ...
0
votes
1answer
25 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
1answer
58 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
0answers
30 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
33 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
31 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
87 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 ...
0
votes
3answers
613 views

mysql simple update with 40 million and 128GB RAM taking too much time

We are having trouble with simple updates on a single table taking a long time. The table contains ~40 Million rows. and the job runs every day that truncates the table and inserts new data from ...
1
vote
1answer
67 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 ...
0
votes
1answer
328 views

Fastest way to check if record is related to a particular user in postgres

While implementing a sharelikes table I added a procedure to add a new record for a like. I wanted to add a check before insert that the item being liked does not belong to the users who submitted ...
4
votes
3answers
1k views

Is there any way I can speed up this large full-table query?

I have a query that selects from only one table and with one WHERE filter. However it takes a very long time to execute and even times out occasionally. This is likely because it is filtering about 4 ...
2
votes
2answers
363 views

SQL Query optimization - Long running query

I have a database with the following tables : CREATE TABLE [Document]( [IdDocument] [int] NOT NULL, [DocumentType] [int] NOT NULL CONSTRAINT PK_Document PRIMARY KEY CLUSTERED (IdDocument)...
0
votes
1answer
143 views

PostgreSQL not using varchar index when ordering by primary key

My company has a pretty large PostgreSQL 9.4.5 database (we'd rather not try updating it atm as it's stable), one of the tables used to hold some sort of statistics wasn't designed well and when it ...
2
votes
1answer
46 views

Testing query speed in two different RDBMS

I'm planning to compare the speed of queries in PostgreSQL and SQL server (on the same datasets). I was planning to run the same queries multiple times in both databases and use average response time ...
1
vote
1answer
293 views

Intermittently Slow Query @ MySQL

Problem statement In a nutshell, the problem is that a query which has been smoothly running for months has started behaving weird. Particularly, the exact same query runs fast most of the times, but ...
0
votes
1answer
42 views

left join query running too long

I have two tables, test1 and test2, and these tables have around 600M(70GB) and 10k(2MB) records, respectively. when I run a query with left join on site_id, it is taking too long, even though site_id ...
0
votes
0answers
20 views

MySQL InnoDB Weird Query Performance

I designed two tables, both using InnoDB. The first one has columns "Offset, Size, and ColumnToBeIndexed" with BIGINT "Offset" being the primary key, and the second one has columns "OffsetAndSize, ...
0
votes
1answer
28 views

Slow query when grouping and ordering

I have a query that is finding the latest documents for a user that are associated with another user having a high contact score. The documents table has 1.2M records and the ...
0
votes
3answers
529 views

MySQL Connections slows down my website

I have a website that runs perfectly normal, until today it becomes very slow. After a few hours debugging, it all points down to database. I ran these queries show status like 'Connections'; +-----...
0
votes
0answers
41 views

“INNER JOIN” vs “INTERSECT” performance comparison when dealing with huge amounts of data in Oracle

I have two tables with the structure below: create table Table_One (Ef_date Date, Customer_Num Number, <Other_Columns> <Combination of Number/varchar2(50)>); create ...
0
votes
1answer
31 views

How can I speed up my count queries? [closed]

Right now I am creating counts for each hour for the whole week. I am using python to connect to the database, and I am looping through each hour so str(i) is the current hour that is being updated. ...
3
votes
2answers
155 views

Changing database compatibility from legacy CE to 120

Just seeking an expert/practical advise from DBA point of view where one of our application DB running on SQL 2014 after migration had old DB compatibility level i.e 100.(SQL2008) From DEV point of ...
2
votes
1answer
75 views

How to optimize Merge Anti Join?

While trying to find the fastest way to do a bulk update of a large table, I came up with the following plan: having main_table (potentially above 1000 mln rows) and update table up to a few million ...
2
votes
1answer
170 views

SQL Server - Prevent Clustered Index Scan on a specific table

My database contains a specific table that is quite large (250+M rows, 100+GB data space). Generating a clustered index scan on this table is always a bad idea. This table has multiple indexes and ...
1
vote
3answers
16k views

MySQL join two large tables is very slow

I have two tables, one of them contains a history of downloaded urls, while the other table contains details about each url. The following query groups the URLs by the number of repetitions during ...
0
votes
2answers
292 views

PostgreSQL 10 Optimizing slow query performance

I have the following query: select i.id as id, i.ts_updated as updated, i."name" as name, cast(attr.data_norm as text) as data_norm, cast(_59.json_agg as text) as _59, ...
0
votes
1answer
37 views

SQL Select query is taking so long time on new Server

I am migrating our live website server from rackspace to AWS. We have already done all parts but stuck at below point: One of query which is running within a second on live server, it's taking around ...
1
vote
0answers
17 views

Calculate total running time of pods using two date/timestamp columns

I am currently storing all the pods in PostgreSQL db. I track each pods creation and deletion through created_time and deleted_time column. When pod is not deleted, its deleted_time columns remains ...
2
votes
5answers
2k views

Can @parameter IS NULL slow down the execution of query?

Option 1: CREATE PROCEDURE [dbo].[GetStudents] @MinimumAge int = NULL AS select * from Students s where @MinimumAge is null or s.Age >= @MinimumAge Option 2: CREATE PROCEDURE [dbo].[GetStudents]...
0
votes
0answers
16 views

Save multiple dynamic filter for aggregation in Postgresql

What is the most efficient way to store multiple dynamic filters which can be created by users on given data and us the filter for aggregating data? Lets assume I have a table called t_filter which ...
2
votes
2answers
137 views

Performance of Query where time captured from SP statement completed differs by large from RPC completed

I am unable to understand the performance of below query when collected data from XE events: Query DECLARE @Importdate DATETIME = ISNULL (( SELECT ImportDatetime FROM Customertable where CustomerID ...
1
vote
1answer
193 views

About real time statistics oracle 19c only for particular table

Reg Real-Time statistics which extends online statistics gathering(12c) to also include conventional DML statements. Real-time statistics helps the optimizer generate more optimal plans.Whereas bulk ...
3
votes
3answers
202 views

An alternative to querying with multiple JOIN over the same table?

I have a Postgresql 11 database. Let's say I have a table called houses. It should have hundreds of thousands of records. CREATE TABLE houses ( pkid serial primary key, address varchar(255) NOT ...
1
vote
0answers
57 views

My question is in a select statement how do you return the RID along with the table data?

I need your help with a concept I want to show developers, that concept is to help them understand how SQL Server retrieves data from a table. In the event that a developer does not use a clustered ...
1
vote
1answer
46 views

How to prevent seek pagination from doing sequential scan over entire table

I have 3 tables and a materialized view: resource_categories contains all category names and metadata create table if not exists resource_categories ( category_id INT, title VARCHAR(255), ...
1
vote
2answers
99 views

How can I optimize and speed up a query [closed]

Below query never completes and runs for over 14 hours: SELECT U.Age, U.CreationDate, U.DisplayName, P.AcceptedAnswerId, P.ClosedDate, p .Title, P.ViewCount, C.CreationDate,C.Score ,C.UserId FROM ...
1
vote
1answer
28 views

Create 7 day rolling average in PostGIS for each feature

I have a timeseries dataset that looks like this: uid | geom | date | count | ------------------------------------- 1 | FeatureA | 2016-02-01 | 1 | 2 | FeatureA | 2016-02-02 | 2 ...
0
votes
1answer
33 views

Is there any performance benefit to minifying MySQL queries?

I recently ran across some tools that help you minify queries, and I'm curious to know if that's actually something that could help performance.
0
votes
2answers
80 views

T-SQL Query or DB Engine Optimization? [closed]

This query takes 100 ms on avarage but when you have hundreds or thousends of calls of them every second this would cost a lot. The dataset can not be cached as it may be changed at any second. Any ...
0
votes
1answer
69 views

Query processor ran out of internal resources and could not produce a query plan” - MariaDB

i have some query SELECT COUNT(loan_payment) as setcolumn FROM table1 WHERE paydate between {d '2020-05-01'} AND {d '2020-05-31'} and period_code = 'MONTHLY' AND company_id =...
0
votes
1answer
515 views

Slow stored procedure performance [duplicate]

I have a stored procedure that used to run fine without any issues before we made this change last evening and now the performance is unacceptable. The stored proc pulls data based on information in ...
0
votes
0answers
34 views

Postgresql - slow distinct on million records table

I have a table with a million of records, i need to select the distinct values of log_type. In other queries i also need to filter log_types result of the distinct and limit the result. But the query ...
2
votes
2answers
1k views

Performance of a big INSERT…SELECT with multiple JOIN

I'm having trouble with a big INSERT...SELECT in my DB. For context, the targeted table already have around a million entries and my SELECT query return approximately 2 millions. On a production ...
3
votes
2answers
59 views

Why is creating and dropping a random index fixing this performance problem?

I have a big heap table with 17,093,139 rows. This table is the most heavily used table in the database. Since this is a heap table, there are only non-clustered indexes in this table. I rebuild/...

1 2 3
4
5
47