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
2 answers
High performance query to get datetime when value was last changed
The data I am working with
Consider the following 2 database tables:
CREATE TABLE [dbo].[Contact](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Contact_UID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Contact_Contact_UID] DEFAULT…

musefan
- 47,875
- 21
- 135
- 185
2
votes
1 answer
Is sorting happens in the DB Buffer cache or PGA in Oracle?
When the data is read from the discs or buffer cache and a sort is needed, is this sort done in the buffer cache(SGA) or PGA? If it is done in both, what is the difference between these sorts? And when & why it is done in the buffer cache or…

oramas
- 881
- 7
- 12
2
votes
1 answer
Does Redo log have the details of the changes made but yet to be written in the database?
Does the Redo log have the details of the changes made but yet to be written in the database?

general46
- 800
- 6
- 16
2
votes
2 answers
SQL left join on maximum date
I have two tables: contracts and contract_descriptions.
On contract_descriptions there is a column named contract_id which is equal on contracts table records.
I am trying to join the latest record on contract_descriptions:
SELECT *
FROM contracts…

Ayxan Əmiraslanlı
- 423
- 5
- 16
2
votes
0 answers
SQL Server overestimates execution plan, grants GBs of RAM but query actually uses only MBs
We manage almost a dozen of SQL Server 2016 Enterprise instances and this happens in all of them: overstimation of required memory for queries. Real overestimation, at the GBs level.
Most servers have either 512 or 256 GB of RAM, so from what I…

rofz
- 95
- 8
2
votes
1 answer
What's the difference between BITMAP MERGE and BITMAP OR in Oracle?
When I check the documentation, I saw that, the BITMAP MERGE performs an OR operation between the bitmaps. So, why there is also a BITMAP OR then? Or, what are the differences between them?
Bests

oramas
- 881
- 7
- 12
2
votes
4 answers
SQL SELECT clause tuning
Why does the sql query execute faster if I use the actual column names in the SELECT statement instead of SELECT *?

Liran Ben Yehuda
- 1,568
- 3
- 12
- 23
2
votes
1 answer
Performance tuning on matching word using CHARINDEX and COLLATION
I have two tables with following records:
Table 1: 100 rows
Table 2: 50 Millions rows
Example:
Table 1: tb100
create table tb100
(
name varchar(50)
);
insert into tb100 values('Mak John'),('Will Smith'),('Luke W')......100 rows.
Table 2:…

MAK
- 6,824
- 25
- 74
- 131
2
votes
1 answer
What are the equivalent columns of tkprof and v$statname in the autotrace of the SQL Developer?
When I get the TKPROF output, I can see the parse, execute, fetch, disk, etc.
call count cpu elapsed disk query current rows
---- ------- ------- --------- -------- -------- ------- ------
Parse 1 0.16 0.29 …

oramas
- 881
- 7
- 12
2
votes
1 answer
SQL Server tuning recommendations beyond indexing
I am dealing with a SQL query that requires many self joining tables to create aggregate functions such as the following.
SELECT
tab1.foo,
tab1.bar,
tab1.baz,
tab1.bam/tab2.bam as bam_ratio,
RANK() OVER (PARTITION BY…

Alex
- 140
- 6
- 19
2
votes
1 answer
SQL query is really slow when I remove one extra condition
I have the following SQL query:
SELECT top 100 Id
FROM TestTable
where
(Code like 'a000' + '%' or contains(Desc , 'a000*' ) )
AND (Active!='N' or Active is null)
AND substring(Code,1,1) in…

rezord
- 53
- 6
2
votes
1 answer
Oracle Parameterized Query Performance
Execution time differs too much between the queries below. These are the generated queries from an app using Entity Framework.
The first one is non-parameterized query that takes 0,559 seconds.
SELECT
"Project1"."C2" AS "C1",
"Project1"."C1" AS…

Tunahan
- 303
- 4
- 22
2
votes
2 answers
What is the most effective way of creating indexes under certain situation
OK, this is my condition and questions.. Assume that this is the only SQL we are using for tables/indexes
SELECT B.DIST_NM
, COUNT(+)
FROM CUST A, DIST B
WHERE A.COUNTRY_CD = 'USA'
AND A.CUST_CD = B.CUST_CD
AND A.CUST_ID IN (SELECT CUST_ID…

Jun
- 21
- 1
2
votes
3 answers
Why this index doesn't improve query performance
Platform: SQL Server 2012
Background: I have two fairly large log tables - around 600k records each that are being joined using Pk/Fk. For the sake of argument, lets call them ReallyBigLog1 and ReallyBigLog2. The query (below) takes about 3.5-sec to…

SteveJ
- 3,034
- 2
- 27
- 47
2
votes
1 answer
MySQL last_query_cost for complex queries
when trying to find the cost of a complex query (one with a sub-query in it) i get a value of 0.
mysql's manual says:
"The Last_query_cost value can be
computed accurately only for simple
“flat” queries, not complex queries
such as those…

Dimor
- 53
- 1
- 7