Questions tagged [query-optimization]

The tag refers to how to make queries run faster. Some of these questions may belong on DBA.stackexchange.com, especially if they involve reindexing, query plans, etc. Please use additional tags to specify your query language. By default, SQL is assumed.

-1
votes
0answers
44 views

Slow query execution on big table

My query takes about 1.8s. Is there any way how to optimize this query? This is the execution plan: The query: SELECT COALESCE("SMTAPP"."MF_MAJST"."DAT", "LPA_P1"."Datum") AS "Dat", ...
1
vote
1answer
61 views

Why given query hangs or slow down?

Usually, it executes ok but sometimes once or twice in a week, it got hanged or slowdown in Oracle. Is there a better way to optimize it? for i in (select * from process_data) loop begin select ...
0
votes
3answers
39 views

Need help to optimize this Sql Query

I want some help to optimize this SQL Query. This is working completely fine. I just want to reduce the runtime of this query select distinct o.usrp_order_number,t.* from ms_bvoip_order_extension ...
0
votes
2answers
28 views

optimize linq query with multiple include statements

The linq query takes around 20 seconds for executing on some of the data . When converted the linq to sql there are 3 nested joins that might be taking more time for execution . Can we optimize the ...
0
votes
1answer
29 views

why the extra is “using where;using index” instead of “using index”

why the extra is "using where;using index" instead of "using index". CREATE TABLE `pre_count` ( `count_id` varchar(38) NOT NULL, `cam_id` varchar(38) ...
1
vote
1answer
17 views

Hibernate 5 Native query support - Slow response generation

I am trying to fetch 1000 rows using the Hibernate createNativeQuery. The query contains two joins. When I execute the same in SQL developer I see that response comes up in less than 400 ms but from ...
1
vote
1answer
45 views

Removing specific items but only for some columns

A Provider can be connected to multiple facilities. providers and facilities are connected using the providers_facilities_pivot table. A Facility can be located_in another Facility. A Facility can be ...
2
votes
3answers
43 views

Filtering elements in a table based on relation with same table

I have a facilities table. The columns are id (primary), sector (int) and located_in (nullable, references id on same table). Most of the time, the located_in column is NULL, but in some cases, ...
0
votes
1answer
24 views

Mongodb aggregation pipeline optimization - 2 stage of $match

I am researching on performance optimization of aggregation pipeline recently. I came across this problem when deciding which to index. I set a compound index for {'receiverId': 1,'type': 1 } . My ...
0
votes
2answers
22 views

How to have optimized compound select for a range defined criteria?

I have in the code two selects, first randomly selecting any 20 sportsmen db.Query("SELECT sp_no, first_name FROM sportsmen LIMIT ?,20", rand.Intn(100000)) Then for every returned sportsmen in a ...
0
votes
1answer
34 views

MySql Query with multiple joins with huge data

I have three tables in a database Table feedback_responses resp_id(Primary) | name | mobile | pnr | message | added_on Table feedback_response_items feed_item_id | resp_id | qn_id | ans_id ...
0
votes
4answers
31 views

Optimize stored-procedure T-SQL

I need to optimize my t-sql query somehow but i do not have much experience in it. Hope on your support. The execution of it lasts to long if I pass many RetailersID into where condition. Apparently I ...
0
votes
2answers
72 views

How to optimize response time for simple web and database application

I'm new to golang and also to database optimizations. I have simple app written on go and mysql database where send queries initialised through the web. For receiving requests it take around 5s or ...
2
votes
1answer
32 views

removing join operations from a grouping query

I have a table that looks like: usr_id query_ts 12345 2019/05/13 02:06 123444 2019/05/15 04:06 123444 2019/05/16 05:06 12345 2019/05/16 02:06 12345 2019/05/15 02:06 it contains a user ID ...
6
votes
1answer
92 views

Slow query optimisation in Postgres

We have a performance issue with a specific SQL query and we're trying to figure out how could we improve here. It's execution time on is about 20 - 100 seconds! Here is the query and it's explain: ...
3
votes
3answers
65 views

Relational database (RDBMS) denormalized data

I believe this question does not address specifically MySQL - which is the database that I'm using -, and it's one about best practices. Up until now, my problems could be solved by creating tables ...
9
votes
1answer
239 views

Same execution path, data and schematics; different query times

We have noticed some inconsistencies in our MySQL performance for query times that we feel cannot be explained by just server load. Some queries seem to be much efficient than others, despite having a ...
2
votes
1answer
33 views

MySQL - Why is this slow running query not using (what seems to be) the correct index?

This query works, and does what I want it to do, but is awfully slow running. We have a table of transactions that holds an amount, a user ID, a partner ID, and a description. When a user has 3 of a ...
0
votes
1answer
20 views

SPARQL DBPedia query for seating capacity, optimize and remove duplicates

I want to get all objects with seating capacity information on DBPedia. Optionally, I want to get their label, address, lat and lon information. My issue is that I get a lot of duplicates even after ...
1
vote
1answer
44 views

Speed up Mysql queries with Hash Table in OPENSIPS

Dears, I have got an Opensips server which make queries to an mysql server. I need to optimice these queries at maximum. One way could be: using mysql table with engine=memory and index=hash, but ¿...
0
votes
1answer
26 views

Is it possible to OPTIMIZE FOR a specific table variable 'value'?

Let's presume I have the following query running on SQL Server 2016: declare @Ids as table ( Id int ) -- Insert some values into @Ids table select * from dbo.Foos where FooId in (select Id ...
0
votes
1answer
38 views

Request Times out before loading, is there any way to speed up this process of the MySql query?

So, I have tried to combine two tables, one table with over 300,000 rows and another one with about 35,000 rows. When trying to complete this query, it simply times out when I use "LEFT OUTER JOIN" ...
2
votes
1answer
44 views

SQL cross match IDs to create new cross-platform ID -> how to optimize

I have a Redshift table with two columns which shows which ID's are connected, that is, belonging to the same person. I would like to make a mapping (extra column) with a unique person ID using SQL. ...
1
vote
1answer
49 views

Optimizing Query based on Inserting into multiple columns into target table from one column of a source table

I am trying to insert values from one column of a table to multiple columns of another table based on conditions. I have prepared the query but it's getting stuck at 80% of the MapReduce phase I am ...
0
votes
2answers
59 views

What are the possible ways to optimize the below postgreSQL code?

I have written this SQL query to fetch the data from greenplum datalake. The primary table has hardy 800,000ish rows which I am joining with other table. The below query is taking insane amount of ...
0
votes
2answers
31 views

Query taking time on production

We have this query where we are trying to identify customers with multiple credit option indicators. This query output we have to reflect in our report and share to business users. We have to run this ...
1
vote
1answer
45 views

Need guidance in re-writing this query

We have this query which we run to generate the calendar week data this query hits the same view twice. and perhaps creates a cartesian product due to absence of a join ON clause. Is there anyway to ...
0
votes
1answer
76 views

Spark DataFrame / Dataset groupBy optimization via bucketBy

I'm researching options for a use-case where we store the dataset as parquet files and want to run efficient groupBy queries for a specific key later on when we read the data. I've read a bit about ...
0
votes
0answers
39 views

How to help SQL Server prepare a better plan?

Sometimes my query in SQL Server takes a few minutes, and running it again takes only a fraction of a second. I assume the query optimizer made a wrong decision the first time, but collected data ...
0
votes
0answers
25 views

Optimizing Image Hash Search Query

I've managed to develop a query that returns similar images from a database of hashes. The rows consist of a path to the image, as well as 64 columns of bits representing the image hash. The query ...
0
votes
0answers
20 views

How to optimize this sql query with xml column

I have a query with left join with result containing xml column.Need to optimize this. This query contain xml column and when running it take more than 10 mins. SELECT * into #temp from ( SELECT * ...
0
votes
0answers
23 views

Applying increasingly slow query filters depending on the number of matches

Is there a way of building a ES query so that it doesn't apply slower parts like wildcard searches or including more fields... If the number of results with the previous conditions already reaches the ...
0
votes
0answers
28 views

Elasticsearch full-text search

I'm trying to build an elasticsearch full-text search query that meets the following rules. The query should run search on all the fields in the index. Results should have all the words from the ...
4
votes
3answers
116 views

Query Optimization to reduce multiple joins statements

Here is the table: CREATE TABLE ABC ( key NUMBER(5), orders NUMBER(5), cost NUMBER(5), dat DATE ); insert into ABC (key, orders, cost, dat) values (1, 3, 5, to_date('10-11- ...
-1
votes
1answer
58 views

SQL Query Takes 9 Hours for 120K Records

I have a small query in which I need to do some simple calculations; however, it is taking forever to finish. I have used similar queries before on larger data and performance wise was way better. I ...
0
votes
2answers
52 views

Optimise mysql query with group by

I have an InnoDB table with 11 columns and around 5 million records in which I am using a query to find the top 10 records with the highest sum. The table schema is as below. id (int 11) (primary key)...
-2
votes
1answer
46 views

Postgres SQL slow when using OR statement

I have a query that take 12 seconds (journal table 1.2m rows), the root cause is below select 1 from myTable myTable join myJournal Journal on (Journal.status=0 and myTable.id = Journal....
0
votes
2answers
87 views

how can i make my query optimize to reduce the execution time

Please let me how i can reduce the query to reduce the execution time . SELECT CASE WHEN MONTH(QBQ_DATEJOUR) = 1 AND QBQ_VALAXECT1 = [AXE1] THEN (QBQ_CAHISTO+(QBQ_CAHISTO*([EV1]))...
0
votes
1answer
64 views

Optimize the SQL query for Report

I have to generate a simple Up time report for gates on railway stations. if there is any error for any gate, it is in the DB with specific reason and startTime and EndTime of outage. have few ...
1
vote
3answers
46 views

postgresql query slow avoiding using function

https://planchecker.cfapps.io/plan/Edo2MMbv EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) SELECT COUNT(*) AS "__count" FROM "juliet" WHERE ("juliet"."whiskey" IN ('F') AND "juliet"."...
0
votes
1answer
38 views

How to optimize mysql join query, Not using join

I am trying to optimize the below query it yields me the following result EXPLAIN UPDATE table1 a JOIN table2 b ON a.id=b.ref_id SET a.value=b.value; When i try to explain the query, I get the ...
0
votes
3answers
41 views

Performance difference between Insert and Insert Where Not Exists

I know its better to use INSERT WHERE NOT EXISTS than INSERT as it leads to duplicated records or unique key violation issues. But with respect to performance, will it create any big difference ? ...
0
votes
3answers
61 views

Why doesn't MySQL calculate the maximum varchar length in a column prior to creating a temp table including that columns values? [closed]

Background: For a long time I used varchar(255) for pretty much any string I knew would be under 255 char. I later learned the benefits of using varchar(20) or similar to better define my fields, and ...
-1
votes
2answers
47 views

SQL optimization GROUP BY same table, same column

I have this GROUP BY query where I need to select some records from TABLE_1 and aggregate them. The SELECTs are similar but I need to aggregate LON and BHAM separately as they are two different ...
1
vote
1answer
25 views

How to fix “Potential unnecessary eager load detected on `LogEntry.user`”

I'm creating a new Django 2.2 project with plugin nplusone(https://github.com/jmcarp/nplusone). When I try to go on /admin URL I get a next error “Potential unnecessary eager load detected on LogEntry....
0
votes
1answer
37 views

Query optimization to fetch data by joining tables along with operation and condition on jsonb fields

I have distribution_order and distribution_order_items tables with following schema. CREATE TABLE public.distribution_order ( distributionorderid bigint NOT NULL DEFAULT nextval('...
0
votes
1answer
26 views

How to create a SQL Server table made up of averages of another table that doesn't lag?

My SQL Server table is running particularly slow and I can't figure out why. This table has 7 columns, 5 of which are function scalars, which all calculate the daily averages of similar data in ...
0
votes
1answer
21 views

Django pagination query duplicated, double the time

In my current project I want to do some filtering and ordering on a queryset and show it to the user in a paginated form. This works fine, however I am not comfortable with the performance. When I use ...
1
vote
1answer
34 views

MySQL query time out while querying JSON object

The following query seems to generate an infinite loop, since it times out after 30 seconds. Running it in C++ never returns to the code. SELECT author_obj->>'$.name' FROM author WHERE ...
1
vote
1answer
34 views

Need assistance in rewriting this query

We have this query in production which runs daily It does a lot of joins and also uses window function in hive We tried to add few set options but that did not help much Structure is something like ...