Use this tag for questions about improving the performance and/or efficiency of database queries.

learn more… | top users | synonyms

0
votes
0answers
384 views

Mysql cursor alternative / Optimization - each row updates are too slow

I'm looking to optimize my cursor based update or actually replacing it... Situation We're carrying out promotional campaigns and I'd like to track user activity per campaign. Logic Each campaign ...
1
vote
0answers
120 views

delete query performance issue on mysql

I have two tables table1, table2 where table2 is a subset of table1 (basically created with the query create table table2 as select * from table1 where .....) Now, I am trying to run the below query. ...
0
votes
1answer
27 views

Optimize Query in MySQL

I've following tables with respective fields 1) wi_individual_g(ind_id,ind_is_recepient,ind_deleted,ind_district_id,...) - Around 200K data 2) wi_individual_p(ind_id,prg_id,...) Around 250K data 3) ...
1
vote
1answer
58 views

SQL Server execution plan using a view, but no view is inside the stored procedure

I am troubleshooting this slow stored procedure and when checking the actual execution plan I noticed a warning of a possible missing index. Taking a closer look, it's against one of the views, not an ...
1
vote
0answers
63 views

Is there a way to improve this query further?

I've got a query that over time I've been improving. Originally it simply selected * from the records table, and the data was formatted on the webapp side. But that was slow and I wanted to make ...
1
vote
1answer
210 views

Postgres - Insert performance with indexes

We have a table with about 20 columns in a Postgres 9.2.10 DB. For better performance on some SELECT queries, we plan to add an index on one column with data type timestamp. As the index also can slow ...
0
votes
1answer
35 views

Clustering a heap by date in stead of unique composite key

I've been working on improving the performance of a large reporting database. This database is 2TB in size, and one of the larger tables is a heap with three non-clustered indexes on it. The non-...
0
votes
0answers
350 views

trying to a lag a column in SQLite

Sorry if this is too basic for this forum, but I have had no luck in StackOverflow. I am a newbie to SQL. I am using SQLite. I have a table where rows represent individuals in a point in time. I am ...
2
votes
1answer
41 views

Finding the end of a Relationship chain optimally

I have a table that contains an id column, a parent column, and it's child column. So for every record, I know what record comes before it and what will come after it in a chain relationship, but from ...
2
votes
0answers
71 views

Performance differs on same query against two (almost) identical tables?

I'm running these two queries in SQL Server 2012: SELECT tpua.Field1, CAST(round(twg.Field2 * 2, 0) / 2 As Decimal(8,1)) As Field2, CAST((round((twg.Field3/10.0) * 2, 0) / 2) * 10 As int) As ...
1
vote
2answers
229 views

Is it a good idea to include a value column (e.g. from measurements) in an index?

Is it a good idea to include a value column into an index for a table which has many million rows? For example a measurement table which looks kind of like this: CREATE TABLE measurements ( id ...
0
votes
1answer
332 views

Complex query performance in MySQL InnoDB versus MyISAM

I am running on MariaDB 10.0.15 on a 4GB RAM cloud server with Local SSD disk for database. My model: users <n---n> right_holders <n---n> tracks ---n> raw_detections I have this ...
1
vote
1answer
132 views

placing conditions in a join query join on vs where

Is there and advantage to one of the following options over the other or are they the same ? select * from T1 Join T2 on T2.id=T1.t2_id and T2.field=value; vs. select * from T1 Join T2 ...
5
votes
1answer
2k views

SQL Server, TOP versus ROW_NUMBER

I'm learning about Execution plans and am trying out different queries and comparing their performance and stumbled upon this: SELECT StatisticID FROM ( SELECT StatisticID, ROW_NUMBER() OVER (...
0
votes
1answer
321 views

What is the best query to get the latest row version based on time?

I'm wondering what can be the most efficient query to get the latest rows from the following table. CREATE TABLE AGE_DATA ( INSERTED_DAY TIMESTAMP(6), HOSTNAME VARCHAR2(255 CHAR), CONCEPT ...
1
vote
3answers
172 views

Special function slow in where but not in select

I have kind of a special problem using a levenshtein algorithm in MySql. But I don't think that it's special for levenshtein. This query: SELECT *,levenshtein(word,'Facbook') FROM Words WHERE length(...
1
vote
1answer
93 views

Auto update stats turned off for a user database on SQL Server

While trying to improve the performance of few queries, we found via execution plan that there were lot of index / clustered index seeks. Therefore: First thing we did, was to check our Re-indexing ...
4
votes
2answers
491 views

What is the most efficient way to count the number of rows in a table?

I am using Postgres with the following query: select count(*) from image; The primary key on this table is non-incrementing; it's a unique serial number for the images stored in the table. Our app ...
0
votes
1answer
206 views

How to speed up mysql query against a 204 million record table

For some reason my server has been finicky lately and for the life of me I can't figure out why. I monitor the memory usage by running shell# watch -n 5 free -m without running any activities ...
4
votes
2answers
518 views

Efficently Remove 90% of a table's data

I am working on a process to remove 90% of a table's data because only 10% is needed for testing. The best way I have found includes storing 10% of the table's rows into a temporary table. Current ...
1
vote
0answers
107 views

Poor performance filtering mysql spatial join

First of all, sorry for the crossposting. I ask this question in stackoverflow and they sent me here I have a mysql table with spatial references (GEOGRAPHIC_FILTER), like cities, countries, natural ...
2
votes
1answer
126 views

MDX query is SLOWER with warm cache

I have a MDX query that executes faster on cold cache than warm cache. If I ran the same MDX query after restarting SSAS it takes 14 seconds. All subsequent runs of the same statement then take 50 ...
0
votes
0answers
74 views

SQL query running slow compared to its earlier execution time

One of our application using an SQL query is reported running slow. Current execution time:- 15 minutes and so Earlier execution time:- 30 seconds. SQL-Sentry Plan for the slow query I've ...
2
votes
1answer
42 views

Why can't I view the plan for view creation…?

mysql> EXPLAIN EXTENDED create algorithm =temptable view fisgen_temptable(inter,phone) as select intercom1,phone from fisgen where fid='f105'; I am getting an error like this: ERROR ...
1
vote
1answer
100 views

How to optimize a table/db with 200 insert/sec

I'm facing a very hard problem. I have a mysql DB (MyISAM) with a main table (user) where I write a row for every new user (around 10 fields). Every INSERT triggers around 10 new INSERT in 10 ...
5
votes
2answers
147 views

Sporadic execution time causing timeouts [closed]

We have a table with 100 million records holding 24 hour worth of data of our clients' customers actions (actions which are performed on 3rd party sites and replicated from there to our server) The ...
1
vote
1answer
375 views

set global variable parameter?

My prod_server version is 5.1.47 and want to set the slow query log dynamically for certain period of time. I refered one site(http://toastergremlin.com/?p=276) there is mentioned below 5.1.6 we need ...
4
votes
1answer
446 views

Subselect of MAX(Version) takes many minutes, though there are only ~20K records

I have a pretty complicated data model. I can't understand most SQL examples without an explanation of what's being modeled, so I'll try to explain. Mainlines -> Releases -> Overlays -> Calibrations &...
1
vote
2answers
230 views

Why would a query work slower when a where clause is added?

I've got two databases and both have got the same view over the same table which has the same indexes. The view selects the top location for a given IMEI from a locations table. CREATE VIEW [dbo].[...
0
votes
1answer
51 views

Query results that not are in previous query result

I want to retrieve from my table the id_product of the products which haven't got some attribute and it's value is not the ones i want For example i want the id_product of the products in which ...
1
vote
0answers
270 views

Simple avg query on large table much slower in PostgreSQL than SQL Server

I have a database with three tables items, parameters and measurements in both servers and want to query the measuerment table. But the query is much slower in PostgeSQL (9.4) vs SQL Server (2012). ...
3
votes
2answers
57 views

Is it a better idea (performance is key) to store in a normalized table and retrieve by JOIN or in a single nvarchar column and retrieve using LIKE?

NOTE: The application is not really about websites or keywords, I am using that here only to provide reference points for discussion. I have a data set that is really just a string of words. Think of ...
1
vote
0answers
33 views

Sampling and matching samples with spatial conditions

The Problem I have two tables with start points of a route and possible end points of a route which need to be matched against each other. I also have a given distribution for the distance between ...
3
votes
2answers
1k views

How to speed up select distinct?

I have a simple select distinct on some time series data: SELECT DISTINCT user_id FROM events WHERE project_id = 6 AND time > '2015-01-11 8:00:00' AND time < '2015-02-10 8:00:00'; And it ...
0
votes
1answer
49 views

Mysql JOIN with 25million rows

Recently I got stucked with a table where its grown to +25million and I have got a query that's doing a JOIN on it. The query take 2.5hours to execute even though I got Good 96GB RAM and RAID10 with ...
0
votes
1answer
53 views

MySQL - How can I improve this query? EXPLAIN attached

I have a query that I feel should go much faster then it does. Right now it'll execute around 1,5xx secs each time. I don't have any advanced knowledge of looking at the EXPLAIN and draw conclusions. ...
2
votes
0answers
31 views

Query optimization - contact should be extracted at most once for a particular customer

I have created a database for a telemarketing company Scenario is, the telemarketing company is selling contacts to the customers. Customers log on to the website and search for the contacts for ...
1
vote
1answer
166 views

intermittent performance issue - query with multiple CTE and trashed execution plan

I'm trying to optimize a SQL query that shows variable performance (less than 2 to more than 15s). Overview This is a ColdFusion website, with a SQL Server 2008 R2 backend The query is part of the ...
2
votes
1answer
171 views

Mysql Partitions with Partition limit

I have a partition table on basis of Primary key having 1.5 Billion rows in it. But when i check ibd files only 80 ibd show in GB's whereas 120ibd shows 1MB file. I would like to know how does the ...
1
vote
2answers
222 views

1 hour query, is there another way to do this?

I saw a couple questions similar to this but none could answer my question, really. I have two tables, and I am trying to find those rows where a match is found. user has about 350,000 entries and ...
3
votes
2answers
83 views

Slow queries on production Server

I am a developer who has been asked to look at a database slowness issue. I have had a look at the waits on the server and retrieved the following using a query from Red Gate's wait stats white paper. ...
1
vote
1answer
2k views

Optimizing Amazon RDS - MySQLTuner Recommendations / Feedback & Suggestions

I'm currently trying to optimize a database I've inherited for speed. The server is an Amazon RDS db.m3.large instance: 2 VCPUs 7.5 GB Memory 500 GB HDD My current strategy is to do the following:...
1
vote
0answers
163 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 ...
2
votes
1answer
5k 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
43 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
0answers
56 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 ...
2
votes
1answer
650 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
48 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
471 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
111 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 ,c....