As of May 31, 2023, we have updated our Code of Conduct.

Questions tagged [query-performance]

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

Filter by
Sorted by
Tagged with
112 votes
3 answers
164k 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, ...
sudo.ie's user avatar
  • 1,221
86 votes
4 answers
45k 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 ...
Erik Darling's user avatar
  • 37.5k
77 votes
6 answers
145k 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 ...
Tom Ellis's user avatar
  • 1,519
70 votes
4 answers
88k 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?
Alireza's user avatar
  • 3,596
51 votes
1 answer
48k 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 ...
Chris Cirefice's user avatar
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 ...
SomeGuy's user avatar
  • 1,973
49 votes
2 answers
51k 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 ...
JPelletier's user avatar
48 votes
2 answers
90k 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 ...
Petr Praus's user avatar
48 votes
3 answers
227k 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)....
Ranknoodle's user avatar
43 votes
3 answers
102k views

Optimize a query that's running slow with nested loops inner ioin

Consider the following simple query (only 3 tables involved) SELECT l.sku_id AS ProductId, l.is_primary AS IsPrimary, v1.category_name AS Category1, v2....
Luis Ferrao's user avatar
41 votes
6 answers
13k 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 ...
Kenneth Fisher's user avatar
41 votes
3 answers
9k 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. ...
Kin Shah's user avatar
  • 61.8k
38 votes
2 answers
12k views

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 ...
David's user avatar
  • 483
37 votes
5 answers
60k views

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 ...
Martin Brown's user avatar
34 votes
2 answers
5k views

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.'s user avatar
  • 31.8k
34 votes
3 answers
35k views

Are Views optimized when I add a WHERE clause to them?

Does it make a difference if you filter a View inside or outside the View? For example, is there any difference between these two queries? SELECT Id FROM MyTable WHERE SomeColumn = 1 Or SELECT Id ...
Rachel's user avatar
  • 8,407
33 votes
1 answer
4k views

How does SQL Server determine key column order in missing index requests?

How does SQL Server determine the order of key columns in its missing index recommendations for a query plan?
Bryan Rebok's user avatar
  • 1,169
32 votes
2 answers
135k views

What is the fastest way to insert large numbers of rows?

I have a database where I load files into a staging table, from this staging table i have 1-2 joins to resolve some foreign keys and then insert this rows into the final table (which has one partition ...
nojetlag's user avatar
  • 2,857
30 votes
2 answers
14k views

Using indexed views for aggregates - too good to be true?

We have a data warehouse with a fairly large record count (10-20 million rows) and often run queries that count records between certain dates, or count records with certain flags, e.g. SELECT f....
Justin's user avatar
  • 910
30 votes
3 answers
34k views

Stored procedures vs. inline SQL

I know stored procedures are more efficient through the execution path (than the inline sql in applications). However, when pressed, I'm not super knowlegeable about why. I'd like to know the ...
webdad3's user avatar
  • 572
30 votes
1 answer
3k views

Why is a scan faster than seek for this predicate?

I was able to reproduce a query performance issue that I would describe as unexpected. I'm looking for an answer that's focused on internals. On my machine, the following query does a clustered index ...
Joe Obbish's user avatar
  • 31.7k
30 votes
5 answers
147k views

How to optimize very slow SELECT with LEFT JOINs over big tables

I was googling, self-educating & looking for solution for hours but with no luck. I found a few similar questions here but not this case. My tables: persons (~10M rows) attributes (location, age,...
Martin's user avatar
  • 411
29 votes
4 answers
6k views

Can spatial index help a "range - order by - limit" query

Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes. We have the following table with a tree structure (Nested Set model) of words and their frequencies:...
ypercubeᵀᴹ's user avatar
29 votes
2 answers
1k views

How to handle bad query plan caused by exact equality on range type?

I'm performing an update where I require an exact equality on a tstzrange variable. ~1M rows are modified, and the query takes ~13 minutes. The result of EXPLAIN ANALYZE can be seen here, and the ...
abeboparebop's user avatar
26 votes
5 answers
4k views

SARGable WHERE clause for two date columns

I have what is, to me, an interesting question on SARGability. In this case, it's about using a predicate on the difference between two date columns. Here's the setup: USE [tempdb] SET NOCOUNT ON ...
Erik Darling's user avatar
  • 37.5k
25 votes
2 answers
6k views

Why filtered index on IS NULL value is not used?

Assume we have a table definition like this: CREATE TABLE MyTab ( ID INT IDENTITY(1,1) CONSTRAINT PK_MyTab_ID PRIMARY KEY ,GroupByColumn NVARCHAR(10) NOT NULL ,WhereColumn DATETIME NULL ...
jericzech's user avatar
  • 945
23 votes
1 answer
27k views

SQL query for combinations without repetition

I need a query which can be used in (or as) a function and retrieves all combinations of n values. And I need all combinations of length k where k = 1..n. Extended sample input and result so input ...
Ario's user avatar
  • 1,032
22 votes
2 answers
14k views

LIKE uses index, CHARINDEX does not?

This question is related to my old question. The below query was taking 10 to 15 seconds to execute: SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id] FROM [company].dbo.[customer] ...
IT researcher's user avatar
22 votes
5 answers
8k views

Using IF in T-SQL weakens or breaks execution plan caching?

It has been suggested to me that the use of IF statements in t-SQL batches is detrimental to performance. I'm trying to find some confirmation or validate this assertion. I'm using SQL Server 2005 ...
AnthonyWJones's user avatar
21 votes
2 answers
19k views

How to make DISTINCT ON faster in PostgreSQL?

I have a table station_logs in a PostgreSQL 9.6 database: Column | Type | ---------------+-----------------------------+ id | bigint ...
Kokizzu's user avatar
  • 1,313
21 votes
1 answer
1k views

Seek and you shall Scan... on partitioned tables

I've read these articles in PCMag by Itzik Ben-Gan: Seek and You Shall Scan Part I: When the Optimizer Doesn't Optimize Seek and You Shall Scan Part II: Ascending Keys I’m currently having a "...
Henrik Staun Poulsen's user avatar
21 votes
1 answer
6k views

Hash keys probe and residual

Say, we have a query like this: select a.*,b.* from a join b on a.col1=b.col1 and len(a.col1)=10 Assuming the above query uses a Hash Join and has a residual, the probe key will be col1 and the ...
TheGameiswar's user avatar
  • 2,929
21 votes
4 answers
949 views

Advice on diagnosing a "sometimes" slow query

I have a stored procedure which returns results from an indexed view via a covering index. Usually, it runs fast (~10ms), sometimes it can run up to 8 seconds. Here's an example random execution (...
RPM1984's user avatar
  • 435
20 votes
5 answers
5k views

Performance of a=0 and b=0 and ... z=0 vs a+b+c+d=0

This is a simple question that I can't seem to find the answer for. In terms of performance, If I have a WHERE clause such as a=0 and b=0 and ... z=0, Would I gain any performance if I replaced that ...
JohnG's user avatar
  • 1,071
20 votes
2 answers
12k views

Scaling PostgreSQL TRIGGER(s)

How Postgres triggers mechanism scales ? We have a large PostgreSQL installation and we are trying to implement an event based system using log tables and TRIGGER(s). Basically we would like to ...
Ugo Matrangolo's user avatar
20 votes
3 answers
13k views

Wrapping query in IF EXISTS makes it very slow

I have the below query : select databasename from somedb.dbo.bigtable l where databasename ='someval' and source <>'kt' and not exists(select 1 from dbo.smalltable c where c.source=l.source) ...
TheGameiswar's user avatar
  • 2,929
20 votes
3 answers
19k views

Huge data and performance in SQL Server

I've written an application with a SQL Server backend that collects and stores and extremely large amount of records. I've calculated that, at the peak, the average amount of records is somewhere in ...
Brandon's user avatar
  • 598
20 votes
2 answers
3k views

T-SQL query using completely different plan depending on number of rows I'm updating

I have a SQL UPDATE statement with a "TOP (X)" clause, and the row I'm updating values in has about 4 billion rows. When I use "TOP (10)", I get one execution plan that executes almost instantly, but ...
SqlRyan's user avatar
  • 1,228
19 votes
3 answers
880 views

Forcing Flow Distinct

I have a table like this: CREATE TABLE Updates ( UpdateId INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ObjectId INT NOT NULL ) Essentially tracking updates to objects with an increasing ID. The ...
Cory Nelson's user avatar
19 votes
4 answers
35k views

Is it better to separate a big query into multiple smaller queries?

There are situations which require having really big query joining several tables together with sub select statements in them to produce the desired results. My question is, should we consider ...
2hamed's user avatar
  • 313
19 votes
4 answers
50k views

Effect of an index on update statements where update column is not in an index

I constantly see people say that indexes slow down update, delete and insert. This is used as a blanket statement, as if it is an absolute. While tuning my database to improve performance, I keep ...
Ryan's user avatar
  • 315
18 votes
3 answers
5k views

Colleague says to never use an OR statement in SQL, is this true?

My colleague now responsible for SQL development says that I should never use an OR statement because it will mess up the query optimizer and ignore table indexes producing slow queries. I couldn't ...
jwize's user avatar
  • 321
18 votes
2 answers
32k views

Most Efficient Way to Retrieve Date Ranges

What's the most efficient way to retrieve date ranges with a table structure like this? create table SomeDateTable ( id int identity(1, 1) not null, StartDate datetime not null, EndDate ...
Thomas Stringer's user avatar
18 votes
5 answers
74k views

Improve delete speed for SQL Server

We have huge production database, its size is around 300GB. Is there any approach to improve performance of a delete query? Right now deletion speed is between 1-10k per minute, it is very slow for us....
user avatar
18 votes
1 answer
5k views

SQL Server--If logic in stored procedure and the plan cache

SQL Server 2012 and 2016 Standard: If I put if-else logic in a stored procedure to execute one of two branches of code, depending on the value of a parameter, does the engine cache the latest version?...
Nicole G.'s user avatar
  • 305
18 votes
2 answers
6k views

'SELECT TOP' performance question

I have a query which runs much faster with select top 100 and much slower without top 100. The number of returned records is 0. Could you explain the difference in query plans or share links where ...
George K's user avatar
  • 2,279
18 votes
1 answer
1k views

Single-row INSERT...SELECT much slower than separate SELECT

Given the following heap table with 400 rows numbered from 1 to 400: DROP TABLE IF EXISTS dbo.N; GO SELECT SV.number INTO dbo.N FROM master.dbo.spt_values AS SV WHERE SV.[type] = N'P' ...
Paul White's user avatar
  • 80.3k
18 votes
1 answer
15k views

PostgreSQL - If I run multiple queries concurrently, under what circumstances would I see a speedup? Under what circumstances would I see a slowdown?

I approach you all humbly as one who is NOT a DBA, and I'm sure that my question is fraught with conceptual shortcomings and "it depends on" land mines. I'm also pretty sure that all of you who ...
Aaron Johnson's user avatar
17 votes
1 answer
12k views

Difference between Seek Predicate and Predicate

I'm trying to performance tune a query that we have in SQL Server 2014 Enterprise. I have opened the actual query plan in SQL Sentry Plan Explorer and I can see on one node that it has a Seek ...
Greg's user avatar
  • 3,212
16 votes
3 answers
3k views

What is retrieved from disk during a query?

Fairly simple question, probably answered somewhere, but I can't seem to form the right search question for Google... Do the number of columns in a particular table affect the performance of a query, ...
Jmoney38's user avatar
  • 1,105

1
2 3 4 5
58