Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
251
questions
4
votes
1
answer
2k
views
Identical tables, identical query, completely different execution times
We have two tables with identical columns and indexing (No indexing at all, basically). We run the same query, which in case of the original table takes 5 seconds to run; in the case of the new table ...
- 264
4
votes
1
answer
4k
views
Partitioning Query
I have a couple of questions about the phyiscal layout of tables when they are partitioned. I've been researching this but am still a little unsure.
Say I have an existing table:-
CREATE TABLE ...
- 982
4
votes
1
answer
14k
views
Confusion about CPU time and Elapsed Time Calculation
I have already searched on different online forums already, but I haven't got any clear answer on this.
The problem is I have a query which has CPU time greater 4 times greater than Elapsed time. I ...
- 141
4
votes
3
answers
6k
views
Speed up search across multiple columns
We have a table:
user_info (user_id,domain_id,first_name,middle_name,last_name,work_place,.,.,.)
Now, we have a user search that search across three columns first_name,last_name and work_place.
If ...
- 385
4
votes
1
answer
19k
views
MySQL SUM with multiple GROUP BY on same table
What is the best way to produce several sum results based on different group by conditions on the same table in MySQL?
I believe I'm doing a bad select, but I can't figure out a better way.
Imagine ...
- 143
4
votes
2
answers
9k
views
Dramatic decrease in SQL Azure performance after scaling to new edition
Yesterday, we scaled one of our databases in Azure from the old Business edition to the new Standard one (S2).
Since then, it has been behaving rather erratically.
We have a relatively simple table ...
- 275
4
votes
1
answer
12k
views
Query performance of index scans slower than parallel seq scan on Postgres
I've been trying to debug a performance problem on PostgreSQL 10 on AWS Aurora.
Specifically I'm trying to understand performance problems on "cold" queries where the buffer cache does not ...
- 1,084
4
votes
1
answer
2k
views
Huge Memory Grant resulting in RESOURCE SEMAPHORE waits for other queries
Issue:
I have few queries which is requesting huge memory grants(~7GB).These queries are run often and this is causing other queries to wait for memory.So i am seeing RESOURCE_SEMAPHORE wait type.
...
- 1,315
3
votes
1
answer
165
views
Query Plan Error
I have an interesting situation. I use SQL2014 Enterprise version with SP1.
The following query results an error message :
The query processor ran out of internal resources and could not
produce ...
- 31
3
votes
0
answers
1k
views
MySQL: Improve performance for one row insert into table with unique constraint
I have a table with ~ 40 mil records, with a unique index on the url column, of type varchar(255).
Now the insert speed is about 30/s, is this expected? how could I improve it?
I can't use bulk ...
- 274
3
votes
3
answers
28k
views
Will more indexes on a table affect performance? [duplicate]
Possible Duplicate:
How do indices impact query performance?
How to know when/if I have too many indexes?
Say ‘X’ report using some "where" condition in the statement so we are creating an ...
- 39
3
votes
2
answers
2k
views
PostgreSQL planner choosing btree or gist index for few result rows
I have a scenario where a search on a range with the same value on both sides is using a plan with an index that is not the composite index I expected. After some work, I was able to generate a sample ...
- 53
3
votes
2
answers
560
views
Should I test against warm cache or cold cache in sql server?
I'm tuning a query which runs slow the first time and fast on the subsequent runs. I understand that it is because SQL Server is using cold cache (reading from disk) the first time and using warm ...
- 87
3
votes
1
answer
749
views
Efficient downsampling of a selected timeseries to equidistant samples
We have timeseries data from several sensors in a table and I am interested in getting N equally-spaced samples from the full range, as the whole data would be too much to display in a web-app.
I am ...
- 143
3
votes
1
answer
855
views
How To Optimize a Query That Aggregates Then Duplicates a Large Number of Records in a Single Table
In the following post, J.D. brings up that I have a poorly performing query.
I am running this query on SQL Server 2019 Standard Edition (query plan was generated in Development Edition)
Let's take a ...
- 205
3
votes
2
answers
2k
views
Dealing with suboptimal postgres query plans related to nested loops
I'm developing an application that dynamically builds up SQL queries and executes them against postgres 13.
Some of the queries are very slow because they use nested-loops but the query planner ...
- 131
3
votes
2
answers
8k
views
Real time much greater than `EXPLAIN ANALYZE`'s execution time (index scan)
I want to fetch up to 100 rows based on their id. The id is the primary key of the table.
The query that I had written looks like this:
select * from table where id = any ($1);
where $1 is ...
- 525
3
votes
1
answer
661
views
What is the best index strategy or query SELECT when performing a search/lookup BETWEEN IP address (IPv4 and IPv6) ranges?
Question: Is there a better indexing strategy or query SELECT that I can use for looking up one large data set against another large data set? Or, should I look at placing the lookup dimension table ...
- 199
3
votes
1
answer
2k
views
Implicit conversion does not affect performance
I have read about implicit conversion on indexes affect performance so by that mean in the following query
select count(*)
from fpc
where SKey in (201701, 201702)
as SKey is of type int, if I change ...
user71787
3
votes
2
answers
3k
views
Optimize a query with two range conditions
I have a structure similar to this one:
CREATE TABLE `author` (
`id` int(11) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8 ...
- 131
3
votes
1
answer
904
views
10195 plans for the same query!
So, I've ran the BrentOzar script which has identified 10195 plans for the same query!!! The query is below:
SELECT *
FROM [table1]
INNER JOIN [table2]
ON [table1].[versionId] = [table2].[...
- 145
3
votes
1
answer
7k
views
Find out beforehand how many records a query has
I develop reports in ExtJS resulting of queries in SQL Server 2012.
I use Ext.Grid with paging, therefore I can use offset to limit the amount of records a query will return. But for this ExtJS ...
- 1,553
3
votes
1
answer
9k
views
Query taking non-consistent time to execute [closed]
I have a query which takes large time to execute sometimes.When i checked the speed it was taking 15 seconds.But actually it should run faster than that.When i again checked the query it again ...
- 3,131
3
votes
3
answers
1k
views
Why can't SQL Server find a seek plan? Is this a bug?
create table Test1 (Id int not null, H char, primary key (Id), index i1 unique (H))
create table Test2 (Id int not null, H char, primary key (Id), index i2 unique (H))
insert into Test1 values (1, 'A')...
- 131
3
votes
2
answers
1k
views
Joining two Mysql VIEWs takes a very long time
I have a textbox (for which I used jQuery auto-completion) in my PHP form where user can insert an actor/actress name and then by clicking "Search" button, a new window is opened showing list of ...
- 285
3
votes
1
answer
128
views
Does the Query Optimizer Prefer to Query on Constants before Columns?
I think I have found the answer for this already but I am hoping to get some additional perspective.
Assume we are JOINing two tables together on a shared column and each table then has a different ...
- 1,031
3
votes
1
answer
673
views
Why does a list of 10,000 IDs perform better in a complicated query with multiple CTEs compared to the equivalent SQL to select them?
I have a Rails application with a legacy query that I'd like to refurbish. The current implementation performs two SQL queries: one to get a large number of IDs and a second query that uses those IDs ...
- 163
2
votes
1
answer
451
views
Why would INSERT, REPLACE or UPDATE statements sometimes take over 1 second to execute?
I have a very good server (great CPU, huge RAM, NVMe) with a huge InnoDB buffer pool that's not full yet.
The website is very write-intensive, but it runs fast enough (SELECT queries well optimized, ...
- 809
2
votes
2
answers
604
views
how to find out the `Key Lookups` in the current query plan?
I am putting a query to list the key lookups that are present in the current requests being executed, I basically would like to tackle then and see if I could eliminate these key lookup from the ...
- 16.2k
2
votes
2
answers
190
views
Archiving process not running fast enough
I am archiving data from one database to another database on a different SQL server.We are archiving multiple tables in our database.Recently our inserts into the source database has increased and ...
- 23
2
votes
0
answers
198
views
How to determine the Tipping Point of which Physical Join Operator gets used to process a query?
I've been noticing some strange occurrences lately on my development SQL Server instance where the Nested Loops Physical Join Operator is used when a Hash Join would be much more efficient, within a ...
- 37.5k
2
votes
3
answers
498
views
Struggling to create an efficient index for this query
I am struggling to create an index for a query and unfortunately I cannot change the query at all as it part of an ERP system. The problem is this query has over 1m reads and sometimes a duration of ...
- 41
2
votes
3
answers
1k
views
SQL Server Actual Plan. Incorrect row estimates
While practicing in tuning queries on StackOverflow database I got stuck on the following one:
SELECT TOP 50 Id AS [User Link],
(
SELECT COUNT(*)
FROM Posts
WHERE PostTypeId = 1 AND
...
- 2,271
2
votes
1
answer
644
views
How to know what to investigate and how for a VERY slow stored procedure
I have inherited a large and slow stored procedure and it's giving me a nightmare:
I'm not a DBA, although I have some knowledge, but I don't know where to start to identify this bottleneck.
I have ...
- 857
2
votes
1
answer
1k
views
Creating a summary table for an averaging query
In relation to another post of mine regarding slow performance for an averaging query on a table with ~30 million rows, I tried creating a summary table to store the averages.
Firstly, I have a table ...
- 143
2
votes
1
answer
239
views
Speed up query in mysql
How can I speed up this query:
SELECT
( select field_a1
from tableTen a2
where week(a1.date) = week(a2.date)
and substr(a1.date,1,4) = substr(a2.date,1,4)
and a2....
- 145
2
votes
1
answer
85
views
Slow performance on averaging query
I am fairly new to DBMS and backend management, and was hoping to get some advice on the following issue.
We have a Postgres database with the following tables: members and activity_scores. The ...
- 143
2
votes
1
answer
5k
views
Using OFFSET in a large table by a simple sub-query
The slow performance for using OFFSET in large table has been widely discussed in various blogs, and the most efficient way is to use an INNER JOIN as
SELECT *
FROM table
INNER JOIN (
SELECT id
FROM ...
- 4,521
2
votes
2
answers
11k
views
How to fix slow LIMIT queries with big offsets without heavily rewriting them?
Imagine a very basic example of your average discussion board. For example:
CREATE TABLE threads (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
title VARCHAR(100),
PRIMARY KEY (id)
)
CREATE ...
- 21
2
votes
1
answer
2k
views
Select data based on records created_at column
I have two tables Keywords, ProjectReports:
http://sqlfiddle.com/#!15/06ae3/1
As you can see in that example everything works fine, but I want to get more data from those 2 tables and I don't know ...
- 353
2
votes
1
answer
164
views
Several values from different tables
I'm totally lost here and not know how to get this to work and hope any can help me here. I have several tables: default_users, default_profiles, default_status, default_comment and latest ...
- 1,764
2
votes
1
answer
3k
views
Postgres Performance Over Group by with MAX and MIN
I just want to retrieve the data for specific location of customers with their ever first and last purchases made in the system.
Table details :
CREATE TABLE customer_location ( id UUID, location_id ...
- 71
2
votes
1
answer
369
views
join to other table multiplied by loop foreign key in mysql
i have 2 table one node and relation and Second store buy each node. my code work fine but when sum multiple buy for each node result multiplied by number of purchases .
table nodes
+-------------+--...
- 149
2
votes
1
answer
1k
views
Resolving a performance issue with BETWEEN join - eager spool
This query (anonymized) takes about 2 minutes to execute.
SELECT
ly.Col1
,sr.Col2
,sr.Col3
,sr.Col4
INTO TempDb..TempLYT
FROM Tempdb..T1 ly
JOIN TempDb..T2 sr on sr.[DateTimeCol] ...
- 896
2
votes
1
answer
2k
views
Optimistic Paging and separate query for COUNT?
I am using SQL Server 2012 FETCH/OFFSET after searching a lot that this will give me the best performance. But for paging to work, I need a total count.
There are 2 ways to achieve this:
Paging ...
2
votes
1
answer
247
views
Slow Query Gets Even Slower After Indexing
I have a MySQL database with just one table containing 8 million records. I need to query the table to find a single record that matches a specific number:
select * from my_table
where
(...
- 21
2
votes
2
answers
163
views
Convert to Self JOIN
I have a correlated sub query, which is taking 16 Minutes to execute. How can I convert it to join or optimize it.
SELECT SSF.SM_StockCode, SSF.ST_ItemSize FROM Stock AS SSF
WHERE SSF....
- 9,677
2
votes
1
answer
149
views
Computational power required for this query
I am not sure if the query I am trying to run is unreasonably large, in terms of computational power. The query will identify islands (and their duration) of a certain condition in my db. Things work ...
- 225
2
votes
1
answer
66
views
Composite index works for larger set of data, but not smaller
This is a follow on question to optimise my.ini for key query
Data model:
DROP TABLE IF EXISTS `contract`;
CREATE TABLE IF NOT EXISTS `contract` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`...
- 171
2
votes
3
answers
1k
views
How to get all non deleted messages from one user
I have two tables one for messages called default_messages and the other for recipients called default_recipient. This is how SQL for those table looks like:
CREATE TABLE IF NOT EXISTS `...
- 1,764