CaptainGiblets
asked on
Slow SQL query
I am running this query on my test sql server. It works great, I change the 0 to a 1 and it still works straight away, the column in question is a bit.
select top 1000 c.firsttableid,
ca.thirdtableid
from firsttable c
left join thirdtable ca on c.thirdtableId = ca.thirdtableid
join secondtable M on c.firsttableid = m.firsttableid
where c.customer = 0
I copy this to my live system and it works fine with a 0 but when I change it to a 1 even a select top 1000 takes 5-6 seconds to execute.
Looking at the execution plan between the 2 servers they are the same when the value is 0. It also stays the same when I change it to 1 on the test system.
However when I run the query with a 1 on the live system the execution plan changes from a Hash Match (Right Outer Join) to a Nested Loops (Left Outer Join)
I have attached a picture of the 2 execution plans below.
I have looked at all relationships and they seem to be the same and I am stuck, if anyone could point me in the right direction I would really appreciate it.
select top 1000 c.firsttableid,
ca.thirdtableid
from firsttable c
left join thirdtable ca on c.thirdtableId = ca.thirdtableid
join secondtable M on c.firsttableid = m.firsttableid
where c.customer = 0
I copy this to my live system and it works fine with a 0 but when I change it to a 1 even a select top 1000 takes 5-6 seconds to execute.
Looking at the execution plan between the 2 servers they are the same when the value is 0. It also stays the same when I change it to 1 on the test system.
However when I run the query with a 1 on the live system the execution plan changes from a Hash Match (Right Outer Join) to a Nested Loops (Left Outer Join)
I have attached a picture of the 2 execution plans below.
I have looked at all relationships and they seem to be the same and I am stuck, if anyone could point me in the right direction I would really appreciate it.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Update stats & Rebuild Indexes on this table. It looks like parameter sniffing issue.
and try again.!!
and try again.!!
ASKER
Which table should I rebuild on? Kind of hesitant to do this during the day as its live data people are using. Would this cause any issues for them?
I tried adding an order to the query on the firsttableid and it went from taking 5 seconds to 88 seconds.
"Also it seems that you don't have an index containing your BIT column. " - We could add this but its strange that it works on the test server fine but not the live server.
I tried adding an order to the query on the firsttableid and it went from taking 5 seconds to 88 seconds.
"Also it seems that you don't have an index containing your BIT column. " - We could add this but its strange that it works on the test server fine but not the live server.
This is normal you have 2 different environments different data ...so time can vary.
ASKER
Adding an index on the bit field seems to have sorted it. Weird as there is no index on the testdb and it is the exact same data / structure.
Could this be because the index on the same table as the bit field needs to be rebuilt?
Could this be because the index on the same table as the bit field needs to be rebuilt?
Now check the execution plan and see the difference between the old one and new one.
ASKER
It's exactly the same.
Then the index has no effect, but creating the index may have filled the buffer pool..
Which table should I rebuild on? Kind of hesitant to do this during the day as its live data people are using. Would this cause any issues for them?And you're right. You shouldn't reindex during the working hours. And you also don't know if you need to reindex and nobody can't tell you need it only by pictures. Do you have a regular reindex task? When was the last time the reindex ran?
You can use the following command to get the last update statistics for all tables in you database:
SELECT name AS index_name, STATS_DATE(object_id, index_id) AS statistics_update_date
FROM sys.indexes
I tried adding an order to the query on the firsttableid and it went from taking 5 seconds to 88 seconds.ORDER BY is a performance killer. The reason you should use it is only to guarantee that the TOP 1000 are always the same when you run the query. Without the ORDER BY you can have random results.
Adding an index on the bit field seems to have sorted it.That's not what was recommend. Recommendation was "have an index containing your BIT column". Because of the selectiveness of a bit column (only 2 values allowed and NULL if it accepts NULL) and index on the bit column wouldn't help but if you include it in a non-cluster index it might help the performance.
ASKER
Sorry you will have to bear with me, I'm not the best when it comes to SQL. Hopefully we will have a DBA soon!
Looking at the indexes they haven't been rebuilt on the live system since 2016-11-30 08:22:23.017 on the live system and 2016-11-28 12:17:24.393 on the test system.
I am only sure how to add an index by clicking the "Manage index and Keys" then clicking "add" and selecting the column.
The index type for the firsttableID is Primary Key, there is no unique key / index PK against it as well.
Looking at the indexes they haven't been rebuilt on the live system since 2016-11-30 08:22:23.017 on the live system and 2016-11-28 12:17:24.393 on the test system.
I am only sure how to add an index by clicking the "Manage index and Keys" then clicking "add" and selecting the column.
The index type for the firsttableID is Primary Key, there is no unique key / index PK against it as well.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Anyway, I can see only Clustered Index Scans and this can mean that there are no indexes in the Foreign Keys. If that's true try to index FKs too.