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
66 views

Is it any good for performance to have master and slave on the same server

There are similar questions on this site, but they mostly cover server failure and data backup aspects. I'm concerned only as far as query performance. If master and slave share the same resources, ...
0
votes
0answers
177 views

Alternatives for UPDATE statement Oracle 11g

I'm currently using Oracle 11g and let's say I have a table with the following columns (more or less) Table1 ID varchar(64) Status int(1) Transaction_date date tons of other columns And this ...
0
votes
0answers
746 views

Database design to track inventory?

Would this be a good starting point for tracking inventory with several warehouses? If yes what is the best approach to sum quantity when displaying stock by warehouse/type/general? mysql structure ...
0
votes
0answers
83 views

My transaction's max time is going beyond a minute

I have a transaction called "updatePolicyStatus" whose response time is usually under a 3 seconds for many instances during a peak load. This transaction processes bunch of business rules and fires ...
0
votes
0answers
80 views

How much RAM do I need?

Let's assume a query like the following as typical: select A.`date` as `date`, count(*) as N from A as A join B as B on B.id = A.b_id join C as C on C.id = B.c_id join D as D on D.id = ...
0
votes
0answers
55 views

Compare elapsed time for 2 oracle Sql files. (2300 pairs of sql files)

Version ORA 11G I have some timing comparisons that I want to do on about 2300 Pairs of Oracle SQL statements. I'm trying to convince management that tuning of these 2300 would give them a ...
0
votes
0answers
37 views

Two-part question on how Postgres queries multiple columns and its performance

I have a Django "nominations" app that stores some personal information of nominees, e.g. name, email, shipping address. The email column has a max length of 255, where the shipping address would ...
0
votes
0answers
139 views

PostgreSQL 9.2 (PostGIS) performance problem

A month ago i got acquainted with PostgreSQL and only now i've noticed that some of my queries are pretty slow. Well, for example, i have table: CREATE TABLE sometable ( gid serial NOT NULL, geom ...
0
votes
0answers
112 views

Slow query with ORDER BY with index created (more than 4mins on 0.6million records

I ran into a very weird query performance situation. If I run this query without order by, it finish in millsecs, with order by, it ends up with more than 4mins. EXPLAIN SELECT `book_book`.`id`, ...
0
votes
0answers
202 views

Mysql high delay in critical peak | Professional Help request

I have a Centos 6.4 under 2.6.32-358.18.1.el6.x86_64, running Asterisk 11& A2billing in real-time switching with Mysql 5.5.33. My Server it's a Dell R420 having dual Intel(R) Xeon(R) CPU E5-2470 ...
0
votes
1answer
919 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` ...
0
votes
2answers
1k views

Oracle query is slower the second time

I have a general question about something I'm seeing in performance tuning in Oracle. I have a query that I'm testing. It's too big and complex for me to provide a simple example, sorry! (I tried ...
0
votes
0answers
40 views

How to optimize this query more?

First at all I am nood into SQL thing, Now I am working on a class project where I have some tables like Table user user_id | username | name 1 | nihan | Nihan Dip 2 | dip ...
0
votes
0answers
535 views

Oracle merge query vs correlated update

I have a query which updates a table with data from another table. The current query is written as a correlated update with an exists clause to remove unnecessary rows. The problem is since both of ...
0
votes
1answer
674 views

Speeding up MySQL NOT IN (Subquery) [duplicate]

I'm currently up to try to improve the performance of some of my queries. As far as I know, statements like "IN" or "NOT IN" are even faster with a large amount of values if a subquery on an indexed ...
0
votes
2answers
101 views

Help creating this query

I'd like to integrate the following query: SELECT parent.id , COUNT(child.id) AS child_count FROM messages parent INNER JOIN messages child ON child.parent_id = parent.id ...
0
votes
1answer
354 views

Is it possible to avoid filesort?

Is it possible to avoid 'Using temporary' and 'Using filesort' for the following SELECT query? I can't figure out a way to do it. I tried adding indexes, for both top_expire and program, but didn't ...
0
votes
0answers
56 views

Faster way to GROUP or ORDER dynamic fields?

We have this query in our app, that searches a table for a song title using MATCH AGAINST and picks the most relevant results. The table has around a million results and is growing. The app has been ...
0
votes
0answers
409 views

Properly index magento mysql tables to improve specific query performance

I am running magento community edition and its db performance is horrible. I have the following query: SELECT `width_idx`.`value`, COUNT(DISTINCT width_idx.entity_id) AS `count` FROM ...
0
votes
0answers
101 views

Identical Postgres index scan taking 5x longer on server

I have an intermediate table for managing a many-to-many relation between tables called Expert and Subject: Column | Type | Modifiers ...
0
votes
0answers
337 views

SQL nested select (query rows after variable), running slow

Have large table (Sybase) rows = 6276225. I'm pulling call status and my query is very slow. In effect what I’m trying to do is pull data for any event after my variable. While querying for just one ...
0
votes
0answers
106 views

Performance Optimising MySQL query

I have this query in my mysql database, when i execute this, it takes about 4 sec. To give me the result (on xampp 1.8.0 with mysql 5.5) I was wondering if there is any way to enhance this query so ...
0
votes
1answer
83 views

Long running query

This is my long running query: select * from t_keyword where id in ( select rel.element_id from t_event event join t_event_element_rel rel on event.id = rel.event_id where ...
0
votes
2answers
175 views

How can I optimize this query and support multiple SKUs?

My current query only can select one SKU at a time. I can leave salesite_id constant. If there is a way to also have varying salesite_ids that would be good too, but not necessary. Also any ...
0
votes
0answers
148 views

How to load XML data into an oracle db table ?

Is there a way to load the XML format data into an Oracle 11g DB table directly ? I mean, Can we write a Stored Procedure to accomplish the same in a more efficient way with the best performance ? ...
0
votes
0answers
41 views

Resource to learn writing most efficient and highly optimized sql queries [duplicate]

Possible Duplicate: Learning to Optimize SQL Queries and Understand Execution Plans - Resources? Can some one point me to a great book/great books or some really solid reference/references ...
0
votes
1answer
204 views

Normalization and Breaking down tables for InnoDB

I'm designing a web application, something small but deals with lots of read and inserts. Let's say 60% read and 40% writes. My storage engine is InnoDB on MySQL. I have a table called User which has ...
0
votes
2answers
73 views

View with left joins are very slow

I have a view based on join of 3 tables and and query on view is taking 10 mins to fetch the results. Can anybody help me in reducing the time taken to fetch results? VIEW CREATE view ...
0
votes
1answer
81 views

Is it possible to optimize this query? Or any recommendations to speed it up?

After looking at my mysql slow log file I see that almost every entry there is related to this query: SELECT n.nid AS nid, AVG(v.value) AS rating FROM node n LEFT OUTER JOIN votingapi_vote ...
0
votes
1answer
147 views

How to optimize indexes on MySQL query with various sorts

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

Speed up a MySQL BETWEEN query

SELECT dateToFind FROM table1 INNER JOIN table2 ON dateToFind BETWEEN dateFrom AND dateTo I need to improve the performance of this kind of query. I added BTREE indexes on dateToFind, dateFrom, ...
0
votes
1answer
380 views

optimize big sql query

My query has to return a statistics for example for march containing productname and price and its sidedishes (1:n relation) with the right price of each sidedish and the right tax for each sidedish ...
-1
votes
1answer
112 views

Query performance in SQL Server [closed]

What factors should I check when I compare the performance of a query? I saw many articles stating about IO cost, subtree cost (by using Execution plans), statistics such as CPU time, elapsed time ...
-1
votes
2answers
2k views

Query to search for a substring in xml

I use below query to search for a substring in whole xml(including node name and node value) SELECT * FROM tablename WHERE ( Charindex('abc',CAST([xmlcolumn] AS VARCHAR(MAX)))>0 ) I want an ...
-1
votes
4answers
130 views

Dropping and recreating indexes [closed]

I most often come in a situation where the users move there database from one server to another and immediately after they move they face performance problems(slowness). I somehow manage to rebuild ...
-1
votes
1answer
130 views

Approximate MySQL Query Time For A Where Clause On A 10 Million Records Table

I'm looking forward to designing a database (in MySQL) but before I start I need to have some calculations to make sure that my application will remain efficient even if there are 10 Million+ rows ...
-2
votes
1answer
101 views

Maximum dataset size for a well-performing SQL Server 2012? [closed]

We're planning a project using SQL Server 2012 for storing about a billion records over less than 50 fields. Assuming good hardware, is SQL Server up to that task? Roughly how many request/second can ...
-2
votes
1answer
95 views

How big the database should be in order to take 30 minutes to query

We have few tables each containing 2,5 million rows in average. Then there is a select query with joins. It takes more than 30 minutes to execute. The db server runs on a machine with 9GBs of RAM and ...
-2
votes
1answer
287 views

Second time query execution using different constants makes faster? [closed]

Can someone explain or direct me how execution on indexes happen with different constants at intervals in Mysql. I notice only for the first execution on the table it takes time, after that with ...
-2
votes
1answer
819 views

what's the fastest method to export a table through sql query ? 11g - performance

I'm connected to the oracle server remotely. I need to export a table data(the whole table) with millions of records but due the hosting restrictions I can only use SQL (no exp , expdp) . Therefore ...
-3
votes
1answer
1k views

Single query to copy data from 3 tables into a single empty table

In an interview today, I was asked if I could write a single query to copy data from 3 source tables to an empty destination table. I started saying "I will use a temp table or table variables", ...
-3
votes
2answers
722 views

Optimisation sub query SQL Server

I would like to optimise the query below because it's actually taking 40% of the time in the real one. I HAVE to get the first line in the table 2 for each line in the table 1. The relationship is 1 ...
-3
votes
2answers
115 views

how to solve slow running sub query TSQL

I have a problem with a slow running query. I have analysed the query with the sql server profiler but I can't seem to find a good solution. I did find why it is running slow. If ask the full ...
-4
votes
2answers
77 views

Performance with IN clause

Does indexed data affect query performance? means If I passed value as 1,2,3,4... in ordered form in any IN clause, can it improve query performance? and what happen in case I pass value randomly ...
-5
votes
1answer
140 views

Recommendation for mysql please :MySQLTuner [closed]

>> MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options ...