New answers tagged

2

Do not use double(10,2) it involves two roundings. Either do DECIMAL(10,2) or plain DOUBLE. For monetary values (price) use DECIMAL(...) so that there will be no rounding. DOUBLE is 8 bytes; DECIMAL(10,2) is 5 bytes. Can you have "negative" Clicks? Suggest INT UNSIGNED. It is usually a bad idea to splay arrays across columns (L1, ... and Attribute1, ...


1

You say that "In every query the user_id clause and date clause is a must", so an index on (user_id, Date) should help everything. Since the other conditions are variable, this is probably the best you can do for indexing for this query. You can add other columns to the end of the index if they are commonly used. With the current structure, it looks like ...


0

Could not find proper my.cnf,so had to find the preferred location where mysql is searching for my.cnf by using the command:- mysql --help | grep cnf displaying:- /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf so created a file /etc/my.cnf with the required settings. And restarted the server ,everything worked like a charm.


0

The cache cannot be enabled dynamically if it was disabled at the start, only the other way around. Just change the type to 2 in my.cnf and restart the server.


2

the query does not return 43 rows but 1 row that contains the number 43 the execution time of a query does not depend on the number of rows it return but on the number of rows it inspects. from your query plan you can see your query makes a full table scan. This means it reads the table from begin to the end and reads all 16 millions of rows of the table ...


2

Try creating the following index: CREATE INDEX s_contact_idx_001 ON s_contact_lupd_lastupdby_name ( last_upd, last_upd_by, last_name ); You should change the name to correspond to your naming convention and make sure that it is unique.


3

According to the statistics provided user I/O wait time is 6172 out of db time 8383. When you see "user I/O" as a major wait event, SQL tuning is the best answer, specially adding missing indexes. Since the plan shows it is using full table scan you can add indexes in the column used in where clause.


4

You should consider using LEAD and LAG Analytic Functions and appropriate indices for sorting. I've changed one self-join query with them. Elapsed time was reduced tenfold.


2

I don't really follow the examples but based just on: Each time an event of type=0 happens, a counter increases for the item involved, and each time an event of type=1 happens, it decreases again. The counter must always lie between 0 and 3 (inclusive). Easier if you just change event_type to -1 and 1 select item, min(seq) from ( select ...


1

Query #1: You are missing indexes on sessions.ip and auth.session. The latter is really important because now during the join for each row in sessions (~2M) MySQL has to check all ~4M rows in the auth table instead of picking the one or few directly using the index. That means the query is maybe 1,000,000 times slower than it needs to be. You can make the ...


2

type int(11) NOT NULL COMMENT '1. chatter, 2. marketing', -- Change to TINYINT UNSIGNED. This will save 3 bytes per row. Ditto for several other fields. And for those fields that are also in indexes, especially the PK, that "3" gets multiplied. bigint(20) unsigned -- If you are not expecting more than 4 billion, save 4 bytes each by switching to INT ...


6

The greatest-n-per-group tag has a number of questions and answers relevant to this type of problem, with the canonical example for SQL Server being: Retrieving n rows per group With the two main options being: ROW_NUMBER (as in Aaron's answer); and APPLY So while the question is most likely a duplicate of that (from the point of view that the answer ...


1

An alternative you might consider is nested groups, such as this. select tt.Id, tt.EffectiveDate, tt.SequenceId, tt.CustomerId, tt.AccountNo from dbo.TestTable tt join ( -- Find maximum SequenceID for each maximum EffectiveDate for each Id select it.id, it.EffDate, max(t1.SequenceId) SeqId from dbo.TestTable t1 join ( -- Find maximum ...


6

Self-joins seem cheap at low row counts, but I/O is exponential as the row count increases. I would prefer to solve this the CTE way, unless you are on SQL Server 2000 (please always specify the version you need to support, using a version-specific tag): ;WITH cte AS ( SELECT Id, EffectiveDate, SequenceId, CustomerId, AccountNo, rn = ROW_NUMBER() ...


2

Are you looking to use the existing methods already implemented in PostgreSQL, or to implement your own new indexing methods? PostgreSQL's implementation of b-tree indexes cannot index values longer than about 2712 (although it will compress the value if it is highly compressible, before failing due to size) unless you recompile PostgreSQL with a ...


2

This is a "kitchen sink" query, for which SQL Server MVP Aaron Bertrand has a good video on how to optimize using dynamic SQL. A few points to get you started on the performance of your query: Use dynamic SQL to simplify the @Read criteria and subsequent lookup in tblReadStatus. I'm guessing that this is going to be your main performance gain. tblMetaData ...


0

This is most easily accomplished in an Access query by using the ConcatRelated() function. For more information see the following question on Stack Overflow: Combine values from related rows into a single concatenated string value


0

Would it not be better to have the dates as variables so you only have to get them once before the query? DECLARE @Today DATE DECLARE @Tomorrow DATE SET @Today = GETDATE() SET @Tomorrow = DATEADD(day,1,GETDATE()) SELECT FROM <Table> WHERE InsertedOn >= @Today AND InsertedOn < @Tomorrow


0

Try the below query will solve your problem: SELECT ID, STUFF( (SELECT ', ' + CAST(Item AS VARCHAR(20)) [text] FROM TBL12 WHERE ID = t.ID FOR XML PATH(''), TYPE) .value('.','NVARCHAR(MAX)'),1,2,' ') Items FROM TBL12 t GROUP BY ID Since you add many tags in the post, I solved in MSSQL using STUFF. Working fiddle: ...


6

Instead, try WHERE InsertedOn>=CAST(GETDATE() AS date) AND InsertedOn<DATEADD(day, 1, CAST(GETDATE() AS date)) This expression is sargable which is what you want for optimum performance. Like @Mikael indicates, you would do well to design one of your indexes so that InsertedOn is the first column, and that all the other columns used in the ...


0

I have tried all thing you guys suggested. Thanks for that, it was useful but did not solve my problem. Indexes suggested by jkavalik helped a little bit, but the query was still too slow (45 seconds~). The problem was that there was 2 large tables - visits and calls. And I had to join them twice in the query. It was taking long time. After few days of ...


6

The biggest difference in time in your execution plans is on the top node, the UPDATE itself. This suggests that most of your time is going to IO during the update. You could verify this by turning on track_io_timing and running the queries with EXPLAIN (ANALYZE, BUFFERS) The different plans are presenting rows to be updated in different orders. One is ...


3

This is the part of the execution plan where you expect an index being used: -> Seq Scan on paid gap (cost=0.00..20265.45 rows=204709 width=63) (actual time=0.024..215.813 rows=198575 loops=1) Filter: ((project_id = 1) AND ((country_iso_code)::text = 'gb'::text) AND ((source)::text = 'website'::text) AND (created_at <= ...


5

I am not exactly sure why the selectivity of an equality predicate is so radically over-estimated by the GiST index on the tstzrange column. While that remains interesting per se, it seems irrelevant to your particular case. Since your UPDATE modifies one third (!) of all existing 3M rows, an index is not going to help at all. On the contrary, incrementally ...


1

Is going to be hard to find something that excels both at data ingress (accepting +50k rows per second) and ad-hoc querying an arbitrary EAV time series (timestmap, signal_id, signal_value). I would give clustered columnstore a try. Clustered columnstore would leverage segment elimination on timestamp and clustered columnstores also have better concurrency ...


1

This is a long shot but seen as you are running SQL 2014 Enterprise I would have a test with using a Clustered Columnstore Index on this table and see if this improves performance for you. Especially considering that you are using the table for selects and bulk inserts only - no updates and no deletes. You will have the added advantage of taking a bit of ...


0

Querying the table in any way takes eons. While queries are running against the table, the BCP processing basically stops. Its like all processing power goes to the query and I get a files backlog. How do I speed up query performance? I should note that rows in this table will never be updated or deleted. Also, it's possible that data could be inserted ...


2

Since you're doing a TOP(1), I recommend making the ORDER BY deterministic for a start. At the very least this will ensure results are functionally predictable (always useful for regression testing). It looks like you need to add DC.D_ID and CJ.CORRESPONDENCE_ID for that. When looking at query plans, I sometimes find it instructive to simplify the query: ...


5

I've read the results from this post and understand the concept of a Row Goal etc. What I'm curious about is how I can go about changing the query so that it uses the better plan Adding OPTION (QUERYTRACEON 4138) turns off the effect of row goals for that query only, without being overly prescriptive about the final plan, and will probably be the ...


1

Personally, I would split that table. Definitely consider Rick James' point about ensuring everything is Indexed properly and is being queried efficiently first, but, at the end of the day, you're removing 90% of the data that you have to sift through to get what you want. Archiving that data will make queries faster by reducing row counts and shrinking the ...


1

Composite indexes. LEFT JOIN calls last ON last.CallSourceMediumID = SourceMediumID AND last.CallCampaignID = 222 AND last.CallDate >= '2015-03-01' AND last.CallDate <= '2015-03-31' needs INDEX(CallSourceMediumID, CallCampaignID, CallDate) (The first two columns can ...


1

First add those indexes to the calls table: (CallCampaignID, CallSourceMediumID, CallDate) (CallCampaignID, CallFirstSourceMediumID, CallDate) Those should make it much faster because they allow the two joins to the calls table to check substantially less rows (it is ~70k each time now, which means millions of combinations). After that check the ...


25

Edit: +1 works in this situation because it turns out that FILE_NUMBER is a zero-padded string version of an integer. A better solution here for strings is to append '' (the empty string), as appending a value can affect order, or for numbers to add something which is a constant but contains a non-deterministic function, such as sign(rand()+1). The idea of ...


27

Since you get the correct plan with the ORDER BY, maybe you could just roll your own TOP operator? SELECT DOCUMENT_ID, COPIES, REQUESTOR, D_ID, FILE_NUMBER FROM ( SELECT dc.DOCUMENT_ID, dc.COPIES, dc.REQUESTOR, dc.D_ID, cj.FILE_NUMBER, ROW_NUMBER() OVER (ORDER BY cj.FILE_NUMBER) AS _rownum FROM ...


27

Try forcing a hash join* SELECT TOP 1 dc.DOCUMENT_ID, dc.COPIES, dc.REQUESTOR, dc.D_ID, cj.FILE_NUMBER FROM DOCUMENT_QUEUE dc INNER HASH JOIN CORRESPONDENCE_JOURNAL cj ON dc.DOCUMENT_ID = cj.DOCUMENT_ID AND dc.QUEUE_DATE <= GETDATE() AND dc.PRINT_LOCATION = 2 ORDER BY cj.FILE_NUMBER The ...


0

Your statistics are way off. The planner thinks it's going to retrieve 948529, 1016801, 5685950 and 5685950 rows, respectively, when infact it retrieves 55, 1, 3 and 3. In the second example, that is why it's preferring a seqscan (I am guessing it expects to have about 5e+6 values in the table and 1e+6, being 20% of the entire table, is not worth an index ...


0

The solution I implemented for this was to add a plpgsql function which ran each individual query and output the results as a table. I defined an output record type and used this function: DROP FUNCTION coeus.getexonvcf(text); CREATE OR REPLACE FUNCTION coeus.getexonvcf(text) RETURNS SETOF coeus.exonvcf AS $BODY$ DECLARE r bigint; s coeus.exonvcf; ...


0

I'd suggest using a CTE in this instance. Something that looks like this: with exons (genename, exonnumber, crom, exonstart, exonend, padding, direction) as (select genename, exonnumber, crom, exonstart, exonend, padding, direction from exons where enename = 'NM_001037501') select * from vcfentries vcf join exons exo using (chrom) where ...


1

SELECT a, b ... GROUP BY a is asking for trouble. You may have been 'lucky' in 5.5 and 'unlucky' in 5.6. The problem is that any value of b can be shown for each 'group'. Sure, there are cases where a given a maps uniquely to a given b (eg, in a normalization table), but that does not feel like the case here. To see that there is a problem: SET ...


2

Mostly, this seems to be a misunderstanding. According to your query plan, you are retrieving rows=1410288 and the query itself is not that slow. It does not "take 224.9 seconds": Execution time: 697.753 ms You could probably improve the performance of your query some more by increasing the locality of clustered data, i.e., CLUSTER (or pg_repack) your ...


2

Please provide SHOW CREATE TABLE. Indexes needed: users_interest: (user_to, new) users_view: (user_to, new) mail_msg: (user_to, folder, new) users_block: (user_from) and (user_to, user_from) user: (activated, online) and (activated, last_visit) The construct NOT IN ( SELECT ... ) performs poorly; change it to a LEFT JOIN ... WHERE ... IS NULL. For ...


1

Vertica query performance depends highly on the predicate used in the query . To get the gist of your performance , try getting the projection name of the selected columns of the query you are firing . The columns in the order by clause of the projection is very important in deciding the performance of your select. you can get that by running explain on ...


3

SQL Server 2012 introduced some missing Windowed Aggregate Functions including LEAD & LAG, e.g. Window Functions in SQL Server: Part 2-The Frame or How to Use Microsoft SQL Server 2012's Window Functions LAG allows to access data from previous rows without using a cursor: SELECT ... FROM ( SELECT ... DATEDIFF(second, dtUTCDateTime, ...


2

As dnoeth mentions, you should be able to use lag to look at the previous value: SELECT ... , DATEDIFF(second, dtUTCDateTime, prev_dtUTCDateTime) AS DateDiffSeconds FROM ( SELECT ... , LAG(dtUTCDateTime) OVER (ORDER BY dtUTCDateTime) AS prev_dtUTCDateTime FROM VehicleMonitoringLog Where dtUTCDateTime > GetDate() - ...


5

Your CTE-based approach with window functions is a very good start. There's another, even more suitable window function that you could use: LAG(). Here's how: SELECT iVehicleMonitoringId AS CurrentID, LAG(iVehicleMonitoringId, 1) OVER (ORDER BY dtUTCDateTime) AS PreviousID, iAssetId AS CurrentAsset, LAG(iAssetId, 1) OVER (ORDER BY ...


0

Why not use this query (sqlfiddle): SELECT [MessageID] , [Body] , [Date] , [MessageParentID] FROM Messages WHERE MessageID NOT IN ( SELECT MessageParentID FROM Messages ) Basicaly, if a MessageID is also a MessageParentID it means there is at least 1 reply. Therefore it only looks for message without replies (ie. without ...


1

My understanding is following: Historically InnoDB didn't scale well with large buffer pool. So - with a lot or RAM - OS disk cache might impact performance a lot -> this is why O_DIRECT performed bad for those environments. MySQL 5.6 has those issues addressed and in recent versions you may set buffer pool as big as ~75% of any RAM. When most of RAM is used ...


8

The warning is there because of the XML function value(). The second parameter to value() is what you want the value stored in the XML to be converted to. You could argue that this is not in fact an implicit conversion but a very explicit conversion since you are asking for it to happen. Perhaps something for a connect item to suggest to Microsoft. Simplest ...


8

While I agree with @Kin about data types, I don't think this warning is as troublesome as you think. You're performing grouped concatenation, which is going to be orders of magnitude more expensive than any conversions anyway (and as Daniel said, unless your catalog views are massive - as in larger than physical memory - it is unlikely to affect anything in ...


3

The warning in the query plan means that because you have an implicit datatype conversion, SQL Server won't be able to accurately guess the correct number of rows returned, which in turn might lead to a less-than-optimal plan. This is important in queries that have to perform well, normally because they work with a lot of data, but in your situation, this ...



Top 50 recent answers are included