Use this tag for questions about improving the performance and/or efficiency of database queries.

learn more… | top users | synonyms

0
votes
0answers
16 views

Mysql Disk Scanning algorithm

How mysql scans data from disk? What scanning algorithm it uses to scan from disk? Since the rows are stored in data pages will it read the entire data page to get the resulting rows?
1
vote
1answer
73 views

Why is it faster to do four simple selects instead of one select with left joins?

I need to find a single value from a table A containing three foreign keys to two other tables B and C. For the sake of experiment, I tested two ways to query the value: Multiple queries: declare ...
1
vote
0answers
37 views
+50

Simplify Month Over Month % Change, 4 Months Moving Average %

invoice created_at | amount | status | ------------+--------+--------- 2016-06-01 | 50 | 2 | 2016-05-30 | 150 | 2 | 2016-04-29 | 32 | 2 | 2016-03-28 | ...
0
votes
1answer
27 views

What happens when the buffer pool size is insufficient in mysql

Let us assume I run a query for a table whose size is very huge. My buffer pool size is as low as possible. What happens now? whether I will get an error regarding the insufficient space in buffer ...
0
votes
2answers
5k views

Optimize IN clause in where query with order by - MySQL

I am trying to optimize a query that using IN clause in WHERE to avoid file sorting. To make it easy , I created the following sample which shows the problem. Here is my query: SELECT * FROM `test` ...
1
vote
1answer
148 views

How to speed up queries in MySQL on a table with ~50 million rows

The table has 4 columns of type datetime, smallint(6), tinyint(4) and decimal(15,5). The first 3 columns are PK. There are four indexes on the table: one with all the PK columns, and one with each ...
3
votes
1answer
200 views

Mysql join table order by index

First Master Table CREATE TABLE `edipostingmaster` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `applied` int(11) DEFAULT '0', `fileName` varchar(255) DEFAULT NULL, `checkNo` varchar(100) ...
0
votes
3answers
72 views

What is the performance impact of indexing a frequently updated column?

I just want to know if I'm doing this right. I've searched google for hours already and I can't find a straight answer. I have a table with potentially a very large amount of inserts and updates. Any ...
0
votes
1answer
35 views

Does Oracle randomly switch execution plans to check if there is a better solution?

I had a talk with an external colleague about oracle "swinging in" its performance. In detail it was about, that oracle optimizes its execution plans for the same query by time. I don't talk about ...
3
votes
0answers
49 views

Do Large Hash Joins Contribute to Stolen Pages?

On a SQL Server 2014 SP1 Enterprise Instance, I'm performing an outer join between two partitioned tables on a numeric(16,0) + char(1) composite key. 100 GB is the max server memory setting on a 128 ...
1
vote
1answer
79 views

Troubleshooting extremely long running query (SQL Server 2012)

We have a purchased reporting application that executes a query that just recently is taking an extremely long time (>2 hours). A week ago, this same query completed in less than a minute. I've ...
20
votes
1answer
673 views

Seek and you shall Scan… on partitioned tables

I've read these articles in PCMag by Itzik Ben-Gan: Seek and You Shall Scan Part I: When the Optimizer Doesn't Optimize Seek and You Shall Scan Part II: Ascending Keys I’m currently having a ...
1
vote
0answers
24 views

Optimal query to show active records by date

I'm trying to determine what the optimal query might be for associating a date with a record that would have been active for that day. Here is a setup to demonstrate what I'm trying to do (demo at ...
0
votes
0answers
35 views

Statistics shows CPU time = 0 ms, elapsed time = 0 on second time and query running blazing fast?

I tried to run this query on a database which is located on my network, but not on my own computer. set statistics time on SELECT id, status FROM MyTable EXEC MySproc @id='3000021' set statistics ...
2
votes
0answers
99 views

Advice on how to improve this stored procedure

Edited: Added table column datatype I have 2 tables ReportItem id (int, primary key clustered index) report_item (nvarchar(100)) flow_name (nvarchar(50)) source (nvarchar(50)) Tech_Details id ...
1
vote
2answers
116 views

Consideration for a little bit heavy database

Environment information OS : Windows Server 2012 R2 ( 64bit ) RAM : 16.00GB CPU : Intel(R) Xeon(R) CPU E5-2609 @ 2.40GHz SQL : Windows SQL Server 2012 Standard Brief Database Data Information ...
0
votes
1answer
125 views

Slow response from SELECT query

I have 50 lac rows (i.e. 5 million rows) in student table. I fire select * from student where s_name like('%something word%') limit 0,5. I define indexing on s_name column. That query takes 2 ...
1
vote
1answer
266 views

Tuning Mysql and Monitoring

I would like to know if anybody can point me towards a tool/way for MySQL database tuning. Actually the tuning of my.cnf is like below: My database engine is MyISAM | Mysql 5.1.73 key_buffer = ...
4
votes
2answers
648 views

Is it possible to rebuild a dropped index?

I'm studying for the 70-462 exam and one of the training resources I'm using gives a scenario where an ETL process is taking a long time to complete an insert into a heap table due to non clustered ...
0
votes
1answer
86 views

What is difference between JOIN and simple query

I have two tables in database one is Orders and another one is customers Orders Table Contains : OrderID , CustomerID and OrderDate Customers Table Contains: CustomerID , CustomerName , ContactName ...
5
votes
2answers
166 views

Running query against DMVs for Query Stats and Execution Plans joined to sys.databases

I originally posted this as a different question in Twitter #sqlhelp, but wanted to post it here differently. I'm trying to create a job that tracks worst performing statements for various statistics ...
0
votes
0answers
47 views

how to find overlapping or duplicated indexes? when is it O.K. to keep them?

this is a script that does the following: create a database called "radha" for testing purposes, create the table ProductItemDetailsDenorm create the clustered primary key create ...
2
votes
2answers
52 views

best way to avoid redundant aggregate functions and/or group by columns

Suppose I have two tables: Foo: id baz Bar: id foo_id boom So a Foo has many Bars. I frequently find myself in situations where I need to compute an aggregate across the Bars for a given set ...
0
votes
0answers
21 views

Example Database for tunning

I've been searching for so long and I've still have not found what I'm looking for ,is there any database or similar resource that people uses for tuning a database, or optimize queries?. I would be ...
0
votes
0answers
77 views

SQL query too slow to fetch single row of data

we have this SP, running extremely slow to fetch a single row of data from tables within 100K rows, which seems not that big. It takes onn avg 15-20 mins to complete. We have tried to remove all ...
1
vote
0answers
12 views

Efficiently get records with latest timestamp per group [duplicate]

I have a table which roughly looks like this uuid uuid, id id, data text, last_updated timestamp Each row has an associated unique uuid. However, the key of interest is the id (indexed). ...
8
votes
1answer
355 views

Update queries slower after enabling SQL Server Full-Text Index

I have an asp.net website with many insert, update, and delete queries running against my database. A few days ago I create a Full-Text Index on two columns of one of tables. After that I realized ...
0
votes
1answer
974 views

Filtering records not present with specific conditions in another table

I have a table (let's call it extra_info) in MySQL that has various extra information/labels on user accounts: account_id / data_key 11 / 'test' 11 / 'blah' 12 / 'coupon a' 12 / 'coupon b' 15 / ...
1
vote
2answers
53 views

How to improve performance of a query with an IN subquery

Get the count of other users having the x tag with order_id in order_id of users having the y tag table: MyCustomTable (200K rows) order_id user_id type ('x' and 'y') my custom tags and the ...
1
vote
1answer
127 views

placing conditions in a join query join on vs where

Is there and advantage to one of the following options over the other or are they the same ? select * from T1 Join T2 on T2.id=T1.t2_id and T2.field=value; vs. select * from T1 Join T2 ...
1
vote
2answers
109 views

Validation of Partitioned table Performance

We are testing partitioning on a table which contains a datetime column (DateColumn). Partition function I am using is: -- 138623 + 16774 records CREATE PARTITION FUNCTION pf_TableName_DateColumn ...
1
vote
1answer
160 views

Optimizing intersect query in MySQL

I'm facing a performances problem in our MariaDB server. We have a table linking users to pages, a user belongs to 1+ pages. I have a page that have users and I want to get the 10 best pages based ...
2
votes
2answers
2k views

Improve MySQL Query Performance - Too many records

I am using MySQL RDMS for a contact management system. Now a days my database grew in size and contains more than 1 million records. Checking duplicate phone numbers became a big issue during ...
5
votes
1answer
59 views

Indexes: integer vs string performance if the number of nodes in the index is the same

I hope this is the right place to ask! I am developing an application in Ruby on Rails with the PostgreSQL (9.4) database. For my use case, columns in tables will be looked up very frequently, as the ...
1
vote
1answer
30 views

Subquery inside a select vs left join

Often I have to return the count of of some row in a query that fetch other non related rows. For example A table User a table Review and a Table picture User: id nickname Review: id to_user_id ...
2
votes
1answer
182 views

Updating large table worked before, now taking hours

BACKGROUND: I am using a non-production SQL Server to manipulate a large data set and run a few basic calculations. The original data file I have, which is about 30M rows, is missing records (not ...
1
vote
1answer
124 views

pt-query-digest results are empty

I have a slow log generated by MySQL 5.1.71-log, that I've copied onto another machine. It appears I'm not getting any results from pt-query-digest. $> pt-query-digest slow.log # 2.1s user time, ...
6
votes
5answers
460 views

UNION ALL or other ways to return first row of result-set

I am writing a table valued function to geocode an address based on its Suburb, State and Postcode. I try to geocode the address using different methods, in order of decreasing accuracy: Exact match ...
3
votes
2answers
264 views

How to optimize indexes on MySQL query with various sorts

I have an INNODB table levels: +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
2
votes
0answers
26 views

Materialized Views and Indexes

Is there any way to force PostgreSQL to use an index that I created on a materialized view table? I tried every combination possible of moving the column attributes around to make Postgres to use my ...
2
votes
3answers
110 views

Poor performance of JOIN, WHERE, ORDER BY combination (MySQL)

Goal: select 10 most recent rows from posts which are related to a specific user. There are two tables: posts (~5,000,000 rows) and relations (~8,000 rows). posts columns: ...
2
votes
2answers
48 views

Improve query “JOIN … WHERE NOT EXISTS (SELECT 1 FROM … WHERE … )”

I'm trying to optimize a query because the plan generates a full table scan for a subquery. Data The (simplified) table structure: CREATE TABLE RECORD ( ID INTEGER PRIMARY KEY AUTO_INCREMENT ); ...
3
votes
1answer
16 views

Sort table rows and save the row numbers using UPDATE

I have Postgres 9.5 with a table movimientos that has the following data: | id | concepto | movimiento | numero | orden | | 1 | AJUSTE 1 | 2542 | 0 | 2 | | 2 | APERTURA | 12541 ...
4
votes
1answer
97 views

How can I optimize a query with multiple derived tables containing `IN` and `GROUP BY`?

I'm collecting nmap data every five minutes and storing it in a database. Information about each scan (e.g. start and end time) is stored in the scans table: CREATE TABLE `scans` ( `scan_id` ...
1
vote
2answers
35 views

Slow query / Indexes creation (PostgreSQL 9.2)

I have the following Query: explain analyze SELECT split_part(full_path, '/', 4)::INT AS account_id, split_part(full_path, '/', 6)::INT AS note_id, split_part(full_path, '/', 9)::TEXT ...
2
votes
1answer
218 views

High I/O for part of query

I have a large query that I'm trying to tune. I write lots of queries but haven't done a lot of tuning. I've included a screenshot from SQL Sentry Plan Explorer Free (SSPEF): In the above part of ...
1
vote
1answer
25 views

index creation - Slow Query - PostgreSQL 9.2

I've got the following query: SELECT * FROM ( SELECT split_part(full_path, '/', 4)::INT AS account_id, split_part(full_path, '/', 6)::INT AS note_id, split_part(full_path, '/', 9)::TEXT ...
0
votes
1answer
54 views

MySQL Query fix and speed up

I'm newish to MySQL and a lot of my query is pieced together from this site and trial and error, so any help is appreciated. If I left out anything, or more detail is needed, I will be happy to give ...
4
votes
1answer
158 views

How can I speed up a stored procedure?

Here is an interview question I just had. I would like to know what a good answer to this would have been and what the different options would be. We have a stored procedure that is taking 26 ...
0
votes
0answers
32 views

best practice to remove leading zeros when writing data to a destination

I have a query like in my other question DBA Question with Source Code of Select statement And i want to remove the leading zeros of the customer Number (KUNDE) with the answer of this question ...