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

-1
votes
0answers
21 views

query explain saidd row is a few but it took an hour to load

i have a query down here select if ( month(td.date) = month('2011-01-01') and year(td.date) = year('2011-01-01') and month(td.date) = month('2011-01-02') ...
1
vote
1answer
38 views

SQL Server 2012 stored procedure runs very slow sometimes

I have a stored procedure with a single large query which takes an ID as a parameter and returns some counts. It has quite a few inner joins and left outer joins but they are all on indexes. The ...
0
votes
0answers
3 views

Delete fabricante_producto_solicitud only if is not related to modelo_marca_producto

I have this related tables: It's possible, in one query, check if fabricante_producto_solicitud has relation with modelo_marca_producto or do I need to rely on programmatic way? I need some ...
0
votes
0answers
31 views

Can an index be added to a PRIMARY with a null key?

I have a query that takes a long time to return, roughly 12-15 seconds, which I'd like to optimize. The query below is the result of running the original query with SHOW WARNINGS. If I run EXPLAIN ...
0
votes
0answers
19 views

Multiple user roles with different attributes

I need to design a database that has multiple users, with multiple roles. For instance, a user can be an admin, company, employee, or customer. Each user can have any number of these roles. Right now ...
0
votes
0answers
26 views

Multiple subqueries performance issue

I have a query that collects customer data. I select a lot of correlated data from different tables. I want to have only ONE result row for each customer. The query looks like that: SELECT c.Id ...
0
votes
1answer
37 views

Why does MariaDB 10.1.2 blow away MySQL 5.6.22 in sysbench?

Problem Maria DB is doing roughly twice the number of transactions that mysql is using standard oltp benchmark. The VM and db's are both fresh installs from vendor apt repos with default configs. ...
2
votes
1answer
47 views

Unused index in range of dates query (PostgreSQL, timestamp with time zone)

I think maybe I am making a dumb mistake here, so apologies in advance. But I have a query that is not using existing indices and I do not understand why. Am I reading the plan wrong? Are the indices ...
1
vote
1answer
19 views

Can I use SPARSE somehow on a non-nullable bit column with mostly false values?

I have a table that stores the results of queries that are run at least once a day. There's a bit column that represents whether the row is from the most recent run of a particular query with a ...
4
votes
2answers
60 views

Take advantage of monotonic columns in PostreSQL

I have a PostgreSQL table with, among the other, two columns named col1 and col2, both of integer type (there are around 10M rows in the table). I want to perform SQL queries like: SELECT * FROM ...
2
votes
1answer
58 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 ...
0
votes
0answers
12 views

Aggregate query on indexed field slows down due to WHERE clause. Can I speed it up?

I have a MySQL InnoDB table with about 33 million rows. It has an indexed 'date' column. CREATE TABLE T ( `Id` int(11) NOT NULL AUTO_INCREMENT, `date` datetime DEFAULT NULL, `Version` ...
1
vote
0answers
15 views

Amazon Redshift - The difference between Query Slots, Concurrency and Queues?

We are building a business intelligence system, and we have a huge PostgreSQL Database (DB) where we make all the info processing, and a Redshift Data Warehouse (DWH) where we store the data and ...
1
vote
2answers
44 views

Slow query when adding additional where clause

Here's my setup (Postgres 9.3) Posts project_id Messages post_id kind updated_at I'm trying to get the most recently updated 100 messages that belong to posts (on a specific project) that are ...
0
votes
1answer
27 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 ...
1
vote
0answers
48 views

Oracle instead of trigger update performance issue

I have a large table containing over 15million records with 4 columns that form a compound key. Because we use a development environment that doesn't support compound keys I was required to create a ...
2
votes
1answer
45 views

Retrieving latest record using DISTINCT ON is slow [duplicate]

Using Postgres 9.4. I have a table cartests with 5.5M rows. Each row is a car test: \d log.cartests; Table "log.cartests" Column | Type | Modifiers ...
4
votes
1answer
158 views

How to optimize: having COUNT(*)>1

Without considering the indexes, Is there a way to re-write this query and get the same result quicker? select fo.OrderNo, fo.OrderItemSeqNo, COUNT(*) from Facts.FactBackOrder fo group by fo.OrderNo, ...
0
votes
0answers
20 views

Propper way to handle NULL (empty) values for User Definde Table Types passed as params to sproc

My stored procedures accepts table value parameters for querying data. Usually those are custom User Defined Table Types for where clause. Business logic of stored procedures push me to these @tables ...
0
votes
0answers
14 views

Optimizing PostGIS ST_Contains query

I've been getting some abysmal performance from this query and I've been trying to find methods for optimizing it. What might be done to it? I've thought of changing the SRID –– it's 4326 now –– and ...
2
votes
1answer
276 views

Performance of query with complex where clause on 10M rows

We have a query that destroys our production server when run. It is part of a reporting functionality and the bad part looks as follows: SELECT DISTINCT mt.ID AS ID FROM ...
0
votes
0answers
44 views

Is there any way to improve this code?

SELECT tblstud.studid, tblsched.ip FROM tblstud LEFT JOIN tblstudsched ON (tblstud.studid=tblstudsched.studid) LEFT JOIN tblsched ON (tblstudsched.schedid=tblsched.schedid) ...
0
votes
1answer
50 views

Scaling PostgreSQL TRIGGER(s)

How Postgres triggers mechanism scales ? We have a large PostgreSQL installation and we are trying to implement an event based system using log tables and TRIGGER(s). Basically we would like to ...
0
votes
0answers
35 views

Use of empty tables in Join in SQL server

If I use empty tables in a join, then will it impact query performance. if it affects the performance, then how much. Also, will index creation make any impact? As I can see the execution plan, the ...
-1
votes
0answers
22 views

Need urgent help for DataBase design decision [duplicate]

I am designing database for B2b market. Catalog: is Colletion of Product,size,prices,delivery date and other data. I need to design catalog on basis of three datasourceType .On The basis of ...
1
vote
2answers
57 views

Get the pais_id not associated to fabricante_distribuidor_id and producto_solicitud_id, the opposite from SELECT query

I have this query for get all the pais_id given a fabricante_distribuidor_id and producto_solicitud_id, the query works fine. SELECT DISTINCT nom.pais_id FROM negocio.fabricante_producto_solicitud ...
0
votes
0answers
30 views

An efficient way to do “reverse queries” in MongoDB for an expert system?

I am working on an "expert system" in which I would like to perform "reverse queries", that is I want to store queries and find queries that match a given object. For example, if I have an object ...
0
votes
0answers
20 views

Usage of temporary tables in my tempdb database on sql server 2005 [duplicate]

I am trying to resolve the disk space issue on one of my drives where tempdb residing occupies more than 100 GB: Stats shown below I read some similar post on the same and i found few queries using ...
0
votes
1answer
32 views

optimizing a select query of 1.5 million entry

I have the following query: SELECT s0_.id, s0_.lowresimageurl FROM app_instagram_shop_picture s0_ FORCE INDEX ( idx_deleted_shopId ) LEFT JOIN app_instagram_shop s1_ ON s0_.shop_id = s1_.id AND ( ...
7
votes
2answers
389 views

Simple DELETE, but complicated execution plan

When I run this delete: DELETE FROM ETLHeaders WHERE ETLHeaderID < 32465870 ...it deletes 39,157 rows. It should be simple because it is deleting on ETLHeaderID which is the clustered index and ...
0
votes
0answers
8 views

Can MySQL Index Merge an Composite index with a single column index?

I am tuning a query and currently have an index as following : Index 1 : (col1, col2) An example query would be : SELECT * FROM t1 WHERE col1 = '1' AND col3 = '2'; I have created an index on ...
0
votes
0answers
22 views

Slow Query Performance - Can't Debug

I have a MySQL query that I am trying to debug the performance issue but cannot. I know that the majority of the query is reading/processing rows (Sending Data state). However, in the explain output, ...
1
vote
0answers
19 views

Best ratio for files and file groups for a highly partitioned table

I have a number of tables with exactly 1000 partitions. Each table can be up to 200G with 100M rows and cannot be compressed. Data in partitions distributed quite equally. The SQL Server has 8 ...
0
votes
1answer
58 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 ...
1
vote
1answer
44 views

Best approach to get last value inserted

I have several devices that report via Edge network their position (and more data) to a UDP server which directly inserts this data in a SQL 2008 r2. I want to know which is the best approach to ...
1
vote
1answer
66 views

Indexed View - how to verify if it is worthy?

I have the following indexed view: CREATE view VProcessStatus WITH SCHEMABINDING AS select processstatus, StatusCount = COUNT_BIG(*) FROM DBO.tbltaxtransaction GROUP BY ...
1
vote
1answer
19 views

Create a log file for check which time import has been done and to split the import files

How to create a log table for when does the last import has been done and also for Query for split the records in to 1000 while importing. to avoid the bulk insert For example : If the data ...
1
vote
1answer
32 views

What are the side-effects of disabling nested loops in postgres?

A query of multiple joins spends a lot of time on nested loops. I disabled the nested loops and the query ran much faster. However, I have been told that nested loops might give me wrong results by ...
2
votes
0answers
47 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" ...
0
votes
0answers
38 views

Querying really slow Postgres

I have multiple tables on Postgres wit GIS and when I query SELECT * FROM rxmsg JOIN Vehicles ON rxmsg.vid=Vehicles.vid JOIN stops on ST_DWithin(rxmsg.point, stops.point,1000) JOIN drivers ON ...
1
vote
1answer
123 views

Estimated rows very wrong when using declared variables

I am getting very, very wrong estimates for the clustered index seek step of this query (estimated rows is 8,637,530; actual is 74,723): Declare @StartDate as date = '10/1/2014'; Declare @EndDate as ...
0
votes
1answer
33 views

large SQL Performing quite slow (roughly 2 seconds for 290 rows) when having to deal with entries that can be NULL

SELECT version_stamp, pscdesc_id, psc, pscdesctext FROM pscdesc_history WHERE ( psc IN (SELECT psc FROM pso_history AS c ...
0
votes
0answers
47 views

MySQL: Optimize query with a range and distinct count

I have a query on a large table with millions of rows that looks like this: SELECT COUNT( DISTINCT clicks.tx_id ) AS unique_clicks_count , clicks.offer_id FROM clicks WHERE ...
0
votes
4answers
43 views

Single SQL query to retrieve data in a particular order (not ASC/DESC)

I have a MySQL table, let's say called reviews review_id, product_id, language_id, rating, review, author language_id ranges from 1 to 10 for 10 different languages. Let's say I determine that my ...
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 ...
2
votes
0answers
34 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
1answer
40 views

How can I speed up an sql query that round trips between two tables

I first posted this question on stackoverflow, and now I see this site, seems more relevant. These tables are not that big, but the time taken to process this query is long. What can I do with the ...
0
votes
1answer
87 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
17 views

Query Performance Regression Between Different Versions

I have two MySQL databases, A and B. They have identical hardware and schema. They have different data and database A has more data then B. Database A is also running version 5.1, while B is on 5.5. ...
3
votes
1answer
63 views

Help with indexing narrow table with many millions of rows

I have a good amount of experience with SQL Server, but not much with indexes and I fear I'm a little bit out of my depth in trying to figure out how to correctly structure a table that has 100+ ...