Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
2,199
questions
3
votes
3answers
146 views
Is a join faster with 5 tables joined together Or 5 separate queries?
I have confusion that I already have in my application 5 tables say a,b,c,d,e and have relationship with each other like a.b_id is primary key of b and chain goes on.
Now my question is that which ...
1
vote
1answer
19 views
Indexing a date column in MySQL doesn't work if a date function is used
I have a table with the following schema
table_name
id int
task_id int
completed_date datetime (INDEX IX_TBL_NAME_COMPLETED_DATE)
I run EXPLAIN on this query
EXPLAIN
SELECT
*
FROM ...
0
votes
1answer
25 views
SQL query speedup/optimization
I am using SQL Server 2008 R2.I have tried to improve the following query in different methods.
Method 1:
DBCC DROPCLEANBUFFERS
SET STATISTICS TIME ON
SELECT count(*) FROM [prglog].dbo.[errorlog] ...
0
votes
0answers
12 views
Optimal selection of predefined non-materialised data
Take the following result set as an example:
Store Category Item Cost Stock Sold Ordered
ABC Accessories Lock 344.31 281 141 225
DEF ...
0
votes
1answer
20 views
Wrong index been peeked
I have an index that filters 99% of the table i.e. ix_magic_composite (for that query arguments).
When I add another or filter it chooses the wrong index, i.e. fTS even if I create an index that ...
1
vote
0answers
29 views
Badly-formed histogram causes bad estimates on Nested Loop
We have a query on a SQL Server 2016 Enterprise where the Query Optimizer estimates that only 1 row would come out of the Nested Loop operator, in reality 104989 rows came back. This caused that the ...
2
votes
3answers
400 views
Need help to understand tuning up a slow SQL server query [closed]
For one of our databases the query like below is extremely slow.
Due to security reasons I am not able to share the actual query or plan but just to give an idea how query is written is below
SELECT [...
0
votes
0answers
16 views
The difference between using parallelism “/*+parallel(4)*/” in the subquery or the outer query in terms of performance in Oracle
I have the below query :
select /*+parallel(4)*/ ---> First Location
<Desc_1> Amount,
<Desc_2> Amount,
<Desc_3> Amount
from (select /*+parallel(4)*/ ---> ...
-1
votes
0answers
49 views
sql server database level performance slow in production? [closed]
We used below queries to monitor the performance:
SELECT session_id, wait_duration_ms, wait_type, blocking_session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id <> 0
and
SELECT * ...
0
votes
2answers
53 views
Why 2 Table Scans of Same Table Have Huge Differences in Elapsed Time SQL Server
This is driving me bananas.
According to both the Execution Plan AND Statistics IO, both queries are doing a full table scan and nothing more, but in one case the query takes LESS THAN ONE SECOND and ...
0
votes
1answer
23 views
SQL Server Slowest Query is NULL
I am looking at both the SQL Server expensive queries report and the query below, but both are showing this mysterious NULL query as the slowest query on my server.
Is there any way I can find out ...
1
vote
1answer
26 views
Query performance degraded after upgrading Postgres
I have a table with several million records in a PostgreSQL 12 database, and after an upgrade from 11 to 12, a few queries started performing horribly. They went from taking ~1 second to ~5 minutes. I ...
0
votes
2answers
46 views
How to search a table with 80 million records faster?
I have a table with about 80 million records, I want to find all the activities of lists and workspaces that a user has access to. So first, I get the ids of the lists and workspaces and then I run ...
0
votes
0answers
26 views
Index Spool (Eager Spool), Difference in number of row before and after the operator
I was optimizing one of the Stored Procedure for which I went through the execution plan of it.
In the execution plan I see the operator Index Spool (Eager Spool).
There I see it is taking 1759 rows ...
0
votes
0answers
21 views
Will a function which wraps a query criteria be a bottleneck?
I've been attempting to make the following query perform better lately :
SELECT
BIN_TO_UUID(album_id) AS e,
firstdateofinterval AS p,
BIN_TO_UUID(territory_id) AS t,
SUM(playbacks) AS ...
0
votes
0answers
19 views
After upgrading from Postgres 9.2.4 to Postgres 10.5 query is 170x slower
We recently upgraded our Postgres database from 9.2 to 10.5.
We noticed a somewhat odd behavior with a query that under the same circumstances became 170x slower.
The query does a join between 2 ...
1
vote
0answers
32 views
How statistics play a major role in legacy CE vs new CE
We have quite some issues going on for one of our SQL server 2014 SP3 after compatibility change to 120 from 100.
Yes i am aware of few hints to make use of legacy CE and have implemented as success. ...
1
vote
1answer
26 views
PostgreSQL incorrectly using primary key index for min/max query
I have a table with the structure similar to this:
CREATE TABLE employees (
id bigserial NOT NULL,
name_id uuid NOT NULL,
department uuid NOT NULL,
details text NULL,
deleted bool ...
1
vote
1answer
25 views
Wrong estimated row count
PostgreSQL 11
Running EXPLAIN returns a row estimate that is way too low compared to the actual row count.
Given the query
SELECT s.offer_id,
category_id, category_name_de, category_name_fr, ...
1
vote
0answers
22 views
MySQL - storing 162 million rows in 1 table as the result of an IFNULL query
I'm working on a legacy web application which uses a MySQL (5.5.65-MariaDB) database which is hosted on a server running CentOS Linux 7.7.1908.
The application is a database of chemical substances and ...
0
votes
1answer
24 views
Bad estimates when sorting, good estimates without sort
I'm struggling with the tuning of one of the queries on our production SQL Server 2016 SP2.
I needed to anonymize the query due to security policies.
The query is like this:
SELECT TOP 1000 Object1....
0
votes
0answers
24 views
Slow query using SQL Server difference (soundex) function
We've got a nasty query that is always doing a clustered index scan even if I add in a non-clustered index with the Forename and Surname columns.
Query takes 2-3 seconds on a 2 million row table and ...
0
votes
0answers
9 views
Sql combination matching criteria - The icrecream kiosk issue
Let's create this case
Icecream type | Available |Normally eats (person) | Price
Small cup 2 1 1.50
Medium cup 0 1.5 2.00
...
0
votes
1answer
32 views
Postgres group and aggregate(sum) JSONb arrays and non JSONb properties
Version
PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc, a 224fe214a p 3971489d3e, 64-bit
I am trying to build a query which (distinctly) groups values from JSONb columns, and also sums values ...
0
votes
1answer
33 views
PostgreSQL server running very slow at minimal work load
Postgres server running very slow. DB has two tables and when I run explain analyze for below query, taking more than 10min and query running forever.
Tables DDL, db<>fiddle.
User query:
...
0
votes
1answer
25 views
how to write queries properly inside of a postgres RLS policy
I'm trying to do a single implicit JOIN inside of an RLS expression. I get a syntax error and have a "hacky" solution using a subquery, but would love to know if there is a better way.
So assuming ...
2
votes
1answer
76 views
Improve COUNT, JOIN and GROUP BY query performance
I am trying to get the count per group for a given user and wrote the query below. The execution plan looks fine. However, in some cases, the user has around 10k articles and with a bit of load the ...
1
vote
1answer
62 views
Query cycles between suspended and runnable without ever completing
I have an insert statement, extracting data through multiple inner joins from around 8 tables. It is getting into suspended state then runnable and vice versa forever; never coming back to running ...
2
votes
1answer
43 views
Testing query speed in two different RDBMS
I'm planning to compare the speed of queries in PostgreSQL and SQL server (on the same datasets). I was planning to run the same queries multiple times in both databases and use average response time ...
0
votes
0answers
19 views
MySQL InnoDB Weird Query Performance
I designed two tables, both using InnoDB.
The first one has columns "Offset, Size, and ColumnToBeIndexed" with BIGINT "Offset" being the primary key, and the second one has columns "OffsetAndSize, ...
0
votes
1answer
41 views
left join query running too long
I have two tables, test1 and test2, and these tables have around 600M(70GB) and 10k(2MB) records, respectively. when I run a query with left join on site_id, it is taking too long, even though site_id ...
0
votes
1answer
26 views
Slow query when grouping and ordering
I have a query that is finding the latest documents for a user that are associated with another user having a high contact score.
The documents table has 1.2M records and the ...
0
votes
0answers
37 views
“INNER JOIN” vs “INTERSECT” performance comparison when dealing with huge amounts of data in Oracle
I have two tables with the structure below:
create table Table_One
(Ef_date Date,
Customer_Num Number,
<Other_Columns> <Combination of Number/varchar2(50)>);
create ...
0
votes
1answer
29 views
How can I speed up my count queries? [closed]
Right now I am creating counts for each hour for the whole week. I am using python to connect to the database, and I am looping through each hour so str(i) is the current hour that is being updated.
...
2
votes
1answer
61 views
How to optimize Merge Anti Join?
While trying to find the fastest way to do a bulk update of a large table, I came up with the following plan:
having main_table (potentially above 1000 mln rows) and update table up to a few million ...
1
vote
0answers
16 views
Calculate total running time of pods using two date/timestamp columns
I am currently storing all the pods in PostgreSQL db. I track each pods creation and deletion through created_time and deleted_time column. When pod is not deleted, its deleted_time columns remains ...
1
vote
3answers
107 views
Slow performance when joining a small table and filtering out on a non-key column in MariaDB (MySQL)
I am fairly new to MariaDB and I am struggling with one issue that I cannot get to the bottom of it. This is the query:
SELECT SQL_NO_CACHE STRAIGHT_JOIN
`c`.`Name` AS `CategoryName`,
`c`.`...
2
votes
1answer
134 views
SQL Server - Prevent Clustered Index Scan on a specific table
My database contains a specific table that is quite large (250+M rows, 100+GB data space).
Generating a clustered index scan on this table is always a bad idea.
This table has multiple indexes and ...
-1
votes
0answers
9 views
Same update, same plan, different reads and duration
I would like your help with a problem i am having, i have a test database (i'm the only one using it), i was asked to run a 10000 contract automatic invoice batch, i used SQL profile to see the ...
4
votes
3answers
1k views
Is there any way I can speed up this large full-table query?
I have a query that selects from only one table and with one WHERE filter. However it takes a very long time to execute and even times out occasionally. This is likely because it is filtering about 4 ...
3
votes
2answers
153 views
Changing database compatibility from legacy CE to 120
Just seeking an expert/practical advise from DBA point of view where one of our application DB running on SQL 2014 after migration had old DB compatibility level i.e 100.(SQL2008)
From DEV point of ...
0
votes
1answer
32 views
SQL Select query is taking so long time on new Server
I am migrating our live website server from rackspace to AWS. We have already done all parts but stuck at below point:
One of query which is running within a second on live server, it's taking around ...
0
votes
0answers
14 views
Save multiple dynamic filter for aggregation in Postgresql
What is the most efficient way to store multiple dynamic filters which can be created by users on given data and us the filter for aggregating data?
Lets assume I have a table called t_filter which ...
0
votes
5answers
72 views
Why does this mysql query take so long?
I have a MySQL server (ver 5.6) that is well equipped with hardware (Linux, in AWS, 32GB RAM, 56 CPUs), and according to top and iotop, it isn't even feeling warm; yet this query takes something like ...
1
vote
0answers
54 views
My question is in a select statement how do you return the RID along with the table data?
I need your help with a concept I want to show developers, that concept is to help them understand how SQL Server retrieves data from a table.
In the event that a developer does not use a clustered ...
3
votes
3answers
194 views
An alternative to querying with multiple JOIN over the same table?
I have a Postgresql 11 database.
Let's say I have a table called houses. It should have hundreds of thousands of records.
CREATE TABLE houses (
pkid serial primary key,
address varchar(255) NOT ...
1
vote
1answer
42 views
How to prevent seek pagination from doing sequential scan over entire table
I have 3 tables and a materialized view:
resource_categories contains all category names and metadata
create table if not exists resource_categories (
category_id INT,
title VARCHAR(255),
...
1
vote
2answers
97 views
How can I optimize and speed up a query [closed]
Below query never completes and runs for over 14 hours:
SELECT
U.Age, U.CreationDate, U.DisplayName,
P.AcceptedAnswerId, P.ClosedDate, p .Title, P.ViewCount,
C.CreationDate,C.Score ,C.UserId
FROM ...
0
votes
1answer
28 views
Is there any performance benefit to minifying MySQL queries?
I recently ran across some tools that help you minify queries, and I'm curious to know if that's actually something that could help performance.
1
vote
1answer
25 views
Create 7 day rolling average in PostGIS for each feature
I have a timeseries dataset that looks like this:
uid | geom | date | count |
-------------------------------------
1 | FeatureA | 2016-02-01 | 1 |
2 | FeatureA | 2016-02-02 | 2 ...
