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
2
votes
1 answer
Adding a join condition in the from clause *and* where clause makes query faster. Why?
I'm tuning a query for a large transactional financial system. I've noticed that including a join condition in the where clause as well as the from clause makes the query run significantly faster than either of the two individually. I note that the…
Robert Bain
- 9,113
- 8
- 44
- 63
2
votes
1 answer
Oracle SQL Tracing on other sessions
I am studying Oracle database. I have a question.
That is tracing SQL on other sessions.
Here is my work:
SYS>
-- Get sid and serial of session which I'm gonna analyze
select sid, serial# from v$session where username = 'DEV';
-- Activate SQL…
user5685187
2
votes
1 answer
Oracle SQL Tuning Advisor Accepting Recommendation
I ran the Tuning Advisor on a query in Oracle SQL Developer. Under the SQL Profile tab there were two suggestions and I wanted to accept the first.
execute dbms_sqltune.accept_sql_profile(task_name => 'staName',
task_owner => 'DATA',…
Kara
- 111
- 1
- 4
2
votes
2 answers
How do I build effecient SQL filters?
After taking an advanced T-SQL performance/query tuning class, something that I thought I remembered hearing was that you can speed up some queries just a little bit if you put your date(time) filters first.
Ex:
WHERE
RunDate = '12/1/2015' AND
…
gh0st
- 1,653
- 3
- 27
- 59
2
votes
3 answers
SQL subquery causing overall query to go slow
The below query takes several minutes(never completes sometimes) to execute. I'm using MySQL database.
select
customer_name as cust,
SUM(num_visits) AS visits
from
visit_history
where
category =…
usert4jju7
- 1,653
- 3
- 27
- 59
2
votes
2 answers
SQL Server query: Union vs Distinct union all performance
Does SQL have a difference in performance between these two statements?
SELECT distinct 'A' as TableName, Col1, Col2, Col3 FROM A
UNION ALL
SELECT distinct 'B' as TableName, Col1, Col2, Col3 from B
versus
SELECT 'A' as TableName, Col1, Col2,…
ChristopherBass
- 301
- 4
- 17
2
votes
3 answers
why does the optimizer choose the higher cost execution plan?
This is a re-occuring Problem for me. I have statements that work well for a while and after a while the optimizer decides to choose another execution plan. This even happens for when I query for exactly one (composite) primary key.
When I look up…
EasterBunnyBugSmasher
- 1,507
- 2
- 15
- 34
2
votes
1 answer
Explain Plan Fails to Use Index
We have a query written in Oracle that has been adapted to run in PostgreSQL. The logic is identical, as are the contents of the tables, but the Oracle query runs significantly faster. The table structures match across the two databases.
In…
Hambone
- 15,600
- 8
- 46
- 69
2
votes
3 answers
teradata SQL tuning pundits - SELECT Failed. 3771: Illegal expression in WHEN clause of CASE expression
I am using a statement as below and get this error:
SELECT Failed. 3771: Illegal expression in WHEN clause of CASE
expression.
I had better hopes from Teradata. SQL Server can do it but Teradata can't.
How can I work around this? Any…
user1874594
- 2,277
- 1
- 25
- 49
2
votes
2 answers
Simple condition break down query optimizer and its performance
I have a simple query:
select top 10 *
FROM Revision2UploadLocations r2l
inner join Revisions r on r2l.RevisionId = r.Id
INNER JOIN [Databases] [D] on [R].[DatabaseId] = [D].[Id]
INNER JOIN [SqlServers] [S] on [D].[InstanceId] = [S].[Id]
where…
Yuriy Gavrishov
- 4,341
- 2
- 17
- 29
2
votes
2 answers
Tuning SQL statement performance
I have two queries that return same results:
1.
SELECT DISTINCT
cvc.object_id
, cvc.object_name
FROM ems.ibo_sm_cvc_rfs cvc,
ems.ibo_alcatel_mse_locale poi,
ems.ibo_nbn_csa csa,
…
Andrei Maieras
- 696
- 5
- 15
- 34
2
votes
2 answers
SQL Server 2012: Sort operator causing tempdb spill
I have a T-SQL query (SQL Server 2012) that does the job, but when I look at the execution plan, I see a sort operator with a warning: "Operator used tempdb to spill data during execution with spill level 1."
The reading I have done so far suggests…
user1043535
2
votes
4 answers
Why is Oracle using a skip scan for this query?
Here's the tkprof output for a query that's running extremely slowly (WARNING: it's long :-) ):
SELECT mbr_comment_idn, mbr_crt_dt, mbr_data_source, mbr_dol_bl_rmo_ind, mbr_dxcg_ctl_member, mbr_employment_start_dt, mbr_employment_term_dt,…
Jason Baker
- 192,085
- 135
- 376
- 510
2
votes
2 answers
Tuning/Rewriting sql query with many left outer joins and heavy tables
I have four - five tables which are really big in size and they are left outer joined using the below query. Is there any way that it can be rewritten so that the performance could be improved?
SELECT t1.id,
MIN(t5.date) AS first_pri_date,
…
Mamtha
- 29
- 1
- 3
2
votes
2 answers
Deleting records in table contains 180 Million in oracle
Friends,
I have order tables which have minimum 100 Million records in each table. We have a job running which invokes a stored procedure which deletes atleast 50K (MIN) and 200K (MAX) records per day.
I'm currently using SQL BULK COLLECT to delete…
Prabhu
- 115
- 9