Questions tagged [query-performance]

For questions about improving the performance and/or efficiency of database queries.

Filter by
Sorted by
Tagged with
0
votes
0answers
7 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 ...
0
votes
0answers
4 views

Slow performance when joining a small table and filtering out on a non-key column in MariaDB

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`.`...
0
votes
1answer
56 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 ...
3
votes
2answers
800 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 ...
2
votes
0answers
33 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
0answers
26 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
70 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
53 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
187 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
41 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
91 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
24 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
74 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 ...
2
votes
2answers
131 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
14 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
25 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
54 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 ...
2
votes
1answer
38 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
51 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
54 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
44 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
102 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
33 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
24 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
40 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
84 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
20 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
217 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
33 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
19 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
45 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
133 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 ...

1
2 3 4 5
44