New answers tagged

4

Use LEAD() to get the next row within the TicketId partition. Then join to a Calendar table to get all the dates between. WITH TAwithnext AS (SELECT *, LEAD(AssignmentDate) OVER (PARTITION BY TicketID ORDER BY AssignmentDate) AS NextAssignmentDate FROM TicketAssignment ) SELECT t.TicketID, c.Date, t.DepartmentID FROM dbo.Calendar c JOIN TAwithnext t ON ...


2

This is a quick way of doing (I have not tested for performance or scalablity) -- create Calendar table -- borrowed from @Aaron's post http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-3 CREATE TABLE dbo.Calendar(d DATE PRIMARY KEY); INSERT dbo.Calendar(d) SELECT TOP (365) DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY number)-1, '20160101') ...


1

One thing to try with IN is to change it to EXISTS (with the proper modifications to the subquery): SELECT COUNT(order_id), user_id FROM MyCustomTable a WHERE type='x' AND EXISTS (SELECT 1 FROM MyCustomTable b WHERE type ='y' AND user_id='56' AND ...


0

SELECT COUNT(UserID) as CountUsers FROM (SELECT A.user_id as UserIDs, A.order_id as XTag, B.order_id as YTag FROM MyCustomTable A INNER JOIN MyCustomTable B ON A.User_ID = B.User_ID AND A.Order_ID <> B.Order_ID WHERE A.type='x' AND b.type = 'y' AND user_id <> '56') A Would this work?


6

They're not redundant, no. Each statistics object includes a histogram of the values stored in the first listed column. For the remaining columns, only density information is kept at each level (no histogram). For example, the proposed statistic on (ClassID, SegmentID, GroupID, Tier1, LanguageID, Active) would create: A histogram for ClassID values A ...


1

The only reason for a recheck to happen in this particular query is that the bitmap is too large to fit in work_mem and so has to be down-graded to lossy. So to avoid the recheck, try to increase work_mem, if you can afford to. You shouldn't have to increase it by much to hold 1138854 tuples. Newer versions of PostgreSQL make this clearer, by including ...


3

Add the second predicate of your query to the partial index as well: WHERE "Post"."createdAt" > '2015-08-19 14:55:50.398' Your timestamp is probably a moving target, but I am going to assume you have lots of old rows that are excluded in most of your queries and only few "younger" rows are of interest. A typical use case. You can cut off old rows in ...


2

Other than the DTA, is there any other way to find out the missing multicolumn statistics? Nope ! query optimizer assumes that columns within the same table are independent. Joe Sack talks about it here The query optimizer assumes that columns within the same table are independent. For example, if you have a city and state column, we may ...


3

I think you will be much better off testing your actual workload under sync and async, and see if you observe any differences. Coming up with a sweet spot to suit a particular feature is kind of backwards. Generally, async is primarily beneficial when you don't care if the query that triggered the stats update still runs that last time with the old stats ...


4

Better data types text is a sub-optimal data type for key columns. It would be more efficient to use integer. Related: Indexes: integer vs string performance if the number of nodes in the index is the same '26c72242-7e3b-4982-92c5-021b622d7ecd' in your example looks like a UUID. If you need to use UUIDs, still don't store them as text. The appropriate ...


2

Your query doesn't loop 10483 times, it loops once (loop=1) for each loop, but it has to sort 10483 rows first prior to discarding all but the first 5 rows. Your last query was almost correct, except for the fact that you filter user_account by the users with greatest follower_count regardless of being involved in the discussion or not. Also, isn't this ...


0

There is a new tool called pg_pathman (https://github.com/postgrespro/pg_pathman) that would do this for you automatically. So something like the following would do it. SELECT create_range_partitions('master', 'dt_created', '2015-01-01'::date, '1 day'::interval);


4

Please note that a computer generating a list of "missing indexes" should not be swallowed whole. You will still need to decide which indexes to create, which recommended indexes are near duplicates of existing indexes, and how you should want to handle those issues. It still requires you making a decision since the generated recommendations need some ...


7

Check the View Tuning Output: If you want to save all of the Transact-SQL scripts that create or drop all database objects in this recommendation into one script file, click Save Recommendations on the Actions menu. As always review and test the recommendations before blindly applying them to your PROD environment. I would highly suggest to look ...


8

You are using a linked server to access Table1 and Table2. The first query is sent as is to the other server and executed there returning only the rows you want. The second query is doing a join between a local table TB_BRANCH and a remote table Table1. To do that it fetches all rows from Table1 and all rows from Table2 to your local server and does the ...


-4

Subqueries are always low on performance when involved with joins. Try replacing the subquery with a LEFT JOIN TB_BRANCH b ON t1.BRANCH_ID = b.BRANCH_ID WHERE b.START_DT <> '99999999' and see if the performance improves.


4

You do not specify which RDBMS you are working with. Most what I write here should be quite independent but I mostly have experence in MySQL so maybe different systems allow some other optimizations. The (SELECT count(*) FROM review WHERE to_user_id = u.id) as reviewCount is a dependent subquery - it will be executed for each row in your results. Even if ...


9

Memory Limit Exceeded The optimizer was forced to stop looking for better plan alternatives due to memory pressure. The reason for that should be investigated and corrected, then query compilation attempted again. The plan returned may very well not be the one the optimizer would have selected had the low memory condition not existed. Time Out This reason ...


2

Assuming "first level" means the leaf level of a nonclustered (secondary) index, the b_li/2 part accounts for the cost of scanning (using sequential I/O) through half those index blocks. Assuming "file records" means the base table, the r/2 component represents the cost of looking up additional data not present in the secondary index. Since this is a ...


8

If you go over to http://schemas.microsoft.com/sqlserver/2004/07/showplan/showplanxml.xsd (which is the link you'll see if you open an execution plan as xml), you'll see the three reasons listed, which are: TimeOut MemoryLimitExceeded GoodEnoughPlanFound The articles you mention seem ok for finding these events, are you having a specific problem? The ...


1

Your strategy for getting information from full_path can be useful for a one-off, but for ongoing queries to it, especially over millions of records and expecting quick results, it is far from optimal. Considering the sheer size of your tables, you'll probably benefit from datawarehousing. If your tables are constantly updated, you'll need a trigger to keep ...


1

Perhaps this will help. If you'll rely on the account_id from full_path often, then you'll benefit from a function and a functional index for it: CREATE OR REPLACE FUNCTION gorfs.f_get_account_from_full_path(p_full_path text) RETURNS int AS $body$ SELECT (regexp_matches($1, '^/userfiles/account/([0-9]+)/[a-z]+/[0-9]+'))[1]::int $body$ LANGUAGE SQL ...


1

My understanding: The table contains 1M rows of which 250k are returned by the query. There are 500k rows with foreign_key_id = 1 and 500k rows with af.foreign_key_id2 IS NOT NULL. The query using full table scan (actually doing full index scan on the PRIMARY key in InnoDB) will read all 1M rows sequentially and check each of them for the conditions. The ...


2

PostgreSQL can only make use of a function index when the comparison is against the results of the function, e.g.: AND (s.full_path)::text ~ '/userfiles/account/[0-9]+/[a-z]+/[0-9]+' Alternatively, create the index without typecasting: CREATE INDEX CONCURRENTLY ix_full_path ON gorfs.inode_segments USING btree (full_path); Note also that the character / ...


0

Your question is too broad for any answer here to fully address, so I'll just address one point. For the major query type of "Give me the latest data generated by a device", you could create a new column in the table that gets the same data as the "data generation time" column and then have a periodic procedure that nulls older values in that column for ...


3

From comments: DISTINCT is very similar to GROUP BY <all selected columns> and getting rid of the temporary table may be impossible when joining many tables as the server needs it to check uniqueness of the returned rows. Covering indexes (Using index) are quite useful when you need to get the top performance. I prefer IN() instead of multiple ORs ...


2

Your query has many issues that difficult optimisation. You use LEFT JOIN in several tables where INNER JOIN should suffice (e.g. all cases of ged_tbl_document_dv and ged_tbl_document_type). You include tables that you later never really require (e.g. all cases of ged_tbl_document_type), so removing all of them wouldn't impact the result (replace all ...


2

Using an index requires bouncing back and forth between the index and the data. In MyISAM, each index is a BTree sitting in the .MYI file. At the leaf node of the index is a pointer into the .MYD file. (Or, for FIXED, it will be a record number.) Your SELECTs are happy to scan linearly through the index (a BTree is efficient at that), but then for each ...


1

Indexes ! Use InnoDB, not MyISAM. Reformulate the query so it does not 'explode' (LEFT JOIN), then 'implode' (GROUP BY)... Something like: SELECT a.article_id AS article_id, article_title, article_content, article_status, article_date_time, ( SELECT SUM(article_hits) FROM phpkb_article_visits WHERE article_id = ...


1

The standard approach to get rows N through M is to do something like SELECT * FROM (SELECT a.*, rownum rnum FROM (SELECT emp_id, last_name FROM employees WHERE positionID in (1,3) ORDER BY <<something>>) a WHERE rownum <= 60) b WHERE rnum > 50 Note that you need ...



Top 50 recent answers are included