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
votes
2 answers
SQl Optimization: using same column twice with OR in Where clause, leads very poor performance
I have contact table, which contain Ids, name, email, delete and other columns. Have records around 5 Million.
Writing below query, then facing no issues getting results with in limits:
execution time: <2 secs.
Select row_id
from contact
where…
Pulkit
- 121
- 1
- 3
-1
votes
1 answer
Oracle performance query on explain plan
I have used explain Plan to check the SQL query performance, query output it showing full table scan so I decided to create index but in my where clause i have 4 columns from same table then how/on which columns I have to create INDEX
-1
votes
4 answers
IN clause causing high execution time
I have a 118 line query similar to this:
select * from (The inner query with many joins) WHERE campus_code IN ('560','598')
The campus_code is getting generated by a function f_get_bookstore(SSBSECT_TERM_CODE,SSBSECT_CRN) AS SSBSECT_CAMP_CODE in…
Sumit
- 856
- 5
- 18
- 38
-1
votes
1 answer
How to avoid two similar CTE definitions
I have a report which uses a dozen of CTEs and takes awfuly long to execute. Thing is that a lot of those CTEs use the same tables, joined in the same way, but on different conditions, depending on the column:
WITH
Kps_CTE (sp_id,ksd_ma_s) As
…
PacoDePaco
- 689
- 5
- 16
-1
votes
1 answer
Performance Improvements in a Java application
This is more of a design/architecture question though can easily find its way in code as well. When improving the performance of a Java application deployed on app server talking to database to retrieve records, what is the best methodology?
Improve…
Rockoder
- 746
- 2
- 11
- 22
-1
votes
1 answer
how to set up long_query_time?
I would like to know , how to set up the long_query_time as 1 minute . I want the queries that run more than 1 minute in my slow_query_log . how to do that ? And kindly provide me how to restart the sql ? is it the command MySQL service restart ;…
Kamlesh
- 1
- 3
-1
votes
2 answers
oracle 11g pivot query optimization - multiple rows to single row
I have below tables
user table
USER_ID USER_NAME
1 smith
2 clark
3 scott
4 chris
5 john
property table
P_ID PROPERTY
1 first_name
2 last_name
3 age
4 skill
user_property…
KNale
- 1
- 1
- 4
-2
votes
2 answers
SQL Multiple Joins Query-Query tuning
Please let me know the ways in which I can tune this query for better performance/execution time.
PS-it was developed by some another person and handed over to me.
the query is attached (I know it's a huge one!)--
Any idea on how to handle joins and…
Sugandha Mishra
- 69
- 7
-2
votes
1 answer
Another way to write this without the or operator for performance
This stored procedure is part of a report that im working on. So it runs very slow runs very slow 48 min to be exact. I got it to run in 6 sec by taking out the or operator in the join you see below. Now it returns not duplicates but data from both…
King Love
- 7
- 4
-2
votes
1 answer
Joining of varchar with number column for huge volumn table
I have to equate the value of organization_id(number) of table msc_system_items with the the value of lookup meaning (varchar2).
i used the to_char(msi.organization_id =(select meaning from fnd_lookup_values ........) but this is taking more time.…
Vikash
- 27
- 4
-2
votes
2 answers
Tuning a SQL query
I am trying to tune the following query which is got 500k IOs against the INVENTTABLE which I thought would be a good place to start. The complexity of all the joins has beaten me though and I have been unable to wrap round where a good start would…
Tom
- 144
- 8
-3
votes
1 answer
performance tuning - Insert
I have one UNIX script
In this we are creating the table, index and loading the date from file to this table using SQL Loader .
And doing near 70 direct update (not using for all or bulk collect) on this table.
At last we are inserting this new…
VIDHYA
- 11
- 1
- 1
-3
votes
2 answers
database perfomance tuning
There is a user table which has 10 million records,
following is the schema of the table
user (user_id, user_name, first_name, last_name, address)
when following select is run on the above table it takes 18 minutes to run the query and the entire…
user2116018
- 13
- 4