FAQ Database Discussion Community


mysql big query optimization

mysql,sql,performance,optimization,query-performance
I'd need to optimize the following query which takes up to 10 minutes to run. Performing the explain it seems to be running on all 350815 rows of the "table_3" table and 1 for all the others. General rules to place indexes the propper way? Should I think about using...

Very large table JOIN with GROUP BY

mysql,sql,join,group-by,query-performance
I need to combine information from a 27 millions lines table with a 7 millions lines table and do some filtering. CREATE TABLE event_participation ( place_id int(4), person_id varchar(12), event_id varchar(10), event_description varchar(230), .... and more fields about that specific participation ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE INDEX IDX_1 ON event_participation...

How to Improve SQL Azure Query Performance

tsql,entity-framework-6,sql-azure,query-performance
I am using EF 6 to execute a SqlQuery, which is a SELECT statement returning 47 columns with a max of 500 rows. The query has eight LEFT JOIN statements. In my local environment, the query only takes around 300 ms, as indicated in Express Profiler. On SQL Azure, the...

TERADATA: query optimization

sql,teradata,query-performance
This query is working but it seems to take longer time than usual to retrieve the data. Is there a better solution to optimize this query? I need to get all PRD_ID from T1 and T2 even if there is no match with S1 and S2. SELECT DISTINCT T.PRD_ID T.AMOUNT,...

SQL Server - Substitute for OR when JOINing on non-indexed field

sql-server,join,query-performance
Background: One of our clients has a system where they have recordings of phone conversations between their agents and their customers, who they create various contracts with. The recordings are stored on a server with their locations saved in the Recordings table in the database. The agents can then "attach"...

Optimizing Select,delete,update query performance in SQL

sql-server,query-performance,sqlperformance
i've got a question about which query is better with higher performance in sql SELECT 1st comparison: Declare @Variable VARCHAR(MAX)<BR> SELECT top 1 @VARIABLE = COLUMN2 FROM TABLE WHERE COLUMN1 like 'Any value' 2nd comparison: Declare @Variable varchar(MAX) select top 1 @variable = t.column2 from table t where t.column1 like...

Query performance - select only if exists

sql,sql-server,query-performance
I'm trying to make my query more efficient. This is a row-by-row check and many times the query won't make it to the update point due to no matching records. I (EDIT) know thought the best way to do this is by checking if there is a match first, then...

why using IN (or NOT IN) clause in a query makes it really slow

mysql,query-optimization,query-performance
I have a query: SELECT DISTINCT field1 FROM table1 WHERE field2 = something (table1 contains 1 million records, execution time:0.106sec, returns: 20 records) Another query SELECT DISTINCT similarField1 FROM table2 WHERE similarField2 = somethingElse (table2 contains half million records, execution time:0.078sec, returns: 20 records) Now if I run a query,...

MongoDB Projection Performance - or just many small documents?

mongodb,database-performance,projection,query-performance
I'm building a collection for User Settings / Preferences and other user related stuff. At the moment a document look like this: { "_id": "USER_ID", "setting1": { "foo": "bar" }, "setting2": true, "setting3": "this might be a huuuuge email template", ... } And I query stuff by the user's id...

How does nesting structures affect DocumentDB query performance?

database,data-structures,nosql,query-performance,azure-documentdb
The question might otherwise be stated as "to flatten or not to flatten?" If I were to store nested JSON documents in a DocumentDB collection, would querying across those nested structures perform on par with having stored those nested structures in a separate collection as flat documents on their own?...

MySQL INSERT shorthand vs longhand performance [closed]

mysql,database,performance,query-performance,insert-into
What is the difference between: INSERT INTO `table_name` SET `col1` = 'val1', `col2` = 'val2', `col3` = 'val3' and INSERT INTO `table_name` (`col1`,`col2`,`col3`) VALUES('val1','val2','val3') Is there a performance benefit of using shorthand? I prefer longhand myself, but logically, for performance reasons, should I be using shorthand every time? Benchmark testing...

3hrs deployment window and Need to Migrate 190k Telecom subscriber from one Telco to other

sql,plsql,performance-testing,query-performance,telecommunication
I need to write PL/SQL script which need to migrate 190 k subscriber from one Telco to other. With this script , I need to update different tables of subscriber and Account and do service agreement changes which has complex logic. Once each subscriber is migrated then I need to...

Avoid filesort when having ORDER BY column in result

mysql,join,query-performance
For a medical calendar, I've got the following query: SELECT `events`.`StartDate`, `events`.`ID`, `events`.`EndDate`, `events`.`Title`, `insurances`.`Title`, `personsPatients`.`PrimaryPhone`, `personsDocs`.`Name` FROM `events` LEFT JOIN `events++persons` ON `events`.`ID` = `events++persons`.`FirstEntityID` AND `events++persons`.`Type` = 'Patient' LEFT JOIN `persons` AS `personsPatients` ON `events++persons`.`SecondEntityID` = `personsPatients`.`ID` LEFT JOIN `insurances` ON `persons`.`Provider` = `insurances`.`ID` LEFT JOIN `events++persons`...

Difference in perfomance

sql-server,performance,datetime,query-performance
I have a date column on SQL Server table called dd. dd --------------------------- 10-01-2015 00:00:00.000 22-05-2015 10:22:32.521 27-05-2015 12:30:48.310 24-12-2014 09:51:11.728 27-05-2015 02:05:40.775 .... I need to retrieve all rows where dd value is from the last 24 hours. I found 3 options for filtering to get the result needed:...

Index scan results in a lot of physical reads (Oracle)

sql,indexing,oracle11g,pagination,query-performance
There have two tables with more than 20 million records. The database version is Oracle 11.2.0.3.13 Standard Edition SQL> select count(*) from atsd_tra_trade_print; COUNT(*) ---------- 20000000 SQL> select count(*) from atsd_mob_allocation_pos; COUNT(*) ---------- 20551780 We run the following queries on the tables. Query structures are similar. Query is intended for...

MongoDB: degraded query performance

mongodb,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...

Default degree of parallelism in oracle hints

sql,oracle,query-performance,optimizer-hints
I have used default degree of parallelism in order to gain performance tuning and I got the best results too. but I doubt it will impact when some other job access the same table at same time. sample code below. select /*+ FULL(customer) PARALLEL(customer, default) */ customer_name from customer; The...

How to optimize mysql queries for performance under high load?

php,mysql,stored-procedures,query-performance,precompiled
I'm a bit confused about how to optimize my SQL queries. I have a mid-complex query with some joins to run a hundred to thousand times per second (two tables on SSD, one table in RAM, 4 JOINs) How can I minimize the overhead for execution? Is there any way...

SQL - Joining together summed data from multiple tables

sql,sql-server,left-join,query-performance,nested-query
I've been having the following problem for days. I've kinda managed to resolve it, but the performance is what bothers me. Basically I have one table of persons, and two tables (debt and wealth) with references to person. debt/wealth tables can have multiple rows that refer to same personID. I...

select with a multiple row in pivot table

mysql,query-performance
I really don't know how to retrieve a specific row from a pivot table. Here's my pivot table: +----+-------------+--------+ | id | peticion_id | tag_id | +----+-------------+--------+ | 1 | 3 | 15 | | 2 | 3 | 21 | | 3 | 3 | 28 | | 4...

I have million rows in one table. I am tolde to use union instead on in clause for performance reason. is it true?

sql,oracle,performance,query-performance
We have million rows in one table. our select: select * from tableA where column1 in ("a", "b", "c", "d") and where column2 = "abc"; and we have unique index on column1 and column2 combined. I was told to swtich to : select * from tableA where column1 = "a"...

Execute multiple functions together without losing performance

postgresql,plpgsql,database-performance,query-performance,postgresql-performance
I have this process that has to make a series of queries, using pl/pgsql: --process: SELECT function1(); SELECT function2(); SELECT function3(); SELECT function4(); To be able to execute everything in one call, I created a process function as such: CREATE OR REPLACE FUNCTION process() RETURNS text AS $BODY$ BEGIN PERFORM...

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

mysql,sql,indexing,query-performance
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...

SQL Server optimal query based on value distribution

sql-server,sql-server-2008-r2,query-performance,table-statistics
I have a table that stores dynamic user data in a key-value pair format. Something like this: UserId | Key | Value --------------------------------- 1 | gender | male 1 | country | Australia 2 | gender | male 2 | country | US 3 | gender | female 3 |...