Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
3,102
questions
121
votes
3
answers
183k
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, ...
- 1,311
88
votes
4
answers
50k
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 ...
78
votes
6
answers
152k
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 ...
- 1,609
71
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?
- 3,656
54
votes
1
answer
58k
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 ...
- 643
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 ...
- 2,023
49
votes
2
answers
56k
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 ...
- 593
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 ...
- 583
48
votes
3
answers
234k
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)....
- 641
43
votes
3
answers
106k
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....
- 545
42
votes
6
answers
15k
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
...
- 24.2k
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.
...
- 62.4k
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 ...
- 493
37
votes
4
answers
62k
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 ...
- 728
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?
- 38.6k
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
...
- 8,497
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?
- 1,169
32
votes
2
answers
148k
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 ...
- 2,907
31
votes
2
answers
15k
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....
- 920
31
votes
5
answers
172k
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,...
- 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 ...
- 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 ...
- 32.6k
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:...
- 98.5k
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 ...
- 481
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
...
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
...
- 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 ...
- 1,062
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 ...
- 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 ...
- 333
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]
...
- 3,131
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 ...
- 321
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 "...
- 2,219
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 ...
- 2,999
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)
...
- 2,999
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 ...
- 608
21
votes
4
answers
974
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 (...
- 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 ...
- 1,091
20
votes
4
answers
40k
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 ...
- 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 ...
- 301
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 ...
- 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 ...
- 1,228
19
votes
4
answers
10k
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 ...
- 331
19
votes
5
answers
83k
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
19
votes
3
answers
907
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 ...
- 293
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?...
- 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 ...
- 3,272
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 ...
- 42.4k
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). ...
- 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 ...
- 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'
...
- 88.2k