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.
1,005
questions with no upvoted or accepted answers
2
votes
0
answers
102
views
Need Clarification of SQL Server Blocking-Process lastbatchstarted
My understanding is that a SQL Server batch consists of 1 or more statements that will be executed sequentially. The last batch time should be the time when the batch was started. Also, multiple ...
- 951
2
votes
0
answers
1k
views
Repair running too slow
Last week I asked a question that was answered below... I need to improve the question I asked earlier (in quotes below) and give a little more context. The answer below did help the repair ...
- 21
2
votes
0
answers
118
views
What can I do to determine why my query occasionally hangs (currently hung for over 3 days)
I have a query in my data warehouse load that occasionally hangs.
Traditionally, it takes only a few minutes to run, however sometimes it can take a few hours.
I'm unsure whether this is the query ...
- 213
2
votes
0
answers
564
views
What to do when you find slow running query?
I have two databases. One with 10.000 rows and one with 10 million rows.
I have a load test and I am running it towards a rest api endpoint. This endpoint does a query to database and average ...
- 141
2
votes
0
answers
106
views
Performance on this query
We have the query below in our software to get a set of results which can be used to calculate the score of a location. As it stands it is too slow to retrieve the results in the timely fashion we ...
- 129
2
votes
0
answers
681
views
Optimal locking - append-only store
I'm designing an internal event store library based off SQL Server. One challenge I had to solve is to allow concurrent writes while still being able to generate a reliable Log Sequence Number (LSN) ...
- 161
2
votes
0
answers
704
views
SSIS 14 - Buffer Memory - Good practices
I've a some packages in SSIS 14 in order to extract the data from a lot of operational databases and load into my Data Warehouse.
I've the following virtual machine:
Windows Server R2 Standard
RAM:...
- 123
2
votes
0
answers
682
views
PostgreSQL: Slow Update Query
I'm running this update script in PostgreSQL 9.6:
do $$
begin
create temporary table if not exists tmp_rutting1 (
road_id integer,
direction_id integer,
begin_km double ...
- 21
2
votes
1
answer
512
views
Index statistics are requiring constant updates due to timeouts
We have been experiencing an odd behavior in our application where various modules will begin to timeout in SQL Server 2012. Each time we stumble across this issue, we find that the statistics require ...
- 1,564
2
votes
0
answers
63
views
How much does a query of deletion costs in terms of operations when the attribute is a key or an attribute?
How much does the following query costs in terms of operations when the attribute is a key or an attribute ?
DELETE
FROM R
WHERE ATTRIBUTE = 50
We assume we have R(K,A,B,other) a relation with ...
2
votes
0
answers
879
views
Postgresql 9.6 autovacuum blocks all update operations
I have a table with following definition:
CREATE TABLE public.json_stream_packet_filter
(
stream_packet_id bigint NOT NULL,
filter_data jsonb,
CONSTRAINT json_stream_packet_filter_pkey PRIMARY ...
- 49
2
votes
0
answers
232
views
In memory table query performance
I am trying to optimize an sql query by using in memory tables. After I had ran the query some times I ran the Transaction Performance Analysis Overview report of SQL Server 2016 which suggested that ...
- 21
2
votes
0
answers
88
views
TSQL: how to define efficient index on partitioned panel-data table?
The underlying table piddt is a panel-data structure containing three fields: (pid, dt, attribs). The defined primary key is (pid, dt).
The number of rows is around 6 Billion, and the table is ...
- 121
2
votes
0
answers
2k
views
PostgreSQL: Which hardware (number of cores, RAM, storage type, storage size) and what number of hosts are needed?
There is a Log DB. As I understand, there are two constraints which influence the answer to my question: space needed to store the database, number of reads-writes/sec needed.
There are 72 M records ...
- 21
2
votes
0
answers
80
views
TVF Poor Performance with Linked Query
I have an inline TVF that take much longer to run than should be necessary. The issue looks to be the Linked Server reference.
The Linked Server is SQL Server 2012. The server with the TVF is SQL ...
- 1,394
2
votes
1
answer
141
views
Disk I/O increased when changing RBR to MIXED
I'm experiencing some replication issues. I have a Master-Slave replication with mysql; the slave had binlog-format = ROW, Last sunday (3 days ago) I changed the format to MIXED (this is master too; ...
- 480
2
votes
0
answers
5k
views
Troubleshooting Preemptive_Xe_Dispatcher wait type
While looking for a reason behind slowness of the database server I accidentaly came across Preemptive_Xe_Dispatcher wait.
Could you please suggest how this type of wait should be troubleshooted? ...
- 498
2
votes
0
answers
44
views
Postgres query changes and degrades radically
I have a hibernate-based query that is not performing well. It is running a query that joins three tables - asset, asset_type, and asset_status - and sorts the results, and running the query on a ...
- 21
2
votes
0
answers
512
views
Modify slow query log rotation on Amazon RDS instance
Our Amazon RDS instance rotates slow logs every hour, and keeps only the last 20 of them.
This is not optimal for monitoring as it prevents us from seeing any slow query that might have been ...
- 1,314
2
votes
0
answers
2k
views
Avoiding array of foreign keys but maintaining its performance?
Consider a many-to-many relationship between sources and tags, modeled in the conventional relational manner:
CREATE TABLE sources (ix INTEGER PRIMARY KEY);
CREATE TABLE tags (ix INTEGER ...
- 121
2
votes
0
answers
391
views
SQL Server Backup/Restore IO performance on Azure VM
We are currently experiencing an issue where the IO performance of our SQL Server running on an Azure VM isn't quite up to snuff.
We are running a Azure VM (Standard DS2). This one has a 127 GB SSD C:...
- 205
2
votes
1
answer
495
views
Indexing for an ascending date value
Let's say you have a table of invoices
CREATE TABLE Invoice
( InvoiceID int IDENTITY(1,1) PRIMARY KEY,
InvoiceDate DateTime CONSTRAINT [df_invoicedate] DEFAULT GETDATE(),
....
)
The invoice ...
- 131
2
votes
0
answers
2k
views
Same query on two identical databases take different time
I have taken a backup of a database from a development environment in order to tune a query. When running the original query on the dev environment people started complaining that the database ...
- 943
2
votes
0
answers
90
views
Why am I getting drastically different performance results between staging vs. prod for a table?
I have two amazon rds postgres instances, one is staging, one is production...
In staging, a table has 21 million rows, and in production that same table has 29 million rows.
If I do select count (*)...
- 245
2
votes
0
answers
68
views
Converting a looped INSERT.. ON CONFLICT to a wCTE in postgres
I have an events table:
Column | Type | Modifiers
---------------------+-----------------------------+---------------
ts | timestamp without ...
- 21
2
votes
0
answers
221
views
postgresql 9.2 hash join issue
This query is a bottleneck in one of my views.How should I reduce my running time production it seems to be a CPU intensive query rather memory intensive query it is taking too much time.
When should ...
- 115
2
votes
0
answers
224
views
Procedure runs slower over time; fixed by cache clear or reboot
We are experiencing a strange issue where an application which was fixed by creating missing index requests, updating stale stats, and removing lookups and other scans, was running fine once fixed, ...
- 2,169
2
votes
0
answers
124
views
Do Large Hash Joins Contribute to Stolen Pages?
On a SQL Server 2014 SP1 Enterprise Instance, I'm performing an outer join between two partitioned tables on a numeric(16,0) + char(1) composite key.
100 GB is the max server memory setting on a 128 ...
- 1,933
2
votes
0
answers
423
views
How does the use of derived tables speed up sorting?
In the article 45 Ways To Speed Up Firebird Database, it states that
Use derived tables to optimize SELECT with ORDER BY/GROUP BY
Another way to optimize SQL query with sorting is to use ...
- 121
2
votes
0
answers
109
views
Loads of parallel inserts (OLTP) into heap table - Operation stops every now and then for 2-3 seconds
We got an analytics database and as new events are coming in we are adding them into our tempdb. From there we have backend processors that pick up the events and merge them into the right tables, i.e....
- 21
2
votes
0
answers
118
views
Advice on how to improve this stored procedure
I have 2 tables:
ReportItem
id (int, primary key clustered index)
report_item (nvarchar(100))
flow_name (nvarchar(50))
source (nvarchar(50))
Tech_Details
id (int, primary key clustered index)
...
- 21
2
votes
1
answer
76
views
Online text based game design - Am I linking the databases correctly?
I'm trying to recreate an old game that I used to play that went offline quite some time ago. I'm doing this as a hobby project for myself with the hope that maybe a few hundred people might enjoy it ...
- 21
2
votes
0
answers
250
views
Postgresql Filter Slows Query
I've got a query like this:
SELECT count(*)
FROM acs2014_5yr.geoheader as g
JOIN tractcache as c ON (c.tract_id = g.state||g.county||g.tract)
JOIN tiger2014.tract AS t ON (t.geoid = c.tract_id)
...
- 121
2
votes
0
answers
84
views
Order by index unexpectedly slowing query down
I have two tables: houses has ~415,000 records, and account_houses has ~525,000 records. I've changed the table names for security reasons, but basically I am using this query:
SELECT "houses".* FROM ...
- 121
2
votes
0
answers
471
views
Basic PostgreSQL 9.x Performance Optimization Strategies for Reads/Analysis
Postgres comes, out of the box, configured as a general purpose database for both OLTP and OLAP types of loads. My need is to do use Postgres, in this case, purely as an OLAP running aggregate queries ...
- 749
2
votes
0
answers
183
views
where to add a column with low cardinality, decimal type into an index?
I got a question regarding the best way to put a bad column for an index in an index, so that I can satisfy my query below, and similar others that follow the same pattern.
on the query below,
all ...
- 15.8k
2
votes
1
answer
310
views
How to influence the planner on Postgresql
I have the following:
a complex query, 'match', that returns about 200 rows (ids)
a huge table, 'usernames', indexed on id that maps them to usernames
When I try to left join the query (1) with the ...
user88331
2
votes
0
answers
116
views
Postgres query running faster, when running on more data
I've encountered a strange behavior with a Postgres query.
In general, the query is built as described here:
Select
<group by fields>
<aggregation fields>
from
(select *, <some ...
- 123
2
votes
0
answers
727
views
Adding a Mongo Shard decreases the insert performance
For a mobile application which will hold quite some documents, we've chosen Mongo as a database.
Let me explain the situation first:
Every day, I have a set of documents that needs to be inserted, ...
- 121
2
votes
0
answers
99
views
Read READ COMMITED transaction advantage in MySQL, high performance object model
I am writing high performance object model with READ COMMITED locking.
My question is if there is any reason to starting READ ONLY transaction for read only operations vs. standard SELECT not ...
- 398
2
votes
0
answers
277
views
Disable/Remove Table Index for INSERT Performance
I am doing a database maintenance where I have to add new fields to a table with over 10GB of data about (20 million rows). To solve that I renamed tableA to be old_tableA and created a new table ...
- 153
2
votes
0
answers
419
views
Insert performance
I am having trouble with Posgresql insert performance. There are 2 tables, documents and words, where documents is essentially a mapping from an external path to a surrogate id, which is foreign ...
- 121
2
votes
0
answers
84
views
PostgreSQL: Improve system performance, KNN modification with GiST Index
In my PostgreSQL 9.5beta2, Postgis 2.2.0 compiled by Visual C++ build 1800, 64-bit I am running a Nearest-Neighbor-Query with the additional complication of unique assignment. I am running this on i5-...
- 21
2
votes
0
answers
1k
views
PostgreSQL long transaction on a table performance
We have a long processing pipeline running on PostgreSQL 9.0.
Is it faster or slower to perform many operations on a single table within one transaction, versus doing auto-commit for each operation?
...
- 115
2
votes
1
answer
103
views
UPDATE on column referenced in sub-query
We have a query that updates a column for all rows matching a condition which involves a subselect on said column, like the following:
update CE_WORKSET_READ_ENTRY workset
set workSet.MARK = 0,
...
- 256
2
votes
0
answers
8k
views
Mongodb Huge Number of Open Connections and Write Locks
We are seeing around 800 open connections to our Mongodb. To clarify our issue:
We have 5 different clients who write the data into Mongodb. Each of these clients - write four different type of ...
- 133
2
votes
1
answer
904
views
Implications of many materialized views in Postgres?
I'm working on a system that includes a scheduling component, which has support for recurring events. After reading many, many posts on storing recurring events, it was suggested that they be ...
- 199
2
votes
0
answers
2k
views
Postgresql delete taking lots of CPU and time
I have the following SQL Query for deleting rows from my table. The query takes a long time and puts a high load on my server.
delete from message_log where from_id = ? OR to_id = ?
My table Schema
...
- 213
2
votes
0
answers
148
views
Query that can benefit from index, cluster or hash-cluster
I'm trying to find a equi-join query that shows a decent performance bump when I use an index, cluster or hash-cluster structure on my data. I initially need to run the query on unstructured data ...
- 273
2
votes
0
answers
703
views
PostgreSQL count() works extremely slow on replication slave
I'm using postgres replication. I have one master and 2 slaves.
I have table first with 1 500 000 rows. If I perform select count(*) from first on master, it takes less than a second, but on both ...
- 143