Stack Overflow
Stack Overflow

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
1
vote
1 answer

Tune a query with ROWID and ORA_ROWSCN

I have a following query which is taking lot of time as table is very big, this query also fetching pseudo columns ROWID and ORA_ROWSCN. select ROWID, ORA_ROWSCN, t.C1, t.c2, t.c5, t.c7, t.c9 from tab t I tried using hint ALL_ROWS and ran the stats…
Sid
  • 582
  • 3
  • 7
  • 28
1
vote
1 answer

SQL sargable and performance tuning

I am using the following where predicate to get only Monday to Fridays tickets from one of our DW tables. SET DATEFIRST 1 SELECT TicketID FROM Tickets AS T WHERE DATEPART(DW,T.StartDate) BETWEEN 1 AND 5 ....` Would it be possible to let me know…
Mathias Florin
  • 48
  • 3
  • 19
1
vote
1 answer

DBA_HIST_SQLSTAT: non zero elapsed time while execution number equals zero

Hello I found strange situation with some sql_ids statistics in a view dba_hist_sqlstat. Those seem to have nonzero elapsed time values when execution values are equal zero. I don't understand how it is possible. Maybe during situations when session…
Przemek Piechota
  • 1,513
  • 2
  • 12
  • 19
1
vote
1 answer

Sybase ASE - Performance Select Into Vs Insert Select

I have seen that the performance of a select into is better than a insert/select combination as it is un-logged. But today I encountered a different scenario. I am doing a select into from a physical table into a temp table and it inserts 2.4M…
Sudeep Hazra
  • 118
  • 15
1
vote
1 answer

Large Datatype length performance impact in Oracle?

I am adding a column with datatype varchar2(1000), This column will be used to store a large set of message(approximately (600 characters).Does it effect the performance of query for having large datatype length, if so how? I will be having a query…
user3225011
  • 161
  • 1
  • 2
  • 12
1
vote
2 answers

Why does Postgres do a sequential scan where the index would return < 1% of the data?

I have 19 years of Oracle and MySQL experience (DBA and dev) and I am new to Postgres, so I may be missing something obvious. But I can not get this query to do what I want. NOTE: This query is running on an EngineYard Postgres instance. I am not…
RubyRedGrapefruit
  • 12,066
  • 16
  • 92
  • 193
1
vote
1 answer

Drawbacks of using manually created temporary tables in MySQL

I have many queries that use manually created temporary tables in MySQL. I want to understand if there are any drawbacks associated with this. I ask this because I use temporary tables for queries that fetch data shown on the home screen of a web…
Ravi
  • 2,472
  • 3
  • 20
  • 26
1
vote
2 answers

Oracle Query/SQL tuning

I have a table with total 198695 records and 96579 records with project_id =555. I have list interval partitioned the table by project_id. If i run the below query(without distinct),it takes 0.75 secs to run. SELECT …
user2342436
  • 492
  • 3
  • 17
1
vote
1 answer

Best possible way to have out put from two tables with high difference in cardinality

I have two tables select col1 , col2 , col3, col4, ........, col20 from ftcm; --TABLE has 470708 ROWS select val from cspm where product='MARK'; --TABLE has 1 ROW i have to make col3 as null if col2=val. have thought of joining as select …
shashwat
  • 61
  • 3
1
vote
1 answer

Improve query performance from many Master table

I have a query. I have created index for all joined key, pre-filter the master table and choose specific rows to select, but it still take long time to show(sometimes it hangs). What should We do to improve the performance? SELECT * FROM …
Wildan Muhlis
  • 1,553
  • 2
  • 22
  • 43
1
vote
3 answers

How can i optimize the below query?

I have a table like this. _id (integer) event_name(varchar(20)) event_date(timestamp) Here is some sample data given below. ID event_date event_name 101 2013-04-24 18:33:37.694818 event_A 102 …
Anant
  • 3,047
  • 2
  • 27
  • 33
1
vote
1 answer

Mysql slow query log keeps recording same queries every 5 seconds

I enabled below variable in my.cnf: log_slow_queries = "/var/log/mysql/mysql-slow.log" long_query_time = 2 log-queries-not-using-indexes and restarted the MySQL service. Then mysql-slow.log keeps recording 2 queries (both are small table with…
user1342336
  • 967
  • 2
  • 16
  • 28
0
votes
3 answers

Is there any performance difference between those two SQL queries?

I’m a new SQL learner and a newbie to StackOverflow. Hope I didn't miss anything important for a first-time post. I happened to get two following queries from my instructor saying they have different performance. But I couldn’t see why they are…
cococ0j
  • 11
  • 3
0
votes
1 answer

Reducing Logical Reads on Join Query

Hope someone could help me. Our client has an existing database and this specific view is problematic and runs really slow. This has been raised to us as consultants and I saw the execution plan with clustered index scan everywhere. I managed to…
Json T
  • 65
  • 1
  • 7
0
votes
1 answer

Regarding index compression

I've searched about index compression on the Internet. But could not find an answer. My question is; How does Index compression work for new entries in the index segment?
general46
  • 800
  • 6
  • 16
1 2 3
…
21 22