Questions tagged [sql-tuning]

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.

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…
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…
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 …
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