Questions tagged [query-performance]

1

votes
1

answer
146

Views

slow query with lots of left joins

When I check SHOW PROCESSLIST; in database I got below query. It heavily uses CPU (more than 100%), it took 80 seconds to complete the query. We have a separate server for database(64GB RAM). INSERT INTO `search_tmp_598075de5c7e67_73335919` SELECT `main_select`.`entity_id`, MAX(score) AS `relevance...
Bilal Usean
0

votes
1

answer
22

Views

MYSQL lot's of update on 40 million record table wih 128GB ram dedicated server take long time

We are having trouble with updates on a single table taking a long time. The table contains ~30 Million rows. The job runs every day that truncates the table and inserts new data from other other sources in that table. Here is the table: CREATE TABLE tempportfolio1 ( SR_NO int(4) NOT NULL AUTO_INCRE...
vishal
2

votes
0

answer
15

Views

Oracle hint on multiple indexes with multiple join statement

I have a query as below : SELECT /*+ INDEX(A T1_IX01) INDEX(A T1_IX02) INDEX(B T2_IX01) INDEX(C T2_IX02)*/ * FROM T1 A JOIN T2 B ON A.SOURCE = B.C1 JOIN T2 C ON A.TARGET = C.C1; and i need to use 2 indexes from T1 I created with SOURCE column and another with TARGET column which has ma...
Achelous01
40

votes
3

answer
3.8k

Views

Why is query with phone = N'1234' slower than phone = '1234'?

I have a field which is a varchar(20) When this query is executed, it is fast (Uses index seek): SELECT * FROM [dbo].[phone] WHERE phone = '5554474477' But this one is slow (uses index scan). SELECT * FROM [dbo].[phone] WHERE phone = N'5554474477' I am guessing that if I change the field to an nvarc...
Greg Gum
1

votes
3

answer
253

Views

MySQL multiple index condition become slower?

I have a table defined like this: article | CREATE TABLE `article` ( `id` varchar(64) NOT NULL, `type` varchar(16) DEFAULT NULL, `title` varchar(1024) DEFAULT NULL, `source` varchar(64) DEFAULT NULL, `over` tinyint(1) DEFAULT NULL, `taken` tinyint(1) DEFAULT NULL, `released_at` varchar(32) DEFAULT N...
seaguest
1

votes
3

answer
43

Views

Indexes on a large SQL db

I have a very large table in a azure SQL database which is already having more than 30million rows and has a lot of inserts happening to the table every day ~50-60k We have various pages in the web app which needs data from this large table... every page has a different way of querying this table in...
CSharped
1

votes
1

answer
20

Views

SQL - adapting query to use CTE

I'm looking for to use CTE in my query because I get kind of same select but I've never used CTE before, can someone help me? I need that firstTable can be used in the others selects SELECT firstTable.id as id, secondTable.holder as holder FROM ( select tb3.id as id from table1 tb1 inner join table...
t9217
1

votes
2

answer
107

Views

too much time to execute

Below query is taking long time to execute around 2mins please help me how to improve performance of this query. so our requirement is to get result within 2 to 3secs. query is using indexes also. but it was performing more scan. query: select max(`log_date`) from `top_competitor_summary_entity` wh...
mithuna kous
1

votes
1

answer
108

Views

too much time to execute also impact on db performance

1.query is taking around 10mins to 1 hr to execute. 2.due to this we are getting load spikes frequently. 3.please help me to rewrite the query. 4.also help me to improve the performance of query. Query with explain plan explain select count(*) from t_event eve left join t_event_element_rel rel on...
mithuna kous
1

votes
1

answer
175

Views

When is it better to flatten out data using comma separated values to improve search query performance?

My question about SEARCH query performance. I've flattened out data into a read-only Person table (MySQL) that exists purely for search. The table has about 20 columns of data (mostly limited text values, dates and booleans and a few columns containing unlimited text). Person ===================...
cbsf
1

votes
2

answer
53

Views

mysql negation from multiple entires

i have one customer_products table with have the mapping of customer to products it is using customer_id product_id c1 p1 c1 p2 c1 p3 c2 p1 c2 p2 c3 p1 I want to get all the customers that are using p2 but not p3. Is it possible to achie...
maneet
1

votes
2

answer
670

Views

MongoDB: degraded query performance

I have a user's collection in MongoDB with over 2.5 million of records which constitute to 30 GB. I have about 4 to 6 GB of indexes. It's in sharded environment with two shards, each consisting of replica set. Servers are dedicated especially to Mongo with no overhead. Total RAM is over 10 GB which...
harshad
1

votes
2

answer
114

Views

Alternative query for calculating percentage

In SQL Server for calculating percentage I have a function like below: CREATE FUNCTION [dbo].[fuGetPercentage] ( @part FLOAT, @total FLOAT ) RETURNS FLOAT AS BEGIN DECLARE @Result FLOAT = 0, @Cent FLOAT = 100; IF (isnull(@total, 0) != 0) SET @Result = isnull(@part, 0) * @Cent / @total; RETURN @R...
shA.t
1

votes
2

answer
49

Views

How to increase data retrieval?

I have a query that takes long to execute and eventually times out. My task is just to get all the data from the table for particular dates. However, the database table does not have indexes on the date column and query takes long time to execute and times out. This is piece of code I have: DateTime...
gene
1

votes
1

answer
294

Views

Sun Solaris 10/11: get disk i/o rates in machine readable way

I need to get the I/O rates of the disks/drives on Sun Solaris 10 and 11. There is the command-line tool "iostat" which returns exactly what I need. But this output is formatted and not in a clean machine readable way. Beside this, I don't want to execute binary out of the java-program in which I n...
Karl
1

votes
2

answer
74

Views

PostgreSQL MAX() inner query really slow

I've been currently tasked with optimising some of the SQL queries that are ran on very large datasets. I have the query below which I'm trying to optimise but at the moment struggling with it The query takes roughly around 40-45 minutes to comeback with an answer which is obviously not acceptable....
dnzdlklc
1

votes
2

answer
146

Views

Should I sacrifice my innodb_buufer_pool _size/RAM to make space for query_cache_size ?

I have a 16GB dedicated Mysql server database.My innodb_buffer_pool_size is set to around 11GB ,i am implementing query cache in my system ,which has a size of 80mb. From where should i make this space ,innodb_buffer_pool_size or RAM ?
Jitin Maherchandani
4

votes
1

answer
639

Views

MySQL slow query with 2 inner joins

The following query takes 4 seconds to run and I don't know why since the index results are low. The way it works can't be changed (since it's a compatibility query to get the appName from 2 different systems) Do you have any ideas? SELECT runList.appName FROM projectList INNER JOIN varMeta ON (proj...
KillDash9
1

votes
5

answer
71

Views

How can I combine two on click functions?

I have two on click function... somewhat like below. It is working. However, I have a feeling that the on click functions can be combined into one for optimization reasons. Basically when xx is clicked it shows some elements, hides some elements, and adds a class. When yy is clicked it does exactly...
1

votes
4

answer
78

Views

Delete repeated Ids from Table - Performance Improvement

I have a table with repeated codes, I need to clean the table removing the repeated, but having at least one left of then in the table. My table is this: FriendlyFunctionCode MemberFirmId FunctionLevel3Desc 1 Value1 Value2 1 Value2...
Rednaxel
1

votes
1

answer
1.6k

Views

Should all sub queries be replaced with temporary tables?

I have been working on a solution (in SQL Server) where all the sub queries with no exception have been rewritten with temp tables in order to boost performance. To give an example, all the queries like this: SELECT something FROM (SELECT * FROM T1 WHERE condition1) JOIN ... have been rewritten t...
Arvand
1

votes
3

answer
170

Views

How can Entity Framework be forced to generate a SQL Inner Join on a nullable Foreign Key?

UPDATE: There is a request about this on the EF Team's UserVoice site. Vote it up This thread about the Include statement is also related. Why it's important Querying with EF6 and Linq rocks! But if it involves a couple of Join Tables, and a nullable Foreign Key, it bogs down with 1000 lines of T-S...
3

votes
1

answer
782

Views

Memory Optimized Tables - Slower INSERT than SSD

I have observed that inserting data into a Memory-Optimized Table is much slower than an equivalent parallelized insert to a disk-based table on a 5-SSD stripe set. --DDL for Memory-Optimized Table CREATE TABLE [MYSCHEMA].[WIDE_MEMORY_TABLE] ( [TX_ID] BIGINT NOT NULL , [COLUMN_01] [NVARCHAR](10)...
Pittsburgh DBA
-1

votes
0

answer
16

Views

sql count function query time takes much longer for postgres than for oracle

The following sql query takes less than 1 second in an oracle database (Oracle9i Release 9.2.0.4.0) and about 10 seconds on a similar database on a postgreSQL server (7.4.2) select dp.OBJEKT, dp.ADRESSE, count(mw.ID) as ANZAHL from DATENPUNKTE dp, MESSWERTE mw where dp.FND_TYPE = 1 and dp.DATUMME...
Julia Kurde
0

votes
1

answer
37

Views

Distinct after join or sub-query with distinct and then join

While writing a procedure, I came across a situation were I have to put a DISTINCT in a query. This is somewhat similar to my table schema CREATE TABLE T1 ( ID INT, TypeID INT, SubTypeID INT, Name VARCHAR(50) ); GO CREATE TABLE T2 ( TypeID INT, SubTypeID INT, TypeName VARCH...
Harsh
17

votes
1

answer
6.4k

Views

SQLite: Should LIKE 'searchstr%' use an index?

I have a DB with several fields word_id — INTEGER PRIMARY_KEY word — TEXT ... ..and ~150k rows. Since this is a dictionary, I'm searching for a word with mask 'search_string%' using LIKE. It used to work just fine, taking 15ms to find matching rows. The table has an index for a field 'word'. R...
nikans
1

votes
1

answer
326

Views

SQL Server : wrong index is used when filter value exceeds the index histogram range

We have a very large table, where every day 1-2 million rows are being added to the table. In this query: SELECT jobid, exitstatus FROM jobsData WHERE finishtime >= {ts '2012-10-04 03:19:26'} AND task = 't1_345345_454' GROUP BY jobid, exitstatus Indexes exists for both Task and FinishTime. We...
nambar
1

votes
1

answer
106

Views

query executing too much time

my query is taking around 2800 secs to get output. we have indexes also but no luck. my target is need to get the output with in 2 to 3 seconds. if possible please re-write the query. query: select ttl.id, ttl.url, ttl.canonical_url_id from t_target_url ttl where ttl.own_domain_id=476 and ttl.ty...
mithuna kous
6

votes
1

answer
97

Views

Which query has a better performance?

SELECT * FROM { SELECT * FROM BigMillionTable UNION ALL SELECT * FROM SmallTensTable } WHERE (some_condition) Vs SELECT * FROM BigMillionTable WHERE (some_condition) UNION ALL SELECT * FROM SmallTensTable WHERE (some_condition) My questions: Does the first query need to put all the rows in the...
kaushalpranav
3

votes
0

answer
286

Views

Seeding SQLite with Entity Framework - Performance Problems

Introduction I am trying to seed a SQLite DB with Entity Framework but it's unacceptably slow. I have an entity MyEntity with 5 columns which has a composite key consists of 3 columns. Initial approach dbContext.MyEntity.AddOrUpdate(x => new{x.Key1, x.Key2, x.Key3}, myEntities.ToArray()); This work...
ferit
2

votes
2

answer
50

Views

MySQL limit syntax with Query performance

I want to know how it is affected in query performance to use limit syntax There are 180000 rows. So I would select data using limit syntax. So I want to know the relationship between limit syntax and query performance.
Mike Will
2

votes
0

answer
115

Views

Improve distinct query performance using indexes

Documents in the collection follow the schema as given below - { "Year": String, "Month": String } I want to accomplish the following tasks - I want to run distinct queries like db.col.distinct('Month', {Year: '2016'}) I have a compound index on {'Year': 1, 'Month': 1}, so by intuition looks like...
hyades
2

votes
1

answer
64

Views

Why does MySQL do a scan for update but lookup for select

I have these 2 queries. As you can see its doing a lookup in TabRedemption for orderItemID. The select takes a fraction of a second while the update takes ~30 seconds. Why does MySQL resort to a full index scan in the update, and how can I stop this. It already has a foreign key constraint and and i...
Stephen
1

votes
1

answer
921

Views

Oracle Switching Plan of Execution to Wrong Index

We had a performance issue in our production environment. We identified that Oracle was executing queries using a Index which is not correct. The queries have in their WHERE CLAUSE all the columns of the Primary Key (and nothing else). After rebuilding of Index and Gather Statistics, Oracle started...
Guddu
5

votes
1

answer
102

Views

Why assigning count of a query to a variable performed better than checking it directly?

Recently I had a performance tuning experience and I want to share it here and trying to understand why this improvement happened. In one of my procs, I wanted to return a data set based on existence of some other records. My query: IF (SELECT COUNT(1) FROM ...) > 0 SELECT … This query was taking...
sqluser
5

votes
1

answer
560

Views

DocumentDB PartitionKey and performance

I have a scenario where I store large amounts of third party data for ad-hoc analysis by business users. Most queries against the data will be complicated, using multiple self-joins, projections, and ranges. When it comes to picking a PartitionKey for use in Azure DocumentDB, I see people advising t...
Chad Levy
7

votes
3

answer
9.4k

Views

DISTINCT with PARTITION BY vs. GROUPBY

I have found some SQL queries in an application I am examining like this: SELECT DISTINCT Company, Warehouse, Item, SUM(quantity) OVER (PARTITION BY Company, Warehouse, Item) AS stock I'm quite sure this gives the same result as: SELECT Company, Warehouse, Item, SUM(quantity) AS stock GROUP BY Compa...
Andris
4

votes
2

answer
123

Views

What is a good index for my query to get fast results?

I have a query that is pulling form many tables but it is slow and I want to improve it. SELECT ph.phone_call_id AS id, ph.call_subject AS callSubject, ac.account_name AS accountName, ph.trigger_on AS triggerOn, ph.isAppointment, ph.status, ind.name AS industry, cc.call_code_name AS callCode, ac.ac...
Mike
1

votes
3

answer
1.1k

Views

SQL query - Order by in the SQL query OR Sort a List<>

I'm trying to improve some SQL queries. I'm using the SQL server management studio 11 with SQL server 2012. I'm working on a webform C# .Net (v4.5) project. When I create my query directly from the SQL server management studio, I can use the "Execution Plan" to check what happens. The bottleneck of...
wytes
2

votes
1

answer
65

Views

Query for calculationg percentage of many columns with a good performance

I have some tables like below examples: [myCategory] [mySales] +------+---------+ +------------+-----+--------+-------+ | id | name | | date | cat | price | code | +------+---------+ +------------+-----+--------+-------+ | 1...
shA.t

View additional questions