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.

Filter by
Sorted by
Tagged with
1
vote
1answer
41 views

Significant performance differences executing alter and grant statements between users with same permissions

I am experiencing significant performances differences between users with the same permissions when executing alter and grant statements. I have an automated build system that runs a series of SQL ...
1
vote
2answers
76 views

Why is ASC 100x faster than DESC and what can I do about it?

I'm on the cusp of understanding this, but I don't understand the fix that I need. The following query takes about minute, give or take, to return 20 records: explain (analyze, buffers) select "...
3
votes
1answer
225 views

Indexing options for Postgres for a table with billion records when you want to index only recent records

We have a table which has close to 1 billion records taking up to ~315 Gigs. This is just the size of the raw table without the size taken by any indexes. This table has a updated_at column which gets ...
0
votes
0answers
33 views

PostgreSQL 13 - Improve huge table data aggregation

I have a huge database (current size is ~900GB and new data still comes) partitioned by Year_month and subpartition by currency. The problem is when I try to fetch aggregation from the whole partition ...
-1
votes
1answer
29 views

Httpd is blocked at 512 connections on dedicated Server

I'm having serious performance problems with Httpd on dedicated server. In traffic peaks httpd stucks on 512 connections and without any reason all connections are slow and files which are downloading ...
1
vote
1answer
189 views

MariaDB Column Store Suitability for Data Warehouse

We want to build a data warehouse to distribute valid information across the organization (reporting) and get some insights into our data (analytics). After some research we found MariaDB using Column ...
0
votes
1answer
90 views

Does using PK and FK effect performance in negative way? [closed]

Few companies I noticed do not use PKs and FKs. I don't understand the logic of not using Clustered Indexes and I hear some say it can cause issues because FK couples records together and takes up ...
-1
votes
1answer
31 views

Are there any advantages to having SQL hosting separate from a website? Hosting my own servers or paying for a service? [closed]

Background I have been tinkering with an application I am writing. It is essentially a POS system that stores information about customers and inventory. Currently I keep all the data stored in CSVs ...
0
votes
0answers
23 views

Fastest way of updating a spider web of data

I have a large table with over 60 million rows, which has relational data inside of it. I need to update each of these rows to the newest date, of the newest member, including members that that ...
0
votes
0answers
25 views

Very slow query for massive stats calculation

In our app, we need to calculate the price comparison between a listing and its applicable comparables. It needs to happen for all active listings in DB on daily basis. The numbers of listings we are ...
1
vote
1answer
463 views

OPENJSON running once per row of an INNER JOIN unnecessarily?

I have a table MP_User with 147 entries, and in order to query it, I am passing in JSON containing an array of "query objects", each of which has a unique ID and either an email address or a ...
0
votes
1answer
115 views

While testing our workload on SQL Server 2019 we experience much higher CPU utilization compare to the same test on SQL Server 2014

We are getting ready to upgrade our SQL Server 2014 systems to SQL Server 2019. As part of our due diligence we created a workload which we are testing against both systems. What we have observed was ...
0
votes
0answers
10 views

Does it make sense to separate records in a document DB (e.g. DynamoDB) for better query performance?

I've recently started working with DynamoDB, and most of my experience is in traditional data warehousing (e.g. sql server). One common pattern which I saw when working with SQL server was that we ...
0
votes
0answers
49 views

PostgreSQL: Query runs fine in Prod but really slow in QA

Postgres 10.4 dbs hosted in AWS environment. I have a query which joins two tables with many-to-many relationship. I learned while troubleshooting that Analyzer won't run until number of rows inserted ...
2
votes
3answers
323 views

Urgent help with TempDB log file growth mystery

I need some urgent guidance for a tempdb issue for past last week: The tempDB log file is growing starting a specific time, lets say 2 PM. And almost continue to grow upto 150 GB and then comes down ...
1
vote
1answer
112 views

Postgres database insert become slow after 10 days, and then it needs a full vacuum

We have a postgres database which is almost 15GB's in size after vacuum. With a dedicated hardware i,e 32GB of ram with 12 Cores. The data automatically gets inserted around 300k inserts per day, and ...
0
votes
1answer
55 views

How do you handle maintenance window for DDL commands on Primary replicating to readable secondary AGs

We are currently running into this issue for one of our availability group design:- On Primary :- Nightly Table switch/Purges are happening (DDL) for approx. 2-3 hours On Secondary which is configured ...
0
votes
1answer
42 views

Slow SQL taking almost 1s to execute with only 24000 records! (EXPLAIN included)

Any ideas how to improve and what the actual culprit is? records in product are only 24000 takes 0.85s to run Mysql 5.7 select `product`.* from `product` inner join `manufacturer` on `product`.`...
0
votes
0answers
53 views

Readers blocking reader on readable secondaries

We recently migrated to AG setup for one of our VLDB's around 20+TB. Current setup is Multi subnet AG with primary accepting mix workload while heavy reporting offloaded to readable secondary replicas....
0
votes
1answer
35 views

In SQLITE, how do you create a new table into its own file?

I am trying to do key value pairing, where performance is the top priority. I understand that the smaller a sqlite file is, the better the read and write speeds are, and so I am going to create about ...
0
votes
0answers
137 views

Postgresql, joining most recent row from one table to another based on dates

We have several tables we have to bring together to match the most recently effective price with the transaction date. The simplified query is shown below: SELECT t.transaction_id, price.* ...
3
votes
1answer
323 views

SQL Server High CPU usage and RESOURCE_SEMAPHORE waits

Last week had an issue on one of SQL Servers, CPU started burning over 80 % (normal is 10-30 %) This lasted for about 2 hours until I manually failed over to secondary replica in AG (and this has ...
0
votes
2answers
112 views

Optimizing nested SQLite query

I have a SQLite table named hashbands that looks like this: hashband | file_id | window_id ------------------------------ potato | 0 | 0 potato | 1 | 0 The table has 100M+ rows (lol). ...
9
votes
2answers
245 views

Why is SQL doing an index scan and seek with the same XML query structure?

I've set up a test to benchmark xml performance in SQL server. Test Setup One Million rows of data XML defined Column with primary key A primary XML index A secondary XML index on the path XML data ...
0
votes
0answers
16 views

Which design could be better for scale a MongoDB query for this usecase

Context Imagine that you have two MongoDB collections, one called nodes and the other called mirrors. Each mirror is replica of a node. So, in case the node is removed (for whatever reason), you ...
3
votes
1answer
353 views

Can shrink database improve performance

Yesterday I've got a mail from one of our suppliers mentioning that there were performance issues with their application. I don't know yet what the issues are but the solution they propose is to ...
1
vote
2answers
58 views

Is it a good approach to design tables according to the queries I'll perform on it?

Watching this video, and pretty new to dbms. The speaker explains that in a row-oriented DB, rows are read in blocks. So, my understanding is that if I have rows with fewer fields, more rows can fit ...
1
vote
2answers
121 views

SP runs slower on new server

We migrated our databases from SQLServer 2012 to SQLServer 2019. Our ETL's are build in Visual Studio and are set up from a master package. The masterpackage calls on different packages, which are not ...
-1
votes
1answer
23 views

Query optimization with joins and unions and fulltext boolean search

I have the following base tables with subtables for searchbait. (This is on aws aurora with mysql 5.7 compatibility) tblA (id, userId, title, ...) tblB (id, userId, title, ...) tblA_searchBait (id-&...
2
votes
1answer
49 views

QuasarDB cluster stuck in IOWAIT on AWS EC2 instances

I am experiencing a system freeze in IOWAIT on my QuasarDB cluster. I am continuously ingesting data at 10 GBit/s using the QuasarDB batch API. After a couple of hours, I have all the qdb threads in ...
0
votes
2answers
52 views

Really poor performance when using subquery to get one value

Hi I have a table defined like below (Its actually lots of partition tables) CREATE TABLE sensor_values ( ts TIMESTAMPTZ(35, 6) NOT NULL, value FLOAT8(17, ...
-2
votes
1answer
39 views

Are unstructured databases faster using arrays or many binary variables? [closed]

Is it faster (or otherwise better) to structure unstructured data like this: { label:"many_variables_example", "a":0, "b":1, "c":1, "d":1, &...
1
vote
2answers
63 views

Tempdb log growth 3x as slow on new server, SP does not perform

We are running into the issue that our stored procedure on our new SQL Server performs a lot slower than on the old server. We looked into the problem and noticed the following on our temp db. The ...
0
votes
0answers
55 views

Improving query performance with two inner joins on tables that have > 40M rows

Context I have three tables - nodes, edges and sections. This is a graph network of paths from open street maps where edges are created from two neighbouring 3D nodes (that give distance, elevation ...
0
votes
3answers
82 views

How to re-write this query or restructure my table for SPEED

I have an InnoDB MySQL table of this structure: column type service varchar(16) url varchar(255) datetime datetime It contains logs for requests made to one of multiple services. It contains ~3 ...
0
votes
1answer
62 views

Can large data deletion affect Innodb buffer pool performance? [duplicate]

At the beginning of this year we lunched a lot of massive data deletions (millions of rows per week) in our production database. Also, there is an automatic integration of new data (insert/updates) of ...
1
vote
1answer
145 views

MySQL ON DUPLICATE KEY UPDATE monitoring

How do I monitor the performance of the following SQL in-depth? INSERT INTO rating (account_id, product_id, rating) VALUES (133236, 107, 0), (133236, 513, 1), (133236, 575, 2), (133236, 677, 3) ...
2
votes
1answer
40 views

MySQL performance issues with occasional complete freezes

we have a fairly large production DB currently at ~50GB but with constant growth. In the last couple of weeks, we are struggling with performances as some queries are getting very slow even if indexes ...
0
votes
1answer
54 views

Why is Oracle so much slower for small atomic inserts compared to Postgres? And what defaults can I change to remedy this?

If I create a brand new database from a Docker image and run a fresh database with the default settings. CREATE TABLE "TEST_TABLE" ( "ID" VARCHAR2(32 BYTE) NOT NULL ENABLE, ...
0
votes
2answers
31 views

How to design tables - dabbler question

I can understand that this is a basic question, but it's not about knowledge (I think) it's about practical experience. I came from another field so, be kind, please. I have my ORDER Every ORDER has ...
1
vote
1answer
17 views

How to optimise query fetching most recent report for each entry in another table?

I am attempting to optimise a query that displays the last reported movement for a set railroad locomotives. The query currently takes 8-12s to run for a common query to generate a report on a webpage,...
0
votes
0answers
38 views

Will database replication increase the size of the replicated database?

First thank you for your time. We have a SQL Server database that was assigned to be replicated. the original size of the database was 5 GB for the datafile, not the log file. Now after the ...
0
votes
0answers
34 views

In what sort of way "v$transaction" view will help DBA's to improve the performance of the query?

I've just started learning some DBA tips and techniques in Oracle and I came across v$transaction view. I've read some articles regarding this view but some things are still ambiguous ! I want to know:...
1
vote
1answer
87 views

Advice on Database Design

context : Teachers create problems to their Students A Problem have Numerals(Questions) A Numeral(Question) have Steps to guide the student to the answer Choice represent the type of mathematical ...
3
votes
1answer
109 views

Does a huge key length value for a mulibyte column affect the index performance?

When I look at the EXPLAIN results, the key len value is always calculated based on the actual column length multiplied on the maximum number of bytes for the chosen encoding. Say, for a varchar(64) ...
0
votes
1answer
64 views

Does daily pg_dump mess up postgres cache?

I migrated my geospatial Postgres 12.5 database to another cloud provider. I use postgis and I have around 35GB of data and 8GB of memory. Performances are way worse than on my previous provider, and ...
0
votes
0answers
20 views

PostgreSQL: Simple IN vs IN values performace

We have queries with IN filters with long list of INT values Sometimes, they running extremely slow, and I have found suggestion to use syntax Field IN (VALUES(1465), (1478), ... Instead of Field IN (...
0
votes
1answer
58 views

How can I measure the impact of queries with errors being rejected by SQL Server?

After configuring XE on November 23, 2020 to capture some errors I could count 2,163,665 occurrences of the same error coming from requests of a legacy app (that probably won’t be corrected so soon). ...
1
vote
1answer
502 views

MariaDB Uuid as Primary Key Performance Problem / Tweaks

im upgrading our mariadb database from PRIMARY KEY INT AUTO_INCREMENT to UUID PRIMARY KEY. We have around 80 tables with multiple relations between them. I've build a migration script to change the id ...
0
votes
2answers
65 views

MySQL partitioning relatively large DB Table

I have table structure similar to the following - CREATE TABLE `ProductCatalog` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `SerialNumber` varchar(20) DEFAULT NULL, `BasePrice` decimal(10,2)...