163

Your query is pretty much the optimum. Syntax won't get much shorter, query won't get much faster: SELECT name FROM spelers WHERE name LIKE 'B%' OR name LIKE 'D%' ORDER BY 1; If you really want to shorten the syntax, use a regular expression with branches: ... WHERE name ~ '^(B|D).*' Or slightly faster, with a character class: ... WHERE name ~ '^[...


145

During parsing, SQL Server calls sqllang!DecodeCompOp to determine the type of comparison operator present: This occurs well before anything in the optimizer gets involved. From Comparison Operators (Transact-SQL) Tracing the code using a debugger and public symbols*, sqllang!DecodeCompOp returns a value in register eax** as follows: ╔════╦══════╗...


125

I actually discussed innodb_thread_concurrency with a MySQL Expert at the Percona Live NYC conference back in May 2011. I learned something surprising: In spite of the documentation, it is best to leave innodb_thread_concurrency at 0 (infinite concurrency). That way, InnoDB decides the best number of innodb_concurrency_tickets to open for a given MySQL ...


115

I am not very familiar with your needs, but perhaps storing each data point in the database is a bit of overkill. It sound almost like taking the approach of storing an image library by storing each pixel as a separate record in a relational database. As a general rule, storing binary data in databases is wrong most of the time. There is usually a better ...


108

I once worked with a very large (Terabyte+) MySQL database. The largest table we had was literally over a billion rows. This was using MySQL 5.0, so it's possible that things may have improved. It worked. MySQL processed the data correctly most of the time. It was extremely unwieldy though. (If you want six sigma-level availability with a terabyte of data, ...


101

Q2: way to measure page size PostgreSQL provides a number of Database Object Size Functions. I packed the most interesting ones in this query and added some Statistics Access Functions at the bottom. (The additional module pgstattuple provides more useful functions, yet.) This is going to show that different methods to measure the "size of a row" lead to ...


88

It certainly is. We discussed that in great detail under this related question: Working of indexes in PostgreSQL Space is allocated in multiples of MAXALIGN, which is typically 8 bytes on a 64-bit OS or (much less common) 4 bytes on a 32-bit OS. If you are not sure, check pg_controldata. It also depends on data types of indexed columns (some require ...


76

Short version: seek is much better Less short version: seek is generally much better, but a great many seeks (caused by bad query design with nasty correlated sub-queries for instance, or because you are making many queries in a cursor operation or other loop) can be worse than a scan, especially if your query may end up returning data from most of the rows ...


70

normalizing the data like crazy Normalizing the data like crazy may not be the right strategy in this case. Keep your options open by storing the data both in the Normalized form and also in the form of materialized views highly suited to your application. Key in this type of applications is NOT writing adhoc queries. Query modeling is more important than ...


69

What would address your question is the subject JOIN DECOMPOSITION. According to Page 209 of the Book You can decompose a join by running multiple single-table queries instead of a multitable join, and then performing the join in the application. For example, instead of this single query: SELECT * FROM tag JOIN tag_post ON tag_post.tag_id = tag.id JOIN ...


59

One of the biggest benefit of using a materialized view is that Oracle takes care of keeping the data in sync. If you have a separate aggregate table, you are responsible for keeping the data synchronized. That generally requires a reasonable amount of code and a decent amount of testing and most organizations manage to make mistakes that leave holes that ...


59

I work at Microsoft in SQL Support and I asked Jack Li, Senior Escalation Engineer and Subject Matter Expert of SQL Server performance, "Does SQL treat != any differently than <> ?" and he said, "They are the same."


59

There are some things in a database that should be tweaked when you use SSDs. For instance, speaking for PostgreSQL you can adjust effective_io_concurrency, and random_page_cost. However, faster reads and faster random access isn't what a database does. It ensures ACID (Atomicity, Consistency, Isolation, Durability) Some form of concurrency control, MVCC (...


57

You can lose up to one second's worth of transactions. The default value is 1, which helps keep InnoDB ACID Compliant. According to the MySQL Documentation on innodb_flush_log_at_trx_commit If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on ...


55

There's a lot going on here, and most of it is pretty broad and vague. 2008R2 RTM came out on April 21, 2010. It's totally out of support. You'll want to prioritize getting on the latest Service Pack, which came out just about 3 years ago to the day. That way you'll be covered if you're hitting a weird bug or something. Head on over here to figure out what ...


51

Before answering when to use it and why, it's first paramount in understanding exactly what GO is, and what it isn't. The keyword GO is used by SQL Server Management Studio and SQLCMD in order to signify one thing and only one thing: The end of a batch of statements. In fact, you can even change what you use to terminate batches to something other than "...


51

I bet you've configured the virtual CPUs in a way that some of the CPU nodes and/or memory nodes are offline. Download sp_Blitz (disclaimer: I'm one of the authors of that free open source script) and run it: sp_Blitz @CheckServerInfo = 1; Look for warnings about CPU and/or memory nodes being offline. SQL Server Standard Edition only sees the first 4 CPU ...


47

Regardless of platform, the following remarks apply. (-) Nested views: are harder to understand and debug e.g. What table column does this view column refer to? Lemme dig through 4 levels of view definitions... make it harder for the query optimizer to come up with the most efficient query plan See this, this, this, and this for anecdotal evidence. ...


45

The answer is no. Don't add a length modifier to varchar if you can avoid it. Most of the time, you don't actually need a length restriction anyway. Just use text for all character data. Make that varchar (no length modifier) if you need to stay compatible with RDBMS which don't have text. Performance is almost the same - text is a bit faster in rare ...


45

The SQL Server development team work on the principle of least surprise - so SQL Server generally has new features disabled in the interests of maintaining behaviour as previous versions. Yes, optimize for adhoc workloads is great at reducing plan cache bloat - but always test it first! [Edit: Kalen Delaney tells an interesting anecdote that she asked one ...


45

Assumptions Since information is missing in the Q, I'll assume: Your data comes from a file on the database server. The data is formatted just like COPY output, with a unique id per row to match the the target table. If not, format it properly first or use COPY options to deal with the format. You are updating every single row in the target table or most ...


45

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? In any performance scenario, you have to test and measure the solutions to see which is faster. That said, it's almost always the case that a joined result set from a properly tuned ...


45

This answer speeded up everything a lot: https://stackoverflow.com/a/2167641/292408 I simply SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0; at the beginning, and COMMIT; SET unique_checks=1; SET foreign_key_checks=1; at the end. Now it took 3 minutes. (Courtesy of @andreasemer via twitter)


43

I've often read when one had to check existence of a row should always be done with EXISTS instead of with a COUNT. It's very rare for anything to always be true, especially when it comes to databases. There are any number of ways to express the same semantic in SQL. If there is a useful rule of thumb, it might be to write queries using the most natural ...


43

In SQL Server, there are three common forms of non-join predicate: With a literal value: SELECT COUNT(*) AS records FROM dbo.Users AS u WHERE u.Reputation = 1; With a parameter: CREATE PROCEDURE dbo.SomeProc(@Reputation INT) AS BEGIN SELECT COUNT(*) AS records FROM dbo.Users AS u WHERE u.Reputation = @Reputation; END; With a local ...


40

Eelke is almost certainly correct that your locking is blocking autovacuum. Autovacuum is designed to give way to user activity, deliberately. If those tables are locked, autovacuum cannot vacuum them. For posterity, however, I wanted to give an example set of settings for hyper-aggressive autovacuum, since the settings you gave don't quite do it. Note ...


40

No. No gain at all. The manual explicitly states: Tip: There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database ...


40

The general rule of thumb is keep the core count as low as possible, and the processor speed as high as possible. The licensing math on that proves the point at ~$7,500 USD per core for Expensive Edition. Buying the correct hardware can pay for itself in reduced licensing costs. See Processor Selection for SQL Server by Glenn Berry. It's a great resource ...


37

Restarting the server is probably one of the most damaging things for performance. It means you force a cold cache for data, a cold cache for query plans, and all SQL Server internal caches are also nuked in the process. Not to mention that by throwing away all the statistics collected in the operational stats DMVs, you diminish your chances of ever ...


36

Just to see which tables qualify for autovacuum at all, the following query may be used (based on http://www.postgresql.org/docs/current/static/routine-vacuuming.html). Note however, that the query does not look for table specific settings: SELECT psut.relname, to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') as last_vacuum, to_char(psut....


Only top voted, non community-wiki answers of a minimum length are eligible