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
1
vote
2 answers
Oracle SQL query improves performance on second and third execution
We are analyzing sql statements on an Oracle 12c database. We noticed that the following statement improved by running several times. How can it be explained that it improves by executing it a second and third time?
SELECT COUNT (*)
FROM asset
…
r0tt
- 379
- 3
- 20
1
vote
1 answer
Oracle 11g: For the same table different query perfomance, seems like indexes destroyed for some data
I have a problem with data querying (Select) perfomance.
in the table Receivables we have account_id which is Indexed.
When I run below script then for Account_ID=2003975183 it's OK, but for Account_ID=900025281 process is slowly.It's happend after…
Ikrom
- 474
- 4
- 10
1
vote
4 answers
Performance issues on heavy loading with MySQL
Basically, we have a big production issue where our sql queries takes more than 300 seconds before ending.
We are using Mysql as SQL server, and our tables are with MyISAM.
The problematic queries are basic queries between two tables, and with a…
gfyhser
- 164
- 1
- 1
- 11
1
vote
0 answers
Performance Tuning on Odoo Account Table
i have around 30K's of account records in an Odoo database, and it takes too long even to list those accounts. Analysing the query logs, i found that these kind of query are taking to long to finish (around 3000 ms) each...
SELECT…
1
vote
2 answers
How do I tune a query
I have a query that is running slowly. I know generally to make performance faster, limit joins, and try to use procs instead of straight queries. Due to business rules, I cannot use procs. I've already cut the number of joins as much as I can…
Pulsehead
- 5,050
- 9
- 33
- 37
1
vote
1 answer
SQL Server: Populate (Minute) Date Dimension table
I'm working on a script to populate a very simple date dimension table whose granularity is down to the minute level. This table should ultimately contain a smalldatetime representing every minute from 1/1/2000 to 12/31/2015 23:59.
Here is the…
John Russell
- 2,177
- 4
- 26
- 47
1
vote
1 answer
Merge two columns into one
I have a performance trouble with the merging of two columns into one for table size of 7m entries.
My main goal is:
merge open_time and close_time in one 'time' column
order time and account_id by DESC
check op_type column for each account_id…
Viktor M.
- 4,393
- 9
- 40
- 71
1
vote
1 answer
Performance Tuning of SQL query for DB2 Z/oS with like predicate(Pattern Match)
Please help me in tuning the performance of below query or suggest any alternate logic.
Select FNAME, MNAME, SURNAME, DOB, ADDRESS, PHONE
from INDIVIDUAL_DATA
WHERE DOB = V_DOB
AND (SURNAME = V_SURNAME
OR (SURNAME LIKE '%' || ' ' || V_SURNAME)
OR…
Gaurav verma
- 79
- 1
- 6
1
vote
1 answer
Teradata redistributing without a volatile table
I am stuck in a situation , where an impromptu report cannot be improved because IBM cognos will not support any kind of DDL including Volatile table creation in Teradata.
The report query aggregations and in lists with some 5000 values . I know…
user1874594
- 2,277
- 1
- 25
- 49
1
vote
2 answers
SQL Query is taking long time
The query below is taking long time and I am trying to tune the query but it seems to be not working .Is there anyway I can rewrite the query. Please help me in tune the query.
SELECT DISTINCT TC.V_ID,A.ID,A.NAME
FROM OM_A_DATA TC
…
MKN
- 497
- 3
- 8
- 24
1
vote
0 answers
Teradata SQL Optimization : NOT IN ( List ) , Col <> and IN LIST Optimization
I have queries with a LOT of these situations
Sel TB1.C1
TB2.C2,
TB3.C4
Tb5.C5
where < Join conditions involving all tables TB1 through TB4 .
Most are inner some are LOJ >
where TB2.C2 NOT In ( List )
OR TB3.C5 <> 'string'
OR Tb5.C8 NOT IN…
user1874594
- 2,277
- 1
- 25
- 49
1
vote
0 answers
Teradata SQL tuning with Sum and other aggregate functions
I have a query like
sel
tb1.col1,
tb4.col2,
(case WHEN t4.col4 in () then T4.Col7
Else "Flag" ) as "Dcol1",
Sum ( tb3.col1),
sum (tb3.col2 ),
sum (tb2.col4)
etc
from
tb1 left outer join tb2 LOJ…
user1874594
- 2,277
- 1
- 25
- 49
1
vote
0 answers
Teradata - Listing objects involved in a query
Is there a SQL statement that will let me list out all the objects ( tables views SP's macros ) that are involved in a Teradata SQL Query- much like show select * from WITHOUT expanding their DDL's
This would be the same as equivalent of what…
user1874594
- 2,277
- 1
- 25
- 49
1
vote
1 answer
Query optimization in SQL Server
SELECT
T2.Entity1Id, T1.Entity1Id
FROM
T1
FULL OUTER JOIN
T2 ON T1.c2 = T2.c2 AND T1.c1 = T2.c1 AND T1.c3 = 1
WHERE
((T1.c1 = 123 ) OR (T2.c1 = 123))
AND (T1.c3 = 1 OR T1.c3 IS NULL)
Above query is taking 12 seconds in…
Sparrow
- 355
- 4
- 19
1
vote
0 answers
Tuning SQL Query which returns REF CURSOR
I am using reference cursor in function to return values. This function is getting called recursively for different input values.
My package Definition is as follows.
create or replace PACKAGE AI_QUERY_EXECUTION_PKG IS
TYPE…
Shriraj
- 133
- 4
- 11