I have a query where using select * not only does far fewer reads, but also uses significantly less CPU time than using select c.Foo.
This is the query:
select top 1000 c.ID
from ATable a
Execution Plan as Query
, VP.ROUTE AS Route
-- Group by Customer
, CustomerQuantity = SUM(...
I am executing a SELECT on 2 Hosts, "old db" running mysql 5.5.54 and "new db" running mariadb 5.5.56. Both the runtime and the EXPLAIN show a difference, with the mariadb being significantly slower. ...
So, as the title says, I have a query that is run by SQL Server Reporting Services, when it ran by SSRS, I can see in SQL Profiler it's executed using RPC Protocol, and takes about 30 seconds. When I ...
I have a query the first part of which looks like this -
With CTE AS (
from tableA A
JOIN tableB B ON
My application goes slow sometimes and when i check the query plan plan for a particular operation,i see that serial pans got generated and gets stored in the plan cache and this is being taken for ...
Sql Server Database on Azure (Sql Server 2014/12.0.2000.8)
I have numerous stored procedures that all had the same basic select properties and joins, so I decided to generate a view that encompassed ...
I am mainly a .NET developer using Entity Framework ORM. However, because I don't want to fail in using the ORM, I am trying to understand what happens within the data layer (database). Basically, ...
I'm using postgres 9.2.4, and run the following query:
explain analyze select * from bubu where id not in
(select bubu_id from kuku limit 33554431);
My boss wants me to parse a set of query plans stored in a table and to determine how many nested loop operators with unordered prefetching are present in each plan. I only have around 100 query plans ...
I have an execution plan which when I open in Sentry One Plan Explorer has areas dimmed out.
However, when I check the cumulative costs, these are the same areas that contribute to 99 pc of the ...
I've encountered this condition while optimising a SP, there's a part of the execution plan I really don't get: SQL Server does an Index scan, then it filter the results. What are the reason for SQL ...
I've got a hash match spill going on here.
I've updated statistics with FULLSCAN on the tables involved so it isn't that.
Any pointers much appreciated.
I'm running a query which updates an Oracle table through Datastage. The query is taking a long time to execute so I want to examine the query/session on that Oracle table in the database and ...
I'm new to execution plans in SQL Server and I'm trying to understand why SQL Server is doing some operations. The query is:
from KSS_V_EMPLEADOS_PER_3_CERRADOS p, COMBHOMEACCT c, ...
I came across this on another forum:
PG has a limit of 100 for values in IN queries after which the index on said column is not used. For ex: a typical SELECT ... WHERE IN (...) query on the PK ...
Does the SentryOne Plan Explorer have a properties window similar to SSMS. I see the tool tip window next to the operators in the execution plan but no properties option ?
I work on SQL Server 2016 and i had severe performance issues with few queries.
So I posted several posts in here and got solution/suggestion etc and greatly improved the execution time and learned a ...
Consider the below query:
CREATE PROC dbo.GetPage @orderid AS INT = 0, -- anchor sort key
@pagesize AS BIGINT = 25
TOP (@pagesize) orderid, orderdate, custid, empid
My question is why a multi-column index is not used for a index only scan, when a partial index with equivalent information (i think) is.
CREATE TABLE test
I have the following query that is taking a long time to execute (nearly 20 seconds).
SELECT Count(*) FROM "workorder" "wo"
LEFT JOIN "workorder_fields" "wof"
I need to find number of pages scanned by the operator in a sql-server query plan.
SET STATISTICS IO ON
This returned number of logical, physical, read-ahead pages scanned per table,
but I ...
In this answer Erwin breaks down IMMUTABLE functions into those that can be inlined, and those that can't. He has an example with to_char() that shows an IMMUTABLE function over to_char() will ...
Id int primary key(clustered index)
Name varchar(255)not null (non-clustered index)
I execute stored procedur as
update table1 set table1.Name=isnull(@...
I have a query which involves a full-text search like this:
SELECT TOP 30 PersonId,
I have a query that populates a temp table with 1692 rows. I had used a table variable but performance was terrible. Now it is improved but still not ideal.
However, I then need to split the TaskName ...
I've created a varbinary hash to check changes between 2 tables.
Here is the execution plan, I'm a bit stumped on the indexing, or indeed if there is a better way of writing this.
In the last weeks I noticed an increased number of cases where Oracle starts to pick notoriously bad plans for some statements on widely different systems (Exadata, ODA and AIX).
I haven’t seen that ...
I have a problem with a long running query, I'm not a DBA but it's become my problem. I've been doing a lot of reading over the last few weeks but for the life of me can't figure out why this query is ...
I'm having an issue with slow queries that is caused by the planner checking every inherited table instead of only the one with the constraint.
I have a table with 0 rows called "search_result". This ...
For the AdventureWorks sample database query below:
FROM Production.Product AS P
SELECT TOP (1)
PostgreSQL using defaults, plus
PostgreSQL 10.4 on x86_64-pc-linux-musl, compiled by gcc (Alpine 6.4.0) 6.4.0, 64-bit
I've vacuumed and ...
I've got this query which used to take minutes to run and now takes like 6 seconds but it runs a thousands of time a day so I'd like to make it faster.
We have heard of writer-writer and writer-reader deadlocks.
Is it possible to create a reader-reader deadlock?
I am investigating the reason why a query is performing bad in my SQL server 2016.
The query executes in 1 sec when i give the date range filter as 2017-06-21 and 2018-06-20.
When i give the date ...
I have a query generated from a .net application and parameters are linq parameters. The procedure I followed to tune the query is as below.
I run the application and capture the query using profiler....
Here's part of an EXPLAIN ANALYZE from Postgres 9.6:
-> Bitmap Heap Scan on cities (cost=90.05..806.49 rows=265 width=4) (actual time=4.733..45.772 rows=17 loops=1)
Recheck Cond: (...
Do unused CTEs in queries affect performance and / or alter the generated query plan?
Need to know workarounds to tackle this query plan.
Tried suggestion given by Brent, but end up with the entire SP itself as topmost cost.
Please let me know if I am missing anything here
where BusinessEntityID = 5
I get convert_implicit() function called ...
Can you please check the attached execution plan from my SQL Server 2016 and suggest me where to improve.
The query is generated from the .Net application using LINQ to SQL.
I can see large sort ...
I have found, through the query store, a query that's performing on average 297582 logical reads.
I wanted to see if I was able to tune that query a bit, and after that, try to execute the query ...
I setup and configured an new SQL database server with SQL Server 2016 Standard Edition.
This is going to replace our existing SQL database server which is 2008 R2 database server.
I restored recent ...
I have a server running SQL Server Enterprise 2016 SP2 and I noticed that every minute several hundred queryplans get invalidated.
I run sp_BlitzCache @expertmode=1 to check the 'Created At'-column to ...
I have the following SQL query
LINT_ERRORS from (...
I have created Four Tables-
1) Registration Table - it has registration id(rid),First Name of user(fname), Last Name of user(lname), gender(gender),birth date(dob).
2) Post Table - it has post id(...
I know there is already similar question here like How does the database decide which Index to use. But it seems that those answers are not cool and useless.
In my development and use of MySQL, I ...
[ ] Well trained professional, [ ] Casual reader, [ ] Hapless wanderer,
I have a (check all that apply)
[ ] query [ ] stored procedure [ ] database thing maybe
So the developer of this app should really be figuring this out, but it's taking too long and we are suffering from performance issues. I'm ...
I've got a schema something like having an Entity table, with a to-many relationship to Attribute, which has a to-many relationship to Value. Attribute has a column Name, and Value has a column Value: