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,353
questions
0
votes
0
answers
39
views
Performance issue on same identical database with same execution plan
Need some help advise on one of the issues that we have bee working on.
For one of our SQL databases (SQL2014) we use database mirroring ( i know its old but we are working on migration) and there is ...
- 762
0
votes
2
answers
105
views
SQL Server database with 100 terabyte of data
We have been assigned a new application where the vendor is expecting the database to grow to around 100 TB. They have mentioned SQL Server Standard Edition is compatible.
My question is whether SQL ...
- 1,021
0
votes
0
answers
47
views
SQL Server unexplained network traffic gaps in traced events
This appears to have started happening a week, maybe two weeks ago.
Essentially, we cannot figure out whether it is the server or the client that is periodically pausing between SQL statements. In the ...
- 4,310
0
votes
2
answers
37
views
Azure Disk performance optimizaton in SQL VM
We are using Azure SQL VM and we have multiple data files of multiple databases stored on same drive (let's say Drive E: of size 3 TB) which is created from 4 * 512 GB disks + 4 * 256 GB disks.
I was ...
0
votes
0
answers
28
views
Sql-Server Performance Degradation on a specific DB
I have a production Sql Server 2019.150.4316.3 instance (2 Core licence) which holds two mid active databaes (~200 concurrent users) along with low active DBs, installed on a VM (64 GB Ram).
...
0
votes
2
answers
58
views
When can we downsize Azure Virtual machine for SQL Server?
For now our production SQL Server is Standard_D96ds_v5 Azure VM machine.
We got request to check if we can downsize server to Standard_D48ds_v5.
What measures will take into consideration to have ...
- 283
0
votes
1
answer
11
views
psql meta-command \d is really slow
My basic question is: what affects the performance of \d?
I have a separate (small) schema that I manage on a database server that I have otherwise no higher privileges on. The server holds a huge (...
- 341
0
votes
2
answers
53
views
How to make a remote SQL Server to pull local SQL Server data
In my scenario, I have a SQL Server Express that is my data source. It periodically produce batch of data, that should be uploaded to a central repository (a SQL Server SE in the cloud). The data ...
- 358
1
vote
1
answer
26
views
VSSSQLwriter error
I've a SQl server 2019 on a Windows Server 2019 standard core, I manage two typer of backup, one with mantainance plans and one with Veeam Backup. The mantainance plans it's ok, the backup with Veeam ...
0
votes
2
answers
57
views
any way to keep track of all queries and their number per hour or per day in SQL server
in SQL server, i would like to keep track of all queries and their count , based on specific time frame and keep thsir historiacl record.
like every hour , which query ran for how many times.
We have ...
- 121
0
votes
1
answer
40
views
How to speed up query for stock balance?
I have 2 tables StockIn and StockOut and calculated stock balance (in-out).
Both tables contain millions of records.
When I calculate the stock balance, due to the millions of records, my query ...
- 3
0
votes
0
answers
40
views
How can I optimize the view in MariaDB for Order By?
I have a list of products. Each has many tags. Those tags have normal tables, but they also have OLAP tables that present CSV for each product.
For example, a chair can have 'furniture', 'wood', and '...
- 1,407
0
votes
1
answer
44
views
Storing segmented time series - performance issue caused by triangle relationship / maximum search
I am trying hard to create a database design from scratch, that allows to store multiple different time series, where each one may be segmented into individual epochs. Inserting new data points for a ...
- 3
2
votes
0
answers
53
views
Efficient Partitioning Strategies for PostgreSQL Table with KSUID and High Volume
I'm working with a PostgreSQL table containing terabytes of data, and it grows by millions of rows weekly. Each row is identified by a KSUID, and my primary read patterns are:
Retrieve a row by its ...
- 21
3
votes
1
answer
96
views
PostgreSQL Query Planner overestimating Row Count leads to slow Hash Join
I have a table with two columns and about 10M rows. About half the records are NULL in the first field, and about half the records are NULL in the second field, although there are a few hundred ...
- 31
0
votes
1
answer
24
views
SSRS with remote ReportServer db - where to focus cpus/memory for processing
On an SSRS instance with a remote ReportServer db, where is the processing done? Should the SSRS instance vm get additional cpus and memory or the sql instance where the ReportServer db is? The ...
- 83
0
votes
0
answers
35
views
PostgreSQL Optimizer does not find best solution when specifying subset of keys in query
PostgreSQL (Version 15) does not find a (in my opinion) obvious optimization for the query plan, is there a way to help the optimizer with this problem?
I have a specific kind of query where I have to ...
- 9
0
votes
1
answer
33
views
Slow deletes in Postgresql with foreign key constraints [duplicate]
I have a number of tables relating to an entity and delete them in a transaction to make sure all are removed simultaneously:
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
delete from table1 where entity_id = ...
0
votes
2
answers
30
views
Can many table names having the same prefix slow down a database?
I understand (maybe wrongly) that SQL databases often use associative arrays based on B-trees and that looking up entries in these arrays entails binary search that lexicographically compares a key ...
- 101
1
vote
1
answer
65
views
Postgres GENERATED ALWAYS performance when adding new column
I have a fairly big PostgreSQL 13 table that I need to add a new TEXT column and with this new column I would like to add a GENERATED ALWAYS column with the following definition:
ALTER TABLE my_table
...
0
votes
0
answers
23
views
Optimizing Performance of Highly Nested Postgres SQL Query
I have been working with a PostgreSQL database and I've encountered a SQL query that seems to have a high degree of nesting, which I suspect might be impacting its performance. I'm seeking advice on ...
- 101
0
votes
1
answer
53
views
MariaDB is in "Sending data" state for too long for COUNT
Recently I have encountered an issue of slow data loads.
Upon looking into the Process list I have found out that the issue is on the database side, it gets stuck in the "Sending data" phase ...
- 99
0
votes
0
answers
20
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
38
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
63
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
126
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
485
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 ...
- 125
0
votes
1
answer
42
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
29
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
55
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
31
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
130
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
135
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
49
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
57
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
378
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
56
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
276
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
578
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
105
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
24
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
111
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
40
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
26
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
26
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
182
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
58
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
327
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
34
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
84
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