New answers tagged performance
1
vote
Slow query with multiple group by
Two composite indexes might be helpful.
CREATE INDEX PAW_IDX2099075689 ON comment(USER_ID,DELETED,NEWS_ID,PICTURE_ID,TOPIC_ID);
CREATE INDEX PAW_IDX0500726826 ON comment(NEWS_ID,PICTURE_ID,TOPIC_ID,...
- 36
0
votes
DataGrip showing prints during a transaction
In DataGrip 2023.1 suggested code:
DECLARE @currChunk INT = 1;
DECLARE @numChunks INT = 20;
WHILE @currChunk < @numChunks
BEGIN
DECLARE @Msg NVARCHAR(200) = CONCAT('Processing chunk ', @...
- 164
0
votes
Large table with too many indexes stalls my app
Eitan Blumin has recently released Drop All Redundant Indexes In Every Database All At Once
It's a 300 lines of code script and you have to port it from SQL Server to PostgreSQL.
There is one less ...
- 1,353
0
votes
Design database table for property listing
Separating fields "because of performance" is not normalization. Usually having good indexes is the key.
Normalization will help you get rid of redundancy and insert/update/delete anomalies. ...
0
votes
MySQL | Measure overall query cost (performance, cpu usage, memory usage)
Be cautious of LIMIT; it makes little or no difference on these
SELECT COUNT(*) FROM tbl LIMIT 1 -- There's only one row
SELECT ... FROM tbl ORDER BY ... LIMIT 10 -- First collect all rows, ...
- 73.8k
0
votes
Use one table and a "type" column to represent two types of vote_counter or use separate table?
Do you store "blog and comment" in two different tables? If so then the last option where each table have its own votes table is preferred I think. Because blog and comment are two different ...
1
vote
Performance for query filtered by user preference
My query to db is something like this:
author_id IN ('1,5,3,7,10') OR topic_id IN('3,6,9')
Hopefully only something like that.
Double check you "IN" clause syntax - I doubt you have an ...
- 7,609
1
vote
How to properly monitor the number of PostgreSQL database connections?
The Postgres 14 release notes contain an information that suggests that the necessary information has been added to Posgres 14 (see section "E.8.3.1.7. System Views"):
Add session ...
- 172
0
votes
Performant design for updating/creating multiple rows at high frequency
Generally speaking, since we don't know the details of your implementation, you should consider this:
Establishing (opening) a connection with the database is a heavy operation, so of course you have ...
- 618
0
votes
How to improve performance on query with nested INs?
in postgres 15, you can use MERGE.
BEGIN;
CREATE TABLE challenge_progress (
"current" int,
status text,
milestone_id int
);
CREATE TABLE challenge_milestones (
milestone_id ...
- 263
0
votes
Accepted
Same MySQL query is so slower on ubuntu than Windows
The tables should be reindexed to solve the problem
ANALYZE TABLE tablename;
- 99
0
votes
Reliable database server solutions for industrial mini PCs
This may be one use case to use a cloud solution for your database.
Obviously, physical server space no longer becomes an issue in the cloud, but increased reliability and uptime will also be another ...
- 30.6k
0
votes
Accepted
Insert Statement on large MySQL table does not terminate
from my knowledge:
Check if there is enough disk space available for the database to perform the insert operation - which you said thats ok!
Check the transaction log to see if there is anything ...
- 20
2
votes
What is a common type of storage hardware for dedicated MySQL servers?
It's hard to generalize, except to say that all of the technologies you name may be used in appropriate situations.
For example, we know that NVMe has higher throughput and lower latency, but spinning ...
- 12.2k
0
votes
Improving OPENQUERY performance from Oracle to SQL Server
Linked servers are not optimised for bulk data transport. You may have achieved the best network throughput possible via this method already. It is typically not impressive.
The ORDER BY clause in ...
- 78.8k
1
vote
What is the difference between "OPTION (OPTIMIZE FOR UNKNOWN)" and "OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING'))"
For most intents and purposes, they are different ways to achieve the same thing.
There is one difference when the RECOMPILE query hint is also specified:
OPTIMIZE FOR UNKNOWN does not use the ...
- 78.8k
2
votes
Query taking more than 5 minutes to execute
I realise this is an old question, but as it is still here, I thought I would address some of the issues that to me seem to be on the surface.
I do not really have a good explanation why your query ...
- 22.4k
Top 50 recent answers are included