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
2
votes
1answer
15 views

Is a join faster with 5 tables joined together Or 5 separate queries?

I have confusion that I already have in my application 5 tables say a,b,c,d,e and have relationship with each other like a.b_id is primary key of b and chain goes on. Now my question is that which ...
0
votes
0answers
21 views

Best field type for cryptocurrency big numbers?

I'm using PostgreSQL for my cryptocurrency exchange database, the question is: for saving currency amount (numbers) with their precision (like 323232323232323.45454545 ~ 23 digit + 1 dot: 15 digit ...
0
votes
1answer
20 views

Wrong index been peeked

I have an index that filters 99% of the table i.e. ix_magic_composite (for that query arguments). When I add another or filter it chooses the wrong index, i.e. fTS even if I create an index that ...
-1
votes
0answers
12 views

The performance of transaction_isolation in MySQL

I know that transaction_isolation has 4 valid values: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE, and the order above is from least amount of consistency and protection to the ...
0
votes
1answer
27 views

MySQL Clustered Index or Non Clustered index? (Database Design)

I know this might be a basic thing for you guys in this corner of SE but I'm having some trouble figuring out the best option for designing my DB. I am creating a custom app where the user will be ...
0
votes
1answer
15 views

Create partial index with single value

I have PostgreSQL 12 DB with a table with a several columns, two of them are a bigint ("numCol") and a boolean ("boolCol"). numCol can have any integer number, and 0 is about 0.1%-...
2
votes
1answer
57 views

Auto Update Statistics threshold SQL Server 2016

since SQL Server 2016 the default auto statistics update threshold changed in that way that it uses a smaller threshold depending on the table size. In our application we periodically update ...
-1
votes
0answers
49 views

sql server database level performance slow in production? [closed]

We used below queries to monitor the performance: SELECT session_id, wait_duration_ms, wait_type, blocking_session_id FROM sys.dm_os_waiting_tasks WHERE blocking_session_id <> 0 and SELECT * ...
0
votes
0answers
22 views

Postgres 12 create table as simple select + hash join takes 8+ hours for 100GB?

I'm using a "create table as select ..." statement to create a copy of a subset of the columns in a ~100GB table (400 million records). I am also performing a hash join with another table ...
2
votes
0answers
39 views

Preventing low-priority tasks from dominating Postgresql server

I have a side project which involves scraping several websites and aggregating their content, which can be queried via an API. As such, I have a few background tasks which update/insert millions of ...
0
votes
0answers
20 views

Is 96k rows INSERTed in ~45 seconds slow or normal?

I just coded a script which created a PostgreSQL table, prepared an INSERT, downloaded a CSV file with 96k rows (which accounts for maybe 5 of those seconds it took to run) and then looped through the ...
0
votes
1answer
23 views

SQL Server Slowest Query is NULL

I am looking at both the SQL Server expensive queries report and the query below, but both are showing this mysterious NULL query as the slowest query on my server. Is there any way I can find out ...
1
vote
3answers
53 views

Best way to choose which columns to index to improve performance

I use a MSSQL Database for my web application and now the user audit(USER_AUDIT) records in the application have crossed beyond 5 million records/rows. Every day the performance of the application is ...
1
vote
0answers
43 views

High CPU usage with MySQL RDS

I'm running t3.large MySQL instance and having high CPU usage issue. There are tons of data stored and tables in one database. After rebooting the instance everything is pretty good and all of queries ...
0
votes
1answer
18 views

Table Filtering Using Big List

Briefly, I am trying to filter a table using conditions plus a list of items in Oracle. In detail, I have a reporting table which has millons of records. I would like to filter it using external lists ...
0
votes
1answer
33 views

How can I design a table with greater than in where statement and ordering to avoid filesort?

I have a list of documents that I store along with creation date and rank: CREATE TABLE `document` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `category_id` INT UNSIGNED NOT NULL, `rank` INT ...
2
votes
1answer
32 views

Function in same Session become increasingly slow when run multiple times concurrently

PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit Client is Data Grip and same behavior from my report server which uses ...
1
vote
1answer
26 views

MySQL my.cnf performance tuning recommendations

Most of the day, the performance of the server is good, but once per day it stops responding totally even I can't access via SSH, and I have to restart it to make it up again. I have a dedicated ...
2
votes
2answers
100 views

SQL Server - quite big data flow - Disk became the bottleneck … what now?

After a lot of searching & follow up of the system we are getting out of ideas to effienctly solve our issue ... hence this question. We suspect (are rather sure) our storage became the bottleneck ...
0
votes
0answers
10 views

Use one table and a “type” column to represent two types of vote_counter or use separate table?

I am building a blog system, the blog can have its comment. And like to provide the upvote/downvote feature for both the blog and comment. Since the vote count number of blog/comment should be ...
0
votes
1answer
24 views

Collecting VLF information on all versions of SQL server

Based on my monitoring collection, i am having an issue while collecting info regarding how many VLF' i have for # of databases in SQL instance. 1st issue - Is there a single script which can be used ...
1
vote
0answers
22 views

MySQL - storing 162 million rows in 1 table as the result of an IFNULL query

I'm working on a legacy web application which uses a MySQL (5.5.65-MariaDB) database which is hosted on a server running CentOS Linux 7.7.1908. The application is a database of chemical substances and ...
0
votes
1answer
25 views

InnoDB performance implications of one 3 column index VS three 2 column indexes

I'm using MariaDB with InnoDB but this would apply with MySql as well. I have a list of records that belong to categories and would like to order them by 3 different criteria. I have come up with two ...
1
vote
1answer
37 views

Sum of all Memory Clerks and Total Server Memory (KB)

I am trying to understand the memory management of SQL Server. I have a Windows Server 2019 on virtual environment with 64 GB of RAM. I also have SQL Server 2019 installed with Max Server Memory 59776 ...
-2
votes
1answer
60 views

How do huge databases handle new users? [closed]

My question is on database design. How do you design a schema for DBs with a humongous number of rows? For example access logs: you can create a table access(date, user, endpoint) but for systems with ...
3
votes
1answer
30 views

Python MySQL prepared statements slower than regular queries

I'm trying to use prepared statements with python to improve query performance, but my prepared statements are slower (by a factor of ~7.5 no matter how many iterations I set in my benchmark script). ...
1
vote
0answers
39 views

SOS_CACHESTORE_CLOCK Spin Lock

We recently moved from SQL Server 2012 (SP3) to SQL Server 2017 via restored backup. When we were running on 2012, performance was adequate. Since moving to 2017 we have noticed a degradation in ...
0
votes
2answers
40 views

Size of a MyISAM table doubles after OPTIMIZE

I have this table: CREATE TABLE `foo` ( `CalculatedResultsId` int NOT NULL, `Md5Hash` char(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL, `SectionData` json NOT NULL ) ENGINE=...
1
vote
2answers
41 views

3 additional varchar(32) keys and also updating them — too bad for performance?

I have a table in mind which basicaly would look like this: table1 -------- id bigserial autoincrement primary key, token1 varchar(32) unique not null, token2 varchar(32) unique not null, ...
1
vote
2answers
36 views

MySQL - Flush Memory after loading files

I am loading a great amount of data which are in CSV files using LOAD FILE command. It is working fine and after loading that files, it executes some data transformation... The problem is that, after ...
1
vote
1answer
33 views

MySQL InnoDB Insertion Faster Than MyISAM

I wrote code that inserts about 15 millions rows into tables in MyISAM and InnoDB for comparsion. MyISAM and InnoDB are set up with no optimization configuration in /etc/mysql/my.cnf. And tables ...
1
vote
1answer
38 views

Foreign keys to primary tables or nested table

In this hypothetical example should the foreign key constraint setup for the ProductId and UserId columns in the ProductUserCommentAction table be referencing the Product/User tables as shown in the ...
0
votes
0answers
18 views

Average Waiting Tasks Count / Resource Waits

I'm trying to help troubleshoot a sudden performance problem with SQL Server 2014 SP2 (12.0.5659.1). I don't have details on what exactly the scheduled stored procedures are doing, but I know that the ...
4
votes
4answers
162 views

SQL Server 2019 performance worse than 2012… am I missing something?

We have a SQL Server 2012 server which far outperforms a SQL Server 2019 database on (as far as I can see) the same infrastructure. We are hosting both databases on a cloud platform with the same SLAs....
1
vote
1answer
26 views

optimizing Postgresql query with left join and indexes

I am trying to run the following query but it is taking forever to execute: Select Distinct ON (S1.sql_id) S1.msgdata as replymg, S1.time as replytme, S2.receiver, S2.time,...
0
votes
1answer
42 views

Same query but different plan on different server

I have recursive CTE query, it was taking 30s to execute, after investigation I found out that a column (Name) with datatype nvarchar(MAX) was the problem so I changed it to nvarchar(1024). After ...
0
votes
1answer
20 views

Azure SQL: Should Manual Indexes be Applied with automatic tuning?

Should Manual Indexes on tables be applied in AzureSQL Cloud Database? We read that AzureSQL does automatic tuning, so are manual indexes even required? We read, there can be cases where AzureSQL ...
1
vote
1answer
29 views

Improve PostgreSQL SELECT performance in a parent table (inheritance)

I have a PostgreSQL 9.2 database where each account has a schema, like below: My_Database |-> Schemas |-> AccountA |-> AccountB |-> AccountC |-> AccountD |-> ...
0
votes
1answer
25 views

AWS RDS MySQL some time get slow performance when update table1 set field='any word' where primarykey=1234

Normally when update table1 set field='any word' where primarykey=1234 take 1sec. or less but sometimes random take much more time possible 500sec. or more. I was set very well and never see high CPU,...
0
votes
0answers
23 views

MongoDb write performance degradation when increasing the number of concurrent writers

I was testing MongoDb write performance and noticed the following behaviour: when I increase the number of concurrent writers, the average time needed to write a document increases as well and ...
1
vote
1answer
28 views

Improve querying of Extended Events target file

As part of our server estate monitoring, I am adding extended events to pick up warnings, blocking etc and I'd like to periodically (Every couple of minutes) query the event file to collect the data. ...
0
votes
1answer
34 views

Feasible to have thousands of foreign data wrappers in Postgres?

We are creating enterprise-grade SaaS where we will have many 1,000s of customers. We are considering creating a database in the Postgres server for every customer to ensure enterprise-grade security/...
1
vote
3answers
52 views

MyISAM around 5x slower than InnoDB

I thought, MyISAM is for simple tables and selects due to its architecture faster than InnoDB. Therefore I changed the engine for this table from InnoDB to MyISAM: CREATE TABLE `table1` ( `DateTime`...
2
votes
1answer
39 views

Why is my indexed DISTINCT ON so much slower than my INNER JOIN?

I have two tables, customers and purchases. There are a lot (thousands) of purchases per customer. I usually only need the most recent purchase for each customer, which is why I have the ...
2
votes
1answer
45 views

Best indexing strategy for spatial join and ranged lookup

Context We have two tables (not the real tables used in the examples below. They are from a toy DB im using for testing): Incidents_2 (Columns of interest are a geom, and a reported_at (int8)) ...
1
vote
2answers
29 views

A SQL query optimzation issue with ORDER BY

An SQL query optimzation issue with ORDER BY Current status I have a database of Chess games and their corresponding moves (stored as strings called fens). I have two main tables 'Game' and '...
1
vote
3answers
58 views

SQL Query taking long to execute especially with offset and fetch for pagination

This is the query I'm running for a report. It takes about 20 seconds to run without the offset and fetch. With those two, The query doesn't even complete in 10 minutes. select * -- h.ActionDate as ...
1
vote
1answer
56 views

Saving images as base64 encoded strings, why is it bad?

I've seen this on one of the production databases I've come across and these images apparently cover a large portion of their DB. After researching a lot I couldn't really find a lot of good answers ...
2
votes
1answer
60 views

Optimization of a query to retrieve records randomly with multiple joins and filters

I have the following schema: This question was posted also in StackOverflow, but I want to consult also to specialists more focused on DB administration because the nature of my project. Sorry if ...
1
vote
1answer
99 views

Slow query time after upgrade to MySQL 8.0.20

I apologize in advance if a similar question has been asked. I've been digging through Google and StackExchange trying to find an answer but now I'm stuck and stumped. I am still relatively new in ...

1
2 3 4 5
119