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
0
votes
2 answers
How to reduce redundant MySQL function calls in a single query?
SELECT hour(datetime), COUNT(animal_id)
FROM animal_outs
WHERE hour(datetime) > 8 AND hour(datetime) < 20
GROUP BY hour(datetime)
I am learning SQL. I am calling hour(datetime) four times in my query. I am curious 1) if this redundancy affects…
pitachips
- 94
- 3
- 8
0
votes
1 answer
Make SQL Server Optimizer to think that record count is huge
We are having few huge tables (with many millions of rows) in Production, holding invoice related data. We are facing performance issues in production due to these tables.
In Pre-production environments, as the data count is lesser, we are not able…
Venkataraman R
- 12,181
- 2
- 31
- 58
0
votes
0 answers
What does the optimizer do with hash join and full outer join?
What does the optimizer do with hash join and full outer join?
As far as I know, with Hash Join and Outer Join, hash table is built for the one whose nonmatching rows will NOT return, what if both nonmatching rows will return ?
general46
- 800
- 6
- 16
0
votes
3 answers
proc sql in SAS run 37 times slower than sql in SQL Server
I have this proc sql step in SAS that takes about 1.7sec to complete, however, if i run this SQL via SQL server or any sql client, it takes only 0.04 sec to complete.
From the sastrace log i found that most of the time are spend on 'Total row fetch…
user3646699
- 149
- 4
- 17
0
votes
1 answer
How to check whether our query is transformed or not in the Oracle Database?
How to check whether my query is transformed or not by the Query Transformer?
I've searched on the Internet but couldn't find a solution.
general46
- 800
- 6
- 16
0
votes
0 answers
How to improve performance of following query in SQL Server?
The execution plan is showing 3 warning messages for following query:
set statistics io on;
select DISTINCT r.CASE_ID as RequisitionId,
e.BV_Employee_Code as EmployeeCode,e.CASE_ID as CaseID,
e.BV_First_Name as FirstName,e.BV_Last_Name as…
Subhendu Mahanta
- 961
- 1
- 18
- 44
0
votes
2 answers
Why does the optimizer choose a keylookup instead of 2 separate queries?
I have a table that has a primary key/clustered index on an ID column and a nonclustered index on a system date column. If I query all the columns from the table using the system date column (covering index wouldn't make sense here) the execution…
schulzey
- 95
- 1
- 2
- 9
0
votes
3 answers
Oracle - NVL(col1,col2) Order By slowness
There is a column in Select clause NVL(b.name, a.name) and I am using this column in Order By due to which the Oracle query has become slow.
I tried creating index on the NAME column but of no use.
SELECT
*
FROM
(
SELECT
…
swet
- 207
- 4
- 15
0
votes
1 answer
How to retrieve last record of each category( faster way )
I use Oracle as DBMS and have a large table(400,000,000 records). Now, I want to retrieve the last record of each category. When I use "group by" as shown below, it takes a long time. Is there a faster way?
Note: I need to retrieve all the table's…
Yaser
- 57
- 8
0
votes
2 answers
SQL Server Select Query Is Slow
I have approx 820,000 records in my SQL Server table and it is taking 5 seconds to select the data from the table. The table has one clustered index on a time column that could be NULL (as of now it does not contain any NULL value). Why is it…
Khushboo Jain
- 1
- 1
- 3
0
votes
1 answer
Rewrite the following SQL query to turn it more efficient/improve its execution and the reasons for that
Relational Schema:
City(cityID, nameCity, nbInhabitants)
Company(companyID, companyName, nbEmployees, cityID) cityID: FK(City)
Given the following statistics:
• City contains 4 000 tuples with 20 tuples per page
• Company contains 200 000 tuples…
User Friendly
- 1
- 1
0
votes
1 answer
How the row locks are managed in dirty blocks if there are multiple changes by different users on that block?
I have a question about the dirty blocks. As you know, there are multiple rows in a block. If multiple users operate on different rows in the same block, will oracle create specific dirty blocks for each transaction?
Or, if transaction just…
oramas
- 881
- 7
- 12
0
votes
0 answers
Oracle tune query by using of temp table
I want to remove redundant call for count in the below mentioned stored procedure and rather want to use a temp table and use the temp table to return in cursor
procedure status(p_order_id in varchar2, p_stat out sys_refcursor) is
l_count…
Sudipto Sarkar
- 346
- 2
- 11
0
votes
2 answers
Query Tuning in stored procedure
I need to tune this below query mentioned to avoid Union with the archive table every time because the below query might be unnecessarily scanning the Archive table even if in most of the cases the data will be present in the first table…
Sudipto Sarkar
- 346
- 2
- 11
0
votes
0 answers
Redshift Query is spending considerably more time in first run than subsequent run
We have a queries(likely complex and big) generated by our BI application.
When I am running the query for the first time it takes around 8 to 9 mins to execute but
when I am executing it second time it is taking less time(15 secs).
I am not sure…
mt_leo
- 67
- 1
- 12