Tag Info

New answers tagged

3

When comparing values of different datatypes SQL Server follow the Data Type Precedence rules. Since nvarchar has higher precedence than varchar SQL Server has to convert the column data to nvarchar before comparing values. That means applying a function on the column and that would make the query non-sargable. SQL Server does however do it's best to ...


1

You can use a TRIGGER to update db1.reps.lastSyncAt when db2.user.lastmodifieddate is updated. Trigger: DELIMITER $$ DROP TRIGGER IF EXISTS db2.user_BEFORE_UPDATE$$ USE `db2`$$ CREATE DEFINER=`root`@`%` TRIGGER `db2`.`user_BEFORE_UPDATE` BEFORE UPDATE ON `user` FOR EACH ROW BEGIN SET @UserVerification=(select reps.veeva_rep_id from db1.reps where ...


0

You have to remember that the leaf nodes of a Nonclustered Index consists of Index pages which contain Clustering Key or RID to locate Data Row. In your where clause you state VeryRandomText = N'111' Since there is a Non clustered index on VeryRandomText (create index will create non clustered index unless you explicitly tell it to create a clustered) the ...


1

Here is your answer: MSDB on C: 86% of 32GB io_stall_write_ms : 192217 If MSDB is on c: master is on c: as well and I'm almost willing to bet that tempdb is still in the default location as the average I/O stall on c is 19 seconds! First, check if tempdb is on c:\ or has any file on the c:\ partition and if so move it away Check if the master database ...


0

Check the latency between your PC and the domain controller after doing ipconfig /dnsflush. Are you connecting to server by IP or hostname ? If IP, it could be doing a reverse-DNS, so add a hosts file entry. Try switching Network Protocol between TCP or Named Pipes in SSMS Connect to Server dialog - Options - Connection Properties.


1

The best query very much depends on data distribution. You have many rows per date, that's been established. Since your case burns down to only 26 values in the result, all of these solutions will be blazingly fast as soon as the index is used. The partial index below will be a bit faster if you have many NULL values. For more distinct values it would get ...


5

This is a known issue regarding Postgres optimization. If the distinct values are few - like in your case - and you are in 8.4+ version, a very fast workaround using a recursive query is described here: Loose Indexscan. Your query could be rewritten (the LATERAL needs 9.3+ version): WITH RECURSIVE pa AS ( ( SELECT labelDate FROM pages ORDER BY labelDate ...


0

From the postgresql documentation: CLUSTER can re-sort the table using either an index scan on the specified index, or (if the index is a b-tree) a sequential scan followed by sorting. It will attempt to choose the method that will be faster, based on planner cost parameters and available statistical information. Your index on labelDate is a btree.. ...


0

You should try to create an index on time, and see if this improves the execution time of your query: CREATE INDEX time_index ON sensor_readings(time); An alternative is to partition or cluster the table on time, as described in another answer.


0

The main problem is that you have 60 GB of data which you want to access quickly on a single slow HDD (I've never heard of 7400 rpm, is it 5400 or 7200?). You can partition on the time, so the last two weeks of data are grouped together in tight set. Or you could try clustering on the time instead of partitioning on it. You could either build on index on ...


1

This is an old question, but I came across this and was appalled at one particular comment made, suggesting the original asked was "confused". The question is perfectly clear, just not strictly about database administration. It falls into the fields of server virtualization and storage provisioning in virtual environments more than anything. It may well be ...


2

The Maintenance Plan task can reorganize indexes and that should have some benefit for you. Likely, however, you will reorganize more than necessary using that approach. You should look at Ola Hallengren's solution for Maintenance at: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


1

Can the Reorganize Index task (maint' plan or ssis package) be used to 'performance tune? Performance tuning is an art. You should avoid knee jerk performance tuning methods - instead you should try to narrow down your investigation. Is this an adequate step of triage for degraded application/query performance? It may or may not be, depending on ...


2

I assume you are talking about the same query, executing with different indexes. You can't just arbitrarily compare 2 totally different queries that do 2 different things. As Aaron stated in the comments, I can compare my 2001 VW to a 2015 Camaro and there will be no correlation, the Camaro may go faster but that's because it has a bigger engine. ...


7

The clustered index is partitioned on ReadTime so it couldn't use the PK as you describe. It would need to find the Max(Id) for each partition and then find the max of those. It is possible to rewrite the query to get such a plan however. Using an example based on the article here a possible rewrite might be SELECT MAX(ID) AS ID FROM sys.partitions AS P ...


0

Did you try to refresh your statistics? You can also try to use OPTION(RECOMPILE)on your query an recheck the actually used plan. Is it still the same? How fragmented is the clustered index?


1

John, I've got a recommendation which may help significantly (I hope!) It requires only a small change to your query, and may yield a significant performance increase. No promises though, but test it and let me know. Apply unnest() Generally speaking, I would avoid using the ANY (array[]) syntax as Craig Ringer metioned, due to the planners limited ...


2

For future readers, I went ahead and tested both methods (with and without the subquery), and they both worked equally well on all tested environments. I do not know if the database statistics needed updated on the databases that originally had a problem or not, but like many developers, I am not in charge of that and don't have any ability to alter it. ...


0

I would suggest to try an index like this: CREATE NONCLUSTERED INDEX NCI_ProcessState_ProcessStateDateLastModified_SleepUntil ON [TABLE](ProcessState,ProcessStateDateLastModified,SleepUntil) This way you can filter sharp on the state as all others will result in bigger result sets due to the <= filter. You can check the index usage easily with this ...


1

To see if the hardware is not limiting: top/htop => cpu percentage iostat -x 1 => sysstat tool to see disk r/w limits (%util) Concerning locking: Mongo 2.6 : database locking Mongo 3.0 + MMAPv1 storage engine : collection locking Mongo 3.0 + WiredTiger storage engine : document locking If you have 1 huge collection (server-prod), maybe Sharding is an ...


2

You can use a regular expression to match '1 ' to '6 ' at the beginning of the string: data.pro rlike '^([1-6] )' Now put this in a CASE: case when data.pro rlike '^([1-6] )' then cast(substring(data.pro from 1 for 1) as unsigned) end


0

If you have an index on data.pro: select substring(min(data.pro),1,1) from products where substring(data.pro,1,2) in ('1 ','2 ','3 ','4 ','5 ','6 ') order by data.pro; Instead of a select case on each record of your products, take the minimum-record between for your range from 1-6 (starts with that number anyway). If there is a good index for that, only 1 ...


1

The optimizer seems to think that your index is not selective enough. I think that you can do one or more of the following things: ANALYZE TABLE bets; ALTER TABLE bets ADD INDEX bets_time_amount (time, amount); /* this is a covering index */ add a FORCE INDEX to your SELECT


2

processing_date is not in the index, so it cannot use an index only scan. Create the index on (presentation_code, pct_id, processing_date, actual_cost) so that it can use an index only scan, and in that order so that it can efficiently use the sort order of the index to do the grouping.


-1

I faced similar situation. The other option is to rewrite the query. If it's not too much of work, rewriting the query should get you where you want to be.


2

The declaration of singleton in the path expression of the index enforces that you can not add multiple <Number> elements but the XQuery compiler does not take that into consideration when interpreting the expression in the value() function. You have to specify [1] to make SQL Server happy. Using typed XML with a schema does not help with that either. ...


3

You are touching on an almost philosophical argument here: should NULLable values be allowed at all given they violate the "closed world" assumption of the model relational databases are derived from (see relevant sections of http://en.wikipedia.org/wiki/Null_(SQL) and a number of other questions here such as Why shouldn't we allow NULLs? for more notes ...


1

Whether a separate table for images is helpful depends on the cardinality of the relationship between the images and the rest of the attributes in the original table: A separate image-table is justifiable if either: multiple rows may contain the same image: enhances normalization since the image only has to be stored once in the image table instead of ...


-2

If you create a new table then you would probably need a "JOIN" to get the image data. Joins can decrease performance of RDBMS. But in your case as there would be only one join so it won't have that much effect on over-all query performance.


-1

While I respect the submitter, I humbly disagree with the answer provided and not for "religious reasons". In other words, I believe there is no facility that Microsoft has provided which decreases the need for the guidance to use stored procedures. Any guidance provided to a developer which favors the use of raw text SQL queries must be filled with many ...


1

It depends. INDEX(a) SELECT ... ORDER BY a LIMIT 10 -- will, if there is no `WHERE`, stop after 10 rows are read INDEX(b,a) SELECT ... WHERE b=3 ORDER BY a LIMIT 10 -- is likely to use the index for both the WHERE and the ORDER BY, and stop after 10 INDEX(b,a) SELECT ... WHERE b=3 AND c=4 ORDER BY a LIMIT 10 -- This _may_ use the index, but _may_ have to ...


1

Let's walk through the steps. This will make some of the suggestions so far not significant. Scan 2.6 million rows out of 4-5M. Please verify by finding out how many rows have store_id = 1. Scan 2.6 million rows out of 4-5M again. (Yes, a single pass with an OR would be better since there is no way to make use of INDEX(store_id, data_index), which is ...


0

I see it as 2 steps: Build tables with just the latest signal (or noise) for each device JOIN or UNION the two tables. Step 1 is a variant of groupwise max: SELECT device_id, stats_time, status, noise -- The desired columns FROM ( SELECT @prev := '' ) init JOIN ( SELECT device_id != @prev AS first, -- `device_id` is the ...


3

By suggestion of @RolandoMySQLDBA, I'm combining my comments into an answer. So, in many cases, LIMIT clause enables the database server to do less work before delivering the result. MySQL docs explain such situations. It is best to check queries with EXPLAIN to see the execution plan. If the execution plan is different with and without limit clause, then ...


0

SELECT * , MATCH (`title`) AGAINST ('tax in work') * 1.65 AS `titlescore` , ( ( CASE WHEN time >=1432173380 THEN 21 WHEN time >=1432162580 THEN 18 WHEN time >=1432097780 THEN 15 WHEN time >=1431903380 THEN 11 WHEN time >=1431320180 THEN 7 ELSE 0 END ) * 1.75 ...


1

A variation on @Akash's answer which uses the LATERAL syntax and results in a better execution plan (check the Index Only Scan using idx2_snapshots on snapshots in the plan below): SELECT hour AS start_hour, hour + interval '1 hour' AS end_hour FROM generate_series('2015-01-01'::timestamp, '2015-01-02 ...


3

I tried creating a small sample of your database, we should probably force the planner to use a semi-join nested loop which would stop processing further rows as soon as it finds the first row for a given hour, camera_id as zugguy suggested. You can have a workaround with the temporary table using generate_series to get hours for the given datetimes. ...


3

Just an idea: create a table with all hours in the day (0-23). create table hours( hr integer ); Then find all hours that have snapshots for the given camera_id and date (of course, you will need to substitute your own camera_id and date into the query): select h.hr, 1 as camera_id from hours h where exists ( select 1 from snapshots s ...


2

The query has a lot of bloat: Two group by, the second is completely unnecessary. Three levels of nesting, the last is also completely unnecessary. It uses GROUP_CONCAT() to find all the product_id that have rows with 'southern' and rows with 'proper'. Not the best way in my opinion. The 1189 AS query_id seems to return redundant information (the same ...


1

First thing I would do is to combine the inner UNION ALL queries to a single query with the two 'like' conditions 'OR'd together and bracketed away from the other filter. This means it would only pass over that table once instead of twice. You can't add an index to help the 'like' filter as you have a '%' at the beginning. Sorry for brevity of reply, on my ...



Top 50 recent answers are included