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

2
votes
2answers
225 views

Dramatic decrease in SQL Azure performance after scaling to new edition

Yesterday, we scaled one of our databases in Azure from the old Business edition to the new Standard one (S2). Since then, it has been behaving rather erratically. We have a relatively simple table ...
2
votes
2answers
216 views

SQL Server - Logical Reads lowered, Execution time remained the same

A view executed Select columns from view where col= "date range" (view contains calculation in it Col1+Col2*col3 AS Col4). It took 7 seconds for 500K rows to return, logical reads some 144k. Added ...
2
votes
2answers
287 views

Super simple mysql query taking too long

I have the following: SELECT * FROM autorepair WHERE zip_code IN (91316,91356,91416,91426,91436) AND specialty = 'transmission' ORDER BY ABS(91316 - `zip_code`) LIMIT 0,10 Indexes: -zip_code ...
2
votes
1answer
137 views

Query returning unused stored procedures

When I run SP_askbrent@asof = '2014-05-20 16:00' a period where the database was timing out, I'm getting dozens of CREATE PROCEDURE xxx entries. When I run the following query, the results show the ...
2
votes
1answer
39 views

Will composite indexes or unique indexes help with query performance?

I need help with identifying indexes to increase performance of the following query. I'm currently using v5.5 with InnoDB. I cannot modify the query. However, I can add indexes as needed. The ...
2
votes
1answer
41 views

General purpose view for last value in a group

I am trying to create a view to show the last value in the group. Looking for a more general purpose query. CREATE TABLE a ( id INTEGER, seq INTEGER, val VARCHAR2(16), PRIMARY ...
2
votes
1answer
324 views

MySQL: What is the most optimized way to use datetime type field in query

I am working on optimization of some slow queries in my application. I found a query in which datetime comparison has been used. Below is the subquery: select * from jos_content c where c.visible = 1 ...
2
votes
2answers
118 views

Convert to Self JOIN

I have a correlated sub query, which is taking 16 Minutes to execute. How can I convert it to join or optimize it. SELECT SSF.SM_StockCode, SSF.ST_ItemSize FROM Stock AS SSF WHERE ...
2
votes
2answers
288 views

Trigger update does not seem to finish

I'm new to PostgreSQL triggers & functions and such. I've got a table: keywords and each keyword should be unique. So when I try to insert a keyword that already exists, it should simply update ...
2
votes
1answer
114 views

Are there differences in performance of getting data between FullText Search and indexed columns

In SQL Server 2008 R2 I have a query which looks like create stored procedure give_me_art @filter varchar(15),@skl_id int begin select id,naziv,sifra,isnull(lager.kolicina,0) as lager from art ...
2
votes
2answers
367 views

Efficient way to query set of rows from MySQL

I am using MySQL database engine. My Java application uses simple JDBC to retrieve data from database. I have following database schema: main table with two columns: (INTEGER id, BLOB data). id is ...
2
votes
1answer
560 views

inner join on PK with extra criteria slow despite indices?

Given the two tables below I am struggling to understand: why is the third query slow even though first two queries are fast what exactly is EXPLAIN saying can I do anything to significantly speed ...
2
votes
1answer
134 views

Combining columns in index

With reference to RolandoMySQLDBA's answer on MySQL: Index when joining to tables not being used (Performance optimizing question) He mentioned Make sure you build an index that involves the ...
2
votes
1answer
99 views

Optimising query on view that merges similar tables with a clear discriminator

Using PostgreSQL 8.4, I have a number of tables that have a very similar structure, but that belong to different categories: CREATE TABLE table_a ( id SERIAL PRIMARY KEY, event_time TIMESTAMP ...
2
votes
1answer
506 views

Should I use left join to do my job in this scenario?

What I want to do is to find out the items which appeared more than once in this table: mysql> desc tables; +-----------------+---------------------+------+-----+---------+-------+ | Field ...
2
votes
1answer
1k views

How to optimise read/write performance a MySQL table with many rows?

I am developing a django application where I use a MySQL database. In my application there is one table for domains information which has only 2 coloumns id domain_name but I have a problem when I ...
2
votes
1answer
256 views

Query tuning - SQL Server

One of our developers are trying to run the below query on a development server, which involves pulling data from a linked server, production. The query ran for more than 14 hours before it was ...
2
votes
1answer
155 views

MySQL optimization question — how to speed up a simple query

I have a very simple database, one table with five columns (one INTEGER key, two VARCHAR, a DATE, and a DOUBLE). The table contains 909,000 records. A simple query like SELECT t.price FROM ...
2
votes
2answers
36 views

Joining two Mysql VIEWs takes a very long time

I have a textbox (for which I used jQuery auto-completion) in my PHP form where user can insert an actor/actress name and then by clicking "Search" button, a new window is opened showing list of ...
2
votes
1answer
61 views

Running a query on an improperly indexed, 600gb table

This is my first post on here and I'm really baffled by this. I have joined a team and somewhere in the past a horrific table was created which has been bloated to a point of 600+gb of data. ...
2
votes
1answer
215 views

Extremely long query time after upgrading to Postgres 9.3 from 9.1

We're attempting to upgrade from Postgres 9.1 to Postgres 9.3 on a FreeBSD 9.2 zfs box. Performance is noticeably slower across the board, but we have one query that has gone from 353ms to >200000ms. ...
2
votes
1answer
193 views

Improve select query performance

The following select query from my application is taking more than 200ms. The table has around 2 million rows. Is there any ways to improve the performance of this query ? select count(id) from ...
2
votes
1answer
637 views

Is there any difference on performance when choosing DISTINCT or GROUP BY to bring distinct values?

I would like to know if there is any difference concerning performance when choosing DISTINCT or GROUP BY to bring distinct rows from a query. I've tried comparing the execution plans, but they seem ...
2
votes
1answer
401 views

T-SQL Query runs quickly on local PC, but slow on SQL Server?

I have a query with a few joins and a couple of basic SUM(...) statements, and no parameters. It's been running without issue for months - but has suddenly started running very slowly on the server ...
2
votes
4answers
5k views

How can I query data from a linked server, and pass it parameters to filter by?

I have a really big query that needs to be run on multiple databases, and the results appended to a temp table and returned. The basic syntax looks something like this: INSERT INTO #tmpTable (Id, ...
2
votes
1answer
59 views

What is the algorithmic complexity of a PosgtreSQL greater than or lesser than index query (compared to equality)?

Assuming there is an index idx_int on an int_col column, what is the algorithmic complexity of a query like this? SELECT id FROM table WHERE table.int_col > 1; I'm specifically interested in ...
2
votes
3answers
438 views

Why is SQL running the same query longer from another connection? [closed]

Here is the issue overview: Why does my stored procedure run faster when executed localy vs remotely? Dont jump to any conclusion just yet, let me explain what I mean... Here is the setup: A Windows ...
2
votes
1answer
294 views

Slow query performance

My database contains 20,000 rows and the following query is slow. Please help me to speed this up: SELECT r.report_id, r.user_id, u.user_name, u.user_mail, ...
2
votes
2answers
3k views

How do I force a JOIN to use a specific index in MySQL?

I have a query that JOINs 2 tables; lineitem and part, select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem force index for join (l_pk), part where ( ...
2
votes
1answer
773 views

Oracle 10g using collection with sql

I have a stored procedure that takes collection of objects. (TABLE OF MyCustomType). Inside the procedure I'm trying to join this parameter with real tables. For example, something like create or ...
2
votes
1answer
625 views

oracle merge operation hangs (or loops); query tuner claims it's a cartesian product operation

I have a Merge query (open to better suggestions) that seems to cause the server to hang. (I'm not sure what it's doing, but it pegs the CPU and never completes) When I run it through the query ...
2
votes
1answer
106 views

Get the id_user_app if not exists in id_user_rq

I'm building a application and have this table for users relationships. I need to build some queries like for example: Who invited me as a friend? Who I invited to be my friend? Who I accept as ...
2
votes
1answer
694 views

How to calculate query that access system view cost in Oracle?

For a query that returns if there are any active transactions within current session , SELECT COUNT(*) FROM v$transaction t INNER JOIN v$session s ON (t.ses_addr = s.saddr ) INNER ...
2
votes
1answer
282 views

datetime index skipped for same query different params

I am running two queries with same query but different parameter and one of them is extemely slow while the other is fast. Both the results are alomost the same. (500 and 575) Any idea why it skips ...
2
votes
1answer
145 views

Should I use an index or a hash function on a primary key if I have 100000 rows of data?

I have a table named PRODUCT that has 100,000 rows. I was wondering if either indexing, or a hash function on the primary key would result in better performance. I was also hoping someone could ...
2
votes
1answer
295 views

Rewriting a query

I have a database with the following structure (star schema): facttable: pros integer mena integer me integer vahet double precision slutttp timestamp without time zone Structure of each ...
2
votes
0answers
24 views

Grant users privilege to use QUERYTRACEON 9481

Microsoft suggests using OPTION (QUERYTRACEON 9481) for queries that have "degraded" performance against SQL Server 2014 databases running under compatibility level 120. In my situation, "degraded" ...
2
votes
0answers
32 views

select on table slows done during update on same table [closed]

I have a weird problem with mysql. During a transaction which runs from 10-30s around 270 statements are performed, some of which are updating and inserting a few rows on a table. If a select is ...
2
votes
0answers
19 views

PLSQL using cursors or functions to query repeated data

Is it better to declare a commonly used lookup query as a package function or as a function (either returning rows or values or returning a sysref cursor)? Whats the difference? What are the best ...
2
votes
0answers
193 views

Mysql search matching multiple values in multiple columns

I need to speed up a search. Currently we let our users input a name: John Doe Then in Java we split that string to create multiple values (or "terms") ["John", "Doe"] and loop through the terms ...
2
votes
2answers
254 views

Slow queries on billions-rows-table // index used

Since I'm a young developer and not really skilled in using databases (PostgreSQL 9.3) I ran into some problems with a project, where I really need help with. My project is about collecting data of ...
2
votes
0answers
33 views

Optimization suggestions for db2 materialized view

I have a view for db2 9.7 which has consolidated o/p from few tables. The view is built and it is working fine I want optimization suggestions on the view SQL CREATE VIEW XYZZ AS SELECT ...
2
votes
0answers
100 views

Almost identical queries but large time difference in execution

Exhibit A SELECT SUM(value) FROM Credits RIGHT JOIN calendar ON datefield = DATE(FROM_UNIXTIME(created_at)) WHERE confirm = 1 && reason = 1 && country = 'it' && created_at ...
2
votes
0answers
38 views

Emulating WITH clause in MySQL/query perfomance review

I have never really worked with MySQL queries till this last week. I was tasked with making a simple tool for my department that tracks contacts. Here are the 2 tables and the type of data they ...
2
votes
1answer
122 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 ...
2
votes
0answers
300 views

mariadb out of memory crash

I have recently migrated from mysql (5.5.36 MySQL Community Server (GPL)) to MariaDB (10.0.10-MariaDB-1~wheezy), along with switching my cloud hosting provider. Both machines have 2GB RAM and 1GB ...
2
votes
1answer
251 views

Optimize table and query, aggregate by date or date-hour

I have a table storing sensor data (100M rows currently) from stations from different sites. In my case, sites may have many stations. Create table statement is as follows: CREATE TABLE sensor_data ( ...
2
votes
0answers
113 views

Does Oracle ADO.NET varchar parameter size influence query plan reuse?

I am considering to apply a fix which will generate constant parameter sizes for (n)varchar parameters. The application is based on NHibernate with the old OracleClientDriver (using Microsoft Oracle ...
2
votes
0answers
67 views

Single table row “sharding/versioning” to customize values

Introduction Hello everyone, I have done quite a bit of searching and I cannot find a solution for my specific issue. There are a few factors that complicate my situation, which I have outlined ...
2
votes
1answer
129 views

Best Indexing Strategy for Query with Equality[A], Range[B], Group By[C], AND Order By[count(P)]

I have a poorly performing query: SELECT user_id, count(item_id) as count FROM table items WHERE category = 'magazine' AND created_at > 1384754400 GROUP BY user_id ORDER BY count(item_id) desc ...