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.

Filter by
Sorted by
Tagged with
280
votes
15answers
145k 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 ...
171
votes
7answers
200k 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 ...
126
votes
5answers
184k 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/...
106
votes
6answers
112k 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 ...
94
votes
7answers
63k 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 ...
90
votes
8answers
125k views

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

I had to write a simple query where I go looking for people's name that start with a B or a D : SELECT s.name FROM spelers s WHERE s.name LIKE 'B%' OR s.name LIKE 'D%' ORDER BY 1 I was wondering ...
85
votes
3answers
29k 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? ...
81
votes
4answers
58k 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 ...
72
votes
4answers
10k 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, ...
65
votes
2answers
95k 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 on the application some times, we only ...
62
votes
4answers
54k 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 ...
57
votes
6answers
67k 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 ...
57
votes
3answers
62k 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 ...
53
votes
5answers
87k 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?
52
votes
7answers
61k 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 ...
48
votes
5answers
32k 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 ...
43
votes
6answers
24k 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 ...
41
votes
5answers
28k 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 ...
41
votes
4answers
59k 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;...
40
votes
5answers
40k 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 ...
40
votes
2answers
89k 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 ...
39
votes
5answers
66k 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 ...
39
votes
2answers
91k views

How to optimize a query that's running slow on Nested Loops (Inner Join)

TL;DR Since this question keeps getting views, I'll summarize it here so newcomers don't have to suffer the history: JOIN table t ON t.member = @value1 OR t.member = @value2 -- this is slow as hell ...
38
votes
3answers
4k 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 ...
38
votes
2answers
57k views

Multi cores and MySQL Performance

Importance of RAM is an established fact but far less material is available about the importance of cores and multithreading when it comes to the usage of CPU by MySQL. I am talking about the ...
37
votes
5answers
69k 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 ...
37
votes
6answers
27k 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? ...
36
votes
5answers
106k 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 ...
35
votes
2answers
13k 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 ...
35
votes
2answers
43k 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 = ...
34
votes
2answers
3k 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, ...
33
votes
3answers
22k views

Is the CPU performance relevant for a database server?

This is a purely theoretical question. Let's say I have an application deployed on multiple servers. A load balancer, Multiple/scalable applications servers A (single) database server (for the ...
33
votes
1answer
29k 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 ...
33
votes
5answers
44k views

Why are simple SELECTs on InnoDB 100x slower than on MyISAM?

I have quite an annoying problem. I want to use INNODB as my main database engine and give up on MyISAM as I need the former for using galera-cluster for redundancy. I copied (description follows) ...
33
votes
1answer
15k 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 * ...
32
votes
1answer
3k views

Very strange performance with an XML index

My question is based on this: https://stackoverflow.com/q/35575990/5089204 To give an answer there I did the following test-scenario. Test scenario First I create a test table and fill it with 100....
32
votes
3answers
29k 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 ...
32
votes
2answers
39k 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 ...
31
votes
4answers
25k 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: ...
30
votes
5answers
8k views

How far should you go with normalization?

I have a decent amount of data in a database. I have well formed tables and good relationships between them with some redundancy in my data. But how far should I go with normalization? Are there ...
30
votes
4answers
14k views

CPU clock speed versus CPU core count - higher GHz, or more cores for SQL Server?

We are beginning to provision a set of physical servers for a virtual cluster of SQL Server 2016 nodes within VMware. We will be utilizing Enterprise Edition licenses. We plan on setting up 6 nodes, ...
30
votes
1answer
7k 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 ...
30
votes
3answers
32k views

Configuring PostgreSQL for write performance

One of my PostgreSQL servers hosts several (1-3) databases which receive a constant stream of data. The data is not particularly structured, it amounts to the current time and a variety of observed ...
30
votes
7answers
52k views

What is the most efficient way to get the minimum of multiple columns on SQL Server 2005?

I'm in a situation where I want to get the minimum value from of 6 columns. I've found three ways so far to accomplish this, but I have concerns with the performance of these methods and would like ...
28
votes
4answers
6k views

Do SSDs reduce the usefulness of Databases

I only heard about Robert Martin today, and it seems like he's a notable figure in the software world, so I don't mean for my title to appear as if it's a click bait or me putting words in his mouth, ...
28
votes
3answers
18k views

How Does Table Partitioning Help?

I am having difficulty to grab the idea of pros and cons of table partitioning. I am about to start work on a project which would have 8 tables and one of them will be the main data table which will ...
28
votes
2answers
3k views

Why would SELECT * be magnitudes faster than SELECT foo?

Consider a table of values and hashes, like so: +------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------...
28
votes
3answers
56k views

Efficient INSERT INTO a Table With Clustered Index

I have a SQL statement that inserts rows into a table with a clustered index on the column TRACKING_NUMBER. E.G.: INSERT INTO TABL_NAME (TRACKING_NUMBER, COLB, COLC) SELECT TRACKING_NUMBER, COL_B, ...
28
votes
6answers
74k 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 ...
27
votes
2answers
79k views

MySQL creates temporary tables on disk. How do I stop it?

We are running a site (Moodle) that the users currently find slow. I think I have tracked down the problem to MySQL creating temporary tables on disk. I watch the variable created_tmp_disk_tables in ...