Skip to main content

Questions tagged [query-performance]

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

Filter by
Sorted by
Tagged with
120 votes
3 answers
181k 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,301
87 votes
4 answers
49k 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
  • 41.9k
78 votes
6 answers
151k 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,609
70 votes
4 answers
94k 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,646
54 votes
1 answer
56k 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
  • 2,013
49 votes
2 answers
55k 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
93k 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
233k 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
105k 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
14k 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
10k 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
  • 62.3k
39 votes
2 answers
13k 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
  • 493
37 votes
4 answers
61k 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
36 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
  • 38.1k
34 votes
3 answers
37k 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,487
33 votes
1 answer
5k 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
146k 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,897
31 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
  • 920
31 votes
5 answers
168k 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
  • 421
30 votes
3 answers
36k 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
  • 32.5k
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
27 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
  • 41.9k
25 votes
3 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
28k views

SQL query for combinations without repetition

I need a function that can generate all possible combinations of a given set of n values. For each combination, the function should also generate all combinations of length k, where k ranges from 1 to ...
Ario's user avatar
  • 1,052
23 votes
2 answers
22k 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,373
23 votes
1 answer
18k 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
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
9k 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
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,989
21 votes
3 answers
14k 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,989
21 votes
3 answers
20k 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
  • 608
21 votes
4 answers
964 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,081
20 votes
4 answers
39k 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
  • 323
20 votes
2 answers
14k 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
4 answers
54k 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
  • 325
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
5 answers
82k 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
19 votes
3 answers
903 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
1 answer
6k 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
  • 315
19 votes
1 answer
14k 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,262
18 votes
4 answers
9k 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
34k 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
3 answers
62k views

MySQL: Which join is better between left outer join and inner join

Which join is better peforming if all of them provides the same result? For example, I have a two tables employees(emp_id,name, address, designation, age, sex) and work_log(emp_id,date,hours_wored). ...
ursitesion's user avatar
  • 2,071
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,296
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
  • 86.4k

1
2 3 4 5
62