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
1answer
109 views

Slow SQL Server authentication, but fast SAN, fast querying?

I am asking you after days of research but I think I don't have required skills to understand what is happening. I have a slow authentication on a SQL Server 2008R2, which leads to a timeout for the ...
12
votes
3answers
5k views

Can a single PostgreSQL query use multiple cores?

In recent versions of PostgreSQL, can we share a query between two or more cores to get a performance boost? Or should we get faster cores?
0
votes
1answer
97 views

Tuning Mysql and Monitoring

I would like to know if anybody can point me towards a tool/way for MySQL database tuning ... Actualy the tunning of my.cnf is like below My database is in MyISAM | Mysql 5.1.73 key_buffer = 500M ...
1
vote
0answers
29 views

How to optimize inefficient query in PostGIS

I'm doing some queries on a PostGIS, and some of them take a really long time ( > 60 secs). My queries are like this, except querying different tables (like osm_placesbelow). This query takes about ...
1
vote
1answer
78 views

How to optimize a very slow query with joins and group by?

I have this GTFS database : All the ***_id columns are indexed, and there are foreign keys between tables (such as a FK on trips.route_id to routes.route_id). So basically the link between a stop ...
1
vote
3answers
2k 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
1answer
132 views

How to optimize query

using SQLite3 and the following query, it seems like there should be a better performing way to write this query? Is there? Select Name, Sum(Case When Used Is ...
1
vote
1answer
25 views

#2014 - Commands out of sync. you can't run this command now. Query was working before. But now it is not working

My table structure is ... There is Counter which has many Box in it. Box has a one BoxType. and Collection stores Box datewise revenue and data. I need data of particular 1 COUNTER (Data of All the ...
1
vote
0answers
18 views

How to query against legacy data that needs to be cleaned up?

I have an existing SQL Server DB with 3 tables (note: this is a made up example, but modelled after a real problem). Artists : contains music artists MusicRecords : contains information on music ...
1
vote
3answers
57 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 ...
1
vote
0answers
36 views

Is it bad practice to launch more than 100 sql statements at once?

I have PHP code that is meant to updating the billing status of someone. I only want it to update if the bill has been successfully created and therefore I think a possible solution would be to ...
1
vote
1answer
41 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 ...
-1
votes
0answers
23 views

query explain saidd row is a few but it took an hour to load [on hold]

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

Mysql join table order by index

First Master Table CREATE TABLE `edipostingmaster` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `applied` int(11) DEFAULT '0', `fileName` varchar(255) DEFAULT NULL, `checkNo` varchar(100) ...
0
votes
1answer
86 views

A single, simple update takes 10+ seconds

We are running MySQL 5.1.71-log and have a table with 2.7 million rows. Simple updates to it are regularly taking more than 10 seconds to execute. The table structure is: CREATE TABLE `person` ( ...
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
35 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
21 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
1answer
38 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
50 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 ...
3
votes
2answers
153 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 ...
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 ...
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
67 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
135 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 ...
0
votes
2answers
174 views

Database is slow once in a while

I am executing a web service for 100 times. The average response time of this service is 1.5 seconds. But when I look at the response time of each execution individually, there are a few executions ...
0
votes
2answers
191 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 ...
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` ...
6
votes
3answers
4k views

PostgreSQL Sequential Scan instead of Index Scan Why?

Hi All I've got a problem with my PostgreSQL database query and wondering if anyone can help. In some scenarios my query seems to ignore the index that I've created which is used for joining the two ...
1
vote
0answers
19 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 ...
3
votes
3answers
409 views

Is database partitioning for performance reasons meaningful in a virtualized environment?

When running SQL server on physical hardware, placing database data and log files on separate disks or RAID arrays can benefit performance, resilience, and maintenance. Partitioning the database over ...
1
vote
0answers
50 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 ...
0
votes
1answer
3k views

Select from a list of values from JSON encoded data

I have saved the details of the product in JSON format. I am wondering how to select from a list of values from a "JSON_ENCODED DATA" For Example I want to select TV with screen_type as LED or LCD. ...
3
votes
2answers
336 views

Query planner thinks doing a sequential scan on a very large table is faster than using an index?

I've got a query that selects the most favorited photo for each of the last 12 editions for a particular domain (this is just a segment of a larger query, but the problem seems to stem from here): ...
0
votes
2answers
963 views

How to fix slow LIMIT queries with big offsets without heavily rewriting them?

Imagine a very basic example of your average discussion board. For example: CREATE TABLE threads ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(100), PRIMARY KEY (id) ) CREATE ...
18
votes
3answers
1k views

difference in execution plans on UAT and PROD server

I want to understand why there would be such a huge difference in execution of the same query on UAT (runs in 3 sec) vs PROD (run in 23 secs). Both UAT and PROD are having exactly data and indexes. ...
0
votes
1answer
28 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 ...
2
votes
1answer
117 views

Minimizing Page Fetches

I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables. As a general rule, is it better to attempt to use ...
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 ...
3
votes
2answers
402 views

SQL server query is slow *without* including execution plan

I have just set up fresh installation of SQL Server 2012 on a new server and I'm experiencing very strange performance problems. Long story short, for some reason not including execution plan makes my ...
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
2answers
131 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 ...
1
vote
1answer
193 views

How can I use mongoDB efficiently in this scenario?

I'm trying to build a system of documents that have hundreds of tags assigned to them, but each document-tag association has a certain condition it has to also meet so the document would be returned: ...
4
votes
3answers
3k views

Query runs slow in test site on first execution. Why?

I found this query by watching a test site with sql profiler for anything taking over 10 seconds. I plopped the code right out of sql profiler and into sql studio, where it was able to execute ...
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 ...
2
votes
1answer
280 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
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 ...
6
votes
6answers
3k views

Recommendations for MS SQL Server monitoring software or service? [closed]

I have a client with MS SQL Server instances (2000, 2005) scattered across 20-30 servers. I've been engaged to get things organized and will be periodically reviewing the state of the database ...
0
votes
1answer
96 views

Complicated data

I have a requirement i got from my father. He gave me a table and wanted me to digitalis it. So that he can the system can do all the dirty jobs for him ( that's why there are developers in the world, ...