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

learn more… | top users | synonyms

2
votes
0answers
92 views

How can one improve the performance of a query that selects on a low cardinality column in MySQL?

I have a "State" column that has a low cardinality (three possible value), on which most of my queries perform an equality selection (WHERE col = 'blah') AFAIK you need something like a bitmap index ...
2
votes
0answers
878 views

MySQL: Improve performance for one row insert into table with unique constraint

I have a table with ~ 40 mil records, with a unique index on the url collumn, of type varchar(255). Now the insert speed is about 30/s, is this expected? how could I improve it? I can't use bulk ...
2
votes
0answers
298 views

Simple Select * on SQL Server 2008 R2 table of 1812 rows is taking 3 minutes to load

A query on a table of 1812 rows is taking over 3 minutes to execute. Is there a reason this should be happening? If I execute a SELECT * statement on any other table the results pop back ...
2
votes
0answers
143 views

How To Analyse Index Scans

I am using merge replication with SQL Server 2012. During a sync using a merge agent there are a large number of index scans. I can see this by running a profile capturing SCAN:Started and ...
2
votes
0answers
323 views

end user complaining poor performance of a query or application in SQL server [closed]

Any suggestions on what set of questions I should be asking an end user or an application user to drill down to the real issue of his complaining about slow responses to his queries? For example: ...
2
votes
0answers
216 views

Get all comment.message, status.message, users.* and profiles.* where users.user_id exists in friend.friend_id or friend.user_id

I need some help building this query because it's a bit complicated at least for me. I need to get all comment.message, status.message, users.* and profiles.* where users.user_id exists in ...
2
votes
0answers
1k views

SQL query optimization in Prestashop e-Commerce solution

I'm working on a layered navigation module within Prestashop e-Commerce solution. I'm trying to optimize a SQL query which creates a temp table during execution. I think this is creating a ...
2
votes
1answer
282 views

poor and/or unreliable InnoDB performance

I recognize that there are a ton of posts like this, but after reading up on InnoDB performance problems, nothing I tried has helped. I have a database containing four InnoDB tables. The largest ...
2
votes
1answer
708 views

SQL Server 2005 - Query optimization for fetching large number of rows from table with 750 million rows

Brief on application: This is audio fingerprinting application, being developed in Java with Microsoft SQL Server 2005 database. I have one application to create fingerprints of original songs and ...
2
votes
1answer
71 views

GROUP BY rewrite to subqueries with easily interchangeable AND OR conditions

Tables: content tag_map extrainfo |id|title| |id|tagid| |id|lat|lng| |--|-----| |--|-----| |--|---|---| |1 |Lorem| |1 |8 | |3 |0,4|0,1| |2 |Ipsum| |2 |9 | ...
1
vote
4answers
660 views

Performance degradation while updating tables having 10s of millions of records

I want to update tables ( my be 20-30 ) having 10s of millions of records each. The problem is that it is taking too much time for the update process and also at that time CPU usage also goes very ...
1
vote
3answers
221 views

Improve a query using cursor to SET Based approach

I am still new to Query optimisation, and I have a Stored Procedure which uses a Cursor to go through each row within the Table, and performs the following operations: Calculate the Time Difference ...
1
vote
2answers
556 views

Query Performance Tuning on a huge table

I have the following table Transactions. The table looks like this: CREATE TABLE [dbo].[Transactions]( [TransactionID] [bigint] IDENTITY(1,10) NOT NULL, [PlayerID] [bigint] NOT NULL, ...
1
vote
2answers
144 views

How to optimize this query?

Query: Select * from `t_event` where `create_user_id`=7 and (`event_create_date`)=('2012-12-18 00:00:00') and `event_type_cd`=11 and `domain_id` =602 and `job_id` =1 limit 1 Table structure: ...
1
vote
3answers
1k views

Improve performance of a Fact Table

I have a Fact table CardTransactionFact Table Structure TABLE [dbo].[CardTransactionFact] [CardTransactionID] [int] IDENTITY(1,1) NOT NULL, [TransactionTerminalID] [int] NOT NULL, ...
1
vote
2answers
116 views

Consideration for a little bit heavy database

Environment information OS : Windows Server 2012 R2 ( 64bit ) RAM : 16.00GB CPU : Intel(R) Xeon(R) CPU E5-2609 @ 2.40GHz SQL : Windows SQL Server 2012 Standard Brief Database Data Information ...
1
vote
1answer
221 views

Understanding execution plans using TVPs

I have some slow running queries (8 seconds across 1M records grouped by months) and have been trying to make sense of the execution plans. We use around 10 TVPs to send in a set of filters that the ...
1
vote
2answers
215 views

Why would a query work slower when a where clause is added?

I've got two databases and both have got the same view over the same table which has the same indexes. The view selects the top location for a given IMEI from a locations table. CREATE VIEW ...
1
vote
2answers
107 views

OrderBy int slow for large table join

I have roughly 1.5 million records. This query works fine for smaller data, but it gets a second slower for every few hundred thousand records. For 1.5 million, the query is noticeably struggling. I ...
1
vote
4answers
185 views

MySQL COUNT(*) performance

I have a table with more than 15m rows. I need the total number of rows. So: SELECT COUNT(*) FROM thetable; Which takes around 50s to complete. Explaining gives me Select tables optimized away. I ...
1
vote
1answer
298 views

Is there a way I can shorten the below query into a single query?

I have the below query. IS there a way I can put this into one single query? If not, can I shrink it further anyway? Please advice. DECLARE @highRegion TABLE(regionId INT, countR INT) DECLARE ...
1
vote
2answers
174 views

Simple query is slow on 4M-rows table

I have in production a MySQL table pageviews with 4M rows that records page views of users on posts. I need to know which posts a specific user have read, but this request takes up to 15 seconds to ...
1
vote
2answers
2k views

Improving `GROUP BY` query performance in sqlite3

I have a little web-application that is using sqlite3 as it's DB (the db is fairly small). Right now, I am generating some content to display using the following query: SELECT dbId, dlState, ...
1
vote
1answer
440 views

Retrieve next queue item

I have a simple table in SQL Server 2012 which implements a processing queue. As data has been inserted the query to retrieve the next item has gone from <100ms to a constant 5-6secs. I would be ...
1
vote
1answer
337 views

Precautions when designing database schema if tables are going to have billons of rows

I am about to start work on telephone dictionary kind of project. Its confirm that there are going to be billions of records in dictionary table, and each entry from this dictionary table may have ...
1
vote
1answer
4k views

Some queries are very slow after index rebuild

After finding out almost all databases on my SQL Server had fragmentation over 40%, I decided to do an index rebuild on all tables using a fill factor of 80. After rebuilding all indexes, some ...
1
vote
2answers
2k views

Why would a long running query remain sleeping and with null wait_info

The following query was run on SQL Server 2008 R2 and took over 1 hour to run: select log_id, type, instance_id from ts_cache_log where log_id > 146400 order by log_id It produced 0 rows ...
1
vote
3answers
652 views

Checking if the query is made async or sync on SQL Server 2008 R2

I have tried to make async calls to my SQL Server instance as you may see on my SO post: ASP.NET MVC 4 asyncronous database call: never returns the HTTP response I did a benchmarking and the sync ...
1
vote
2answers
108 views

Validation of Partitioned table Performance

We are testing partitioning on a table which contains a datetime column (DateColumn). Partition function I am using is: -- 138623 + 16774 records CREATE PARTITION FUNCTION pf_TableName_DateColumn ...
1
vote
3answers
90 views

charindex wrong result

Charindex returns no record if only one letter is provided. If parameter matches the whole word it shows the record. Why??? USE [database] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER ...
1
vote
1answer
61 views

Optimization of complicated sums and counts in related tables

I have a categories table, which have two columns, id and name. I have a types table, which have three columns, id, category_id and points. I have a products table, which have three columns, id, ...
1
vote
1answer
52 views

Can't eliminate index scan

I have a query, and even using SQL Sentry, I can't eliminate the index scan. This is the query: SELECT TOP 30 codCliente FROM ( SELECT t1.CodCliente, codcampo, valor, t1.chavealeat ...
1
vote
3answers
108 views

Why does the query plan still sort a table despite having an index sorted on the columns?

I'm using Postgres 9.1 I have two tables I am joining: wikidb=> \d page Table "public.page" Column | Type | Modifiers ...
1
vote
2answers
65 views

Get the pais_id not associated to fabricante_distribuidor_id and producto_solicitud_id, the opposite from SELECT query

I have this query for get all the pais_id given a fabricante_distribuidor_id and producto_solicitud_id, the query works fine. SELECT DISTINCT nom.pais_id FROM negocio.fabricante_producto_solicitud ...
1
vote
3answers
114 views

MySQL select with between optimization

I got issues with some geo IP query. So basically here is create code of table: CREATE TABLE `geo_ip_city` ( `id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT, `begin` BIGINT(20) NOT NULL DEFAULT ...
1
vote
1answer
996 views

Why isn't SQL Server using my non-clustered index and doing a clustered index scan?

Here are my full tables: CREATE TABLE [dbo].[tblCrawlUrls]( [cl_IdUrl] [int] IDENTITY(1,1) NOT NULL, [cl_CrawlNormalizedUrl] [nvarchar](200) NOT NULL, [cl_RooSiteId] [smallint] NOT ...
1
vote
2answers
366 views

optimization of expensive subquery

The following query gets the last report (where latitude and longitude and secs are <> 0) associated with the specified units: SELECT reports.* FROM reports INNER JOIN ...
1
vote
2answers
314 views

Prune unused joins

I have expected that selecting from a view would not join the tables from which I don't query any values, but it doesn't seem to be the case. I have a bunch of tables with the following structure: ...
1
vote
2answers
303 views

Does this cause overhead ? Select name from User where user_name LIKE '%alex%'

I'm wondering if this statement can cause real overhead if many concurrent users send the same request to mySQL InnoDB storage engine. Let's say I have a table that has the user_name, and someone want ...
1
vote
2answers
325 views

Why are my results returned twice?

I've this query: SELECT DISTINCT `default_wall_posts_comments`.`c_id` as c_id, `default_wall_posts_comments`.`comments` as comments, UNIX_TIMESTAMP() - default_wall_posts_comments.date_created ...
1
vote
1answer
2k views

Index Strategies on Text or NVARCHAR(MAX) Fields

I have the following query (simplified for question) I'm trying to speed up for a read only DB ... SELECT [sysid] ,[Date]=CONVERT(CHAR, DATEADD(D, [date], '1800-12-28'),101) ,[From]=[from_addr] ...
1
vote
1answer
30 views

Subquery inside a select vs left join

Often I have to return the count of of some row in a query that fetch other non related rows. For example A table User a table Review and a Table picture User: id nickname Review: id to_user_id ...
1
vote
1answer
58 views

Forcing correct execution plan when passing a table UDT to a table function

I have a scalar function that returns a big XML, created over a bunch of invoices. The exact list of invoices to feed to the function can be calculated using several different ways, but the function ...
1
vote
2answers
76 views

How to optimise MySQL query that uses dependent subquery

I have a web app that has a custom feed of images. The image queried are based on whether the users have previously seen the image and ordered by a Hacker News style score. There are only 1000 rows ...
1
vote
2answers
68 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
2answers
68 views

Is LIMIT processed before or after retrieving data?

MySQL. My heavy query crashed our MySQL server. If I add LIMIT 10 will it make my query more lightweight? (both for indexed and unindexed queries). Which way LIMIT are processed: before the rest of ...
1
vote
3answers
209 views

Index optimization and cardinality

I'm by no means a DBA expert. Rather, a Magento developer having to troubleshoot some slow queries. I'll jump right in -- here's my attempt to EXPLAIN the troublesome query: EXPLAIN SELECT ...
1
vote
1answer
54 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
2answers
222 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 ...
1
vote
3answers
3k 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 ...