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

learn more… | top users | synonyms

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 ...
7
votes
1answer
185 views

Query against sys.schemas and sys.synonyms runs very slow for one user

Scenario: SQL Server 2014 (v12.0.4100.1) .NET Service runs this query: SELECT name, base_object_name FROM sys.synonyms WHERE schema_id IN (SELECT schema_id FROM sys.schemas ...
7
votes
1answer
88 views

how come a table variable is improving the performance of a query in this circumstance?

for this specific case, that I will try to explain below, using a table variable is performing better than not using a table variable. I would like to know why, and if possible, get rid of the table ...
0
votes
3answers
50 views

Performance of updating row if content has changed

I'm trying to make a website where there is a form that contains input type fields that are editable. In fact I get the elements from one of my tables (containing an id column and a string column) ...
0
votes
1answer
65 views

Hashed and heap indexed object storage table insert and query performance

This question is partly to ask advice and assure this design is sane. I state the the desired properties upfront here, then go in more detail in the rationale and follow up with the design and example ...
2
votes
0answers
41 views

Order by index unexpectedly slowing query down

I have two tables: houses has ~415,000 records, and account_houses has ~525,000 records. I've changed the table names for security reasons, but basically I am using this query: SELECT "houses".* FROM ...
3
votes
1answer
79 views

Workaround for the limit of the number of missing indexes suggested by SQL Server's DMVs

In production, each instance of SQL Server has over 250 databases (what we call 'OrgDb's). The project I'm currently working on, aims to send all the reported missing indexes by SQL Server's DMVs to ...
0
votes
2answers
112 views

How to optimize this SQL Server query?

Anyone have any suggestions for how to optimize this query? Running SQL Server 2008.. The object is to look at accounts that were created yesterday, compare their email addresses and phone numbers ...
0
votes
1answer
55 views

slow count on text column

Table t1 with 500k rows, 2TB in size, one text column. SQL Server 2008 R2 SP3. CREATE TABLE [dbo].[t1]( [TId] [int] IDENTITY(1,1) NOT NULL, [Weight] [int] NULL, [TDate] [datetime] NULL, ...
0
votes
1answer
57 views

Searching from Database having billion records, which database should i use [duplicate]

We are having 1 billion phone numbers and we have to search them against numbers which we send for promotion to filter out which are not promotional registered. That is we have lets say 1 million ...
0
votes
0answers
25 views

MySQL user variables vs repeated parameter literals. - performance

I am currently designing a large query which uses a number of parameters numerous times throughout the query and subqueries. After checking out some posts on multiple repeated params, I decided to ...
3
votes
2answers
62 views

Slow MySQL query

I have 3 tables: Name Engine Rows Data Size Index Size Total Size product MyISAM 11.06M 859.59M 861.20M 1.68G product_manufacturer MyISAM 3.09K ...
0
votes
0answers
52 views

How could I improve speed of fetch in Oracle?

I've got a table of taxi GPS trajectory records with approx 60 million rows, including date, time, ID, longitude, latitude, speed, bearing etc., which were imported from .csv files with setting each ...
20
votes
6answers
4k views

Performance of a=0 and b=0 and … z=0 vs a+b+c+d=0

This is a simple question that I can't seem to find the answer for. In terms of performance, If I have a WHERE clause such as a=0 and b=0 and ... z=0, Would I gain any performance if I replaced that ...
3
votes
2answers
50 views

IN Clause - listing values VS query

My app runs a lot of queries like this SELECT ... FROM Table WHERE CategoryID IN (SELECT CategoryID FROM OtherTable WHERE ..) To save performance I decided to cache the second subquery in the ...
3
votes
1answer
68 views

Can this SQL Query be improved

Related to one of my previous questions: How to select a subset from a query result I now have this Query: CREATE TABLE #JourneyLogs ( JourneyStartId INT, JourneyEndId INT, ...
2
votes
1answer
171 views

Query Time is doubled because of a column in select

I have a Stored Procedure which is calculating some data and inserting it into a Temp Table. Then I am selecting the Data from the Temp Table. Select AssetId = iAssetId, ..... SpeedKM = ...
1
vote
1answer
49 views

SQL getting IDs from one table with multiple entries in another table

In my database I have two tables: Persons and PersonAttributes. One Person may have several Attributes associated with it. What I would like to do is get a list of all Persons(/IDs) that have at ...
2
votes
1answer
91 views

Optimize performance for query with CTE using a multicolumn index

I've got this slow query: WITH flks AS (SELECT id FROM public.ja_feedlog WHERE gtime BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2016-01-24')::BIGINT AND EXTRACT(EPOCH FROM ...
2
votes
3answers
100 views

Update a column for every row of a large table to a unique value in a set range

We have a SQL Server table with over 3 million rows in it. Each row represents a product. These products are provided by about 500 suppliers, and are updated each night via a file from each supplier. ...
0
votes
0answers
23 views

Advice on query optmization - Multiple Unions

I'm trying to understand how this might be better optimized. I currently have these three different queries to union the results of each. The first case finds all records with the where condition of ...
1
vote
1answer
36 views

Query taking more than 5 minutes to execute

I've a query that runs in 2 identical environments: Production and QA. In QA server, it takes around 6 seconds and in production it takes 5 minutes or more! Query is as follows: mysql> SELECT ...
0
votes
1answer
32 views

Is the same thing(internally) putting conditions in a query over a view that directly on the view?

I'm having trouble understanding performance with queries on views. Given this two scenarios, the queries internally will be the same? SCENARIO 1 View: CREATE VIEW TEST.SIMPLEVIEW AS SELECT ...
4
votes
1answer
122 views

Efficiently search for a prefix range in SQL Server

Let's say I want all records with a prefix between two user-supplied alphanumeric values of the same length. Thus, if the user enters A010 and A025, I want to return A0101, A0200 and A0259. What I've ...
7
votes
1answer
321 views

Better execution plan if parameter is stored in local variable

I have two stored procedures. This one is incredibly fast (~ 2 seconds) CREATE PROCEDURE [schema].[Test_fast] @week date AS BEGIN declare @myweek date = @week select distinct serial ...
11
votes
3answers
236 views

Why is my EXISTS query doing an index scan instead of an index seek?

I am working on optimizing some queries. For the query below, SET STATISTICS IO ON; DECLARE @OrderStartDate DATETIME2 = '27 feb 2016'; DECLARE @OrderEndDate DATETIME2 = '28 feb 2016'; SELECT ...
3
votes
1answer
56 views

Updating multiple columns using Case

I have a query that looks like below: UPDATE some_table SET column_x = CASE WHEN x_specific_condition THEN new_value_for_x ELSE column_x END, column_y = CASE WHEN y_specific_condition THEN ...
1
vote
1answer
70 views

Awful performance on joined queries while great separately

I have a simple queries: SELECT t1.[IntField], CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField' , t1.[FloatField1], t1.[FloatField2] , t2.[FloatField3] FROM ( SELECT ...
0
votes
1answer
33 views

Which columns to index with LEFT JOIN and GROUP_CONCAT

So i have a complicated MySQL search query, and I wonder which columns I need to index to speed up searching: SELECT wpp.ID, post_title, wp_terms.name AS category, wp_terms.slug AS slug, ...
2
votes
0answers
46 views

where to add a column with low cardinality, decimal type into an index?

I got a question regarding the best way to put a bad column for an index in an index, so that I can satisfy my query below, and similar others that follow the same pattern. on the query below, all ...
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 ...
6
votes
2answers
253 views

Is there a SQL Server implementation of the Longest Common Substring problem?

Is there a SQL Server implementation of the Longest Common Substring problem? A solution that checks with all rows of a column in SQL Server? I have seen solutions that take two strings as input, but ...
0
votes
1answer
52 views

getting the total of amount of money of each user between two dates even if no record is found

I am trying to get the total amount of money which the user spent between a certain period. The code that I have come up with can successfully get the result but I also want to show the users who ...
2
votes
1answer
64 views

Why is index not used for IN expression with unnested array

I have a query that joins two tables but refuses to use use index unless I add a limit. The case is a bit complicated so best described by showing you. I have two tables, users and ...
0
votes
1answer
132 views

Huge database… queries too slow. How to improve performance? [closed]

I'm currently managing a MySQL database with 30 million rows(many more to come). The entire database is 280GB. The server has 4GB of RAM and 3.2Ghz 4 core processor. The largest column stores an array ...
0
votes
0answers
45 views

Recursive CTE Optomization

I have the following rCTE used in several different queries to determine the requesting user's place in the hierarchy. Buyer is the User who is purchasing. ItemID is the thing that they can ...
1
vote
0answers
73 views

Improving the performance of procedures showing warnings for implicit conversions

We have recently migrated from SQL Server 2008 R2 to SQL Server 2012 Standard Edition. We and witnessing poor performance related to quite a number of stored procedures running against a database that ...
1
vote
4answers
187 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 ...
7
votes
2answers
217 views

Finite State Machines in SQL

I'd like some input on an issue I'm having. We have a section of code that we repeat throughout our stored procedures, and every time it takes quite some time to process, and when combined the number ...
1
vote
1answer
57 views

Extra fields in user_role / roles tables or a new table

I have a user_roles table which I use to manage permissions and user's access to certain things. For example, a team manager is a role. Since a team manager belongs to a specific team, the way I ...
-6
votes
2answers
142 views

Why over 1.3TB desired memory for this query, it should have no memory grant? [closed]

SQL Server version is 2014 Developer SP1. Trace Flags 272, 610, 1118, 1206, 1222, 8048, 9481 are globally turned on. We had to turn on flag 9481 since the new cardinality estimator badly effected ...
3
votes
1answer
78 views

Optimal design for a large database

Brand new to databases, using postgresql on linux. I've done a decent amount of reading on the issue and looked through similar questions on here, but just want to confirm the most efficient way of ...
3
votes
1answer
484 views

Improving select query

I was at an interview for a new job today and got asked a question regarding a SELECT query: SELECT lastname FROM users WHERE name='John'; This SELECT query is to run over a million rows. I was ...
1
vote
2answers
59 views

Check existence on other tables: performance advice

This is my first post. I have this data model: I want fetch some profile, with checking if they have any page or user assigned or not. I have tried multiple queries and finally came to these tows: ...
1
vote
1answer
97 views

Slower running Query with better Filter?

mysql> SELECT * FROM history_uint h WHERE h.itemid='1825689' ORDER BY h.clock DESC LIMIT 2 OFFSET 0 ; Empty set (0.00 sec) Explain: id: 1 ...
0
votes
0answers
24 views

How to Efficiently Handle Queries On a Table Subset

I have a MySQL database where one table in the database contains a datetime field and the other fields contain numeric data. One application frequently queries the server using a WHERE clause on one ...
0
votes
1answer
131 views

How to change query_cache_type to 2 in AWS RDS?

I want to change my AWS RDS parameter query_cache_type value to 2 , I have done it in parameter group dashboard as in the given image. I have also restarted my mySQL server with the changed ...
0
votes
1answer
43 views

Limit crosstab query based on join from outer query

In my database I have two tables, assessments, and responses, in a 1:N relationship. I have a query that looks a little something like this WITH "cross_responses" AS (select * from crosstab( ...
1
vote
1answer
69 views

get query execution time in command prompt [duplicate]

I'm doing this query select * from rentals. This table has more than 10,000 rows and it takes more than 4 minutes to retrieve the results. But I get this time after I get the results: 10000 rows in ...
7
votes
0answers
121 views

SQL Server - Handling localization of strings in nested non-deterministic view stacks

While profiling a database I came across a view that is referencing some non-deterministic functions that get accessed 1000-2500 times per minute for each connection in this application's pool. A ...