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.
6,309
questions
1
vote
2
answers
395
views
What is most efficient design pattern for a sql database containing billions of rows per user in a single table?
I work on a relatively large system where have started to run into performance problems scaling for multiple users.
The system is a .NET application, so query's are written using an ORM (entity ...
- 13
1
vote
1
answer
3k
views
Postgres' conditional trigger: performance consideration
In my DB i need to make conditional trigger, and i stumbled upon this article. Discussed in the article, there are 2 options to implement the conditions:
Option A: condition resides in the trigger ...
- 189
2
votes
0
answers
101
views
Huge Difference in IO and CPU for same query
I saw that same query got more than two execution plans. Query is included inside procedure which runs once a day every day. All tables in query got from 2000 to 50000 additional records every day. I ...
- 261
1
vote
1
answer
640
views
Split one big and often updated table into two for performance
Let's assume I have one big relation for my web application and analogical java object (JPA Hibernate is used for mapping). This is main relation, which consists of over 20 columns, and is heavily ...
- 15
7
votes
2
answers
480
views
Is the Query Store's Overall Resource Consumption report telling me my database is doing terribly or are the numbers just whacked?
Recently we upgraded one of our production servers from SQL Server 2016 to SQL Server 2019 (CU 15).
This was a great opportunity for me to enable Query Store on our main application database.
It's ...
- 31.9k
0
votes
1
answer
560
views
Is there any way to let Postgres know it can filter results and then calculate aggregations?
Given a table A with 10 million records and another table B with child items (one-to-many), I have to provide a consumable view with totals from several fields in B grouped by the FK of A.
None of the ...
- 1
2
votes
1
answer
162
views
How to performantly query using a function applied to an MySQL auto-increment column?
I have a users table in MySQL 8 with an auto-increment id as primary key: CREATE TABLE users (id int NOT NULL AUTO_INCREMENT, primary key (id) ).
I have a use case for sorting the users into 1000 ...
- 141
2
votes
2
answers
85
views
Allocation scans in heaps and indexes
I'm trying to comprehend the difference between an allocation scan in a heap and in a B-tree, performance-wise.
Let's say, that we have an Orders table, with a clustered index defined on the OrderDate ...
- 43
0
votes
1
answer
316
views
drop and re add an index on innodb table causes slow performance
We just made a code and db update, that involves a table on production.
This INNODB table was "altered" by adding a new index and dropped the old one.
The new is made by 3 params and the old ...
- 101
-2
votes
2
answers
49
views
Join table performance
This question is more about performance.
Assume that there are two tables one with millions of records and one with some thousands of records in a DB like MySQL. By this example, I am trying to show a ...
- 129
0
votes
0
answers
197
views
Order by id is slow
So I've read the same issues here that people experience, I could find at least a few topics talking about how ORDER BY is slow.
However, it looks like I have all the indexes necessary. But still with ...
- 101
0
votes
1
answer
43
views
what does the number in the "A_ROWS" column of the execution plan shows for operation "Index scan"?
I have a query as you can see below :
select /*+gather_plan_statistics*/
*
from mi_dimcustomer t
where t.CUSTOMER_NUM = 321937
There's a Unique Index (IDX1_DIMCUSTOMER) on Customer_Num column of ...
- 1,402
1
vote
1
answer
610
views
In Postgresql, can I create a column of a one-dimension array type with foreign key constraint? If not, how to simulate it?
In essence I have to design todo table, and an item table. I need to preserve item order, as well as allowing client to rearrange the order. If postgres allow an array of foreign key that would be ...
- 15
2
votes
3
answers
1k
views
Constant rebuilding indexes to fix performance - why?
We have a database server (2016 SQL Server), that we have added a step of 'rebuilding indexes' to the deployment process.
In decades of working with MS SQL Server at a many companies, I've never ONCE ...
- 153
-2
votes
1
answer
383
views
LEFT JOIN Performance Issue
I have a view, which has a column that comes from query
Without this query my count(*) against that view executes in less than 2 seconds, whereas it takes 2 days for the view with above extra column ...
1
vote
0
answers
170
views
How to prove Unique index could be better than non Unique index by the execution plan [closed]
I know Unique index will be faster than non Unique index theoretically if data is unique.
Because Unique index be able to provide more information let query optimizer choose more effective execution ...
- 141
4
votes
2
answers
2k
views
Azure postgresql really slow compared to VM
I work with postgresql (and Postgis) for years now on a VM with dockers, and I start to be used to tweak servers parameters and optimize request, and I never had the kind of problems I have with Azure ...
- 140
0
votes
0
answers
153
views
After a restore my transaction is slow, so I stop it, do a roll back, then re-run and it's fast...why?
On my development server I delete all data from two tables then pull fresh data from the production server via Delete From then Select Into. It's only about 100,000 rows total. On dev I run a routine ...
- 109
1
vote
2
answers
1k
views
Postgres slow query on nullable index
Postgres version: 12.7
Table definition:
column_name, data_type, is_nullable
"id", "integer", "NO"
"data",...
- 13
3
votes
2
answers
461
views
How can Temp Table creation time be reduced in terms of CPU time?
Inside a frequently executed stored procedure (sometimes up to 10-15 times per second), there are two particular statements that show up in Top 10 heaviest queries by cumulative CPU impact
These are ...
- 5,182
1
vote
1
answer
460
views
Group by query performance with composite keys
recently I joined new company and their database design contains only of composite keys and they are only created for queries.
Tables look like this (some of the column names are anomyzed): (...
- 111
0
votes
1
answer
282
views
Insert Into Table performance questions
I am writing a stored procedure, main purpose of it will be the insertion into table
Procedure will be signed by certificate, and sending email notifications
Procedure will accept user-defined table-...
- 5,182
0
votes
0
answers
11
views
Best way to store bookmarks MYSQL, PHP [duplicate]
I was recently thinking which way is the best to store bookmarks and thought i will ask this question on here:
Method 1(this is the simple and most used way):
+-------------------+
| bookmarks_users ...
- 3
0
votes
1
answer
87
views
Query optimization: LEFT JOIN makes the query incredibly slow
I have the following query with explain analyze and it's incredibly slow because of the LEFT JOIN.
EXPLAIN ANALYZE SELECT DISTINCT ON (project_name, release_version, project_id, release_id) vbc.id
...
0
votes
0
answers
102
views
Postgres - Index a materialised view with groupby and filter
I am running Postgres 14 in a k8s cluster.
I have the following table (which has ~15 million rows):
Table "public.messages"
Column | ...
- 1
0
votes
1
answer
94
views
How to identify where mysql slows down?
I have two servers.
Server A : 32 core ,128GB ram , centos 7 , ssd (Dedicated Cpu Cloud Server)
Server B : 8 core 32GB ram , centos 7 , ssd (Bare Metal Server) .
I have a issue with server A . It ...
- 23
5
votes
1
answer
739
views
How can I improve the performance of where clause with LIKE %abc?
I had below select statement that turned out very slow (41s)
SELECT DISTINCT a.Doc_Resource_ID
,a.Parent_ID
,a.Logical_Path
,a.lvl
,CAST(SUBSTRING(a.Security_Level, 21, ...
- 59
6
votes
1
answer
824
views
What happens to queries that are in progress when we change maxdop in live system and while they are executing?
We have a prod system that sometimes gets stuck and nothing we do helps but changing maxdop parameter on live system, after we change that all gets back normal. And it does not even matter if we ...
- 61
0
votes
1
answer
74
views
Is there a formula to calculate the max acceptable number of reads a query should have?
How can I measure how far a query is from the threshold of acceptable (regarding performance) based on the number of reads reported after its execution?
Example: Just to make myself clear, if we were ...
- 4,974
4
votes
3
answers
961
views
Query Store takes a very long time to load duration
I'm running Query Store to make a DB perform better.
It's been working fine until now..
When i try to load the Top durations of last day it took me 26 min to load the screen.
The SQL version im ...
- 41
1
vote
1
answer
305
views
Altering a column and cancelling the alter greatly improves SQL performance
We have a table that has roughly 5.5 million rows. We use Entity Framework and run a query on this table and get our row of interest based on 2 columns. One FK column that has an index (Type) and ...
- 11
0
votes
3
answers
297
views
How Important is the buffer pool really?
I have been doing some testing of a new (virtual) server which is to replace an existing production server. We suspect the current production server is overspec'ed and therefore are tweaking the ...
- 1,741
2
votes
3
answers
388
views
Improving performance for large volume of simultaneous reads/writes on Percona 8
A general description of the environment and the use case:
Technical:
We're running a distributed application with a single server and ~25 clients; each client is running ~75 threads.
At one ...
- 121
2
votes
1
answer
323
views
Performance difference between environments when adding column
We have 2 exact same Database Environments. The second environment contains a copy of the production database and hosts approx 11M records in the Invoice table. The goal of this environment is used to ...
- 553
1
vote
2
answers
4k
views
very high Temporary tables created on disk, how to fix them?
Hello we use nextcloud hand use as backend a mysql-db on Debian 10. I run ./mysqltuner.pl which said
Temporary tables created on disk: 99% (48K on disk / 48K total)
(I recently restart the mysql-...
- 111
3
votes
1
answer
108
views
Why is FROM VALUES massively faster than visiting a table once?
Given this setup,
CREATE TABLE t(foo)
AS SELECT 'foobar'::text;
Why is this query,
EXPLAIN (ANALYZE, TIMING OFF)
SELECT
generate_series(1, 1000000),
upper(foo)
FROM ( VALUES ('foobar') ) AS t(foo)...
- 60.4k
1
vote
1
answer
196
views
Query Store internal queries being executed thousands of times per minute
I have an SQL Server 2016 Standard production server with more 59 clients Databases.
Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64) Mar 19 2021 19:41:38 Copyright (c) ...
- 1,999
1
vote
2
answers
66
views
Execution time/cost of function in assembly
I have a query calling XmlTransform from Microsoft.MasterDataServices.DataQuality on about 1000 rows or so and it takes 30s to do the whole set (the XSL is in a fixed variable), is there any way to ...
- 6,592
8
votes
1
answer
738
views
Query time of fetching a particular, single row id by PK is extremely slow
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
SELECT *
FROM contract
WHERE contract.id = 33129;
Index Scan using contract_pkey on public.contract (cost=0.29..8.31 rows=1 width=305) (actual time=0....
- 183
0
votes
0
answers
111
views
Excessive TempDB Usage When Using Pivot + Temp Tables
I have a stored procedure that is acting quite strangely, it basically works in this way:
--Populate #tmpActualsVertical with data(in this example 84,652,788 rows)
SELECT ...
- 205
0
votes
2
answers
110
views
Understanding SQL aggregation efficiency
How would the first query compete in terms of performance against the second alternative:
Query 1:
select count(*) page_views,
count(distinct(session_id)) sessions,
(count(*) / count(distinct(...
- 53
0
votes
1
answer
865
views
Why the JOIN doesn't use index?
I have a query like this:
EXPLAIN SELECT dictionary.id, dictionary.word, lwd.grammatical_role, lwd.phonetics
FROM loghatnameh_dehkhoda dictionary
LEFT JOIN loghatnameh_words_details lwd ON lwd.word = ...
- 187
0
votes
1
answer
421
views
SQL Server select into vs. insert into performance with embedded cross apply openjson
Note: updated 11/15/2021 to address comment feedback.
It seems SQL Server 2016 has some odd little corners when it comes to performance.
Version:
Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13....
- 21
0
votes
3
answers
483
views
Does using "qb_name()" hint (query block name) in a query have significant effect on performance?
I've just become familiar with the hint "qb_name" (query block name) in Oracle and below you can see an example of using this hint in a query:
select
/*+
qb_name(main)...
- 1,402
0
votes
2
answers
259
views
Performance of implementing product images in an e-commerce store as a separate table vs. as columns of the Product table?
I am creating an e-commerce store in Django and am currently trying to decide between implementing product images as an entity of their own then linking to the product by a foreign key or putting ...
- 101
0
votes
1
answer
37
views
Does changing boot disk to SSD affects performance?
We recently changed our production database disk to SSD.
To clarify, all data paths, caching, tmp, ... directory references in the MySQL configuration point to that database disk, now SSD.
The boot ...
- 133
2
votes
1
answer
247
views
Is using a set-returning function as an intermediate step bad for performance?
Say I have two queries that do the same thing, such as filtering something out via a complicated join, but the one queries then joins it with one table, and the other query just orders it.
Can I use a ...
0
votes
1
answer
266
views
What replacement algorithm does this ALTER TABLE use
Question
I am trying to figure out what kind of replacement algorithm a command like the following would use
ALTER TABLE `catalog_category_product_index_store1` COMMENT='Catalog Category Product Index ...
- 101
1
vote
2
answers
1k
views
Different execution time for same query on different Postgres databases
I have two identical databases, development and live.
I run this query on both of them....
select count(*) FROM DeviceB where deviceID not in (SELECT distinct deviceId FROM Device)
On the dev box the ...
- 1,027
2
votes
2
answers
304
views
improve performance of large scan calculation in PostgreSQL table
I have a table flags that collects user contributions for each distinct polygon (flag_value, in this case a 'YES') during their usersession. The idea is to count how many users said YES out of the ...
- 123