Questions tagged [query-tuning]

The tag has no usage guidance.

0
votes
0answers
16 views

Oracle tune query by using of temp table

I want to remove redundant call for count in the below mentioned stored procedure and rather want to use a temp table and use the temp table to return in cursor procedure status(p_order_id in ...
0
votes
0answers
14 views

How to tune below query

I am trying to tune the below piece of code but not able to get positive results. I have tried some indexes and hints . Below is the query which is taking lot of time . I need to tune this query. I ...
1
vote
2answers
35 views

Error logging with MERGE query in Oracle 11

I am using Oracle 11 and I have millions of records in my table. I am using a MERGE statement to update records from source table to target table. At any moment while updating these millions of ...
0
votes
0answers
6 views

How to increase number of responds nginx

I have small script that handles json rpc requests. And I want to make sure, I can increase respond per second by configuring nginx. Script receives and handles requests in special port (e.g. port ...
1
vote
1answer
31 views

Query Tuning and rewrite - SQL Server

Could you help to optimize the below query to perform better? Can I reduce the cost? SELECT this_.id AS id1_20_0_, this_.version AS version2_20_0_, this_.domain ...
1
vote
1answer
18 views

hive analyze query taking lot of time

In order to speed up ETL queries on large tables, we run many analyze queries on these tables and date columns in the evening. but these analyze queries on columns take lot of memory and time. we are ...
1
vote
2answers
53 views

selecting from a view is taking longer than 30+ minutes

I am working on making this view fast enough to fetch the result set in reasonable time which is at the moment taking more than 30+ minutes, going parallel and causing all sorts of pain with increased ...
0
votes
1answer
20 views

need help in re-writing this query, which uses same data set multiple times, as per explain plan

We have a query run by our development team that's heavy in resources and looking at the explain plan, it looks like its uses the same data set multiple times. Is there anyway we can re-write this ...
0
votes
1answer
96 views

SQL Query takes a long time when filtering recent rows

I have this SQL query, but I've found that it can take up to 11 seconds to run. I'm really confused because when I change the date selection to a 2018 date, it returns instantly. Here's the query: ...
0
votes
1answer
38 views

User session stuck in killed\rollback state

One of the session that is executing a stored proc from an application is stuck in the killed\rollback phase. Arguably, it shouldn't be this long for the sproc to rollback and it has been stuck there ...
0
votes
1answer
18 views

Is it safe to run explain <insert query in hive> on production database

We have many hive queries that take lot of time on production most of them are insert queries. want to check the execution plan and table related data so we are thinking of checking the plan of this ...
0
votes
1answer
31 views

Most efficient way to filter a table with 1 to many parent and child mappings

I have a client table which with a foreign key to itself where each client has a specific id in each department but one master id. I am trying to find the most efficient way to restrict my query to ...
0
votes
0answers
19 views

Oracle Subquery Factoring Not Working For Function Call

I'm using Oracle 11g. I have a query that takes 6 seconds to execute select * from tab1 where exists (select 1 from tab2 where tab2.col1 = tab1.col1 and tab2....
1
vote
2answers
62 views

Expanded tree cache full error need to tune the query

Description: $enumValues will have sequence of strings that I have to look into $assetSubGroup will have a element value from XML (for loop) i.e string that I have to match in above maintained ...
1
vote
0answers
29 views

Slow PostgreSQL query even after index scan

I am running below PostgreSQL query select * from (Select * from person where lastmodifieddate >='2018-06-20' and lastmodifieddate <='2018-06-25')p left join persondetail pd on p.personid=...
0
votes
0answers
39 views

Postgres autovacuum - taken on the fly

I have question regarding postgres autovacum - 1.) Does vacuum/autovacuum are resumable once we kill the session (and does it apply for both normal vacuum and wraparound vacuum). 2.) And does ...
1
vote
0answers
42 views

Explain plan for mysql performance. Query taking more than 8hrs and still executing [duplicate]

I read various blogs and documents online but just wanted to know how i can optimize the query. I am unable to decide if we have to rewrite the query or add indexes in order to optimize. I tried ...
1
vote
1answer
161 views

Explain plan in mysql performance using Using temporary; Using filesort ; Using index condition

I read various blogs and documents online but just wanted to know how i can optimize the query. I am unable to decide if we have to rewrite the query or add indexes in order to optimize. Adding ...
0
votes
0answers
25 views

performance intensive query needs tuning in hive

I am not that good in very long queries and need your assistance in tuning this long query. Is there anyway we can optimize the below query which is resource hungry. will clustering help here? the ...
3
votes
3answers
86 views

Is there an alternative to SET STATISTICS TIME which also shows the statements?

SET STATISTICS TIME statement is only useful while developing as with it one can performance tune additional statement being added to the query or UDF/SP being worked on. However when one has to ...
1
vote
1answer
45 views

SQL Server - Efficient generation of dates in a range

Using SQL Server 2016. I have a stored procedure that produces a list of options against a range of dates. Carriage options against days for clarity but unimportant to the specifics here. The first ...
0
votes
1answer
111 views

Key Lookup using columns outside of the index in SQL Query

I have a query as follows SELECT ActivityId, AnotherId, PersonId, StartTime AS MyAlias FROM Activity WHERE DeletedStatus='Active' AND StartTime>='2018-02-01'AND ...
0
votes
1answer
59 views

Indexes for a large MYSQL table

hope you will allow me to pick your brains so I can gain some knowledge in the process. We have 3 tables - data_product, data_issuer, data_accountbalance CREATE TABLE `data_issuer` ( `id` int(11) NOT ...
1
vote
2answers
52 views

Index partial values in Oracle

I am creating a composite index. create index I on TEST (A,B); And my query is like select * from TEST where A=:1 and B IS NOT NULL Above query will only return few rows ( < 10) but my leading ...
1
vote
1answer
484 views

Searching 13 million records using full text search with additional conditions

Performance issue while doing SQL Server full text search with additional conditions. (SQL Server 2012) I am trying to filter the data based on search filters list (table value parameter), which will ...
1
vote
0answers
50 views

SQL Server Fix Correlated Sub-Queries for Better Performance?

SQL Server 2014 I am trying to learn better SQL code practices to improve performance. I am inheriting some old code, and wanted to get an idea of how someone who actually knows what they're doing ...
-3
votes
2answers
111 views

How can I speed up my Moodle mysql query with multiple self joins and index

I have created a query with multiple joins for data fetching from multiple tables. For 5000+ records it is working very slow. I had checked and indexes are properly set up for each table used in a ...
0
votes
0answers
103 views

how to improve the performance UNION ALL WITH ORDER BY

table A has billions of data and table B have very less amount of records. we are fetching union all of A and B using UNION ALL. when we do sort by on top of view query, it took a long time to fetch ...
-2
votes
2answers
175 views

Performance issue using while loop in insert statement

I am using a try catch block to catch the data with constrain errors. eg. if null inserted in not null column or if duplicated record inserted or the type mismatch occurs, all the source records with ...
2
votes
2answers
364 views

Date Parameter causing Clustered Index Scan

I have the following query DECLARE @StartDate DATE ='2017-09-22' DECLARE @EndDate DATE ='2017-09-23' SELECT a.col1, a.col2, b.col1, b.col2, b.col3, a.col3 FROM ...
0
votes
2answers
73 views

SQL Server investigating time out error

I've been tasked with investigating timeout error for an existing ETL. I would like to access logs of previous ETL run to identify where the timeout occurs. The ETL sits on Azure and one task keeps ...
0
votes
1answer
72 views

Oracle database help optimizing LIKE searches

I am on Oracle 11g and we have these 3 core tables: Customer - CUSTOMERID|DOB CustomerName - CUSTOMERNAMEID|CustomerID|FNAME|LNAME Address - ADDRESSID|CUSTOMERID|STREET|CITY|STATE|POSTALCODE I have ...
0
votes
0answers
62 views

Sql Server - BIG DATA (horizontal scaling table)

everyone. I have a table called "Files" that has almost 100 million of rows. My question is how can I improve my performance when try to search into this table. Actually, I'm using "FullText Index" ...
-2
votes
1answer
55 views

SQL Performance Issue : Timing Out

Following SQL query is taking more than 5:19 minutes to execute in Production. This SQL is dynamically generated from server side C# code and takes long time to execute for few clients. Due to long ...
1
vote
2answers
59 views

MarkLogic: Understanding searchable and unsearchable queries?

I have the following expression: let $q1 := cts:element-range-query(xs:QName("ts:week"), ">=" ,xs:date("2009-04-25")) return cts:search(fn:doc(), $q1, "unfiltered") I did a xdmp:plan, and got to ...
0
votes
1answer
42 views

Using xdmp:plan in MarkLogic

I wanted to compare two queries: 1) xdmp:plan(fn:distinct-values(/ts:top-song/ts:genres/ts:genre/text(), "http://marklogic.com/collation/en/S1/AS/T00BB")) 2) declare variable $options := <...
1
vote
2answers
49 views

How to increase Simple T-SQL query Performance - taking long hours to complete. Please…!

I have the following simple query with 2 table joins and a WHERE clause. I already indexed the key joining fields as well as the WHERE clause field. These 2 tables have about 123 million rows which ...
0
votes
0answers
277 views

Performance of inner join on two tables with large data

JOIN query on below table which contains large amount of data. This executes very slow with java jdbc and jpa. Use case UI has an option to select list of traders,category and date range.If i select ...
0
votes
0answers
86 views

Optimizing the hive query :Apache Hive

The following hive query which finds the lead and lag on a single column. The query spawns 1 Mapper and 50 Reducers. How can i optimize the query to spawn less reduces. Table description col_name ...
0
votes
0answers
50 views

Query Performance Teradata

SELECT SBSCRPN_KEY, DAILY_DT_KEY, BAL_EXPRY_DT_KEY, RANK() OVER(PARTITION BY SBSCRPN_KEY ORDER BY DAILY_DT_KEY DESC) AS ROW_NUM FROM DT_SDMVW.FCT_SBSCRPN_BAL_DAILY WHERE DAILY_DT_KEY <='...
0
votes
1answer
124 views

Tuning in large queries in SQL Server

I am new to SQL Server, though have spent sufficient time in Oracle databases. In the current application I am managing contains a lot of denormalized staging tables to receive upstream data. Views ...
0
votes
0answers
94 views

Need help in tuning Group by cube with 6 columns

I want to aggregate data from table1 in all possible combinations of the 6 columns. Table1 has 60Million rows in it. The query is running for more than an hour and still not fetching any data. Need ...
2
votes
2answers
313 views

Tuning a query to parse XML data on SQL Server 2014

I have a table on a SQL Server 2014 database that stores auditing information for record changes in a VARCHAR(MAX) column (poor man's CDC.) This data is in following format: <span class="...
0
votes
1answer
39 views

How can I optimize a loop, which is calling an object in Oracle?

Below is the code which needs optimization. for i in 1 .. p_in_util_data_list(j).factlist.count LOOP SELECT count(*) INTO v_non_factor_exists FROM ...
0
votes
1answer
14 views

suggest a alternate query to improve the performance?

SELECT B.tblBooking_id, F.M_Merged_CustomerId, B.depart_date, B.cancellation, B.booking_num, B.end_date, B.date_booked, B.booking_price, B.tblBooking_Id as SOURCE_RES_ID , B.agentid, B.is_insert_date, ...
0
votes
1answer
47 views

Using indexes with where and join clauses

In a lecture note which I use to learn about database systems, I saw this sentence. All columns involved in indexes should appear on WHERE and JOIN clauses on the same sequence they appear on index....
0
votes
2answers
65 views

Restructure Inline view

SELECT MAX(column1) FROM table1 B , table2 A, table3 H WHERE B.unit=A.unit AND B.value=A.value AND B.unit=H.unit AND B.value=H.value AND A.number=1234 Can someone help me to restructure this query in ...
1
vote
1answer
320 views

Mysql intersection query performance

I am quite new to mysql. I have 2 identical mysql tables which have 50K rows (70 columns) each. Those tables are updated everyday by a datafeed. I need to execute some nested queries like ...
4
votes
1answer
301 views

Cypher: Use WHERE clause or MATCH property definition for exact match?

In Neo4j (version 3.0), the following queries return the same results: 1. MATCH (a:Label) WHERE a.property ="Something" RETURN a 2. MATCH (a:Label {property: "Something"}) RETURN a While playing ...
0
votes
3answers
677 views

How to Performance tune a query that has Between statement for range of dates

I am working on performance tuning all the slow running queries. I am new to Oracle have been using sql server for a while. Can someone help me tune the query to make it run faster. Select distinct ...

http://mssss.yulina-kosm.ru