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

Query Performance - Why is a query slower when there are two criteria on the same column?

I have these two queries against a view, where I've added WHERE clauses against a date column (Indexed, Unique, Non-Clustered, Ascending): SELECT ColA FROM MyView a WHERE a.[Date] >= ...
0
votes
2answers
42 views

SQL Server 2005 query with multiple conditions, some indexed, some not

I have a table with 50 million rows. It is indexed on zip. A query Select * from table where zip = '12345' returns 20,000 rows in about 2 seconds When I add an additional condition it jumps to ...
0
votes
1answer
19 views

MySQL 2 Million Rows, longtext and varchar indexes

I'm trying to find a solution for this, with no success. I have a table with ~2M rows, and I need to do a WHERE with 3 expressions using OR. name LIKE '%PARAM%' OR text1 LIKE '%PARAM%' OR text2 LIKE ...
-1
votes
2answers
110 views

Why is “select join insert” so much faster than “loop insert”?

MySQL docs state that the time required for inserts has these approximate proportions: Connecting: (3) Sending query to server: (2) Parsing query: (2) Inserting row: (1 × size of row) ...
0
votes
2answers
186 views

How to optimize indexes on MySQL query with various sorts

I have an INNODB table levels: +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
3
votes
1answer
146 views

Unused index in range of dates query

I have a query that is not using existing indices and I do not understand why. The table: mustang=# \d+ bss.amplifier_saturation Table ...
1
vote
2answers
34 views

Two (almost) identical queries, but only one of them uses the index

I have a table with two columns, batch_id and study_id. There are indexes on both columns. I want to get a random row back from MySQL. I originally wrote: SELECT * FROM alt_join_batch_study ORDER BY ...
-3
votes
3answers
40 views

How work SELECT in database [closed]

My table has 8,400 rows of user information. Say I submit the query SELECT name FROM users WHERE name = 'jhon'; I know that 'jhon' is the 400th row in my dataset. My question is, will the DBMS ...
0
votes
1answer
32 views

Optimizing a query on Mysql 5.5.24

I am trying to optimize the query SELECT CONCAT(lastname, ', ', title, ' ', firstname) AS fullName, e.lastname, e.firstname, e.email, e.userType, e.userID FROM employee e, ...
1
vote
1answer
49 views

MongoDB document size for collection - impact on RAM and query performance

I have been advised on stackoverflow to ask this question on dba.stackexchange instead - We are using MongoDB version 3.0 with WiredTiger storage. As newbies to MongoDB, we may have designed our ...
6
votes
2answers
299 views

Unexpected Implicit conversion in a procedure

I have a procedure like this (simplified): CREATE PROCEDURE test @userName VARCHAR(64) SELECT * FROM member M INNER JOIN order O ON M.MemberId=O.MemberId WHERE M.Username = @userName There is a ...
0
votes
0answers
41 views

Which of sys.dm_exec_query_stats columns should I pay most attention?

I'm trying to tune my database and currently staring into sys.dm_exec_query_stats (with actual queries): SELECT SqlText.text, * from sys.dm_exec_query_stats CROSS APPLY ...
0
votes
2answers
64 views

Oracle query optimization

We have a query that runs on a view and its execution time is about 6 min. SELECT COUNT(*) FROM someschema.item_total_violations WHERE channel_id = 122507833 AND item_date >= ...
0
votes
1answer
21 views

represent multiple entities to one entity relationship

If suppose, I have following tables: Here, person can own either home or car. person can't own both at a time. If person owns home, then owns field will be equal to 1, if it owns car, owns field ...
1
vote
1answer
152 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
8 views

indexing table column on the go of mysql query commit

It is said that to optimize the query cost of execution, then it would be by indexing the field of tables in database. Since I don't have a permission to enter the phpmyadmin in my web server as I am ...
0
votes
1answer
75 views

Improve Query Performance

I have the following sqlite3-Tables: db.execute("""CREATE TABLE IF NOT EXISTS STATIONS (StationUID INTEGER PRIMARY KEY AUTOINCREMENT, StationNumber TEXT NOT NULL, ...
0
votes
0answers
24 views

Best way to run a formula (segment) a large number of users with custom attributes

Here is my question. I have a table that stores a large number of people (80 mil). Each person has a GUID id. Then I have another table (or set of tables) that stores information about people. e.g. ...
0
votes
1answer
34 views

InnoDB Table has backed up INSERT and UPDATE queries, high CPU

I am running an MySQL DB with all InnoDB tables. I often have many queries backed up for the same table when I run SHOW PROCESSLIST; Basically, I keep a table of every logged in customer and update ...
6
votes
2answers
150 views

Hindrance in TSQL SVF Call

I have a simply Products data table with 100k records (just generates some random data): set nocount on create table dbo.temp_Products ( [ProductID] int, [Type] tinyint, [Price] float, ...
2
votes
1answer
2k views

Mysql search matching multiple values in multiple columns

I need to speed up a search. Currently we let our users input a name: John Doe Then in Java we split that string to create multiple values (or "terms") ["John", "Doe"] and loop through the terms ...
0
votes
0answers
60 views

Slow running Oracle query caused by unnecessary full table scan

I'm by no means an expert with Oracle, but I know enough in terms of how to analyze explain plans and perform basic optimizations. However, this time, Oracle has stumped me with it's explain plan. ...
1
vote
0answers
54 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 ...
0
votes
2answers
208 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 ...
0
votes
0answers
41 views

How to divide a big SQL Query into small query

I am trying to traverse the whole file system. I am picking each and every file and then trying to insert it into the SQLite Database. Before inserting I am also calculating the checksum of each file. ...
0
votes
0answers
38 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
50 views

Viewing execution plans for queries with parameters

I have a sample query generated by an application (Microsoft Dynamics AX 2012 in this case) which is ineffective performance-wise (cross joins, order by etc.) I would like to display its execution ...
0
votes
0answers
58 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
1answer
40 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 ...
0
votes
1answer
21 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) ...
0
votes
0answers
20 views

DB space vs query complexity tradeoff

I have a dilemma - Right now I have a query that I do constantly - joining 5 tables on different columns, calculate stuff per row, distinct on that, and do that with offset and limit (pagination). I ...
2
votes
3answers
2k views

MySQL: can not get rid of “Using filesort” in a simple query

Simple query but can not get rid of "using filesort": CREATE TABLE IF NOT EXISTS `online` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `uid` int(11) NOT NULL, `expiration` int(11) NOT NULL, ...
1
vote
1answer
34 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
25 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 ...
2
votes
0answers
47 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 ...
0
votes
0answers
39 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 ...
0
votes
1answer
25 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 ...
1
vote
2answers
185 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 ...
3
votes
3answers
4k views

GROUP BY one column, while sorting by another in PostgreSQL

How can I GROUP BY one column, while sorting only by another. I'm trying to do the following: SELECT dbId,retreivalTime FROM FileItems WHERE sourceSite='something' GROUP BY seriesName ...
0
votes
1answer
69 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 ...
5
votes
1answer
448 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 ...
2
votes
1answer
4k views

statistics State in MySQL Processlist

I have a busy MySQL Server having query SELECT sys_sess_state, index_state, timeout_lvl, last_queued_dt, last_polled_wait_dt, create_id, create_dt, modify_id, modify_dt FROM PQR_AM_SYSTEM_SESSION ...
23
votes
5answers
10k views

Why would I NOT use the SQL Server option “optimize for ad hoc workloads”?

I've been reading some great articles regarding SQL Server plan caching by Kimberly Tripp such as this one: http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/ Why ...
0
votes
1answer
69 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
55 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 ...
2
votes
1answer
2k views

MySQL: Which join is better between left outer join and inner join

Which join is better peforming if all of them provides the same result? For example, I have a two tables employees(emp_id,name, address, designation, age, sex) and work_log(emp_id,date,hours_wored). ...
0
votes
0answers
46 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 ...
0
votes
1answer
71 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 ...
3
votes
2answers
433 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
31 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 ...