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
0
votes
2 answers

How to empty or clean some of the system variables value after mysql database is rebuild?

For testing, I almost rebuild the newly designed MySQL database every day recently, I also have a Php application based on that. For my understanding, some of system variables value has been accumulated in every rebuild, such as: mysql> show global…
user1342336
  • 967
  • 2
  • 16
  • 28
0
votes
1 answer

how does mysql allocate the key length of a primary key or foreign key?

I created a table with 2 different charset type: utf8, and latin1. 1) CREATE TABLE `aaa` ( `id` int(11) NOT NULL DEFAULT '0', `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT…
user1342336
  • 967
  • 2
  • 16
  • 28
0
votes
2 answers

cursor_sharing parameter in Oracle

I would like to know the tradeoff in setting cursor_sharing parameter in Oracle as "FORCE" . As this would try to soft-parse any SQL statement, and certainly the performance must be improved. But the default value is "EXACT", so I would like to know…
0
votes
1 answer

SQL - Avoid an additional GROUP BY (and improving query performance)

I am stuck on solving this issue and it should be nice hearing new fresh ideas :) I have a table with billions of records like this TAB_IX (int) (PK) TAB_ID (int) (PK) PR_ID (int) (PK) SP_ID (int) (PK)(IX) .... Previously I was retrieving data like…
JoinZ
  • 1
  • 1
0
votes
1 answer

Maximum no of joins possible in a select statement for better performance

Maximum no of joins that can be used in a select statement from tables containing huge data for better fetching time(for better performance).What i mean is that if i have a select statement with 50 inner or left joins whether it is better to split…
Sachin K S
  • 107
  • 1
  • 4
  • 12
0
votes
1 answer

Assistance in querying an Entity Attribute Value model ( "dynamic attributes" ) in SQL Server 2012

I need assistance to query from a model that contains one table of a specific object (i.e. Products) and one table of its dynamic attributes. Let's say that I can store a Chocolate with attributes such as Price, Color, Weight and also a Car with…
Bick
  • 17,833
  • 52
  • 146
  • 251
0
votes
0 answers

Slow mysql query on large table with GROUP BY, AVG, etc

I have a query on a large table(over 2 million rows) which takes ~10 seconds to complete. Is there any way to optimize it? The query is as below: SELECT DATE_FORMAT(date0, '%Y-%m' ) AS Yr_Mo, DATE_FORMAT(date0, '%p' ) AS AM_PM, …
xiaolong
  • 3,396
  • 4
  • 31
  • 46
0
votes
2 answers

SP slow on powerful server, but quick locally (both from SSMS)

I have inherited a large and slow stored procedure and it's giving me a nightmare: I have SQL Server 2008 installed on my desktop with a copy of the production db. I am running everything from SSMS and I have tried straight SQL and SP. Time for Sp…
BlueChippy
  • 5,935
  • 16
  • 81
  • 131
-1
votes
1 answer

Get date filter from a table in Oracle?

I would like to know how to access date column from a table and use it as date filter for another large volume table.. I have the following query that currently uses sysdate and it gets completed in twenty minutes. select a,b,datec, sum(c) from…
CuriP
  • 83
  • 10
-1
votes
4 answers

Optimize Oracle SQL query

Oracle SQL Performance Tuning Can someone please help me optimize this query? (Please note that I had multiple subqueries in select statement, I am only providing here 2 subqueries for ease). I have provided explain plan here and will be adding the…
-1
votes
1 answer

how to do a NULL check in unpivot in sql

I have the below sql query which I am using on sql server. select distinct [Value],Label,Id from( select distinct [Name], Code, I as Id,I + ':' + [Value] as label, [Value] from [test].[dbo].[emp] unpivot ( [Value] for I in (product, model) ) as…
Naxi
  • 1,504
  • 5
  • 33
  • 72
-1
votes
1 answer

Response time increases largely from100 to 150 users load test

Response time increases largely from 100 to 150 users in load test.also we see constant 75 % utilization of processor in 150 while 100 it reaches 75 % twice during steady state comes down to 65 %.the large increase in response time in 150 user test…
-1
votes
1 answer

MySQL slow query processing

I have an AWS EC2 instance with DUAL-CORE and 4 GB Memory. I have setup my Apache2 HTTP server running PHP "7.0.30" and MySQL "Ver 14.14 Distrib 5.7.22". There are various devices that are sending GET/POST request to my Http server. Each post and…
-1
votes
2 answers

Oracle Query Taking Time

I am running this oracle query , But its taking lot of time, I have also tried indexing, but still taking lot of time. Can the below Query can be tuned or can be write in other way to execute fast. select * from table1 where (col_1 like '8%' OR…
Suyash Gupta
  • 11
  • 1
  • 5
-1
votes
2 answers

Function in WHERE clause - dealing with poor performance

I have a large table with over 100000 records. I need to add one more condition in the where clause used to evalute the totalSum and return only records with the totalSum <> 0. Ton of joins and temp tabes is used here and I did not intend to…
unknown
  • 461
  • 9
  • 23
1 2 3
21
22