Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
2,588
questions
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 ...
