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

learn more… | top users | synonyms

3
votes
1answer
310 views

which one is more efficient query?

I have a table called STUDENT +-----------+-----------+-----------+---------------+ | StudentID | FirstName | LastName | EnrollmenDate | +-----------+-----------+-----------+---------------+ | ...
3
votes
2answers
1k views

How does compound index help speed up query?

Say we have a bunch of column in mysql table. Latitude Longitude Say we make an index (Latitude and Longitude) Well, the index will sort by Latitude first and IF the Latitude is the same it'll sort ...
3
votes
2answers
4k views

Will altering order of columns in GROUP BY affect performance?

I have a SQL query like this: SELECT A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END), COUNT(D) FROM SomeTable GROUP BY A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END) On a huge dataset the actual execution plan ...
3
votes
1answer
48 views

Query Plan Error

I have an interesting situation. I use SQL2014 Enterprise version with SP1. The following query results an error message : The query processor ran out of internal resources and could not produce ...
3
votes
2answers
55 views

What's the best way to diagram this “relation” looking for performance and|or disk space savings

I am working in a DB diagram and I have a table called cm_identifier_type. Each record of this table can have or not a downloadable file (saved a file path). I have identified two possibles ways to ...
3
votes
2answers
5k views

Dramatic decrease in SQL Azure performance after scaling to new edition

Yesterday, we scaled one of our databases in Azure from the old Business edition to the new Standard one (S2). Since then, it has been behaving rather erratically. We have a relatively simple table ...
3
votes
3answers
193 views

Change table scan in query plan to seek

I have an ORM generated query (Django) running against a local Postgres 9.1.11 instance that looks up ~5 records from two specific match fields. Due to the nature of the EDI load program, I need to ...
3
votes
2answers
805 views

UPDATE query with JOIN taking forever

I'm not very good with databases so please keep in mind even if the solution sounds like something everyone would have already tried, please post it because I likely have not tried it and it would ...
3
votes
3answers
147 views

How can I improve this query?

About a year back I introduced a query which returns a sort of "Customers Also Purchased" data-set. At the time it ran reasonably fast however, as of late it's become very slow, sometimes taking up to ...
3
votes
2answers
684 views

How do I count rows with two properties in one index scan in SQL?

I have a table Units where I have rows ItemId, ItemCreationDate, ItemUnitsCount. ItemId is a primary key and I have a clustered index over it. I need to output the following: for each day I need to ...
3
votes
1answer
156 views

Do WHERE IN queries reindex the table after each set within the query?

I have a basic WHERE IN query that takes too long (30+ secs) to process. DELETE FROM `table1` WHERE ( djID, localID ) IN (("128","26"),("110","32")) The table I'm querying on has about 20 million ...
3
votes
1answer
64 views

Do fixed-width rows improve PostgreSQL read performance?

I have a table articles: Table "articles" Column | Type | Modifiers | ...
3
votes
2answers
416 views

Slow fulltext search due to wildly innacurate row estimates

Fulltext queries against this database (storing RT tickets) seem to be taking a very long time to execute. The attachments table (containing the fulltext data) is about 15GB. The database schema is ...
3
votes
2answers
26k views

Why can't we access SQL Server when a long query is running in Management Studio?

We have a SQL Server Database (2012 Standard edition). The database is accessed through an ASP.Net web page which displays the count of databases in different tables on page load. This work fine ...
3
votes
2answers
60 views

Why is MySQL ignoring my indexes?

I have a query select distinct id from patient where company_id in (1) and (name_last like 'peter%' or name_first like 'peter%' or remote_id like 'peter%') running on a table that looks like this ...
3
votes
2answers
780 views

Efficiently query MAX over multiple ranges

When performing a MIN() or MAX() over a single range covered by an appropriately sorted index, SQL Server does a TOP() and so returns the value after fetching just one row. When the search criteria ...
3
votes
2answers
1k views

Improve CTE performance

is there a way to improve the performance of a recursive CTE like below, I am unsure if I can add indexes on joins when the joins are using ROW_NUMBER? DECLARE @File_Name VARCHAR(8000), @Disk ...
3
votes
3answers
2k views

MySQL optimization - year column grouping - using temporary table, filesort

I have a transactions table which is having 600,000 records, I need to list the count for the dashboard on financial year basis. The table used is MyISAM. I tried adding index for the transaction date ...
3
votes
1answer
1k views

Query taking long on table with low record count

I have an Oracle flat table that grew to just below 800MB. The table was truncated and new records are being inserted. Currently there are <40 records in the table but a select * takes >30secs. ...
3
votes
5answers
682 views

Query optimization [Oracle]

The Query: select JOIN_RESULT.batch_id as BATCH_ID, JOIN_RESULT.unique_doc_id as DOCUMENT_ID, JOIN_RESULT.brand as BRAND, JOIN_RESULT.message_date_time as UPLOAD_DATE, ...
3
votes
4answers
2k views

Optimize Query with Derived Table

I have the following query select ps.id, ps.title , IFNULL(s.likes,0) as num_likes , IFNULL(s.comments,0) as num_comments , IFNULL(s.ratings,0) as num_ratings , IFNULL(s.views,0) as num_views , ...
3
votes
1answer
58 views

Update multiple Ids when SET = (Sql Query)

I want to update multiple records however the value being set is based on the primary key of the given record. This is not an issue with a single record as I can use: UPDATE T1 SET A1 = (SELECT ...
3
votes
1answer
126 views

what would be a good choice of clustered index (and other indexes) for this situation involving a indexed view?

To help me improve the performance of some queries I have created the following indexed view: CREATE VIEW DBO.vwOrders WITH SCHEMABINDING AS ...
3
votes
1answer
121 views

Best database design for website ranking

I am working on a project in which I need to rank websites in two way - Global rank and their rank in a particular country. There are various factors based on which both are calculated. There are ~200 ...
3
votes
2answers
172 views

Any SQL where clause performance similar to where 1=0

(i'm using SQL Server 2008R2 or later) For centralize manage and consistent, there is a view definition as follow: CREATE VIEW view_all_situation ( select null as src, tf.col1, sum(col2) as fee, ...
3
votes
1answer
276 views

Why are my queries getting really slow?

I own a WordPress installation, and Updated to 3.4.1. After the upgrade, I experienced a very strange slow-down. After all, I used the define('SAVEQUERIES', true); in my wp-config.php with ...
3
votes
3answers
2k views

Speed up search across multiple columns

We have a table: user_info (user_id,domain_id,first_name,middle_name,last_name,work_place,.,.,.) Now, we have a user search that search across three columns first_name,last_name and work_place. ...
3
votes
1answer
1k views

Can you have a WHERE clause with a greater than operator on a composite key?

I'm a web developer (not DB admin!) having performance problems processing a 1.7m record table on MySQL. Here is that table : CREATE TABLE `bbcmapimp_pc_raw` ( `postcode` char(8) NOT NULL, ...
3
votes
2answers
60 views

SQL Server “fix” query for Hash Match instead of Nested Loops

At the moment I have a view that is running very slow. It takes about 3 minutes to execute. This suddenly happened. To debug what was going on I used an old dataset and compared both queries. On the ...
3
votes
1answer
53 views

Indices for a join query with datetime range

Given the following query: SELECT DISTINCT COUNT(DISTINCT "patients"."id") FROM "patients" INNER JOIN "transactions" ON "transactions"."patient_data_id" = "patients"."patient_data_id" WHERE ...
3
votes
1answer
148 views

How to insert/update millions of rows inside a sql server table(s) in an effective way?

Often in my job I have to create a procedure inside SQL Server that will process millions of data rows, save them into a temp table (staging table) and finally save them into a table (s) in a ...
3
votes
1answer
93 views

Select performance problems with conditional index vs non-conditional on large volume table

We are trying to change some indexes on a large-volume table to be conditional indexes (function-based indexes) to reduce how much space is being used for indexes against this table. When testing the ...
3
votes
1answer
512 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 ...
3
votes
1answer
265 views

Help with indexing narrow table with many millions of rows

I have a good amount of experience with SQL Server, but not much with indexes and I fear I'm a little bit out of my depth in trying to figure out how to correctly structure a table that has 100+ ...
3
votes
2answers
2k views

SQL Server - Logical Reads lowered, Execution time remained the same

A view executed Select columns from view where col= "date range" (view contains calculation in it Col1+Col2*col3 AS Col4). It took 7 seconds for 500K rows to return, logical reads some 144k. Added ...
3
votes
1answer
981 views

How to optimise window queries in postgres

I have the following table with approximately 175k records: Column | Type | Modifiers ...
3
votes
1answer
534 views

SQL Server WHERE clause on CLR method (spatial) peformance

I have a query that joins to a table containing 2 million rows and need to filter on its geometry column by its geometry type (using geom.STGeometryType()). The query takes less than a second to run ...
3
votes
2answers
11k views

SQL SERVER - Linked Server and query performance

I have a performance problem related to SQL Server/linked servers/views. I hope you can help me understand what is the best way to do what I want =). I have a database K with 3 linked servers ...
3
votes
1answer
348 views

Query Performance Issue

I have this below query which is behaving bit weird . well Weird in the sense that I couldn't find complete explanation for this. Version : Sql Server 2008 R2 Enterprise No fragmentation . ...
3
votes
2answers
239 views

Query taking too much time

My query is taking too much time to execute. Please help me here. mysql> explain select pcm.catalog_id from cat_produ_catal_map_defer pcm, cat_catal_catal_map_defer ccm, cat_catal_defer c ...
3
votes
3answers
668 views

Why does the query cost change so much and how to prevent it

The query causing me the issue is too big, but the piece that seems to be the core is quite simple, I will try to go with that: The query has an structure like: SELECT ... a lot of stuff ... ...
3
votes
1answer
234 views

Get profiles values where message.sender_user_id = profile.user_id

I have this tables: default_messages CREATE TABLE IF NOT EXISTS `default_messages` ( `id` int(11) NOT NULL AUTO_INCREMENT, `subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL, `message` text ...
3
votes
2answers
3k views

Copy huge amount from 1 table to other

I have two tables with same structure I need to copy over all tables from table A to table B, the problem is that some records from table A already exist in table B so that made the Import Fail. So I ...
3
votes
1answer
659 views

Mongodb high lock percentage / slow queries

I'm working with an application using MongoDB that is getting a fairly large number of incoming requests and I've noticed that the responses are getting slower as the number of requests get higher. ...
3
votes
3answers
117 views

Is it better to create another table to possible null values in relational databases?

I'm designing a relational database to use in MySQL. I have the next situation: In one table there are fields that in the most of situations are NULL. The fields would be like: Table name: tabla ...
3
votes
1answer
321 views

Pagination performance, with subquery, inner join and where

I am trying to optimize following query (this is most simplified version I could came up with): SELECT tr.Id, StatusDate FROM ( SELECT tr.Id, tr.StatusDate FROM mon.ArchivedTaskResults_201504 ...
3
votes
2answers
78 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. ...
3
votes
1answer
95 views

Running a query on an improperly indexed, 600gb table

This is my first post on here and I'm really baffled by this. I have joined a team and somewhere in the past a horrific table was created which has been bloated to a point of 600+gb of data. ...
3
votes
3answers
466 views

Nested table as condition prevents using index

The following query joining a subquery does a TABLE ACCESS FULL when the master-table uses nested table as condition. When the condition is a simple =, the same query does an INDEX RANGE SCAN as ...
3
votes
1answer
566 views

Extremely long query time after upgrading to Postgres 9.3 from 9.1

We're attempting to upgrade from Postgres 9.1 to Postgres 9.3 on a FreeBSD 9.2 zfs box. Performance is noticeably slower across the board, but we have one query that has gone from 353ms to >200000ms. ...