Questions tagged [performance-tuning]
Improving the performance characteristics of a database application or system.
505
questions
0
votes
2answers
111 views
An error occurred while updating the entries. See the inner exception for details.; [INNER] Execution Timeout Expired [closed]
One of our production SQL Servers database is performing slow. On the application side, they are experiencing this error:
An error occurred while updating the entries. See the inner exception for ...
0
votes
2answers
16 views
How to check the source of analysis task in Oracle Performance Analyzer?
We have provided sql tuning set as input to the analysis task of performance analyzer
variable l_task_id VARCHAR2(64);
exec :l_task_id:=dbms_sqlpa.create_analysis_task(
sqlset_name => '<...
-1
votes
1answer
47 views
Oracle Execution Plan - Outline Question
I'm spending the last few days trying to learn some tips/tricks on tuning an Oracle query being run in Crystal Report. Obviously it has some performance issues. I suspect there are some deficiencies ...
0
votes
2answers
50 views
In PostgreSQL, how can I make Bitmap Index Scan parallelized?
I want to improve the performance of an SQL statement.
I am on version 13.
Here are the sample codes and the query I am interested in.
drop table ords;
CREATE TABLE ords (
ORD_ID INT NOT NULL,
CUST_ID ...
1
vote
0answers
46 views
Query performance in describing a race's relative finishing positions
Is there a faster, and more parsimonious way to do this?
For each race (i.e. 1, 2, and 3), there are many race entries X (horses), some details including it's finishing position, and in the same row ...
0
votes
1answer
31 views
Sql Server consuming lot of memory Creating Bottleneck for my web application Hosted in the same server
HI I have a server with 64 GB Ram and I have assigned around 52GB to the SQl Server itself. However Even this has effected the performance of the server and the web application hosted in the same ...
1
vote
3answers
138 views
Should the T and L phase of a well tuned ETL report process take the same amount of time regardless of the time bucket width and data size?
For an ETL reporting system, is it normal for the total execution time for a pull of 15 minutes with no data to be similar to a 24 hour pull with data?
I had expected that the total time for ETL when ...
0
votes
0answers
11 views
SSRS High IOPS and Slowdowns
Please let me preface by saying that I understand poorly formed reports can cause poor SSRS performance and I have people looking at that issue separately.
I have an SSRS server that is seeing ...
2
votes
2answers
127 views
MySQL 8.0 Tuning Advice Needed - Slow Locking & Various Issues
I recently upgraded my database to MySQL 8.0 and I'm using phpmyadmin to get status information. I also upgraded my virtual server to 4GB RAM and 2 vCPUs, intended to be a MySQL-only server for my ...
-2
votes
1answer
35 views
Database scoped configuration applicable to Secondary
I saw some new database scoped configurations like MAXDOP and legacy cardinality for secondary options and have few questions or doubts i am not able to understand
MAXDOP-
How do i setup MAXDOP, say ...
1
vote
0answers
46 views
Why performance of this query deteriorates over time?
I have a 1 million row table in oracle 19c. I am running this query. Table has an index on ID column, stats are up to date.
select <columns>
from (select <columns> from <tableA> ...
6
votes
5answers
2k views
Is OPTION (RECOMPILE) used in production environments?
Is OPTION (RECOMPILE) used in production?
This option seems to get a lot of bad press. Is it deserved?
I have a DBA who, so far, is not a fan of the idea of OPTION (RECOMPILE) within the meat of ...
2
votes
1answer
50 views
Do SQL OS Background Tasks Yield to Normal Tasks?
I have a SQL Server 2019 server that is running at about 22% CPU. I look at what's running and I see that about half of that 22% is coming from background tasks (i.e. the status is "background&...
0
votes
1answer
32 views
Why turning off condition_fanout_filter speed up query
bin/mysqld_safe --user=mysql --optimizer-switch=condition_fanout_filter=off --innodb-flush-method=O_DIRECT &
Query (from TPC-H query-19):
SELECT Sum(l_extendedprice * ( 1 - l_discount )) AS ...
1
vote
1answer
56 views
Stale Statistics and View performance on SQL Server 2016
I have a report query which behaves abnormally, but rebuilding the indexes/refreshing statistics seems to sort it momentarily.
So my doubt essentially is -
What could be the reason that even with ...
0
votes
1answer
39 views
Tuning query - reduce temporary and/or filesort use with join
My query runs in 60-90 seconds, I would like to see how can I improve it.
One table contain partitions and have tens of million rows each partition.
The other table contain 5-8 million rows between ...
0
votes
0answers
85 views
Improving the run time of a DW query
I have a user running query which is sometimes under 10 seconds while other time going over 30 minutes.
This is not a store proc , he is doing it manually from his machine to get data and later after ...
1
vote
0answers
56 views
Help me tune Mysql instance to support more users
I'm currently supporting, A Moodle 3.10 instance, running MySQL 8.0.20. This is running through an Azure Provider as a Standard DS11(2 vcpus, 14 GiB memory). Running as a LAMP stack server.
Currently, ...
0
votes
1answer
55 views
Execution plan push sort operator to avoid too many PK lookups
For a query like this:
select top 40 ColumnNotInIdexCausingPKLookup from table
where ColumnIncludedInIndex=A
order by ColumnIncludedInIndex,ColumnIncludedInIndex,ColumnIncludedInIndex
Any way to ...
7
votes
2answers
1k views
Do I understand indexes correctly?
I'm trying to wrap my head around the difference between various kinds of database indexes. I have created a little example for myself and I don't know whether I'm understanding everything correctly.
...
0
votes
0answers
248 views
Query performance drop after upgrade to MariaDB 10.5 (group_concat + join)
We have upgrade our database servers and are experiencing some performance issue.
Some complex queries, with lots of join and group_concat are 30% to 60% slower than on our old servers.
(Same query ...
0
votes
3answers
103 views
How to fine tune a huge problematic Heap Table? [closed]
Problem:
A huge table is causing to a critical performance issue on a very critically important production database (MS SQL Server 2016 SP1 Standard Edition).
Facts about the problematic Table :
Heap ...
1
vote
2answers
120 views
Help with tuning this UPDATE Query or possible re-write
I have below SQL query which runs on avg. 40 secs and need some guidance where can i improve it or possibly re-write:
Declare @status_D INT,
Declare @StatusIP INT,
Declare @Status_F INT,...
2
votes
0answers
46 views
SQL Server sys.query_store_runtime_stats max_dop field reporting wrong values?
I have been trying to use the Query Store in SQL Server as an easy way to identify queries running in parallel and the degree of parallelism. However, when I look at the max_dop field in sys....
-1
votes
2answers
60 views
Index usage across databases [closed]
I would like counseling as to what is the best way to get performance in the following scenario. This is on a SQL2016 server.
I have a newly created central database where data is replicated to from ...
0
votes
2answers
152 views
Performance benefits of multiple .ndf files
Is there a benefit to having multiple mdfs with SSDs? I assume that with multiple cores there is, since there is less contention?
1
vote
1answer
84 views
Postgresql Query estimated time vs actual time difference
We have a hosted Postgres 11 DB server to fetch medium sized dataset and ingest into another application.
"States" table is the largest one in the following query, which will fetch ~1.5 ...
1
vote
2answers
179 views
Best Way to Optimize MySQL / MariaDB for 250M Row Table on a 64GB 16 core Machine [closed]
I have an extremely large dataset (250M Rows in one table, 170G on disk) on a very powerful desktop computer that I'm utilizing to clean-up the data and do analysis on it.
As part of this analysis:
I ...
0
votes
2answers
43 views
In what range can you expect a cloud based Sql Server data reading in mb/s? [closed]
Say everything is optimized, queries are simple, indexes are good, etc.
Should a SQL (Azure) database where you spend about 150 to 300 euros per month easily handle data reading with about 250-500 mb ...
-1
votes
1answer
51 views
MYSQL optimization for Magento 2
Im running my magento 2 project on 5 core 10gb ram VM.
I did some benchmarks and i see that my DB queries take quite some time to execute. Traffic on this site is almost zero. Mysql is running on 5.7....
8
votes
2answers
224 views
Sqlite: Finding the next or previous element in a table consisting of integer tuples
I have a sqlite table called tuples defined like
create table tuples
(
a INTEGER not null,
b INTEGER not null,
c INTEGER not null,
d INTEGER not null,
primary key (a, b, c, d)
) ...
1
vote
1answer
72 views
Optimisation select with large IN and millions of records in result
I work with a table which consists of 1.5B records.
DB: RDS, PostgreSQL 12.4, 16GB RAM, 4vCPU
Schema:
CREATE TABLE public.trip (
id bigint NOT NULL,
cell_to character varying NOT NULL,
...
1
vote
2answers
125 views
Configure MySQL to minimize file locks
*** EDIT ***
8GB RAM
SHOW VARIABLES: https://pastebin.com/ANAiq1E2
SHOW GLOBAL STATUS: https://pastebin.com/pwWKRiie
*** END EDIT ***
To start off, I'm not a DBA, so I may have overlooked something ...
0
votes
1answer
50 views
Query data via username - pair - best performance
RDBMS: MariaDB 10.3
Let's say you have a table which stores specific information (info_1, info_2, etc.) according to a given pair of usernames; like so:
+----------+----------+----------+----------+---...
0
votes
0answers
23 views
Semantics and significance of a one time query execution, delete with Like vs =
I was required to delete the values of a certain table XX_choice_value, but this will be a delete run only once in two different stages. Raw query execution and nothing more, and then in my Pull-...
0
votes
2answers
174 views
Investigating RDS MySQL 8.0.16 stalls on write-heavy workload
I'm trying to track down some sporadic and difficult-to-reproduce issues with a MySQL 8.0.16 RDS instance connected to a web application with a write-heavy workload on a couple tables with large JSON ...
0
votes
1answer
29 views
set autotrace on is just estimate plan?
I've made a test:
SQL> set autotrace on; << autotrace on
SQL> select * from danilo.testepart where nome = 'danilo3';
ID NOME
---------- -------------------------------------------...
0
votes
1answer
23 views
How to set up indexes for dynamic filtering queries in Oracle
Given I have SOURCE_EVENT table in Oracle:
create table SOURCE_EVENT
(
ID NUMBER(19) not null
constraint PK_SOURCE_EVENT
primary ...
0
votes
2answers
56 views
How can i tune below proc either by changing query or adding index
I am having issues with one of the proc running very slow approx an hour:
Please pardon as I have to write entire code out of system to paste here. I will be editng the question with more details on ...
3
votes
4answers
133 views
SQL Server memory
It's regarding the SQL Server memory. When the server is running out of memory, we recommend adding additional memory to the hardware to overcome CPU utilization issues.
But before proceeding to the ...
0
votes
1answer
476 views
InfluxDB with Zabbix or Telefraf?
I'm considering the use of InfluxDB to store thousands of measurements per second from routers in a network. Each measurement is lightweight (between 10-20 bytes). Now, I'm wondering what is the ...
1
vote
0answers
125 views
Anomalous performance of mass INSERT in Oracle
Can you please help me diagnose, explain, and possibly solve the
problem of unstable peformance of mass insertions in Oracle?
I need periodically to load large amounts of data into an Oracle
database. ...
0
votes
2answers
160 views
PAGEIOLATCH wait and Page Life Expectancy very low
Training for SQL Server, has following scenario:
SQL Server on Azure VM, DS-series machine, it has 32 GB of RAM
Server has below performance conditions:
- very high PAGELATCH_IO waits
- average Page ...
0
votes
2answers
45 views
Are there better alternatives rathet than joining a table twice for having one record for a specific column in the output?
I have two tables with below structures:
create table TABLE_LC
( lc_num NUMBER,
brn_code NUMBER,
brn_role NUMBER)
create table BRN
( brn_code NUMBER,
brn_name VARCHAR2(50))
Some example data ...
0
votes
1answer
1k views
PostgreSQL LWLock: lock_manager issue
In our application we need to insert a lot of historical data that will be consumed later by some mobile devices.
These data is organized by a numeric incremental attribute that the device will use to ...
1
vote
1answer
102 views
how to speed up / fix this Ubuntu Apache MySQL Server Configuration?
I got new to the project, my predecessor is far gone and I'm left on my own.
Currently the MySQL-database seems super slow and after checking some variables and values I cannot identify the problem on ...
0
votes
0answers
33 views
Slow on searching duplicate records in millions of records
I've below three tables
CREATE TABLE [dbo].[RegisteredUser] (
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[UserCode] char(9) NOT NULL,
[Email] [varbinary](5000) NULL,
...
0
votes
0answers
20 views
Sampled rows is very less for auto update stats in SQL Server 2016 [duplicate]
I have SQL Server 2016. One of the table is having 9.24 million records. One of the stats (no filter on stats) on it is getting auto updated by SQL Server with sampling rate of 1 % (120023 rows).
As ...
0
votes
0answers
13 views
Postgres tracking count of recent items in count table or by selecting count of recent records from history table
Which would generally be more efficient, or does it not matter?
Option 1 is incrementing a count in a table that stores counts by the unique id, and resetting the count to zero when enough time has ...
-2
votes
1answer
42 views
What are some good SQL Server Performance Metrics from System Views?
I am working on a project where I need to collect as many SQL performance metrics as possible, all from system views.
I currently have collected, and in parenthesis the system view from which I have ...
