Questions tagged [performance]
An evaluation of whether a system works well enough to be fit for purpose. Normally performance refers to the speed with which a system completes an operation or set of operations over time.
6,378
questions
0
votes
0
answers
23
views
5k ms Planning time for 2k rows table
I have a table that has 44 columns and slightly less than 2000 rows. Also, the data are all text or integers. Texts don't go longer than a few characters. Table size is about 1900kb. All my other ...
- 1
2
votes
0
answers
87
views
Why does the same query require more "Heap Fetches" when run on my postgresql replica?
I have a Postgres 13.10 database running on AWS RDS, with a primary instance and a replica. The replication is managed by AWS - it's not logical replication I manage myself.
The replica is configured ...
- 311
0
votes
1
answer
125
views
What are the use cases for MySQL Generated Invisible Primary Keys (GIPKs)?
I have read in the MySQL documentation that it is possible to enable the generation of invisible primary keys (https://dev.mysql.com/doc/refman/8.0/en/create-table-gipks.html).
I'm quite a newbie in ...
- 3
0
votes
2
answers
129
views
The fastest and most memory efficient way to reset PK column values for a table with over 5 mililon records
I have the following table schema:
CREATE TABLE [inputs].[source](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CreatedOn] [datetime] NULL,
[ImportedOn] [datetime] NULL,
[Identifier_Value] [nvarchar](25) NULL,
...
- 49
2
votes
2
answers
557
views
Does PERCENTILE_CONT still give terrible performance when calculating medians in 2022 versions of SQL Server?
I'm currently reading a book from SQL Server 2014. It, like every other online source that I've found, tells you that PERCENTILE_CONT is a very slow way of calculating medians and does not show the ...
- 47
0
votes
1
answer
56
views
Does a long string with "unique" constaint take a lot of space?
I've created a table "global_settings" for storing different settings of a web applications -- the ones, that I want to be able to edit on html page:
global_settings:
id primary key,
key ...
- 35
0
votes
1
answer
41
views
Partitioning using column from different table
I have a table ActionValues with the following columns:
AccountID
Action
ActionValue
This table is related with a second table called Actions that has the following columns:
AccountID
Action
...
0
votes
2
answers
84
views
MySQL struggling to remove tmp tables
Windows Server 2016, MySQL 5.6, PHP 5.6, 64 GB of RAM.
My server is very busy, especially at certain times of the day. I get thousands of queries and sometimes hundreds of connections per second. ...
- 349
1
vote
1
answer
33
views
Slower MySQL queries involving both wp and custom table
Below are less efficient MySQL queries involving wp posts, taxonomies, and a custom movie table search. The custom movies table contains around 30k rows, and wp posts have been generated with the ...
- 141
0
votes
1
answer
182
views
How to optimize the SQL query which uses cte, group by and run for 23 hours?
I have a query which first performs joins inside cte and then based on cte join with other table and perform group by with some aggregations and get the top 15 records.
This query is taking 23 hours ...
- 129
2
votes
1
answer
355
views
Postgres-15 response time jumps up from 0.5 ms to 100-200 ms once in a while (~ 0.5% of requests are affected)
I have a simple database with one table of ~5 millions rows, another table with ~10 millions rows, couple of other tables have 10th of thousands of rows.
There is a constant load without any spikes at ...
- 213
1
vote
2
answers
59
views
How to optimize SELECT COUNT with LEFT JOIN?
I have a MariaDB 10.5.10 database, with a table activity made of 150,000 rows. it holds:
an id field (integer, primary key)
a tenant. There are about 85,000 rows with tenant = 12
an activity_type (...
- 113
1
vote
1
answer
61
views
Performance issues that seem to be server dependent
I'm dealing with a performance issue that seems to be server dependent. The server in question is version 15.0.4316.3 (X64). Here's the code below.
SELECT b1.ID
,ParentID = (
SELECT TOP 1 ...
- 13
2
votes
3
answers
399
views
How to manage mysql database table index
I am not aware of how to manage an mysql database table index and I attempted to implement it, but I am uncertain about managing the index for this extensive MySQL database table (Will do it at my end ...
- 141
0
votes
1
answer
79
views
SQL Server 2019 SE adoption of in-memory OLTP table consideration
i have an SQL Server database, that is almost a read-only db (the write procedure will happen at scheduled time, tipically twice a day).
I have recently discovered about the in-memory table (i'am a ...
- 358
0
votes
1
answer
460
views
Tips for improving MariaDB performance and tuning queries
I'm currently working on optimizing the performance of my MariaDB database and have come across a tool called mysqltuner. I've installed and run mysqltuner on my server, and it provided me with some ...
1
vote
1
answer
621
views
Do statistics have an impact on delete performance?
Background:
I am a developer working on an application that has a relatively large table (in my experience at least) of 1.7 billion records.
The table is a history table, and the application imports ...
- 113
0
votes
2
answers
138
views
Is there a benefit to using a surrogate key for a many-to-many table?
It is understood that a table with a clustered index with non-sequential value(s) will cause fragmentation due to page splitting.
In my case, I'm working with a pair of uniqueidentifiers in a many-to-...
0
votes
0
answers
28
views
MariaDB unresponsive on alter InnoDB table & performance improvements
MariaDB 10.8.3 on CentOS / 8 processors / 32GB RAM / 700GB SSD partition for DB, 600 GB for OS / Dedicated
I have a MariaDB database instance that performs a large number of LOAD DATA INFILE, and ...
- 1
0
votes
0
answers
213
views
Performance tuning for jsonb column containing nested json arrays
I have a jsonb column in a table with the following model:
{
"framework": {
"name": "framework name",
"sections": [
{
"controls&...
0
votes
2
answers
48
views
Query a table with association to itself
General question here.
If a table e.g. Person has a foreign key to itself to model "parent" association and you had to do a query get anyone who is named "Peter" or their parent is ...
- 211
1
vote
0
answers
36
views
MongoDB RBAC limitations
I'm researching if MongoDB is the solution for my problem of storing schema-less data for multiple users. Let me sketch the problem: I've got a lot of users (100k) and want to have some place for ...
- 111
0
votes
0
answers
29
views
Temporal Attribute using supersededBy vs updating the entity?
Back in the day when databases were on physical disks and seek times are slower, I was told that one performance tweak that can be done is to avoid doing UPDATE or DELETE in a table and instead rely ...
0
votes
1
answer
191
views
Identifying the reason of the CPU pressure
I see a high CPU pressure in the SQL Server and SQL Sentry shows only the following command which consumes the CPU abnormally. I think this is an internal process of the database engine but what stuff ...
- 110
0
votes
2
answers
66
views
Moving tables to different database within same sql server
There is a SQL server, there are around 100 databases in it. I have to query a few tables from one of the databases. when I query, it's very slow and I think CPU utilization is very high at that time. ...
- 101
1
vote
2
answers
653
views
Live query statistic is waiting for query plan forever
I have a large query with performance issues. I want to run it in live query statistics mode, but instead of the execution plan, I see the inscription "Waiting for query plan".
In sys....
0
votes
0
answers
35
views
If I am using group by in a Postgres query to a Type 2 db can I improve the performance by putting a portion in a view?
Apologies for any typos, copied the query from code and renamed things for IP protection.
This is a Type 2 database, updates are inserts with the value of the old record for all fields except for the ...
- 207
0
votes
1
answer
125
views
Best practices to minimize latency in a globally available application using a SQL db?
We have a globally available application that uses a SQL database. We have a fairly low number of users and load on database is low, however latency for users located far from the database server is a ...
- 101
0
votes
1
answer
79
views
Indexing of hash-value column vs INT IDs for session tokens
Let's say:
your application should use session IDs generated with a CSPRNG, which shall authenticate your user sessions
your system uses PHP 8.2 and MariaDB 10.6
Which of the approaches below ...
- 163
-1
votes
1
answer
76
views
Fastest way to get a intrinsic cartesian product of grouped data
Consider table:
CREATE TABLE dbo.DIMENSIONS (
dim_order int,
dim_name varchar(50),
member_key varchar(50)
)
SELECT * FROM dbo.DIMENSIONS
dim_order dim_name member_key
----------- --...
- 51
0
votes
1
answer
127
views
Slow count on empty table unexplained
My incremental materialized view refresh starting to become slower and slower. Each day there are millions of new records. So there are many deletes on the mv log table. After investigation I see one ...
- 513
0
votes
0
answers
158
views
Mutliple sessions on commit status/ aurora_redo_log_flush event in AWS Aurora 2.11.1 - MySQL 5.7
Our database runs on Aurora 2.11.1 - MySQL 5.7 and we have enabled performance insights.
It's usually runs in between 15 to 50 sessions at a given time. According to the performance insights around 20 ...
- 1
0
votes
1
answer
63
views
Why the following first of two queries in MSSQL is faster as I want the second one?
1st Query: result 32 seconds (1,758,220 rows)
SELECT
CAST(ID.PYYY AS varchar(8))+','+
CAST(ID.SYYY AS varchar(8))+','+
(
SELECT TOP 1 (
CAST(IMM.INVOICE_DATE1 AS varchar(8))+','+
CAST(...
- 101
1
vote
2
answers
86
views
Custom Operator Class for BigInt Using Bloom Indexes Not Working in PostgreSQL
I'm currently dealing with a large PostgreSQL table containing approximately 350 million rows. For performance optimization, I'm trying to create Bloom indexes on some bigint columns in my table.
...
- 11
1
vote
1
answer
30
views
RDBMS performance on updating an index key(non primary)
I am creating a scheduler service for which I am using RDBMS(Mariadb, engine:InnoDB), which has following schema for jobs table
CREATE TABLE `jobs` (
`id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,...
- 111
0
votes
0
answers
51
views
how to reduce the time to list all schemas of PostgreSQL
QGIS 3.18 PostgreSQL 14.
We use QGIS to access data from a PostGIS server. The data are stored in 33 schemas.
one contains all the data in 17 tables;
the others schemas contain views and materialized ...
- 207
0
votes
0
answers
60
views
Oracle database connection performance dropped to bottom, causing severe performance issues in web application and scheduler job failures
We have some batch scripts as task scheduler jobs, which are running good from few years. These jobs connects to DB and will do multiple inserts and update operations using sqlplus, sqlldr and java ...
Shadowtest
0
votes
1
answer
37
views
Impact of creating separate indexes on high-insertion PostgreSQL table [closed]
I'm building a taxi application that uses PostgreSQL as the database backend. One of the tables, taxi_order, has the following columns: start_address, country_id, taxi_fleet_id, and driver_id.
...
- 111
0
votes
0
answers
17
views
Memory of server never drops data of sql server [duplicate]
I have this problem that occurred in one of the servers our System works in
the SQL server consumption of memory grows and grows until it gets 96% of memory
the system makes a request to the database ...
0
votes
0
answers
177
views
postgresql on docker eats all the cpu for query
I have a Django app with a PostgreSQL database. Everything was running smoothly until I moved the stack to Docker. Now, one of my big queries is running really slowly, and I noticed that the ...
- 101
1
vote
1
answer
102
views
Rewriting a DELETE statement to reduce pagelatch_ex waits
I have the following TSQL statement, which is often executed in multiple sessions in parallel and leads to serious pagelatch_ex waits:
DELETE dbo.huge_table1
WHERE c1 = 0
AND c3 IN (SELECT v ...
- 1,159
2
votes
1
answer
212
views
Mariadb: Can I increase performance by having multiple column indexes with different orders?
In my Mariadb table I have two id-columns (A,B), which combined are unique, but not individually. This table is quite large. To speed up queries like
SELECT B WHERE A=?
I have added a primary key (A,...
- 23
0
votes
2
answers
48
views
Help trying to optimize a query for active user items (where on a boolean column)
We have a production project running on MySQL with following scheme
[There is obviously many more columns but I omitted irrelevant columns to simplify the question]
# Has approximately 9 million rows
...
6
votes
3
answers
1k
views
Is there an option or hint possible to improve performance of query with multiple values in the "in" clause
We have a table CustomerNote with 4 columns ID, CustomerID, Note, Date
There is an index on CustomerID asc, Date desc
When the following query is executed
select top 30
Date
from CustomerNote
...
- 563
0
votes
1
answer
399
views
How to Check if SQL Server is Running Out of Memory or CPU Resources RIGHT NOW?
I recently noticed an error message in my SQL Server error log that said "The query processor ran out of internal resources and could not produce a query plan." I suspect that my server ...
- 1,159
0
votes
2
answers
106
views
What's the most performant condition when dealing with multiple OR?
Let's assume a translation table:
Column
Type
🔑 id
bigint
key
text
locale
varchar(10)
value
text
with a unique index on (key, locale).
I have a bulk endpoint where I want to get multiple key/...
- 103
0
votes
2
answers
58
views
Why making temporary table is faster than multiple WHERE conditions?
I have a table like that:
CREATE TABLE Sessions (
SessionId int(11) NOT NULL AUTO_INCREMENT,
ExternalId int(11) NOT NULL,
Active bit(1) NOT NULL DEFAULT b'0'
) ENGINE=InnoDB DEFAULT CHARSET=...
- 215
1
vote
1
answer
77
views
Azure VM Drive size configuration for MS-SQL
I have run into a scenario for a production server where I was recommended to create a separate drive for each one of the bigger clients (based on database sizes) whereas all other smaller clients can ...
0
votes
1
answer
56
views
Improving the performance of STEquals
We have a table containing all polygons (geometry/geography) of the objects in our application.
This is because most polygons need to have a 'geometry' (RDNew) and a 'geography' (WGS84) version of the ...
- 119
4
votes
1
answer
99
views
How can I avoid this poor Postgres query plan for a sparse aggregate query?
I have the following database schema:
CREATE TABLE foo(
id SERIAL PRIMARY KEY,
tag INTEGER NOT NULL,
val INTEGER NOT NULL
);
CREATE INDEX foo_tag ON foo(tag);
CREATE INDEX foo_val ON foo(val);
...
- 140