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
46 views

Optimal index for a nested query?

I have a nested query and I am not sure if the index I've chosen fits it's needs. Currently I am not happy with it's performance. The table contains > 2 Mio rows counting. I am working with Oracle. ...
0
votes
0answers
20 views

De-duping postgres date ranges with other date ranges for analysis

In my app, there is a table called schedules which have mixed types type: 'project', 'pto', 'sick' and have a date range date. CREATE TABLE public.schedules ( id int8 NOT NULL DEFAULT next_id(), ...
0
votes
1answer
80 views

Avoiding a duplicate CASE expression

SELECT x, y, z, CASE WHEN COUNT (PH.header_id) OVER (PARTITION BY PL.header_id) NOT IN (null,0) THEN L_Count ELSE COUNT (PH.header_id) OVER (PARTITION BY PL.header_id) END as quantity Is there a ...
1
vote
1answer
69 views

MongoDB - Slow aggregate query

It takes around 6min to get the result from MongoDB, when I use the following aggregate query. db.barcodes.aggregate([ { $lookup: { from: 'company', localField: '...
0
votes
1answer
48 views

Counting number of records newer than X date matching a condition quickly

I have a table with 100 million rows. These are transactions for different accounts. A single account may have more than 500,000 transactions. Here is basically what I'm working with: CREATE TABLE ...
1
vote
1answer
35 views

Filtering a large number of rows in Cassandra

Let's assume we have a lot of potentially heavy rows (e.g. 500k) in a table that we want to filter by primary keys and send over the internet to a processing engine.Is it reasonable to use the IN ...
0
votes
2answers
45 views

db design for storing inventory per customer

We have a product microservice which stores drugs and we have an inventory microservice that stores inventory for that product. inventory id productid quantity on hand 1 1 10 2 2 10 3 3 10 One of ...
0
votes
0answers
14 views

Postgresql POD Master/Slave don't clean the cache when Client stopped sending data

I'm running a Performance Stress test on our backend (NODE JS) using a client(JAVASCRIPT) that generates the stress , with a Postgres DB. I've stopped the client from sending data after 5-6 hours , ...
0
votes
0answers
29 views

SQL Server query tuning strategy

I'm new to query tuning and have been using Brent Ozar's tools to identify costly queries on the server. One query that is executed numerous times per minute is triggering warnings for: Forced ...
1
vote
2answers
67 views

Select * - Why or Why Not?

Is there really any difference between these two queries? SELECT TOP 1000 [COL_1], [COL_2], [...], [COL_n] FROM [database].[schema].[table] SELECT TOP 1000 * FROM [database].[schema].[table] ...
1
vote
1answer
27 views

Can I improve this query by adding indexes or tuning mariadb config?

We're having a problem with a query that keeps executing very very slowly SELECT `e`.*, IF(at_news_from_date.value_id > 0, at_news_from_date.value, at_news_from_date_default.value) AS `...
1
vote
1answer
52 views

Best solution for performance - to extract 400 over fields with varchar (300) for one single BI Data set

I have this requirement to extract about 450 fields with most of it's fields length with varchar(300) from multiple tables for one single BI report. For better performance, I tried to create one ...
1
vote
1answer
77 views

Query a table of domain names to get matching top level domains using index

I need to have a table that will contain a single column with a domain+suffix that will looks something like this: domain_and_suffix google.com ynet.co.il 360.cn I want to be able to query this ...
-1
votes
1answer
35 views

How to rewrite slow proc with deletes

How should this be re-written/optimized to speed up? On avg deletes 80 million rows. No FK references. Cannot use move data to another table and truncate methods. SQL Server 2012 Stnd CPU 8 Cores ...
0
votes
1answer
25 views

In which case Optimizer rearranges the conditons in mysql?

recently I have added index "new_index" on this table. CREATE TABLE `y` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `shipping_provider_id` int(10) unsigned DEFAULT NULL, `...
0
votes
0answers
47 views

JOIN vs WITH differences

apologize if there is the same question. When I started my career, my manager told me that you don't need to use "WITH", you can do anything with "JOIN". Now I am wondering is it ...
0
votes
1answer
42 views

Optimizing a recursive CTE or replacing it with a temporary table

I have a recursive query like this: with recursive PCte(id) as ( select p1.id from Product p1 where p1.parent_id is null and p1.serial_number in ('123', '124','125', 'n') union all ...
2
votes
1answer
216 views

SQL Server using index scan instead of seek, all of a sudden

We have a stored procedure that runs daily. In one of the sub queries (query 20, in the execution plan), SQL Server was using an index seek till 2021/09/24. But all of a sudden when the query ran on ...
0
votes
1answer
48 views

Performance issues after upgrading to Mysql 8.0.19 version

Few queries are performing very poorly after the upgrade. Here are the examples. ****Example 1**:** SELECT x.CI_TITLE 'ciTitle', x.DEPARTMENT_MEANING 'departmentMeaning', x....
1
vote
0answers
109 views

How to determine the Tipping Point of which Physical Join Operator gets used to process a query?

I've been noticing some strange occurrences lately on my development SQL Server instance where the Nested Loops Physical Join Operator is used when a Hash Join would be much more efficient, within a ...
0
votes
2answers
48 views

How to optimise this IN query?

Lets say I have this schema Resource(resource_mapping_id:Uuid, reaource_id: uuid, node_id: varchar, date: date, available: boolean, resource_start: varchar, resource_end: varchar) So i have the ...
0
votes
2answers
68 views

Does changing database growth rate force a new execution plan?

For many years there has been a long running SQL Agent job that has taken nearly two hours to complete. Approximately two weeks ago on a slight whim a decision was made to change the growth rate on ...
0
votes
0answers
11 views

Fastest way to insert in-memory JSON data into Postgres, other than json_populate_record

We ingest a lot of data, and one of the most time-consuming tasks is inserting data that NodeJS has in JSON format into the database. Currently we're running a query like this, with the giant JSON ...
1
vote
2answers
58 views

Group by x, get other fields too (comparing options and performance)

I'm a novice, trying to find the best way to sum up my data. Oracle 19c. I have a WORKORDER table that has work orders (ISTASK=0) and tasks (ISTASK=1). Tasks are children of work orders. Work orders ...
3
votes
3answers
129 views

Reducing Temp Table Scans when joining two temp tables using OR conditions

I'm working on a complicated query that I have up to this point been able to refactor to reduce execution time as well as number of scans and reads. At this point in the query, we have two temp tables ...
0
votes
3answers
44 views

Is it possible to perform two checks in the same query in one traversal?

I'm working on a forum project using PHP/Laravel and MySql where I have 3 models : User, Thread, Like (users, threads, likes tables). In index page, I display thread components, and each component ...
2
votes
2answers
85 views

MySql DB slow after restore until I run mysqlcheck

I frequently have to restore our website's production DB to our test server. When trying to access the restored copy of production, the performance is AWFUL. For example, one particularly heavy web ...
0
votes
1answer
92 views

How to index when partitioning for start and stop time columns in PostgreSql 12

I have a log table that takes inserts at a rate of 5k-10k rows per second and number of records grow upwards of 20 Million every 24 hrs. Here is the table structure: CREATE TABLE IF NOT EXISTS public....
0
votes
0answers
28 views

How to improve efficiency of multiple JSON_EXTRACT SELECT expressions

In MariaDB + InnoDB, I have a table like: CREATE TABLE `stuff` ( ...
-2
votes
1answer
70 views

Relational database with Kafka-like durability/caching implementation

When reading about Kafka I found it really interesting how they are providing durability, especially in comparison to databases: Kafka - never writes synchronously to disk, provides durability by ...
1
vote
1answer
43 views

PostgreSQL Query Optimization - Joining with a lookup table does not use index

I am attempting to optimize indexes and querying for a postgres database, and have run into some issues around a lookup table I have used to support joins for a many:many relationship. Here is the ...
0
votes
3answers
37 views

MySQL query performance on single table

I've got a slow query (about 6-10 minutes) that I've been trying to speed up and I'd really appreciate any pointers. I've followed various guides to creating indexes based on cardinality of columns ...
0
votes
0answers
24 views

Why Sql execution delays sometimes?

I made my project in VB.NET with SQL Server as backend but sometimes SQL query execution delays even from SSMS. I have the following query which I execute every time I want to fetch a product list: ...
0
votes
1answer
33 views

Performance of WITH tables that later get joined to smaller tables

I'm trying to develop a general framework for getting chunked cohorts out of a large database. Because of various factors, it's convenient to write queries like this: with tab as ( select ...
0
votes
1answer
45 views

Recalculate "Estimated I/O Cost" without exporting and importing database

I have the following query: SELECT [t].[Key], SUM([t].[ValueA]), SUM([t].[ValueB]) FROM [MyTable] AS [t] GROUP BY [t].[Key] This table had an nvarchar column with value lengths of 2000 characters (I ...
0
votes
1answer
49 views

Postgres Planner not using GIN index Occassionally

Platform: Heroku; PostgreSQL Version 13 I have a table around 46GB and there are two GIN indexes on name and email columns I have a search query that is suppose to search in name and email columns ...
0
votes
1answer
44 views

Could hardware RAM disks (with battery) have any benefit in database servers?

For database servers with many writes, I am wondering why can't these ramdisk/ramdrive be a solution to avoid wearing out SSDs and to avoid losing data if too much is kept in normal ram. Can these ...
1
vote
2answers
84 views

I am burning trough NVME SSDs, 3x 60GB MySQL databases (web crawler)

I made a web crawler that is split on 3 computers, each of them makes many db queries, I think around 200 3000-4000 queries per second (each server) non-stop, with short spikes to 12000-14000. I am ...
-3
votes
1answer
60 views

is HAVING clause before GROUP BY clause processed differently than after?

Is there any difference how query is processed (translated to extended relational algebra), if HAVING condition is written before GROUP BY (compared to after)? In logical query plan it should be ...
1
vote
1answer
45 views

How can we reduce the cost of a view with a Window function

I have the following table structure in a Postgresql DB activity_planning: A table with conceptual activities (a "template" of an activity) scheduled_activity: a table with concrete ...
0
votes
1answer
97 views

SQL Server chooses very poor query plan

Recently, we've moved with our database from MySQL to Microsoft SQL Server (software is compatible with both engines). Overall performance after migration is quite good, but we've got problem with ...
4
votes
3answers
133 views

Postgres select on a very large table

We've a very large table with more than 2.2 billion rows at present on Postgres 12.5. The total size of the table (including index) stands at 500 GB. There is one query that we need to do in order to ...
2
votes
1answer
46 views

Speed the response time of the Query

So I have this query that I am using on an API endpoint, the query takes between 3-4 seconds to run, returning a small OFFSET of 10 rows. Is there a way I can put indexes somewhere or improve on ...
0
votes
1answer
51 views

Why does this sql join query take much longer using a reference to a row from the first table in the 'on' clause versus using a literal variable?

I have two tables, 'auth' and 'ips', which I'd like to join in a query. Table 'auth': column name data type Extra id int AI date varchar(6) - timestamp varchar(8) - result varchar(8) - ip ...
3
votes
1answer
45 views

How to get index scan for OR'ed time-range predicates?

I have table events with fields: id user_id time_start time_end ... And have b-tree index on (time_start, time_end). SELECT user_id FROM events WHERE ((time_start <= '2021-08-24T15:30:00+00:00' ...
0
votes
1answer
37 views

SQL Server Partitioning by Local And Remote database

We have a table related to documents(about 17m records on one table), and another one for properties of that documents. And team decided to archive old entries. I thought about partitioning with ...
0
votes
1answer
25 views

What dataset(s) is Marcus Winand using in his book "SQL Performance Explained"?

I am currently reading SQL Perfrmance Explained by Marcus Winand. I would like to follow along with the queries and examples he used to explain database tuning. It would greatly allow me to understand ...
1
vote
1answer
36 views

Optimizing a distinct on a parent table in a multi-level join in MySQL 5

I have the following tables: in a MySQL 5 database: projects - 1,000 rows tasks (FKed to projects) - 10,000 rows task_tags (FKed to tasks) - 350,000 rows task_comments (FKed to tasks) - 750,000 rows ...
0
votes
2answers
18 views

MySQL slowlog optimsing for Having and Group By Keywords

I have below entry in the slow-log, i just wonder how can i optimise this below query as it looks very simple query that, i don't see any way of optimising it, # Query_time: 32.797535 Lock_time: 0....
0
votes
0answers
15 views

How bulk query mongodb where each filter has its own limit?

I'm trying to create a search algorithm that will return more diverse results based on one common field in my "Articles" collection in my MongoDB. My "Articles" schema basically ...

1
2 3 4 5
52