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
0
votes
0answers
20 views

Many sessions AWAITING COMMAND

I need some guidance please. An application is going through migration and the processes seem to be quite cpu intensive. CPU utilisation on the server is very high - in the 90's. The headblocker ...
1
vote
1answer
26 views

Speeding up SELECT FROM a large table

I have a large table (500M rows with ibd size of 1.5TB) CREATE TABLE t1 ( ID int(11) unsigned NOT NULL AUTO_INCREMENT, ChildID int(11) unsigned NOT NULL, Number smallint(5) unsigned, Title varchar(255)...
0
votes
1answer
44 views

What's the best practices for storing visits?

I would like to save visits in a table. I am using MySQL but I am not sure how I should save the visits as the records may increase considerably and this might cause performance issues and I am also ...
0
votes
1answer
47 views

Slow query Mysql with Order By and JOIN

I've been investigating an issue with a slow query when trying to get the last 50 comments of a user. To give some context the database size is 9 TiB and the COMMENT table has about 7 billon of rows. ...
0
votes
0answers
24 views

High use of CPU on AWS RDS mysql

I am experiencing trouble with a database, I am pretty sure there are some troublesome queries created by the programmers, but I need to find the culprit. So usually I dont have a problem with the ...
0
votes
0answers
59 views

How to speed up restore database from snapshot in SQL Server? [duplicate]

To revert a test database to an initial state (after running a test), I would like to restore the database from snapshot. I'm using the following script to achieve that. However, the script execution ...
0
votes
2answers
47 views

Choice between two antipatterns: Overdesign versus Over-optimization

Sometimes I need to keep state, like an IsVerified flag. There are two options: boolean (or bit) Advantage: smallest and fastest ...but in theory "over-optimization" and thus a bad argument. ...
0
votes
1answer
55 views

Speeding up DBCC CHECKDB on a large database

I have 16 GB RAM, 12 GB allocated for SQL Server, total size of all databases on an instance 5TB. DBCC CheckDB with PhysicalOnly checking on the biggest database ~ 3TB. 1 socket, 2 virtual processors. ...
0
votes
2answers
50 views

Query with CASE in subquery join clause is very slow on PostgreSQL but fast on Oracle and MariaDB

We have an attribute-oriented data management system with bitemporality and flexible tenant priorization logic. Deletion of records is done by inserting the same row in DELETED status. Tenants may ...
2
votes
2answers
1k views

Join condition vs. WHERE...does it make a performance difference?

I've developed a habit of separating join conditions from other additional conditions. However I understand the logical execution order is: FROM WHERE Can it be detrimental to performance if I add ...
0
votes
0answers
32 views

Is there any alternative to Sage Evolution's way to backup & restore databases

I am currently working on a handful of web apps that work in coherence with our Sage 200 (Evolution) database. However, one problem that we have noticed at the moment is the ability to backup and ...
0
votes
0answers
14 views

Postgresql POD Master/Slave don't clean the cache when Client stopped sending data

I'm running a Performance Stress test on our backend (NODE JS) using a client(JAVASCRIPT) that generates the stress , with a Postgres DB. I've stopped the client from sending data after 5-6 hours , ...
1
vote
2answers
67 views

Select * - Why or Why Not?

Is there really any difference between these two queries? SELECT TOP 1000 [COL_1], [COL_2], [...], [COL_n] FROM [database].[schema].[table] SELECT TOP 1000 * FROM [database].[schema].[table] ...
1
vote
0answers
61 views

How can I confirm why a SQL Server query runs faster on development laptop than production server?

I am finding that queries are taking slightly more than twice as long to complete on the production server compared to my development laptop. My assumption (possibly incorrect) is that the query ...
2
votes
1answer
242 views

What perfmon metric will help me to monitor whether the SQL server memory is under pressure and resulting in usage of page file on disk?

SQL server is assigned 110GB memory. It is consuming the entire memory. I want to understand whether there is memory pressure or not. Usually SQL server will remove old pages from the memory (that it ...
0
votes
1answer
35 views

Why insert statements get slow when having too many indexes?

I am having a slowness in my insert statements when working with MongoDB, and when I did a quick investigation, I though this might happened because the table has 11 indexes. Plus, I know that when I ...
0
votes
0answers
45 views

Oracle multithreaded JDBC bulk select high-performance data export

The goal of our solution is to export as fast as possible CLOB data from an events table. The concept is to logically partition the data with a partition_id column such that every thread from the ...
0
votes
1answer
30 views

How does proportional fill algorithm help with tempdb performance once the files are full?

Having about 8 tempdb data files is a good practice because of large number of object creation and deletions that happen in the tempdb. So sql server can use the data files in parallel. Given this ...
0
votes
1answer
37 views

Is this horrendously low row estimate causing a slow insert?

reference: https://www.brentozar.com/pastetheplan/?id=Bklc3fGNt I have a clustered index on both #LargerRange and #SmallerRange. These indexes contain 5 key columns: Name, InOrOut, LocCode, VisitID, ...
0
votes
0answers
44 views

Working with index-key sizes which are close to the DBMS's index-key size limit

I might need to work with relatively large (non-clustered) index keys (between 1KB to 4KB). I found that Spanner and DB2 support 8KB index-key size, MSSQL allows 1.7KB and Postgress around 2.6KB (1/3 ...
1
vote
1answer
36 views

What are the reasons to have an API back end if everything required can be handled securely by direct Database connection?

Today's database allow strict user restrictions, such as readonly on specific tables. If a client application is very simple and only needs to read a database, is it a better idea to allow the client ...
0
votes
0answers
53 views

How to change IOPS on SQL DB server

Can anyone advise please? An application I'm supporting requires IOPS on DB server to be increased during a process, in order to improve throughput and then put back to original value after the ...
0
votes
1answer
43 views

Automatically materialize CTE? (instead of temporary table CREATE and INSERT)

I’m a novice trying to learn about query optimization and temporary tables in Oracle. The examples I’ve seen for Oracle temporary tables involve CREATE TABLE and INSERT INTO statements. CREATE ...
0
votes
3answers
44 views

Is it possible to perform two checks in the same query in one traversal?

I'm working on a forum project using PHP/Laravel and MySql where I have 3 models : User, Thread, Like (users, threads, likes tables). In index page, I display thread components, and each component ...
2
votes
2answers
89 views

MySql DB slow after restore until I run mysqlcheck

I frequently have to restore our website's production DB to our test server. When trying to access the restored copy of production, the performance is AWFUL. For example, one particularly heavy web ...
0
votes
1answer
38 views

Why does mysql join strategy depend on this value?

I have a simple select, involving a join: SELECT `user`.*, `group`.`alias` AS `group_alias` FROM `user` INNER JOIN `group` ON `user`.`group_id` = `group`.`id` WHERE `user`.`state` = 1 AND `group`...
0
votes
1answer
13 views

convert RDS csvlog into pgreplay-go compatible for replaying

Before upgrading AWS RDS PostgreSQL version at production, I want to replay 1-hour recorded logs at the test instance. Unfortunately pgreplay is single-threaded application, limited by single CPU ...
0
votes
0answers
38 views

script to detect alwayson redo latency in SQL Server

I have 2 node SQL 2019 AlwaysON setup where i regularly see latency for redo thread in AG dashboard when applying the redo transactions on secondary. we have identified that this redo thread delay in ...
0
votes
1answer
108 views

Why is read performance using a covering index better than using a primary key?

I have this table with just Id and Value columns, and this is joined to to get the value in a common query. The Id is the (clustered) primary key and the value column is read in the select. If I add a ...
1
vote
3answers
92 views

Partitioning and existing Indexes

I have a table that consumes almost 900 GB of space and has lots of historical data which are mostly irrelevant. Since performing bulk DELETE is going to cause a lot of heavy-lifting, I'm trying to ...
1
vote
1answer
65 views

The expected read speed of SQL*PLUS

The issue is many-fold but it boils down to this simple scenario: I have a Oracle Database Version 19.4.0.0.0 and SQL*PLUS Version 21.3.0.0.0 running on the same Red Hat Linux (I actually tried both ...
0
votes
1answer
55 views

What is the best way to check performance for table vs view?

Could you please advice what is the best solution for compare performance of query for Table vs View. I mean have have two databases on same server (SQL Server 2016). Both include same data but in ...
0
votes
1answer
33 views

How to reuse list of columns in multiple statements

I have a list of columns for each entity I want to get from a SELECT/UPDATE/INSERT statement. I want to reuse the same list instead of having a copy in each query. Example: SELECT col1, col2 FROM ...
0
votes
1answer
30 views

Joining historical data in postgres

I have a table of data (omitted below), a table of state transitions for that data, and a table of snapshots of that data. CREATE TABLE thing_event (thing int, created timestamp, old_state bool, ...
0
votes
1answer
44 views

Could hardware RAM disks (with battery) have any benefit in database servers?

For database servers with many writes, I am wondering why can't these ramdisk/ramdrive be a solution to avoid wearing out SSDs and to avoid losing data if too much is kept in normal ram. Can these ...
0
votes
1answer
31 views

Sybase ASE 15.7 procedure cache fail on INSERT

Sybase executes SELECT query with 50k rows in 10 seconds. If execute INSERT INTO ... SELECT ... FROM ... construction it executes 2 minutes But if we increase retrieved rows number in 2 times - 100k ...
0
votes
1answer
52 views

SQL Server high SQLCONNECTIONPOOL consumption

SQL Server Version : SQL Server 2017(RTM-CU21) on Linux Runing environment : kernel 4.19,Docker 18.09 Docker Image : docker pull mcr.microsoft.com/mssql/server:2017-CU21-ubuntu-16.04 Problem ...
1
vote
2answers
86 views

I am burning trough NVME SSDs, 3x 60GB MySQL databases (web crawler)

I made a web crawler that is split on 3 computers, each of them makes many db queries, I think around 200 3000-4000 queries per second (each server) non-stop, with short spikes to 12000-14000. I am ...
6
votes
2answers
513 views

Is there a performance or storage space advantage to lowering time/timestamp precision in PostgreSQL?

PostgreSQL allows time/timestamp to specify a precision: time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds ...
1
vote
1answer
38 views

Identify the line of a procedure of a spid/session

In SQL Server 2017, how can I identify or pinpoint at which line of a stored procedure a spid (session) is currently at and what the execution plan of the current statement looks like?
0
votes
2answers
192 views

Production database so much slower than development database?

I am running a script on both servers, one in my docker local developement system, and one on production. However, the same script is running much slower on production than on development. I am using ...
1
vote
0answers
82 views

How to optimize for performance when querying millions of rows in real-time analytics?

Suppose I have a simple table called events like this: timestamp event_id action_count apr 5 01:05:00 foo 10 apr 5 01:04:15 bar 20 apr 5 01:06:10 baz 200 apr 5 01:04:30 baz 5 apr 5 01:03:00 baz ...
0
votes
1answer
55 views

Get current execution metrics from sys.dm_exec_sessions

I've got a pretty complex batch processing procedure that takes hours to run on real data. I've found that some CPU and I/O counters are stored in the master.sys.dm_exec_sessions table, and so thought ...
2
votes
0answers
43 views

Troubleshoting slow running queries/jobs in Azure Databricks

I have Azure Databricks workspace with cluster configured to run Standard 6.4 runtime (Apache Spark 2.4.5, Scala 2.11). Cluster uses shared metastore (Azure MySQL). I'm trying to figure out possible ...
0
votes
2answers
46 views

insert on table being unnaturally slow

I have an "Basic" pricing tier Azure SQL database with a table of 7 columns, an ID column of int that is the clustered index and primary key, one datetime2(0) column, 3 varchar(100) columns ...
0
votes
1answer
26 views

Cloning some records and updating one field at the same time

The need is to clone some records (with a field update at the same time). I found three ways but not sure which is the best. solution 1, LOAD IN APPLICATION: Fetch the records in the application, ...
2
votes
0answers
79 views

Multiple EXISTS statements make query exponentially slow [closed]

I have a query with multiple EXISTS statements and I don't understand why it becomes exponentially slow after about 7 EXISTS. SELECT `products`.`id` FROM `products` WHERE EXISTS(SELECT `vehicles`.`id` ...
0
votes
0answers
26 views

Why MongoDB Shard becomes slow in data insertion over larger amount data inserted?

General We generate about 10GB of records each day. Each record is composed of four columns: id,A,B,C. The column id must be unique but will be never queried. Columns A,B,C must be indexed (or some ...
0
votes
0answers
56 views

SQL Server table design handling ubiquitous information

I have a doubt about table desgin in SQL Server 2019. Usually i try to map tables with each entity in my "conceptual model", so as usual i do that and create tables for user, articles, ...
2
votes
1answer
78 views

LATCH_EX and CX_PACKETS for a stored proc seen when timing out

I am trying to troubleshoot an issue with below things noticed:- There is this stored proc which gets timed out on some random days while other days or after retry may complete faster. Time out ...

1
2 3 4 5
124