Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [query-performance]

For questions about improving the performance and/or efficiency of database queries.

Questions regarding query performance should use this tag. Be sure to include tags for the database type (, , etc) and database version tags such as . If you are joining tables, you can include tags such as or if you know what type of join you are using, include that, such as , , or ,

Your question should include a minimal query that exhibits the issue, a description of the tables and their columns (including data types), an execution plan (if possible one that shows actual row counts, times, etc.) and what troubleshooting you have attempted such as hints and rewriting the query.

IF joining tables, list the approximate number of rows of each table and the cardinality of the join columns, such as "there are approximately 200 order_id values for a particular customer_id.

3255 questions
109
votes
3 answers

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, we only need to use only 1 column. So would it be faster…
sudo.ie
  • 1,191
  • 2
  • 8
  • 5
85
votes
4 answers

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 was running fine (if applicable) [ ] yesterday [ ] in…
Erik Darling
  • 36,388
  • 14
  • 127
  • 357
76
votes
6 answers

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 rows of another table. I have a table, inventory…
Tom Ellis
  • 1,499
  • 3
  • 13
  • 13
70
votes
4 answers

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?
Alireza
  • 3,586
  • 10
  • 35
  • 43
62
votes
6 answers

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, TH.TransactionDate, TH.ActualCost, …
Paul White
  • 78,233
  • 28
  • 392
  • 615
51
votes
1 answer

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 searching for very specific attributes on a model. I…
Chris Cirefice
  • 613
  • 1
  • 5
  • 7
50
votes
5 answers

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 is there even an option to "optimize for ad hoc…
SomeGuy
  • 1,973
  • 7
  • 33
  • 46
49
votes
2 answers

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 for all our tables: timestamp(Timestamp) ;…
JPelletier
  • 593
  • 1
  • 5
  • 6
48
votes
3 answers

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). Queries against this table routinely show up in…
Ranknoodle
  • 641
  • 1
  • 6
  • 8
48
votes
2 answers

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 memory settings, I thought it might be a good…
Petr Praus
  • 583
  • 1
  • 4
  • 6
41
votes
3 answers

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. QUERY: set statistics io on; set statistics time…
Kin Shah
  • 61,609
  • 5
  • 116
  • 235
41
votes
6 answers

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 ON dc.DOCUMENT_ID = cj.DOCUMENT_ID WHERE…
Kenneth Fisher
  • 23,932
  • 9
  • 58
  • 109
38
votes
2 answers

How (and why) does TOP impact an execution plan?

For a moderately complex query I am trying to optimize, I noticed that removing the TOP n clause changes the execution plan. I would have guessed that when a query includes TOP n the database engine would run the query ignoring the the TOP clause,…
David
  • 483
  • 1
  • 4
  • 6
37
votes
5 answers

UPDATE performance where no data changes

If I have an UPDATE statement that does not actually change any data (because the data is already in the updated state). Is there any performance benefit in putting a check in the WHERE clause to prevent the update? For example would there be any…
Martin Brown
  • 720
  • 1
  • 5
  • 15
34
votes
2 answers

If a CTE is defined in a query and is never used, does it make a sound?

Do unused CTEs in queries affect performance and / or alter the generated query plan?
J.D.
  • 30,123
  • 7
  • 45
  • 96
1
2 3
…
99 100