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
0answers
21 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
1answer
203 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
3answers
118 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
40 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
90 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
72 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
192 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
30 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
27 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
373 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 ...
1
vote
0answers
49 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) ...
2
votes
1answer
256 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
44 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
vote
2answers
58 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
58 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
41 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
414 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 ...
1
vote
0answers
24 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
63 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
25 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
81 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
71 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
76 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
23 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
45 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 ...
4
votes
0answers
222 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
1answer
56 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
125 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
36 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 ...
1
vote
1answer
280 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
77 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
71 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
48 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
51 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
211 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
22 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
111 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
35 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
50 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
124 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
53 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
68 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
66 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
46 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, ...
0
votes
1answer
107 views

SQL execution 2 seconds with LIMIT parameter, without only 0.2s why?

This is my query and I need to return only 12 rows, but it takes 2 seconds with parameter LIMIT 12 while it takes only <0.2 to process all ~600 results (without LIMIT 12 or with LIMIT 1200 for ...
0
votes
0answers
53 views

Table design for optimum performance

For example let's say I have to create a table called Employee with columns EmployeeID, Name, DOB, Address, DepartmentID[FK]. So I create the table as follows with a primary key and a nonclustered ...
1
vote
2answers
102 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 ...
2
votes
0answers
31 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 ...
0
votes
0answers
35 views

Table maintenance after dump/source

I just completed a major DB migration, all went well. I had a 450 GB DB on old underpowered windows hardware using MySQL 5.0 and I moved everything to much more powerful (512gb memory, server 2012) ...