New answers tagged

5

Since you are running Microsoft SQL Server 2014 - 12.0.2000, the very first RTM build including the new Cardinality Estimator I would strongly suggest you try updating to one of the latest CU's. As stated in this blog post on msdn You need to apply SP1 but you must also enable trace flag 4199 in order to activate the fix. SQL Server 2014 Service ...


2

We solved the issue. The issue was caused by the new Cardinality Estimator in SQL 2014. We disabled the new estimator by activating trace flag 9481 and removing the query plan from the cache then the query worked again.


0

This Query is the answer to my question. It is dynamic. Declare @Skill NVARCHAR(MAX), @Degree_Field NVARCHAR(MAX), @Experience NVARCHAR(MAX), @query NVARCHAR(MAX) select @Skill = STUFF(( select SEQ From ( ...


2

You'll speed-up the query significantly if you create an index (ideally a unique index) on property_ad_id and created_at, and another on just created_at: CREATE INDEX ON property_ads_history (property_ad_id, created_at); CREATE INDEX ON property_ads_history (created_at); # if not already there! Note that the condition t1.price > -1 OR t1.price <> ...


3

ORDER BY in the sub query is what's making it slow. ORDER BY makes query slow since you need to order (sort) all the objects in the query results. It is in order of N Square if you don't have an index, and N log N if you do. And you don't even need to order by all of it. What you need is only to filter out the minimum, so instead of: ORDER BY ...


1

I found that your query had many redundancies in the conditions, and you used cross joins that were good candidates for simple joins. This might confuse the planner. Perhaps you could try the following rearrangement of the query (it is functionally exactly the same but uses joins and removes all the redundant comparisons) to see what the planner comes up ...


2

You can UNPIVOT and then PIVOT. Half of it is below (untested) WITH P1_Src As ( SELECT ID_Skill, Val, Col FROM Job_Skill UNPIVOT (Val FOR Col IN (Min_Job, Idea_Job, Max_Job)) AS U ), P1 AS ( SELECT Col, [1], [2], [7], [8] FROM P1_Src PIVOT (MAX(Val) FOR ID_Skill IN ([1], [2], [7], [8])) AS P ) SELECT * FROM P1 You would need to apply a case expression to ...


1

It is best to store your tags in a separate table because of the many to many relationship. With something like tagging images you can end up with a very large number of comma separated values that would need to be parsed any time you search your database which will lead to massive performance problems as your data grows. Going that route and searching for ...


0

history: INDEX(created_reason, created_at) Are you trying to avoid the "last record" via the price <>? If something else, then consider changing AND price <> ( SELECT ... LIMIT 1 ) to AND NOT EXISTS ( SELECT * ... ) Please explain, in words, what the query is trying to do. Perhaps some drastic reformulation is called for. Anyway, it is a ...


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

SELECT tbl_A.id FROM tbl_A LEFT JOIN tbl_B ON tbl_A.id = tbl_B.id WHERE tbl_B.id IS NULL This is most likely how I would do something like that. Just a simple join where the column in A does not equal the columns in B. The DBMS should take care of optimizations. As for varchar and char: char is more efficient when using data that is all the same length. ...


4

Summary The question does not provide execution plans or a full reproduction script, but based on the information given, you should use a FAST 1 hint. If you can, you should also consider converting the scalar function to the inline table-valued type. AdventureWorks-based examples Download the Microsoft sample database here. Table type CREATE TYPE ...


0

The standard deviation can be calculated knowing the number of values, the sum of the values, the sum of the square of the values. Each of these can be updated incrementally as new values arrive and stored in a work table. The work table will look something like DailyTotals ( OrderDate, NumberOfValues, SumOfValues, SumOfSquareOfValues); Since the ...


1

I was able to reduce the query time from 24.5 minutes to 2.5 minutes by making a minor change in the query joins. The modified query makes the initial select statement from my view (14 rows for a biweekly period), and that filters both of the tables down to only the relevant rows. The new query is: SELECT t1.calendar_dttm, t2.unit, t2.start_dttm, ...


1

I see joins to v1 on start_dttm and end_dttm but dont see it in the view or the table listed. Looking at your definition of your view, are you sure this is an issue with the join, or does the view itself have issues? There are a few problems, and I will list them in order my brain saw them: , DATEDIFF(d,GETDATE()+(14-(select DayofPayPeriod from ...


-3

Use below code. change r.* according to your result ; with a1 as (SELECT a.* FROM tblabc a INNER JOIN tblxyz b ON a.ID = b.ID ) select r.* from a1 r


3

...the original table contains more data than the new table, which should theoretically mean that the new table should finish the query faster. If the execution plans were the same, this would most likely be true, but they're not. The number of rows expected (and the distribution of the data according to the statistics) affects the strategy chosen by ...


3

I think the condition ON a.ID = ISNULL(b.ID,a.id) is not the best way to do this. It may lead to inefficient plans and may return unwanted results (according to your specifications). If the tblxyz table has rows with NULL values, you'll still get all the rows from table a (and possibly multiple times). I would write the query without using the ISNULL() ...


2

The way you've done it is correct. So the reasons for slowness will likely come down to: when is it slow (is it when the temp table has a lot of rows, or when it has few rows?), how many rows are usually in each table, what indexes exist on each table, and what data type is ID? You can also look at your real-world execution plan for some answers. I ...


3

Two likely possibilities. Blocking Your production machine probably has a lot more going on than your test/dev machines. You could simply be seeing blocking. Run your query in one window and this in another. SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id > 0 If you are being blocked you may need to look at what's blocking you and how ...


3

For this WHERE clause, I would try an index on (customer_account_id, sync_inactive, last_modified, id): ALTER TABLE main_contactinfo ADD INDEX customer_active_last_modified ( customer_account_id, sync_inactive, last_modified, id ) ; The ORDER BY with e small LIMIT complicates things though, so a different order of the columns in the ...


4

Why would you expect performance to be different with partitioning? Partitioning improves manageability but performance depends greatly on the workload and queries. For example, if you have a useful index on the DateColumn, you will likely get the same performance with and without partitioning. With no index on the column, performance may be better with ...


3

One possibility is that the stored procedure was created with settings for ANSI_NULLS and QUOTED_IDENTIFIER that differ from those set on your local connection. These settings are stored with the procedure definition and override the session's current value. These two options can dramatically change an execution plan, and may prevent the use of indexes on ...


1

It looks like the join between ja_feedlog and ja_jobs is the culprit (it appears to be taking most of the time doing a filtered indexed scan on ja_jobs for each ja_feedlog resulting row). In cases like this where there are two joined heavily filtered large tables, I find it useful identifying which set of filter conditions will render the least rows on one ...


2

Don't randomly add indexes. Look at your queries to decide which indexes are needed. See my cookbook. InnoDB really needs a PRIMARY KEY. Keep in mind that a PK is, an index, is UNIQUE, and is clustered. So don't add any index(es) that start with the same column(s). WHERE a=2 AND b=4 begs for a "composite" index: INDEX(a,b) or INDEX(b,a). Those are ...


2

in order to tackle this I would like a T-SQL query that would show me all the values that have been set for the session. To view the SET options for current sessions: SELECT * FROM sys.dm_exec_sessions;


2

For a table with 1000 rows I think it does not make sense to put any index. The complete table could fit into memory and that would be fast enough. But if you want to put indexes you should always take the business value into consideration. Take into account that each index generates an overhead to the table. Not sure where the limit is, but in any case ...


5

If it's a case of different options causing different plans to be used, just check the options using sys.dm_exec_plan_attributes. If the options are not the same for a good and a bad plan, that can then be the reason. A list of plan cache keys can be found in the following answer by Martin Smith: What would cause parameter sniffing one one computer and not ...


0

This may be obvious, but if the columns are INT, then a+b+c could equal zero even when none of them actually are zero. You're testing two different things!


4

You should retype the various Name columns from nvarchar(max) to nvarchar([right size]). It's unlikely that names will be up to 2GB in length, and making them max sized prevents them being used as a key in an index. A good general rule of thumb is to avoid large object data types wherever possible. You'll probably need to make that change to the EF code, ...


2

The problem is that TopN Sort in the top left. It has to pull in all possible rows to then find the smallest one. If you have an index on DiameterInches, it's likely to start searching on the smallest ones first, and give you vastly improved performance. Of course, then you'll need an index on Widgets.SizeId, so that it can find those easily. And it'll ...


4

You may want to re-write your query as follows (I'm using dbo rather than XXXX so that I do find some synonyms on my testing database). This is similar to the re-write you found to be more efficient, but avoids the need to declare a variable and use two queries. SELECT name, base_object_name FROM sys.synonyms WHERE schema_id = SCHEMA_ID(N'dbo') ORDER BY ...


5

The main factors in play here are: The optimizer does not try to find the best plan; its goal is to find a reasonable plan quickly It assumes the query will be run with a cold cache The cost model used favours sequential I/O over random I/O Repeated seeks into an index are assumed to be randomly distributed The cardinality estimate for a table variable ...


1

It is more efficient to check if data has changed in the web application Sending an update to myslq with the same value uses more resources If there are no changes then send nothing Better yet don't even have the button active until there are changes If there are changes only send the changes and do so in one statement UPDATE yourTable SET field1 = ...


1

If your business logic layer(s) know that there has been no change (i.e. it has already re-read the data and can compare it to the provided input then you can simply not send anything to the DB. If you don't know before hitting the data layer if there has been any change or not then you can do something like: UPDATE yourTable SET field1 = @input1 , ...


0

If I understand well then you read the information from the database and display it. The user has the possibility to change it. The fastest way is to compare the information that you read with the information that you get from the user. If there is a difference then you update the information in the database.


1

(The comments below apply to MySQL; some may apply to other engines.) UUIDs slow things down because of their random nature. Don't use Unicode; use utf8. (Better yet, CHARACTER SET utf8mb4) InnoDB keeps index fragmentation low by design. Rule of Thumb: In a table with millions of rows, a "point query" via the PRIMARY KEY can be expected to take about as ...


5

You can't reset the DMVs, however you can work around this limitation and remove rows from the DMVs by creating a small filtered index on the tables mentioned in the DMVs then immediately dropping that index. For instance: CREATE INDEX IX_temp ON dbo.SomeTable(SomeKey) WHERE SomeKey IS NULL; DROP INDEX dbo.SomeTable.IX_temp; I've created a script to ...


2

There are multiple things to look at to improve query performance. Understand your system load Volume of data you are expecting to be in the tables mentioned in query Volume of data you are going to fetch from tables mentioned in query Indexes applied on tables mentioned in query Structure of the query I can suggest you improvements related to only ...


2

First of all, if custId is the Customer's Primary Key, then there's no need to specify DISTINCT at all. Second, grouping and then filtering by the grouped key may be very inefficient when the filtering will likely render a small subset: filter first, then group. Third, I assume you don't require casting to varchar when removing the DISTINCT clause. Fourth, ...


3

I suggest a filtered index: CREATE INDEX ix_valid_text_column ON dbo.t1(Tid) WHERE text_column IS NOT NULL; Now your query should be able to use that index instead of the clustered index, and it should be much more efficient. Even better, it enables you to look at the metadata views, so you read one row (or a number of rows equal to the number of ...


0

You could use a parallel system such as Azure SQL DW. This would basically split your data up into sixty parts, and should be able to optimise just as well as your regular SQL database, but spreading the work out across sixty databases. You would still need to make sure that your design was optimal, in regard to table structures and indexing, but you should ...


2

1 - Query was introduced to this website 2 - An index has been created to improve the Query: CREATE INDEX CONCURRENTLY ix_feedlog_client_time_notif_id ON public.ja_feedlog USING BTREE ("clientid","gtime" DESC, "log_type", "id"); Total time before the index: 346507.823 ms Total time after the index: 625.375 ms 3 - The query was fast, but not enough. So ...


0

Switch to InnoDB to avoid table locks. Table locks can tie up MyISAM tables for a long time. product: INDEX(number_cleaned, manufacturer_id, id) product: INDEX(number_cleaned, source_id, id) number and number_cleaned sound like numeric values, yet you declared them VARCHAR. Be sure to always quote the numbers you compare them against.


0

I would aim for an index on: (number_cleaned, source_id) i.e. KEY ... (number_cleaned, source_id) I never understood why MySQL uses the term key for something that's not, but that's a different story. As @mysql_user points out, investigate whether it's possible to migrate your tables to innodb. If you want to stretch things a bit further you can ...


2

This is a simple question No, it is not. This (kind of) question is what plagues many DBAs and software developers day in day out, and it is all but trivial. that I can't seem to find the answer for. Yes, you won't. At least not a general answer. First of all, it will depend hugely on which RDBMS you are using (OK, you are using sql-server, but ...


46

In your question, you detail some tests that you've prepared where you "prove" that the addition option is quicker than comparing the discrete columns. I suspect your test methodology may be flawed in several ways, as @gbn and @srutzky have alluded to. First, you need to ensure you're not testing SQL Server Management Studio (or whatever client you're ...



Top 50 recent answers are included