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

2
votes
1answer
34 views

Row with latest timestamp

How to get the row with the latest value in a TIMESTAMPZ column? Is an index needed? Does an index change the strategy? Would behavior vary by database (I'm using Postgres 9.4)? My app records data ...
3
votes
0answers
22 views

Problem with Cluestered Index? or bad indexes? ( SQL Server 2008 R2 )

I have this query ( it was running in 2 minutes. With new indexes, it takes 40sec: select Top 30 codCliente from ( select t1.CodCliente, codcampo, valor, t1.chavealeat from ...
0
votes
2answers
59 views

How to improve perfomance or Query Execution Time in MySQL?

This is my query: SELECT a.z_companyid_pk "z_companyid_pk" , a.company_name "Client" , a.display_name "Display_Name" , ...
1
vote
3answers
84 views

Finding all date ranges that overlap a target date range

Say I wish to store when staff are on holiday (FromDate, ToDate) and then I wish to find all staff that are on holiday between two given dates (QFromDate, QToDate). Now assume that I have many such ...
4
votes
1answer
37 views

Fast alternative for “NOT IN”

I have a table A that got a field called id that is the primary key of this table. I also have a table called B which also have a field called id as primary key. Now I want to get all rows from ...
2
votes
1answer
136 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
2answers
2k views

Improve MySQL Query Performance - Too many records

I am using MySQL RDMS for a contact management system. Now a days my database grew in size and contains more than 1 million records. Checking duplicate phone numbers became a big issue during ...
0
votes
1answer
65 views

how to minimize the data scanned on my database using index?

Is there anyone how to minimize the data scanned on my database?? please show me.. using index? query SELECT IF (e.channel LIKE 'IAX2%', e.calldate, IF(d.channel LIKE ...
-1
votes
0answers
17 views

Which one is better Linq or stored procedure or TSql in EF [closed]

Our project is about HIS and we search for optimization queries in the database layer but we don't know which one of them is better for performance in EF. Please give us a proposal. Thank you. ...
4
votes
3answers
5k 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 ...
1
vote
1answer
22 views

Calling a View from Procedure

I have a Large SQL Statement with several Join which need to be executed with different Where Conditions based on Procedure Input Parameters. Ex: CASE pSearchType WHEN 1 THEN SELECT Col1, ...
1
vote
1answer
30 views

Int comparison very slow in query

I have this for simplicity fictional table which explains my problem: CREATE TABLE IF NOT EXISTS `posts` ( `pid` int(11) NOT NULL AUTO_INCREMENT, `user` int(11) NOT NULL, `posts` int(11) NOT ...
2
votes
1answer
84 views

Two queries usually run fast but get slow when joined

There are those 2 tables CREATE TABLE IF NOT EXISTS `media` ( `mid` int(11) NOT NULL AUTO_INCREMENT, `user` int(11) NOT NULL, `order_type` int(11) NOT NULL, `media_id` varchar(50) COLLATE ...
1
vote
1answer
27 views

Procedure to read from crontab like table and populate another one periodically

Postgres 9.3 Debian 7 I'm working on a mobile game project (poker like) where an admin user can register a recurrent tournament. As it's recurrent, I modeled it crontab like as follows. ...
0
votes
2answers
211 views

How can I optimize this Cross Table COUNT() mysql query?

I am trying to cut down the retrieval speed of this MySQL query. All instances of COUNT() point to the PRIMARY key of each field. Currently this query makes my MySQL hang. It seems ridiculous as I'm ...
1
vote
0answers
11 views

Optiming ACT_RU_JOB Query

We're using Activiti version 5.15.1 with timers in our workflow which sends reminders to users who are in the workflow. Problem is that we're creating jobs in ACT_RU_JOB table and even though there ...
8
votes
1answer
133 views

Overcome MERGE JOIN(INDEX SCAN) with explicit single KEY value on a FOREIGN KEY

Added 7/11 The issue is deadlocks occur because of index scan during MERGE JOIN. In this case a transaction trying to get S lock on the whole index at the FK parent table, but previously another ...
7
votes
1answer
384 views

What does duration measurement in sql server actually measure?

What precisely does the query duration measure when conducting a profiler trace? I have a stored procedure which performs data access (SELECT), and it is taking an average of around 30 seconds to ...
1
vote
2answers
210 views

Is possible to optimize this query or it's already optimized?

I've three tables: default_users, default_profiles, default_friend. This are the SQL for each one: -- -- Table structure for table `default_users` -- CREATE TABLE IF NOT EXISTS `default_users` ( ...
5
votes
3answers
205 views

Postgres is performing sequential scan instead of index scan

I have a table with about 10 million rows in it and an index on a date field. When I try and extract the unique values of the indexed field Postgres runs a sequential scan even though the result set ...
2
votes
1answer
12 views

Is Oracle 10g smart enough to work on a WHERE clause identically as the equivalent INNER JOIN?

Way back when Michael Jackson was still alive, I made this question: http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause And now I'm performance-auditing a system in which the ...
0
votes
1answer
141 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 ...
0
votes
1answer
36 views

PostgreSQL window functions very slow on big table

I have a PostgreSQL table constructed as device_id | point | dt_edit ----------+-----------+---------- 100 | geometry | timestamp 101 | geometry | timestamp 100 | geometry | ...
0
votes
2answers
290 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 ...
1
vote
1answer
54 views

Query or a New Column for Performance

I have below tables: ProductsTable (has product details), UsersTable (has all application users), RatingCategry (category for which user can rate a products like Quality, Cost etc.) and ...
1
vote
1answer
45 views

How can I improve this query in PostgreSQL?

I've been using PostgreSQL for a little over a year. I am continually whacked by queries that have counts in them, such as: SELECT COUNT(*) AS count_all, listings.id AS listings_id FROM "listings" ...
1
vote
1answer
127 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, ...
5
votes
1answer
104 views

'SELECT my_table.id' slower than 'SELECT my_table.*'

Running MySQL with InnoDB: I have a SELECT wide_table.* query that I want to refine to SELECT wide_table.id since that's all the calling code needs. Testing it out I've found that the execution time ...
0
votes
1answer
180 views

MySql: Slow join and group by on indexed columns

The following query takes 15-20s to run SELECT "Trade_US"."ID" AS "Trade_US.ID", "Server_US"."Name" AS "Server_US.Name", "Trade_US"."TradeTypeID" AS "Trade_US.TradeTypeID", "Users"."ID" AS ...
3
votes
2answers
103 views

Postgres 9.4.4 query takes forever

We're running Postgres 9.4.4 on CentOS 6.5 and have a SELECT query that has worked for years, but stopped working and hangs after we upgraded from 9.2 (it took a while to notice it, so I don't know if ...
0
votes
1answer
60 views

execution plan question

I have this query running for long time (@P0 nvarchar(4000)) select emaildmo0_.Id as Id7_ ,emaildmo0_.CreatedDate as CreatedD2_7_ ,emaildmo0_.EmailAddress as EmailAdd3_7_ ...
6
votes
1answer
136 views

sp_cursoropen and parallelism

I'm running into a performance problem with a query that I can't seem to get my head around. I pulled the query out of a cursor definition. This query takes seconds to execute SELECT A.JOBTYPE FROM ...
9
votes
2answers
151 views

SARG cardinality estimate, why not full-scan?

Why there is no full-scan (On SQL 2008 R2 and 2012)? Test data: DROP TABLE dbo.TestTable GO CREATE TABLE dbo.TestTable ( TestTableID INT IDENTITY PRIMARY KEY, VeryRandomText VarChar(50), ...
1
vote
2answers
62 views

Should I stop an awfully inefficient MySQL DELETE statement?

I'm running the following statement on MySQL 5.5 in a 1GB 1CPU SSD virtual machine from Linode: DELETE table FROM ( SELECT MAX(id) id, field FROM table GROUP BY field ) temp_table INNER ...
2
votes
1answer
139 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) ...
2
votes
1answer
51 views

MySQL Filesort/Order By Slow

I've been struggling with a query for the past few weeks and it now just appears as gobbly-gook to me when I try to optimize. The query runs - at about 2.3 seconds, but I know there has to be a more ...
1
vote
1answer
44 views

Update records from one table to another in different databases where condition is accomplish

I have two tables stored in two differents DBs and I need to perform some sync between both tables based on lastmodifieddate and lastSyncAt. Let's call them as db1 and db2. This is the DDL schema for ...
2
votes
1answer
168 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 ...
1
vote
2answers
200 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
3answers
285 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 ...
1
vote
1answer
428 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
2answers
70 views

Minimizing SELECT latency in PostgreSQL

I am evaluating Postgres 9.4 performance on a machine with Intel i7 quad-core 3.6 GHz CPU, 8 GB ram, and 7400 rpm HDD (no RAID) running Linux Mint. The DB schema has the following table: ...
3
votes
4answers
569 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
43 views

Select SQL_Calc_Found_Rows query extremely slow

I'm Wordpress 4.2.2 on an Ubuntu 14.04 LTS server, with PHP 5.9x and MariaDB 10.0.17. When clicking on a post on my site I see many slow queries that all resemble this and it ends up taking as 8 or 9 ...
1
vote
2answers
33 views

Reorganize Index Task [closed]

Can the Reorganize Index task (maintenance plan or SSIS package) be used to tune performance? Is this an adequate step of triage for degraded application/query performance?
1
vote
1answer
36 views

Statistics needs to be captured for query analysis

What statistics are required to theoretically prove that X query work better than Y query. As per my knowledge I need to look for: Query elapsed time Logical read Logical write Physical read ...
4
votes
0answers
96 views

Why is query thousands of times slower with DESC?

I have a PostgreSQL database (AWS RDS) and ran the following queries in psql: livenet=> EXPLAIN ANALYZE SELECT * FROM outputs ORDER BY height LIMIT 10; Limit (cost=0.57..36.82 rows=10 ...
0
votes
0answers
201 views

How to load XML data into an oracle db table?

Is there a way to load the XML format data into an Oracle 11g DB table directly ? Can we write a stored procedure to accomplish the same in a more efficient way with the best performance? Can we ...
9
votes
2answers
106 views

Why isn't the primary (clustered) key being used in this query?

I have a SQL Server 2008 R2 table whose schema structure looks as follows: CREATE TABLE [dbo].[CDSIM_BE] ( [ID] [bigint] NOT NULL, [EquipmentID] [varchar](50) NOT NULL, [SerialNumber] ...
1
vote
1answer
130 views

Rerunning query in the same connection takes significantly longer

I am having a weird issue when a running a query that joins some semi-large tables (one table is about 2 million rows and the other about 21 million) together. What happens is the query does not run ...