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,376
questions
302
votes
15
answers
193k
views
Can MySQL reasonably perform queries on billions of rows?
I am planning on storing scans from a mass spectrometer in a MySQL database and
would like to know whether storing and analyzing this amount of data is remotely
feasible. I know performance varies ...
- 2,493
207
votes
7
answers
294k
views
How can I optimize a mysqldump of a large database?
I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to ...
- 4,229
172
votes
6
answers
165k
views
Guid vs INT - Which is better as a primary key?
I've being reading around reasons to use or not Guid and int.
int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid, the only advantage I found is that it is ...
- 3,433
142
votes
5
answers
249k
views
Possible to make MySQL use more than one core?
I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer than DBA for MySQL so need some help
Setup
The servers are quite hefty with an OLAP/...
- 69.7k
137
votes
5
answers
155k
views
Measure the size of a PostgreSQL table row
I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some ...
- 1,631
129
votes
7
answers
150k
views
How to determine if an Index is required or necessary
I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now ...
- 1,393
123
votes
3
answers
59k
views
Is a composite index also good for queries on the first field?
Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index?
...
- 1,661
118
votes
3
answers
173k
views
What is faster, one big query or many small queries?
I have been working for different companies, and I have noticed that some of them prefer to have views that will join a table with all its "relatives". But then in the application sometimes, ...
- 1,281
96
votes
4
answers
20k
views
Authoritative source that <> and != are identical in performance in SQL Server
Consider this answer on SO that reassures the asker about the <> operator that:
<> is ... the same as !=.
But then a commenter pipes up and says:
It's true that they are, functionally, ...
- 4,305
96
votes
7
answers
139k
views
Very slow DELETE in PostgreSQL, workaround?
I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign ...
- 1,062
86
votes
4
answers
47k
views
Why is my query suddenly slower than it was yesterday?
[Salutations]
(check one)
[ ] Well trained professional, [ ] Casual reader, [ ] Hapless wanderer,
I have a (check all that apply)
[ ] query [ ] stored procedure [ ] database thing maybe
that ...
- 38.8k
86
votes
4
answers
85k
views
Should I add an arbitrary length limit to VARCHAR columns?
According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT.
Should I add an arbitrary length limit to a name or address column?
Edit: Not a dupe of:
...
- 1,199
78
votes
6
answers
148k
views
How do I efficiently get "the most recent corresponding row"?
I have a query pattern that must be very common, but I don't know how to write an efficient query for it. I want to look up the rows of a table that correspond to "the most recent date not after" the ...
- 1,559
70
votes
4
answers
91k
views
Can a single PostgreSQL query use multiple cores?
In recent versions of PostgreSQL (as of Dec 2013), can we share a query between two or more cores to get a performance boost? Or should we get faster cores?
- 3,646
70
votes
4
answers
58k
views
Index Seek vs Index Scan
Looking at an execution plan of a slow running query and I noticed that some of the nodes are index seek and some of them are index scan.
What is the difference between and index seek and an index ...
- 3,222
69
votes
5
answers
130k
views
Is it safe to use innodb_flush_log_at_trx_commit = 2
I turned innodb_flush_log_at_trx_commit = 2 and get a very fast write speed. But is it safe be used in production web site?
- 835
69
votes
3
answers
106k
views
Optimizing a Postgres query with a large IN
This query gets a list of posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others.
With this style of query, the obvious optimization ...
- 1,023
68
votes
3
answers
86k
views
What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
I have an indexed column that stores an MD5 hash. Thus, the column will always store a 32-character value. For whatever reason, this was created as a varchar rather than a char. Is it worth the ...
- 781
63
votes
6
answers
69k
views
Date range rolling sum using window functions
I need to calculate a rolling sum over a date range. To illustrate, using the AdventureWorks sample database, the following hypothetical syntax would do exactly what I need:
SELECT
TH.ProductID,
...
- 82.9k
63
votes
6
answers
85k
views
About single threaded versus multithreaded databases performance
H2 is a single threaded database with a good reputation regarding performance. Other databases are multi-threaded.
My question is: when does a multi-thread database become more interesting than an ...
- 1,451
63
votes
6
answers
88k
views
Why not use a table instead of a materialized view?
I'm new to Oracle databases. If I have understood correctly, materialized view is a view which result set is saved as a physical table in the database and this view/table is refreshed bases on some ...
- 5,333
60
votes
2
answers
28k
views
Are WHERE clauses applied in the order they are written?
I'm trying to optimize a query which looks into a big table (37 millions rows) and have a question about what order the operations are executed in a query.
select 1
from workdays day
where day....
60
votes
6
answers
32k
views
Performance implications of MySQL VARCHAR sizes
Is there a performance difference in MySQL between varchar sizes? For example, varchar(25) and varchar(64000). If not, is there a reason not to declare all varchars with the max size just to ensure ...
- 4,883
55
votes
4
answers
46k
views
Does updating a row with the same value actually update the row?
I have a performance-related question. Let's say I have a user with first name Michael. Take the following query:
UPDATE users
SET first_name = 'Michael'
WHERE users.id = 123
Will the query actually ...
- 653
53
votes
2
answers
85k
views
Optimizing bulk update performance in PostgreSQL
Using PG 9.1 on Ubuntu 12.04.
It currently takes up to 24h for us to run a large set of UPDATE
statements on a database, which are of the form:
UPDATE table
SET field1 = constant1, field2 = ...
- 651
53
votes
1
answer
52k
views
Indexes: integer vs string performance if the number of nodes is the same
I am developing an application in Ruby on Rails with the PostgreSQL (9.4) database. For my use case, columns in tables will be looked up very frequently, as the whole point of the application is ...
- 633
53
votes
6
answers
151k
views
How to update 10 million+ rows in MySQL single table as Fast as possible?
Using MySQL 5.6 with InnoDB storage engine for most of the tables. InnoDB buffer pool size is 15 GB and Innodb DB + indexes are around 10 GB. Server has 32GB RAM and is running Cent OS 7 x64.
I have ...
user16108
50
votes
5
answers
35k
views
Why would I NOT use the SQL Server option "optimize for ad hoc workloads"?
I've been reading some great articles regarding SQL Server plan caching by Kimberly Tripp such as this one:
http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/
Why ...
- 1,973
50
votes
4
answers
141k
views
SQL Server commands to clear caches before running a performance comparison
When comparing the execution time of two different queries, it's important to clear the cache to make sure that the execution of the first query does not alter the performance of the second.
In a ...
- 931
49
votes
5
answers
53k
views
Aggressive Autovacuum on PostgreSQL
I'm trying to get PostgreSQL to aggressively auto vacuum my database. I've currently configured auto vacuum as follows:
autovacuum_vacuum_cost_delay = 0 #Turn off cost based vacuum
...
- 763
49
votes
2
answers
53k
views
Configuring PostgreSQL for read performance
Our system writes a lots of data (kind of Big Data system). Write performance is good enough for our needs but read performance is really too slow.
The primary key (constraint) structure is similar ...
- 593
49
votes
6
answers
44k
views
In MySQL, does the order of the columns in a WHERE clause affect query performance?
I am having performance issues on certain database queries that have large possible result sets.
The query in question, I have three ANDs in the WHERE clause
Does the order of the clauses matter?
...
- 4,229
49
votes
4
answers
89k
views
Are individual queries faster than joins?
Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient?
TL;...
- 2,420
48
votes
2
answers
117k
views
When To Update Statistics?
I've inherited a Maintenance Plans that does the following:
Cleanup old data
Checks DB integrity
Performs Database and Transaction Log Backups
Reorganizes Our indexes
Updates Statistics
Delete old ...
- 1,099
48
votes
2
answers
91k
views
Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
I have a PostgreSQL 9.2 instance running on RHEL 6.3, 8-core machine with 16GB of RAM. The server is dedicated to this database. Given that the default postgresql.conf is quite conservative regarding ...
- 583
48
votes
3
answers
231k
views
How to speed up queries on a large 220 million rows table (9 gig data)?
The issue:
We have a social site where members can rate each other for compatibility or matching. This user_match_ratings table contains over 220 million rows (9 gig data or almost 20 gig in indexes)....
- 641
47
votes
5
answers
44k
views
Is nested view a good database design?
I have read somewhere long time ago. The book states that we should not allow to having a nested view in SQL Server. I am not sure the reason why we can't do that or I might remember incorrect ...
47
votes
2
answers
76k
views
Order by column should have index or not?
I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How ...
- 1,094
44
votes
1
answer
16k
views
Would index lookup be noticeably faster with char vs varchar when all values are 36 chars
I have a legacy schema (disclaimer!) that uses a hash-based generated id for the primary key for all tables (there are many). An example of such an id is:
922475bb-ad93-43ee-9487-d2671b886479
There ...
- 683
42
votes
3
answers
55k
views
Is it a good idea/approach to index a VARCHAR column?
We're using PostgreSQL v8.2.3.
There are tables involved: EMPLOYEE and EMAILLIST.
Table 1: EMPLOYEE (column1, column2, email1, email2, column5, column6)
Table 2: EMAILLIST (email)
2 tables are ...
- 977
41
votes
6
answers
14k
views
Why does adding a TOP 1 dramatically worsen performance?
I have a fairly simple query
SELECT TOP 1 dc.DOCUMENT_ID,
dc.COPIES,
dc.REQUESTOR,
dc.D_ID,
cj.FILE_NUMBER
FROM DOCUMENT_QUEUE dc
JOIN CORRESPONDENCE_JOURNAL cj
...
- 24.1k
41
votes
3
answers
10k
views
difference in execution plans on UAT and PROD server
I want to understand why there would be such a huge difference in execution of the same query on UAT (runs in 3 sec) vs PROD (run in 23 secs).
Both UAT and PROD are having exactly data and indexes.
...
- 62.1k
40
votes
2
answers
6k
views
IF EXISTS taking longer than embedded select statement
When I run the following code it takes 22.5 minutes and does 106million reads. However, if I run just the inner select statement by itself it only takes 15 seconds and does 264k reads. As a side note, ...
- 1,761
40
votes
3
answers
6k
views
SQL Server's "Total Server Memory" consumption stagnant for months with 64GB+ more available
I have run into an odd issue where SQL Server 2016 Standard Edition 64-bit has seemed to have capped itself off at precisely half of the total memory allocated towards it (64GB of 128GB).
The output ...
- 1,564
40
votes
1
answer
33k
views
GO After every T-SQL statement
What is the reasoning behind using the GO statement after every SQL statement? I understand that GO signals the end of batch and/or allows the reputation of statements but what advantage does it have ...
- 503
40
votes
2
answers
17k
views
Check existence with EXISTS outperform COUNT! ... Not?
I've often read when one had to check existence of a row should always be done with EXISTS instead of with a COUNT.
Yet in several recent scenarios I've measured a performance improvement when using ...
Serge
40
votes
5
answers
120k
views
When a previously-fast SQL query starts running slow, where do I look to find the source of the issue?
Background
I have a query running against SQL Server 2008 R2 that joins and/or left-joins about 12 different "tables". The database is fairly large with many tables over 50 million rows and about 300 ...
- 503
40
votes
6
answers
70k
views
How do you tune MySQL for a heavy InnoDB workload?
Assuming a production OLTP system with predominantly InnoDB tables
What are the common symptoms of a mistuned/misconfigured system?
What configuration parameters do you most commonly change from ...
- 1,377
40
votes
1
answer
18k
views
USING construct in JOIN clause can introduce optimization barriers in certain cases?
It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases.
I mean this key word:
SELECT *
...
- 172k
39
votes
5
answers
84k
views
Methods of speeding up a huge DELETE FROM <table> with no clauses
Using SQL Server 2005.
I am performing a huge DELETE FROM with no where clauses. It's basically equivalent to a TRUNCATE TABLE statement - except I'm not allowed to use TRUNCATE. The problem is ...
- 1,875