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

learn more… | top users | synonyms

1
vote
2answers
255 views

How to avoid timeouts during big DML operations

I have a big insert script I need to run. Its about 55,000 records and 160 columns. The script is already created and I can't create it again. The problem I have is that this runs for about 4 hours ...
1
vote
2answers
15k views

Query to search for a substring in xml

I use below query to search for a substring in whole xml(including node name and node value) SELECT * FROM tablename WHERE ( Charindex('abc',CAST([xmlcolumn] AS VARCHAR(MAX)))>0 ) I want an ...
1
vote
1answer
305 views

query optimization delete nested query [closed]

Can the following query be optimized ? DELETE FROM COLOR_TAGS WHERE COLOR_PROJECTID = $id AND NoteID IN( SELECT ID FROM note_note WHERE BibID=$bid AND isdel=0);
1
vote
2answers
216 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` ( ...
1
vote
1answer
39 views

How to optimize query with max on Postgres?

I'm trying to make a relatively simple query, but it's taking much longer than I'd expect. I have an index in place, but it doesn't seem to be helping much. Here's the query. It takes over an hour ...
1
vote
1answer
60 views

how to check whether or not queries are doing partition elimination when accessing my partitioned table?

I am considering partitioning a table. I am trying to think in terms of how we query that table so that I achieve partition elimination. Suppose I have a table that has a partition by month, what ...
1
vote
1answer
68 views

How to measure I/O contention after adding calculated fields on indexed views to a table in SQL Server?

I have successfully added several calculated fields to different tables, for example: ALTER TABLE dbo.tblBGiftVoucherItem ADD isUsGift AS CAST( ( ISNULL( CASE WHEN ...
1
vote
1answer
57 views

how to get the missing indexes that are “filtered indexes”

I have a procedure that shows me the missing indexes. It has been very good to me, but it does not show me the missing FILTERED INDEXES. I would like my procedure to be able to catch and suggest ...
1
vote
1answer
37 views

optimise my.ini for key query

I have a database system with a fairly complex dashboard query. First of all the details. Data model: DROP TABLE IF EXISTS `contract`; CREATE TABLE IF NOT EXISTS `contract` ( `id` int(11) NOT ...
1
vote
1answer
68 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 ...
1
vote
2answers
168 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 ...
1
vote
3answers
353 views

Slow query when adding additional where clause

Here's my setup (Postgres 9.3) Posts project_id Messages post_id kind updated_at I'm trying to get the most recently updated 100 messages that belong to posts (on a specific project) that are ...
1
vote
1answer
93 views

What are the side-effects of disabling nested loops in postgres?

A query of multiple joins spends a lot of time on nested loops. I disabled the nested loops and the query ran much faster. However, I have been told that nested loops might give me wrong results by ...
1
vote
1answer
48 views

stored procedures location influence their performance?

I have a procedure SP_MYPRO that updates data in MYDB1. 1) the performance of SP_MYPRO would be different if I place it in MYDB2 instead of MYDB1? 2) what if the procedure SP_MYPRO only read data, ...
1
vote
1answer
259 views

Performance of checking for data in related tables

NB: this question is purely academic / to help improve my understanding of SQL Server performance. Given a master table which relates to one or more other tables, how would you determine the best ...
1
vote
2answers
106 views

MySQL query performance question

I have a MySQL database where I would like to return results based on multiple rows. id | key | value | selector --------------------------------------------------------- 1 | username ...
1
vote
2answers
2k views

Increased query_cache_size, Queries slowed drastically on increased traffic

I increased our servers query_cache_size to 2gb, hoping for performance benefits. After sometime the query performance would drastically decrease. Once I dug in deeper, I found the queries in State ...
1
vote
1answer
3k views

Have Postgresql query planner use nested loop w/ indices over hash join

I'm having a problem with some StackOverflow-schema related data loaded into PostgreSQL 9.3.4. I have a query that is taking about 10x longer than it should, due to the fact that it is choosing to use ...
1
vote
2answers
236 views

Why does “SELECT *” take longer than “SELECT <primary_key>”?

I'm having a problem with a query running on a little PHP program. When I use * to select all the columns, it takes way more to return the result than when I use just one column. The query that is ...
1
vote
3answers
5k views

Is table partitioning improving performance? Is it worth it?

I have just been involved in a project on which I will have to develop a data migration process and a web interface that uses an already existing SQL Server DB. This DB was developed by another person ...
1
vote
2answers
188 views

Oracle result cache behaviour

Oracle by default caching the query results and function results(Buffer cache). I have noticed this with AutoTrace utility, where the physical reads are huge on first execution, but from next ...
1
vote
2answers
3k views

Find nearest values from MySQL Table

I want to fetch nearest rates for a given value from below table. mysql> select * from rates; +------+----------+----------+ | ID | Type | Rate | +------+---------------------+ | 1 | ...
1
vote
2answers
221 views

Need some support on MySQL Query

I have a query SELECT leads.id, sfav.id is_favorite FROM leads INNER JOIN teams tst ON tst.team_set_id = leads.team_set_id INNER JOIN members members ON tst.team_id = ...
1
vote
1answer
2k views

Optimizing ORDER BY for simple MySQL query

I'm trying to optimize this really simple query, and it gives me grief for a day now :( Seems pretty straightforward, I need to select with a JOIN from 2 tables, and get top X results sorted in a ...
1
vote
2answers
1k views

Mysql. Block/disconnect slow queries

I am experiencing issue. Someone 'attacked' my server: simply by searching the same phrase with multiple requests. As it is text search request and database indices are not used, the engine searches ...
1
vote
1answer
881 views

Query is not using indexes on third table in left join

My query is not using indexes on third table(pci promotion_coupon_images) . i was tried with using index and force index in query but there is no change in result. any body please suggest me how ...
1
vote
1answer
48 views

simple query is not using indexes

My query is not using indexes. it was taking around 5 seconds. output is 500 rows only. it is a simple query Please find the explain plan and table structure here. Explain Plan mysql> explain ...
1
vote
1answer
93 views

Dealing with large log files

I'm new to the database world and have recently started working with a large database with several tables with mainly varchar text and integers. The two largest tables are of ~50 million and ...
1
vote
1answer
1k views

Splitting a column to normalized table

I have a de-normalized table as Original De-Normalized Table id text 23 first,second|third,fourth,fifth|sixth I want to normalize the database to create the following tables Table 1: (Split by ...
1
vote
1answer
195 views

EXISTS and sub-query cost

i've just wondered if there's a difference in cost(memory,cpu) of an EXISTS or NOT EXISTS in following queries. Does it make a difference if i select NULL, 1 or columns? 1) SELECT id FROM Parent p ...
1
vote
1answer
432 views

SQL Server 2012 Performance Test

I've been given a task to test where an SQL Server 2012 can achieve better performances - we have two options: Use the SQL Server as an On-Premise machine (Standalone server that we have) Use the ...
1
vote
2answers
180 views

Can a poor database layout cause performance issues for simple queries?

I have a database layout that is realy a mess and needs to be optimized. Does the layout of the database and some queries that are executed cause other queries to be slow too? For example, an insert ...
1
vote
2answers
1k views

Update Large Table

I have these two tables: user_profile: user_profile_id_int int primary key user_profile_id varchar user_activity: user_profile_id_int int key user_profile_id varchar both fields in user_profile ...
1
vote
1answer
79 views

SELECT Causes Performance Issue [closed]

I have a SQL SELECT statement that has joins on 27 of our warehouse tables. Original statement was taking more than an hour everyday so I decided to optimize it. I created some indexes but still it ...
1
vote
1answer
25 views

What are the consequences of enabling slow_query_log and flushing the cache on mysql?

I have a large database that is performing poorly recently. I want to enable the slow_query_log to investigate the queries and flushing the cache but I need to know what are the consequences of doing ...
1
vote
1answer
52 views

Does InnoDB store row counts in secondary index B-Tree non-leaf nodes?

Does InnoDB store row counts of child nodes in secondary index B-Tree non-leaf nodes? If so, does MySQL use this information to speed up COUNT(*) queries with range constraint (assuming appropriate ...
1
vote
2answers
166 views

Slow SQL query with LEFT JOIN

What my query is doing: I have two SQL tables and I want to copy datarows from the one table to the other table. I am doing this for statistics. The source table includes all current ...
1
vote
1answer
77 views

Speed up COUNT(*) when using an index

I have a table similar to this (simplified): CREATE TABLE books ( id INT AUTO_INCREMENT, category INT NOT NULL, PRIMARY KEY (id), KEY (category) ); This table has over 10,000,000 ...
1
vote
1answer
75 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
54 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
42 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 ...
1
vote
2answers
81 views

Is there's another function to optimize this query in MySQL?

I want to use a function that returns for me the same result in one line is that's possible?? MIN( CASE WHEN (data.pro LIKE '1 %' ) THEN 1 WHEN (data.pro LIKE '2 %' ) THEN 2 ...
1
vote
1answer
122 views

Why isn't Postgres doing an index-only scan?

I am using Postgres 9.4. This is my table: id | integer | not null default nextval('frontend_prescription_id_seq'::regclass) presentation_code | character varying(15) ...
1
vote
2answers
38 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 ...
1
vote
2answers
204 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 ...
1
vote
1answer
89 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 ...
1
vote
1answer
90 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 ...
1
vote
1answer
45 views

Can I use SPARSE somehow on a non-nullable bit column with mostly false values?

I have a table that stores the results of queries that are run at least once a day. There's a bit column that represents whether the row is from the most recent run of a particular query with a ...
1
vote
4answers
103 views

Why does this query take 32 sec to execute?

I keep my history records in one table and my pertinent data in another. I want to grab only the files from myfiles that currently have a given status recorded in statuses (ie, the latest status is ...
1
vote
1answer
87 views

Query performance issues

I need help in improving the performance of the following query. It has the purpose to select one record out of a group of records with an identical ID (doublettes). Rules are that Date2 must be the ...