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
vote
0answers
14 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 ...
1
vote
0answers
11 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
49 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
55 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
11 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
41 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
23 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
44 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
42 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
155 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
19 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
273 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
28 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
19 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
388 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
57 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
31 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
43 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
39 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
81 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
62 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+ ...
0
votes
0answers
33 views

Performance decline function vs command window

I have a function that I first wrote as a serious of commands. In the SQL command window when executed, those steps take about 8 seconds to return the data set. When those same commands are turned ...
1
vote
3answers
40 views

MySQL select with between optimization

I got issues with some geo IP query. So basically here is create code of table: CREATE TABLE `geo_ip_city` ( `id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT, `begin` BIGINT(20) NOT NULL DEFAULT ...
2
votes
2answers
52 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 ...
1
vote
1answer
44 views

Taking the difference in timestamps between successive entries

This is my first foray into DBA.stackexchange, so I'll say up front: this is a query optimization question. If this is not an appropriate question for DBA, let me know and I'll skulk away quietly... ...
0
votes
1answer
56 views

Similar hardware, Same DBconfiguration and Database but different status variables result between Windows and Linux server [closed]

I have two database servers - one is Windows - 2008 and another is RHEL5 with similar hardware configuration. Both OS are 64 bit. MySQL 5.1.58 community edition is installed on Windows servers while ...
0
votes
1answer
65 views

Skewed Clustered Index

I have a SQL Server database that has a pretty important table (join wise) that contains just over 2.1 million rows. For the VARCHAR column that defines the clustered index, 17.5k rows contain unique ...
1
vote
1answer
44 views

stored procedures location influence their performance?

I have a procedure SP_MYPRO that updates data in MYDB1. 1) the performance of SP_MYPRO would be different if I place it in MYDB2 instead of MYDB1? 2) what if the procedure SP_MYPRO only read data, ...