SQL tuning commonly involves finding more efficient ways to process the same workload. It is possible to change the execution plan of the statement without altering the functionality to reduce the resource consumption.
Questions tagged [sql-tuning]
328 questions
0
votes
0 answers
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 some help in tuning this
select /*+…
Soumya7oct
- 13
- 5
0
votes
2 answers
index skip scan for 2 value in where clause for second column
I create an index on (SWcode,cdcardno)
and I run this query :
SELECT cd.*
FROM KS cd
where cd.cdcardno in ('6219862012953805')
ORDER BY cd.ROWID
and oracle use index skip scan and it is true . but when I run this query :
SELECT cd.*
FROM…
Mahsa ehsani
- 117
- 12
0
votes
0 answers
SQL query tuning issue
I created a functional index on "sum(coalesce(INDICATED_REVENUE,0) - coalesce(ACTUAL_REVENUE,0)) " in query below query but still its very slow , what else can I try ?
the stats are current
select
sum(coalesce(INDICATED_REVENUE,0) -…
Ahmad S
- 21
- 6
0
votes
1 answer
Oracle performance Issue
Need help query performance.
I have a table A joining to a view and it is taking 7 seconds to get the results. But when i do select query on view i get the results in 1 seconds.
I have created the indexes on the table A. But there is no improvements…
Mahi
- 73
- 1
- 9
0
votes
0 answers
Query tuning performance
I want to optimize my query, which is as follows, it returns the correct results but I feel like I'm doing something wrong or could do it in less steps, my query looks like this.
Select /*+ Parallel 8 */
Mes, Score, Solucion, Ruta, Rango_Monto,…
Zombraz
- 152
- 1
- 9
0
votes
1 answer
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 engine_usage_factors
WHERE usage_month =…
CodERORR
- 19
- 4
0
votes
1 answer
Why machine learning for index advisor?
I have a database used by a multi-tenant application. Recently we are having some performance issues with the database. I think adding some proper indexes will fix this issue. I read about Azure SQL Advisor. Microsoft says they are analyzing usage…
Deepan Cool
- 476
- 1
- 6
- 16
0
votes
1 answer
SSIS Package Execution taking long time (Not frequently)
I have a ETL job running, which is scheduled for every 5 minutes from 1 AM to 9 PM everyday. Generally it takes 10 minutes to execute that package, But unfortunately the first cycle i.e. at 1AM is taking 2 hour or 3 hours or 4 hours from last few…
joeprince
- 1
- 1
0
votes
2 answers
Optimize multiple subselects with WITH clause in Oracle
I have a query like:
select
qsn.code,
(select prs.display_name from prs where prs.id = qsn.fk_prs) display_name,
(select prs.address from prs where prs.id = qsn.fk_prs) address,
(select prs.tel from prs where prs.id = qsn.fk_prs)…
Amir Pashazadeh
- 7,170
- 3
- 39
- 69
0
votes
1 answer
MYSQL Query Performance - Searching by Distance
I have the following MYSQL query which is running on a table with around 50,000 records. The query is returning records within a 20 mile radius and i'm using a bounding box in the where clause to narrow down the records. The query is sorted by…
Mark
- 79
- 1
- 7
0
votes
1 answer
Performance tuning tips -Plsql/sql-Database
We are facing performance issue in production. Mv refersh program is running for long, almost 13 to 14 hours.
In the MV refersh program is trying to refersh 5 MV. Among that one of the MV is running for long.
Below is the MV script which is running…
Ajay Shetty
- 3
- 4
0
votes
0 answers
increase the cost of query after partition the DB
i have a query with high cost , and this cost appear after i partition my db . i don't know what make this problem . partition is on date.
the query is :
update EBSESSIONSERVICE set RESPONSETIME=:1 , DURATION=:2 where ID=:3 and…
Mahsa ehsani
- 117
- 12
0
votes
1 answer
Query optimization with 3000000 in single date oracle
Table x contains millions of rows and I have to fetch data for single date using function based index(trunc).
Single date data for eg, for 22-07-16 we have 3000000 rows. I am also using case for sum of columns. Query taking 18 sec. How I can reduce…
Aamir
- 738
- 2
- 17
- 41
0
votes
0 answers
Tune SQL query in SQL server
How would I go about tuning the following t-sql query? The execution plan looks ok (i.e it is using index seeks, index scans).
SELECT
CodeID = CL.codeID,
CodeName = PR.PetTypeCode,
DisplayName = PR.PetTypeDisplayName,
…
Ajit Goel
- 4,180
- 7
- 59
- 107
0
votes
1 answer
Postgres Query Plan keeps changing - takes a query a minute to finish sometimes and never finishes sometimes
I have huge SQL Query. Probably 15-20 tables involved.
There are 6 to 7 subqueries which are joined again.
This query most of times takes a minute to run and return 5 million records.
So even if this query is badly written, it does have query plan…
Ramanan
- 461
- 1
- 5
- 8