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
2answers
54 views

Awful execution plans after migrating from MySQL 5.5.11 to MariaDB 5.5.41

We just migrated our web site database from a server running MySQL 5.5.11 to one running MariaDB 5.5.41. Everything is working mostly fine, except there's at least one query from our storefront that's ...
1
vote
1answer
146 views

high PAGEIOLATCH_SH waits on server [closed]

EDIT: I know this question is closed, but I hope it will help someone. The problem is on our disks ( cluster ). Using PerfMon, I could create some counters ( disk reads and writes ) and the reads ...
1
vote
0answers
55 views

Most effective way to manage a growing list of time series data in an MS Access database

I am building a database to store time series data along with some other summary statistics. Since this is a local application I do not have access to more robust database solutions and am confined to ...
6
votes
3answers
142 views

Convert date in date list condition to list of date ranges

I want to look for all the records that occur on specific dates. SELECT * FROM table1 WHERE date(column) in ($date1, $date2, ...); However, as many of you, know this kind of comparison doesn't get ...
2
votes
0answers
107 views

Why is this MySQL query “using where” instead of “using index”?

Could you help me with this query, please? SELECT p.patid, MAX(c1.eventdate) as eventdate from patient as p left join op_adv_effects._clinical as c1 on p.patid = c1.patid left join ...
2
votes
1answer
91 views

Query looks optimal but logical reads is huge

I run the following (really simple) query in SQL Server: SELECT MAX(PK_Field1) FROM MainTable kh WHERE kh.PK_Field1 >= '2014-12-01T00:00:00' AND kh.PK_Field2 = 1572 AND ...
3
votes
1answer
97 views

DB2 CASE statement faster with parentheses around entire WHEN

Trying to optimize slow queries, someone on my team come across this. Having this in our stored proc: CASE WHEN MYCOL BETWEEN @STARTNUM AND @ENDNUM AND RECTYPE = 'RO' THEN 1 ELSE 0 ...
1
vote
0answers
41 views

PostgreSQL DB freezes with large inserts

I am developing a web application with an SQLAlchemy ORM linked with PostgreSQL database backend. I have this usecase where a client uploads a file of about 20 MB containing large data. What I am ...
2
votes
2answers
79 views

MySQL taking too much CPU

Below is my .cnf file and if I see my CPU consumption 99.99% of it my the mysqld commands. The MySQL server is connected from remote machines that update data in it frequently, but I make sure that ...
1
vote
1answer
92 views

Index to filter a view before joining the underlying tables [closed]

Using SQL Server 2008 R2 Suppose you have a query like the following select top 1 * from view_cms_user where UserId = 1234 or UserPasswordRequestHash = 'anxazno121o3n' view_cms_user is basically: ...
0
votes
0answers
33 views

Create table with select Query on DB link

I'm trying to execute the following query, it is taking more than usual time. I'm retrieving data from db link. Without the create table statement the query is giving results. For Create table also if ...
0
votes
2answers
90 views

How can I replace the following query with ISNULL function in SQL?

I have the following query. For performance optimization reasons I have been asked to replace the logic with ISNULL function in SQL. How can I achieve this? please advice. SELECT ...
1
vote
0answers
63 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
1answer
29 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 ...
0
votes
2answers
51 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 ...
1
vote
2answers
35 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 ...
0
votes
1answer
37 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, ...
-3
votes
2answers
42 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 ...
1
vote
1answer
107 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 ...
-1
votes
2answers
225 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
0answers
46 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 ...
6
votes
2answers
306 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
2answers
68 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
33 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 ...
0
votes
0answers
9 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
0answers
26 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
76 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
140 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. ...
6
votes
2answers
154 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, ...
0
votes
1answer
39 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 ...
0
votes
0answers
62 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
2answers
86 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
106 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 ...
0
votes
0answers
44 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
24 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
42 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
57 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
0answers
23 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 ...
1
vote
1answer
51 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
26 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 ...
0
votes
0answers
58 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
0answers
52 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
188 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
89 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 ...
0
votes
1answer
31 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
582 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
88 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
64 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 ...
0
votes
0answers
57 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
82 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 ...