A class of issues where a database executes a query inefficiently due to the optimiser selecting an innapropriate query plan.

learn more… | top users | synonyms

0
votes
1answer
195 views

T-SQL Optimizing a Join on TOP Value from another table

I've got a data warehouse that goes through a full refresh each night that can take about an hour to process 16 million rows/25gigs of data and we're looking for ways to reduce this time without going ...
0
votes
2answers
611 views

How to get rid of using temporary when using DISTINCT in MySQL?

When I do the following, I get Using where; Using index; Using temporary. How do I get rid of Using temporary? http://sqlfiddle.com/#!8/f61a2/3/0 CREATE TABLE `test` ( `a` varchar(45) NOT NULL, ...
0
votes
1answer
1k views

Optimize MySQL Self JOIN Query

I have a query which is running in 15+ seconds SELECT t1.`ST_StockCode`, t2.`SM_StockCode`, t2.`ST_ItemSize` FROM `stocks` AS t1, `stocks` AS t2 WHERE ...
0
votes
1answer
735 views

Query that only uses index takes long time to execute [closed]

I have a table with ~69,000 rows. I have this very simple query: -- SET STATISTICS TIME ON SELECT ID, FirstName, LastName FROM Customers -- SQL Server Execution Times: CPU time = 31 ms, elapsed time ...
0
votes
2answers
337 views

Query gets slow when bind peeking is turned on

There is an old database that was upgraded from Oracle 10 to 11G R1. In Oracle 10 we had disabled bind peeking. Since Oracle 11 has adaptive cursor sharing, we want to turn bind peeking back on. When ...
0
votes
1answer
109 views

Can I speed up this slow query which should be limited by MIN/MAX values?

The intention here is to look at the user retention over a period of time. My current solution employs a temporary table, since I do this over several ranges. CREATE TEMPORARY TABLE ...
0
votes
1answer
1k views

How to measure a perfomance of SQL Server database? [closed]

I have the task to improve the performance of a SQL Server 2012 database (one of 4 in an instance) by 60% confirmed by corresponding statistics. So, I need to measure "performance" of the RDBMS ...
0
votes
2answers
89 views

Understanding multiple indexes with MySQL (and performance)

I have a "phones" table with around 120 000 records, it contains phone_id, phone_number, phone_user_name, phone_last_contacted, etc. The phone_id is index and looks like: 1,2,3,4 ... 120 000. But I ...
0
votes
1answer
86 views

How to optimize query?

I need to optimize the following query: SELECT DISTINCT column1, column2 FROM tbl_name WHERE column3 = 'x' AND TRIM(UPPER(column4)) = TRIM(UPPER('y')) AND TRIM(UPPER(column5)) = ...
0
votes
1answer
570 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 infomation/labels on user accounts: account_id / data_key 11 / 'test' 11 / 'blah' 12 / 'coupon a' 12 / 'coupon b' 15 / ...
0
votes
2answers
225 views

How can I increase performance for select in select statement

The item table is like this id category_id name registered I have to show total number of items from the beginning. Suppose between 2013-05-01 to 20-05--05, I have to count all the items ...
0
votes
1answer
127 views

Looking for performance improvements for this query

This query is taking a long time to execute. We are expecting results in 0 sec, if possible. Please help me to do any other following: Rewrite the query Suggest any indexes Any other optimization ...
0
votes
2answers
362 views

Large time difference between two almost identical simple queries…why?

I'm trying to understand why is there so much difference in execution time and CPU usage between two simple queries that only differ in a computation. The queries are the following: SELECT ...
0
votes
1answer
265 views

Create a VIEW that spans 3 tables

Sorry if the title is not clear - feel free to improve. Here are 3 tables (not all data is relevant to the question): CREATE TABLE `content` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, ...
0
votes
1answer
326 views

Design database to query a date range for scheduling

I am designing a db that should include a table with fields for datetime scheduling, so as I know what kind of queries will be made​​ periodically I would like to design the table for them to be as ...
0
votes
2answers
646 views

Slow query with straight_join for small result

I have a performance-problem with a query in my application and I don’t understand the behavior of mysql. The query consists of different joins, especially the join to the mentioncache-table. If the ...
0
votes
1answer
194 views

Is possible to optimize this query or it's already optimized?

I've three tables: default_users, default_profiles, default_friend. This are the SQL for each one: -- -- Table structure for table `default_users` -- CREATE TABLE IF NOT EXISTS `default_users` ( ...
0
votes
1answer
885 views

Slow performance of MySQL Join Query

I have inherited a site (running Coldfusion8 and MySQL 5.0.88) with a product search using multiple criteria. I'm struggling with optimizing the search, especially a pricelist check, which makes the ...
0
votes
1answer
189 views

MySQL query took 98 seconds to run but EXPLAIN looks okay

I have a query that took 98 seconds to execute this morning, but I can't recreate the problem now, and the EXPLAIN output isn't giving me any clues. Am I missing something? Here's the SQL: SELECT ...
0
votes
1answer
154 views

Creating Indexes to optimize query

I have this query and I want to increase its performance by adding appropriate Indexes. DELETE FROM MYTAB1 WHERE MYID1 IN ( SELECT MYID2 FROM MYTAB2 ); I am not familiar with the ...
0
votes
1answer
28 views

How bigger page size effect at performance?

I know in case of Cluster index bigger page size improve peformance for range and multipoint query. Does bigger page size can effect to degrade performance when we have non cluster index .Because ...
0
votes
1answer
54 views

How database buffer pool work?

For example we have 2 scenarios: Scenario 1:- We have very small buffer pool size of 100MB. My query data is 500MB. Does the system keep 500MB query data in RAM such that a new ...
0
votes
1answer
91 views

Columns count in SELECT query and SQL Server buffer cache

Let's assume that I have a table with 30-50 millions records. It has ~40 columns. Currently when I request data it always requests all columns because of legacy ORM that we use. I know that if we ...
0
votes
1answer
63 views

Simple query is slow on 4M-rows table

I have in production a MySQL table pageviews with 4M rows that records page views of users on posts. I need to know which posts a specific user have read, but this request takes up to 15 seconds to ...
0
votes
1answer
40 views

Mysql query performance

We have a database table and we found a weird query from old code. We can't really explain it, so we decided to analyze why it could have been done that way. The weird thing is that the results are ...
0
votes
1answer
95 views

Slow running aggregate of aggregate query

I am looking for the total balance for all accounts that had a negative balance as of a specific point in time. I've already got the code below, and it works, but works horribly. The transactions ...
0
votes
1answer
405 views

MySQL SUM with multiple GROUP BY on same table

What is the best way to produce several sum results based on different group by conditions on the same table in MySQL? I believe I'm doing a bad select, but I can't figure out a better way. Imagine ...
0
votes
2answers
87 views

Find the closest bar, when searching for a drink

Problem: Find the closest bar that serves the drink(s) I'm looking for. Here you can find the generated MySQL code http://pastebin.com/5Uc2ewUW The API Request that interacts with this problem ...
0
votes
1answer
128 views

How to increase sql server performance? denormalization (making cascade delete off) vs making duplicate data

I have a database with following related table ( sql server enterptise edtion 2014) each state have many citites which each city has may municpalArea and so on State => Cities = > ...
0
votes
1answer
57 views

The materialized view is already optimal and cannot be tuned any further

I have tried DBMS_ADVISOR.TUNE_MVIEW to verify some materialized views created locally. One of the result returns an error: Error report: ORA-13600: error encountered in Advisor QSM-03116: The ...
0
votes
2answers
79 views

Best way to store few rows of data every minute without creating 1440 columns a day?

I will be calling a server that returns some JSON data every minute. The values are along the lines of Value, Currency, DateTime, and maybe 1 or 2 more values. What is the best way to keep save these ...
0
votes
1answer
58 views

If a query contains two identical subqueries, will they both run?

Say I have a SELECT query that contains two identical subqueries, like the below: SELECT (SELECT SUM(Price) FROM Product p WHERE p.GroupID = g.GroupID) AS 'TotalCost', (SELECT SUM(Price) ...
0
votes
1answer
47 views

How can I time SQL-queries using psql from command line?

I am sorry, I cannot comment, so I am posting a new question. There is already a question "How can I time SQL-queries using psql?" but I am missing answer how to do that from command line. How to run ...
0
votes
1answer
117 views

PostgreSQL - If I run multiple queries concurrently, under what circumstances would I see a speedup? Under what circumstances would I see a slowdown?

I approach you all humbly as one who is NOT a DBA, and I'm sure that my question is fraught with conceptual shortcomings and "it depends on" land mines. I'm also pretty sure that all of you who ...
0
votes
3answers
142 views

Why is my select statement so slow?

SELECT name FROM sys.databases WHERE name not in ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB','1303VISBOC') AND CASE WHEN state_desc = 'ONLINE' THEN ...
0
votes
1answer
73 views

Last row from a query without Order by

I have something like SELECT var1,var2 FROM table where var3=X AND var4=Y order by var5 desc limit 1; Running this query takes roughly 5 seconds on my database which is far to slow. I was ...
0
votes
1answer
32 views

How to optimized mysql query having large dataset

I have two tables in MySql with the following schema, CREATE TABLE `open_log` ( `delivery_id` varchar(30) DEFAULT NULL, `email_id` varchar(50) DEFAULT NULL, `email_activity` varchar(30) DEFAULT ...
0
votes
1answer
78 views

Help optimizing very slow mySQL query

I have a query which takes about 6 secs to execute. SELECT * FROM (SELECT ord.ID, op.name AS prodName, op.code, ord.date, ord.email, op.total, stat.ID AS statusID, stat.value AS status, ...
0
votes
2answers
270 views

Increased query_cache_size, Queries slowed drastically on increased traffic

I increased our servers query_cache_size to 2gb, hoping for performance benefits. After sometime the query performance would drastically decrease. Once I dug in deeper, I found the queries in State ...
0
votes
1answer
51 views

How to optimise very simple query with subquery that only needs to execute once

SELECT products_id, products_model, products_iban_no, products_allocation_number FROM products WHERE products_model IN (SELECT products_model FROM products GROUP BY products_model HAVING ...
0
votes
1answer
333 views

Couldn't find the long running SQL under v$session_longops

I have following SQL running from a long time. I couldn't see the status When I tried to look the time remaining for the query to complete. Can someone explain me why I couldn't find the SQL status ...
0
votes
1answer
169 views

Query with INNER JOIN takes long time than expected?

I have the following query, which takes longer than expected. Anybody help me find a faster alternative? SET SESSION MAX_JOIN_SIZE=18446744073709551615; SELECT claims.claim_id, ...
0
votes
1answer
128 views

too many entries for one single session and serila# in V$Session_longops

I have a concern with the entries in v$session_longops. I used a query to identify query running from past 25 minutes for sid=62 which is given below: SELECT s.sid, s.serial#, ...
0
votes
2answers
196 views

Instead of static list, using a table select result for IN key

I use a static list for some queries WHERE clause like WHERE STypeID IN ('12', '13', '14', '265') This list will be changed in the future; new values will be added. When list changed, I don't want ...
0
votes
1answer
40 views

Oracle Autodop disabled at the table level

I have a question related to Oracle auto DOP and parallel DML concepts. I have a scenario, if I set the Auto DOP set to 1 on the table and if I run parallel DML statements on that table through ...
0
votes
1answer
76 views

MySQL: What points should we consider when create composite index

There are multiple views behind the logic of creating composite index in a table. Creating composite index is obvious when we have composite primary key. Beside this, we create composite index on the ...
0
votes
2answers
76 views

How should I structure my time based index?

So I have this table in SQL Server 2008. Let's just say it's called TimeSheets. Now, that table has two columns I am concerned with. TimeSheetDate and UserCustomerKey. TimeSheetDate is a ...
0
votes
1answer
301 views

What is the Difference between IN and Not IN performance wise?

I am new to database.I am having doubt that what is the difference between IN and NOT IN performance wise. When I am using IN, takes less time for the same query(Logically same query) when I use NOT ...
0
votes
2answers
546 views

MYSQL - Why does this SELECT get slower the further down the table?

My table: CREATE TABLE `wp_users` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_login` varchar(60) NOT NULL DEFAULT '', `user_pass` varchar(64) NOT NULL DEFAULT '', ...
0
votes
1answer
59 views

How can I efficiently search for all records from a given month, or a given year?

We have a couple queries that involve datetime fields. In particular, we are checking for things like SELECT field1, field2, ..., field20 FROM table WHERE MONTH(startDate) == 1 SELECT field1, field2, ...