Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
246
questions
49
votes
2
answers
52k
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
86
votes
4
answers
47k
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 ...
- 38.3k
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:...
- 96.9k
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.
...
- 62k
77
votes
6
answers
147k
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,539
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 ...
- 483
6
votes
3
answers
8k
views
Does a query with a primary key and foreign keys run faster than a query with just primary keys?
SELECT something FROM table WHERE primary_key = ?
vs.
SELECT something FROM table WHERE primary_key = ? AND other_key = ?
Say that this is a scenario where the inclusion of other_key does NOT ...
- 419
7
votes
3
answers
15k
views
Why does my query run fast in Environment A, but slow in Environment B?
I have a piece of SQL that seems to run really fast in Environment A, but the exact same query runs really slow in Environment B!
Environments are supposed to be the same, so what should I do and/or ...
- 10.9k
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?...
- 305
53
votes
1
answer
51k
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 ...
- 633
6
votes
1
answer
5k
views
Unused index in range of dates query
I have a query that is not using existing indices and I do not understand why.
The table:
mustang=# \d+ bss.amplifier_saturation
Table "bss....
- 163
37
votes
5
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 ...
- 720
4
votes
3
answers
6k
views
Optimize a query on two big tables
I have a very important query in my system that is taking too long to execute due to huge amount of data on tables. I'm a junior DBA and i need the best optimization I can get for this. Tables have ...
2
votes
1
answer
360
views
Combining columns in index
With reference to RolandoMySQLDBA's answer on
MySQL: Index when joining to tables not being used (Performance optimizing question)
He mentioned
Make sure you build an index that involves the ...
30
votes
3
answers
35k
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
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
18
votes
2
answers
33k
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 ...
- 42k
16
votes
3
answers
10k
views
Algorithm for finding the longest prefix
I have two tables.
First one is a table with prefixes
code name price
343 ek1 10
3435 nt 4
3432 ek2 2
Second is call records with phone numbers
number time
834353212 10
...
- 285
5
votes
2
answers
1k
views
Why is a secondary index chosen over a clustered index for SELECT COUNT(*) ...?
In this query:
select count(*) from largetable;
a secondary index is chosen:
mysql> explain select count(*) from largetable;
+----+-------------+------------+-------+---------------+------+------...
- 1,308
4
votes
2
answers
9k
views
Inner join using an array column
Having trouble indexing and executing a query in O (log n) time.
The query includes an INNER JOIN, an ORDER BY, and an equality operation. If I understand the laws of databases correctly, a query can ...
- 265
70
votes
4
answers
90k
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,616
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....
- 920
22
votes
2
answers
20k
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,343
11
votes
3
answers
43k
views
GROUP BY one column, while sorting by another in PostgreSQL
How can I GROUP BY one column, while sorting only by another.
I'm trying to do the following:
SELECT dbId,retreivalTime
FROM FileItems
WHERE sourceSite='something'
GROUP BY seriesName
...
- 1,316
6
votes
1
answer
2k
views
Stored procedure with recompile
I have this stored procedure..when i told the developers that it is not recommended to use with Recompile option they replied "This was because this SP can be called with a number of different params ...
- 1,383
5
votes
2
answers
9k
views
Optimize simple query using ORDER BY date and text
I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records
The data types of the table are:
fcv_id = serial
fcv_fecha_comprobante = ...
- 271
32
votes
2
answers
139k
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,877
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
...
- 38.3k
11
votes
3
answers
24k
views
Postgres is performing sequential scan instead of index scan
I have a table with about 10 million rows in it and an index on a date field. When I try and extract the unique values of the indexed field Postgres runs a sequential scan even though the result set ...
- 241
9
votes
1
answer
411
views
How to Optimise Query
I have a database structure similar to this,
CREATE TABLE [dbo].[Dispatch](
[DispatchId] [int] NOT NULL,
[ContractId] [int] NOT NULL,
[DispatchDescription] [nvarchar](50) NOT NULL,
...
- 2,187
9
votes
1
answer
2k
views
Do fixed-width rows improve PostgreSQL read performance?
I have a table articles:
Table "articles"
Column | Type | Modifiers ...
- 745
8
votes
3
answers
59k
views
Is table partitioning improving performance? Is it worth it?
I have just been involved in a project on which I will have to develop a data migration process and a web interface that uses an already existing SQL Server DB. This DB was developed by another person ...
- 545
7
votes
2
answers
3k
views
Why does MySQL choose this execution plan?
I have two queries,
select some_other_column
from `table`
order by primary_index_column asc
limit 4000000, 10;
and
select some_other_column
from `table`
order by secondary_index_column asc
...
- 1,308
7
votes
1
answer
6k
views
Query is slow in SQL Server 2014, fast in SQL Server 2012
During the migration of one of our databases from SQL Server 2012 (SP1, CU2) to SQL Server 2014 (SP1), we have experienced some strange issues.
One of the queries that completes within seconds on SQL ...
- 73
6
votes
2
answers
2k
views
SQL Prove memory pressure - high Buffer Cache Hit Ratio, but low Page Life Expectancy
Our production server (2012, VM + SAN) has 32 GB of RAM, the database size is ~80GB. The application uses TempDB heavily - disk is hit ~100 MBps both reads & writes. Seeing tons of SQL ...
- 1,134
5
votes
2
answers
2k
views
Wordcount in a field (all and unique) - is there a more elegant/optimal way?
Answering this question,
Given this table (constructed from the question):
CREATE TABLE wordcount (id SERIAL NOT NULL PRIMARY KEY, description TEXT NOT NULL);
INSERT INTO wordcount (description) ...
- 29.3k
5
votes
1
answer
2k
views
Pull data from multiple tables in a view or function
There are 3 tables from which I need to pull data, and print it in a specific way.
http://sqlfiddle.com/#!15/59481/8
Refer to this question to see how the query was produced. The version in the ...
- 353
4
votes
1
answer
314
views
Badly-formed histogram causes bad estimates on Nested Loop
We have a query on a SQL Server 2016 SP2 CU12 Enterprise where the Query Optimizer estimates that only 1 row would come out of the Nested Loops Join operator, in reality 108501 rows came back. This ...
- 1,994
4
votes
4
answers
1k
views
Why are [Seemingly] suitable indexes not used on a LEFT JOIN with OR
I have the following [fairly meaningless, just for the purpose of demonstration] query in the StackOverflow database:
SELECT *
FROM Users u
LEFT JOIN Comments c
ON u.Id = c....
- 1,898
3
votes
1
answer
727
views
Why is my filtered index being ignored?
--Setup, actual queries after the space
--create table fltrind (a integer,b integer)
truncate table fltrind
DROP INDEX fltrind.nf
DROP INDEX fltrind.f
DECLARE @i integer
SET @i=0
while @i<100
BEGIN
...
- 1,012
3
votes
1
answer
174
views
Efficiently storing query-affecting settings in a database
I want to know how to efficiently store query-affecting settings in a large analytical database (MS SQL Server). By efficiency I mean no hardcode (1) and fast/optimal query processing (2).
My example
...
3
votes
1
answer
578
views
What is the name of this type of query, and what is an efficient example?
The purpose is to find a parent, given it's children. For example, say you have a marketing package (aka a "combo"), and want to match it based on the products in it. Example table/data:
create table ...
- 8,281
3
votes
1
answer
1k
views
Does the Int type primary key insert order affect query performance in MySQL?
Table t has an Int type primary key id without "Auto Increment"
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);
Use the following three ways to ...
- 251
2
votes
1
answer
29k
views
Row with latest timestamp
How to get the row with the latest value in a TIMESTAMPZ column? Is an index needed? Does an index change the strategy? Would behavior vary by database (I'm using Postgres 9.4)?
My app records data ...
- 10.4k
2
votes
1
answer
11k
views
Get top most parent by nth child id?
Now there is a question we commonly use this technique to maintain the parent child relation i.e we store all the entities in one tables with a parent_id column and all top most parents have 0 in the ...
- 356
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 ...
- 1,973
48
votes
2
answers
91k
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
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
23
votes
1
answer
28k
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 ...
- 1,042
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,949