Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
2,158
questions
0
votes
1answer
21 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
49 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
168 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
40 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
90 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
26 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
votes
0answers
23 views
Different scan count and logical read, which one is best? [closed]
I have a SQL Server table, I created an exact copy of the table (including indexes, Pk, data, etc.)
In one of the table I have created partition.
I ran the same query in both the table and the IO ...
1
vote
1answer
22 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 ...
0
votes
2answers
70 views
T-SQL Query or DB Engine Optimization? [closed]
This query takes 100 ms on avarage but when you have hundreds or thousends of calls of them every second this would cost a lot. The dataset can not be cached as it may be changed at any second.
Any ...
1
vote
1answer
109 views
Performance of Query where time captured from SP statement completed differs by large from RPC completed
I am unable to understand the performance of below query when collected data from XE events:
Query
DECLARE @Importdate DATETIME = ISNULL ((
SELECT ImportDatetime
FROM
Customertable
where CustomerID ...
0
votes
1answer
12 views
Query processor ran out of internal resources and could not produce a query plan” - MariaDB
i have some query
SELECT COUNT(loan_payment) as setcolumn
FROM table1
WHERE paydate between {d '2020-05-01'} AND {d '2020-05-31'}
and period_code = 'MONTHLY'
AND company_id =...
0
votes
0answers
23 views
Postgresql - slow distinct on million records table
I have a table with a million of records, i need to select the distinct values of log_type.
In other queries i also need to filter log_types result of the distinct and limit the result.
But the query ...
3
votes
2answers
53 views
Why is creating and dropping a random index fixing this performance problem?
I have a big heap table with 17,093,139 rows. This table is the most heavily used table in the database. Since this is a heap table, there are only non-clustered indexes in this table. I rebuild/...
2
votes
1answer
26 views
Should “Long Running with Low CPU” be expected for an SPROC that performs millions of INSERTS?
Brent Ozar's sp_BlitzCache flags the sproc below as the most "expensive" one in my system ("Frequent Execution, Long Running With Low CPU"). The sproc just checks a table for a row corresponding to a ...
1
vote
1answer
31 views
Why is my indexed DISTINCT ON so much slower than my INNER JOIN?
I have two tables, customers and purchases. There are a lot (thousands) of purchases per customer. I usually only need the most recent purchase for each customer, which is why I have the ...
1
vote
1answer
38 views
About real time statistics oracle 19c only for particular table
Reg Real-Time statistics which extends online statistics gathering(12c) to also include conventional DML statements.
Real-time statistics helps the optimizer generate more optimal plans.Whereas bulk ...
0
votes
0answers
31 views
Find all the actors that made more movies with Yash Chopra than any other director
Please can anyone help me with the query? I'm naive in SQl and I have to write this query and I need a simple query so that I can understand.
2
votes
1answer
43 views
Best indexing strategy for spatial join and ranged lookup
Context
We have two tables (not the real tables used in the examples below. They are from a toy DB im using for testing):
Incidents_2 (Columns of interest are a geom, and a reported_at (int8))
...
1
vote
2answers
100 views
Performance issues after upgrading to MySQL 8
UPDATE (tl;dr;):
I filed a bug report here: https://bugs.mysql.com/bug.php?id=99593 which has since been acknowledged and a workaround provided. See answer below for details.
Certain queries seem to ...
0
votes
1answer
32 views
problem with optimizing SQL query
I'm noob when it's come to DBMS so i wrote a query on couple of relations and i want to know what's the best way to do it because i don't feel good about my query.
Here are the relations:
CREATE ...
0
votes
0answers
56 views
SQL Server Full-Text Search Order by another Column Performance
I have two tables, Entity and EntityDetails.
Entity table has columns EntityId, Col1, Col2, Col3, LastUpdated (smalldatetime).
EntityDetails table has columns EntityId, Details1, Details2.
Where ...
1
vote
1answer
34 views
I need help resolving a retrieval problem [closed]
I have the following tables. I need to update the download.refer to show the ip_lookup.id value where (download.ADDRESS >= ip_lookup.start_ip AND download.ADDRESS <= ip_lookup.end_ip) AND download....
0
votes
1answer
21 views
Return master and master details record(s) in a single row in postgresql
I am trying to formulate a PostgreSQL query where I wanted to return all the payment table data in an array and customer data as a header in a single row.
I am writing the following query to get data ...
0
votes
1answer
31 views
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
An online optimization tool offered to add indexes. One of the index is successfully created but i cant create the second one.
Do you think that indexes going to reduce query run time?
Any ideas how ...
1
vote
1answer
82 views
Slow query time after upgrade to MySQL 8.0.20
I apologize in advance if a similar question has been asked. I've been digging through Google and StackExchange trying to find an answer but now I'm stuck and stumped. I am still relatively new in ...
2
votes
1answer
19 views
Same query on same same table, different response time
I have a development server that has some problem to access the data, user reporting that it's too much slow sometimes.
The setup is:
* virtual server;
* 4 virtual CPU;
* 8 GB of virtual memory ;
* ...
2
votes
1answer
216 views
SQL Server Query with literals runs instantly, using variables takes minutes
For Microsoft SQL Server 2017 (RTM-CU20) on Azure running an ERP system (so changing the code here is not an option).
I have a table with over 42 million rows. It's a machine operating stats ...
0
votes
1answer
31 views
Eav model sql select only products with all 3 values?
I have an EAV (Entity Attribute Value) based table for smartphones like the following:
What if the user wants to see all phones with
- red color
- steel material
The query is like:
WHERE (...
-2
votes
1answer
28 views
Clustered index scan in Delete query
I am trying to improve the performance of the delete query below. #ROW_IDS.Column_ID and Table1.ID are big integer values.Table1.ID and #ROW_IDS.Column_ID are clustered indexes. Table1 has 131 million ...
0
votes
1answer
55 views
User info retrieval takes too long - ASP.NET Membership
I have an application that has been taken from a sharepoint user control and converted to plain web pages. Everything is working fine - I can sign up and retrieve my password, etc. However, when I try ...
1
vote
1answer
23 views
How to identify hung queries, their importance, and then take action using command line?
From a production support perspective, how can we find hung queries and then find how important they are and then how to deal with them?
I tried only processlist which gives currently running queries ...
1
vote
1answer
17 views
MySQL: Queries limitations per host/user
In MySQL 5.7, is there a way to limit specific type of queries up to a certain limit? select queries for instance to be max 100 query at a time.
And is it doable to limit queries per host or user?
0
votes
1answer
45 views
Indexing a GROUP BY Select with Multiple Inequalities
I have a pair of linked issues I need help with:
Issue #1: How to Index a Table to Optimize for a "Group By" SELECT
I have a table of stock price information with the following Create statement:
...
1
vote
1answer
23 views
Query performance improvement
Can I improve performance of this query?
SELECT transact.id
FROM transaction0 transact
JOIN journ_notes journ ON transact.journal_jdoid = journ.jdoid
JOIN product ...
-2
votes
2answers
66 views
SELECT CASE WHEN - Not performing index seek [closed]
Im using SQL server 2017 and I have a query which does a select and joins from a few tables. When I look at the query plan I notice that the SELECT CASE WHEN part of the query is performing an index ...
0
votes
0answers
34 views
Optimise the selection of two distinct column pairs from the same table
I need help with rewriting or editing the query/logic to improve it's performance. SQL Server 2014
I have the table structure specified below. I want to select two column pairs:
(SourceID + ...
1
vote
1answer
24 views
Optimize JOIN query with DISTINCT
I have 2 tables, one for links (Links) and one for comments (Comments) where each comment contains a reference to a link (it can also point to NULL if it has no associated link, such comments we ...
0
votes
2answers
42 views
How to improve multiple case when query performance?
I have the following query which counts when the event_time is equal to the day of the month.
I also have indexes on the id of each table, template_name, from_email_address, event_time, event_status....
0
votes
0answers
12 views
Varchar column, record value length & performance
I have a quick question regarding SQL queries performance. The project is using WordPress and the wp_posts table comes with a post_type column that is a varchar(30).
The wp_posts records are going ...
0
votes
0answers
37 views
Performance of searching for hex strings in large tables (using LIKE, Full-Text Search, etc.)
I have a table with 40+ million rows in MS SQL Server Express 2019. One of the columns store pure hexadecimal strings (both binary and readable ASCII content). I need to search this table for rows ...
0
votes
0answers
43 views
Is there a better way to join 4 tables?
hope you're doing well.
I have 4 tables as below:
z_branchlf_user :(USER_NAME,BRANCH_CODE)
Z_BRANCH_TO_CUSTOMER :(CUS_NUM, BRANCH_CODE,REL_CODE)
z_fact_customer_branch :(EFFDATE , CUS_NUM , ...
2
votes
3answers
132 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')...
1
vote
1answer
54 views
Postgresql 11 - Storing billions of relationships in normalized table paritions or intarray[] column
Some pre information
Postgresql Version 11.2
GCE
CPU: 6cores
SSD: 250gig
RAM: 64gigs
We are currently deciding on how to store massive amounts (in the billions) of links/relationships between items ...
2
votes
1answer
77 views
Query planner enerates different plans in psql and application for the same query
I am optimising an application on a development server; the database is being dropped and restored from time to time (always the same database and data) and the caches flushed.
I want to identify ...
1
vote
1answer
38 views
MariaDB - Slow GROUP BY on 55 Million row table
I have a table with ~55 million rows, which holds user music listening history. I want to generate a report for the last quarter detailing how many times songs have been played per user. The query ...
0
votes
1answer
26 views
Which query has better performance? [closed]
I have 3 tables as follow:
class
id:bigint(PK)
name:varchar (unique)
principal:
id: bigint(PK)
pid:bigint
role:boolean
uniqueConstraint(pid, role)
entry:
cid: (FK, reference class)
...
0
votes
1answer
100 views
Calculate the duration in an interval and outside of events that can last over several days
I need to calculate the total duration of events in a given time interval and on a given day of the week with PostgreSQL 11
For an event on the same day no problem. But over several days I manage to ...
0
votes
2answers
23 views
slow UPDATE of rows by COUNTing from another table
I try to update a table by counting rows from another table as
UPDATE t1 SET c1 = (
SELECT COUNT(*) FROM t2 WHERE t2.id=t1.id GROUP BY id
)
strangely, this query stuck at Sending data state ...
3
votes
1answer
117 views
How can I improve the performance of this query on a table with millions of trigrams?
In an effort to make searching across many string fields in certain tables faster, I've been attempting to use trigrams.
I've created a separate table to hold them, and a query to search against them ...
0
votes
1answer
39 views
Understanding “bitmap heap scan” and “seq scan”
I'm trying to understand that which scan is efficient, Here is the explain for the query,
EXPLAIN SELECT *
FROM 'myTable_v' --view
WHERE cast(TIMESTAMP AS VARCHAR(100)) > CAST(1586415955169 AS ...
