150

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: ╔════╦══════╗...


117

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 ...


111

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, ...


107

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" ...


94

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 ...


90

Dear [your name here]! Oh no, I'm sorry to hear that! Let's start with some basics to get you fixed up in a jiffy. The thing you're running into is called Parameter Sniffing It's a way out wiggy weird problem. The name rolls right off the tongue. Like the German word for squirrel. And it's usually your friend. When a query hits your server, a plan has ...


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 ...


74

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 post ...


72

To elaborate on @alci's answer: PostgreSQL doesn't care what order you write things in PostgreSQL doesn't care at all about the order of entries in a WHERE clause, and chooses indexes and execution order based on cost and selectivity estimation alone. The order in which joins are written is also ignored up to the configured join_collapse_limit; if there ...


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 ...


61

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 ...


60

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 (...


55

Data alignment and storage size Actually, the overhead per index tuple is 8 byte for the tuple header plus 4 byte for the item identifier. Related: Use GIN to index bit strings Calculating and saving space in PostgreSQL We have three columns for the primary key: PRIMARY KEY ("Timestamp" , "TimestampIndex" , "KeyTag") "Timestamp" timestamp (8 ...


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 ...


54

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 as generic character string type. Performance is almost the same - ...


53

No, for versions of PostgreSQL prior to v9.6. Please see the PostgreSQL FAQ: How does PostgreSQL use CPU resources? The PostgreSQL server is process-based (not threaded). Each database session connects to a single PostgreSQL operating system (OS) process. Multiple sessions are automatically spread across all available CPUs by the OS. The OS also uses CPUs ...


52

It very much depends on circumstances and exact requirements. Consider my comment to the question. Simple solution With DISTINCT ON in Postgres: SELECT DISTINCT ON (i.good, i.the_date) i.the_date, p.the_date AS pricing_date, i.good, p.price FROM inventory i LEFT JOIN price p ON i.good = p.good AND i.the_date >= p.the_date ORDER BY i.good, i....


51

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 ...


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

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)


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

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 ...


46

An approximation of the size of a row, including the TOAST'ed contents, is easy to get by querying the length of the TEXT representation of the entire row: SELECT octet_length(t.*::text) FROM tablename AS t WHERE primary_key=:value; This is a close approximation to the number of bytes that will be retrieved client-side when executing: SELECT * FROM ...


46

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 ...


46

In your question, you detail some tests that you've prepared where you "prove" that the addition option is quicker than comparing the discrete columns. I suspect your test methodology may be flawed in several ways, as @gbn and @srutzky have alluded to. First, you need to ensure you're not testing SQL Server Management Studio (or whatever client you're ...


45

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 ...


44

I believe this sentiment was true at one point, but not in current versions of SQL Server. The whole problem was that in the old days ad hoc SQL statements could not be properly optimized because SQL Server could only optimize / compile at the batch level. Now we have statement-level optimization, so a properly parameterized query coming from an application ...


44

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 ...


44

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 ...


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