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
34 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
17 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 ...
2
votes
1answer
29 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
25 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
39 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
24 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
21 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
20 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
0answers
35 views

simple DML's are taking more than 60 seconds During Huge Insert(10million Rows) Commit in MySql 8.0 [closed]

when we are inserting 10 millions of rows in to table(innodb) . During Commit ,All Dml's are taking more than 60 seconds. Currently we are using 8.0.15 version i have upgraded to different version ...
0
votes
1answer
32 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
46 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
35 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
44 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
28 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
56 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
53 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 ...
0
votes
0answers
29 views

SQL server working set vs commit memory [closed]

I've noticed a general degradation in SQL server performance last night and the first thing that I've noticed is that a sqlsrervr.exe process states that Working Set memory is only 790ish MBytes and ...
2
votes
1answer
51 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
84 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 ...
0
votes
0answers
17 views

Same server, same query, different response time

I have a development server that has some problem to access the data, user reporting that it's too much slow sometimes. The setup is: * virtual server; * 4 virtual CPU; * 8 GB of virtual memory ; * ...
0
votes
1answer
33 views

does using optimizer_mode=first_rows_n for application that using Oracle database is a best practice?

So I'm working in application that involving display of data in table/grid. And the grid will invoke paging query to oracle. So does by tuning my application to get the best query cost with ...
0
votes
1answer
32 views

Eav model sql select only products with all 3 values?

I have an EAV (Entity Attribute Value) based table for smartphones like the following: What if the user wants to see all phones with - red color - steel material The query is like: WHERE (...
0
votes
0answers
22 views

How to minify my PostgreSQL queries?

AWS can't collect query statistics for any query over 1024 bytes (soft limit) or 5120 bytes (hard limit). https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_PerfInsights.UsingDashboard....
0
votes
1answer
37 views

Database Normalization Advice [closed]

I have database for multi store application. So, I have 3 type of user thats Admin, Customer, Merchant and I have 3 table for users. User table, Customer table, Merchant table. User Table; id ...
0
votes
0answers
45 views

What happens when you update a column which should not be updated? Are rows updated completely? [duplicate]

Let's start with the bottom-line question. Say I have an UPDATE statement as following: UPDATE my_table SET my_column = <some new value>, my_other_column = my_other_column Is there a ...
1
vote
1answer
50 views

Weird planning time data dependency in Postgres

We got a fairly large table holding session data in Postgres. Recently, we found that the planning time for the most common query on this particular table increased over time, at one point to ~130ms ...
2
votes
1answer
41 views

High write latency on tempdb datafiles only

I'm experiencing large write latency on our tempdb database, but on no other database. (from sys.dm_io_virtual_file_stats) Tempdb log file latency is also listed as being below 5ms. For comparison: ...
1
vote
1answer
32 views

Postgres multi tables vs single table performance [closed]

I am building a db using PostgreSQL for a survey application. The legacy system uses separate tables for each survey which has around 1 million rows each(maximum). Since the columns are exactly the ...
0
votes
1answer
28 views

How to quickly cascade rare delete to a large table?

I have a schema that looks like this: create table items ( id serial primary key ); create table revisions ( id serial primary key, item_id int not null references items(id), name ...
0
votes
0answers
12 views

Varchar column, record value length & performance

I have a quick question regarding SQL queries performance. The project is using WordPress and the wp_posts table comes with a post_type column that is a varchar(30). The wp_posts records are going ...
0
votes
1answer
30 views

Slow Trigger performance when big batches (take2) [duplicate]

take2 of this post since it was missing information and it was closed prior to the update that I added. I have an update trigger that inserts into auditing tables. We had no problem until someone ...
0
votes
0answers
22 views

Multiple tables perfomance hit (mysql) [duplicate]

I've been thinking of introducing multiple tables each of which will store the chat history between two users in an attempt to not have a big table that would eventually be slow and hard to maintain. ...
0
votes
1answer
26 views

Select query on 8 million records with greater and less than columns (Slow)

I looked through the posts but cannot find an answer. I inherited this application so the response is 5+ seconds Server version: 10.2.25-MariaDB-log SUSE I added an index on 'LFT' and 'RGT' which ...
1
vote
2answers
76 views

Optimizing MariaDB for 5-10K small databases

Running a SaaS based on MariaDB LTS (1:10.1.44-0ubuntu0.18.04.1) with nearly 5000 databases (1 per tenant). Average per tenant: 8MB of data (on disk) with ~50 tables. CPU/Mem load is negligible. It's ...
0
votes
1answer
39 views

Understanding “bitmap heap scan” and “seq scan”

I'm trying to understand that which scan is efficient, Here is the explain for the query, EXPLAIN SELECT * FROM 'myTable_v' --view WHERE cast(TIMESTAMP AS VARCHAR(100)) > CAST(1586415955169 AS ...
0
votes
0answers
25 views

How to improve query performance sort operation overloads query

I have simple database which is consist from two tables. I want to build simple query that will return ForumId - Forum id (Topics). Title - Title of last post (Posts). DateTime - Datetime of last ...
1
vote
1answer
41 views

Avoid Sort on Indexed Group By caused by DATE_TRUNC

Is there a way to avoid Sort operation for GROUP BY, caused by the DATE_TRUNC function not being recognized as tstamp, which is a part of the ix index? That function doesn't really affect the order ...
0
votes
0answers
39 views

LOAD DATA INFILE Performance Degrading

I have very little database experience, but I've inherited a database for a production system that has major problems that I am attempting to fix. The system receives a continuous stream of data from ...
0
votes
1answer
14 views

How to create month ranges without using a specific date in Postgresql

What's the best way to create month ranges without using specific dates? For example, today is April the 4th and I want to calculate the sales rate of the past 12 months, that I can run every month ...
1
vote
1answer
43 views

why would a simple query have poor performance on MS SQL Server 2014 or 2014SP3 when on other versions it performs well?

I am running the following query on MS SQL Server 2014 (and also on 2014SP3) SET NOCOUNT ON CREATE TABLE #GUIDs( PartyNames_GUID UNIQUEIDENTIFIER NULL, Party_GUID UNIQUEIDENTIFIER NULL, ...
1
vote
2answers
45 views

Measuring the need of Capacity or scaling SQL server

This would be more of General approach question rather than too technical: I have one of my application reach out to me wandering if we need to scale up SQL server in terms of CPU memory disk or CPU ...
0
votes
3answers
45 views

Do you think is necessary to create an auxiliary translate table for table that has only two elements (id and name)

I have a question. In my database I have an Allergen table that has two elements: Id and Name. I want that my Allergen table has translations, so what is better ? Create an auxiliary translate table ...
0
votes
0answers
28 views

SQL Server - is there a way to convert OR conditions to UNION to improve performance (as part of execution plan, under the hood)? [duplicate]

Let's assume we have following query (greatly simplified, of course): SELECT * FROM MyTable Where A or B The query takes about 30 seconds to run When I rewrite the query as SELECT DISTINCT * FROM ...
1
vote
0answers
29 views

How to monitor query load, counts, and durations in PostgreSQL

I'd like to understand load (average active), count, and, average time of various queries. This is an AWS RDS Performance Insights dashboard. This is the sort of information that I want, though as ...
0
votes
2answers
54 views

Percona XtraDB Cluster (5.6.28-76.1-56-log) - random OOM events

We have a multi-node (each of 64G RAM) cluster. The main (the one we use for mutating data) node randomly just OOMs, this happens both in peak times and in extremely quiet times. What we've noticed ...
0
votes
1answer
26 views

script to find all bigint that can be converted to int fields in postgresql performance

I want to compact a postgresql 11 db and my idea is to convert lots of bigint to int data types if and only if there is a reasonable lag to the limit of int data type considering the data inside. So, ...
0
votes
1answer
26 views

mysql separate email and cellphone from user table to avoid extra unused-space usage

i have a question about putting some string (fixed-length and variable (which Might be always null)) into a table or separate them (Because of space-usage even if it's null) ... for example, my user ...
0
votes
1answer
60 views

What does pg_stat_all_tables.idx_scan actually mean?

We have a busy PostgreSQL 9.5 server with OLTP-like traffic where pg_stat_all_tables.idx_scan values go up with considerate rate while we're having performance problems. Does that (idx_scan ...

1
2 3 4 5
118