August 6, 2012 at 9:23 pm
DISCLAIMER I did not write this code
Assume that #InstitutionChild is just a temp lookup table filed with about 95,000 int values
Ex: Create Table #InstitutionChild (ChildAID int)
Given the following code SELECT
@DailyCnt = COUNT(tranid)
FROM
COREACQUIRE.dbo.AcqLogARTxnsAdd l WITH (NOLOCK)
LEFT OUTER JOIN COREACQUIRE.dbo.trans_in_acct t WITH (NOLOCK, INDEX (idx_tran_id_index))
ON l.tranid = t.tran_id_index
WHERE
t.atid = 39
AND l.artxntype IN ('91', '95')
AND l.ARTxnBusinessDate <= '1/1/2011'--@DateFrom
AND InstitutionId IN
(SELECT childaid FROM #InstitutionChild)
AND l.TransactionAmount <> 0
AND ISNULL(l.ExcludeFlag, '0') = '0' When viewing the execution plan (image snippet attached - full plan has over 50 queries and I'm just interested in this portion of code) I see something I don't understand :
[COREACQUIRE].[dbo].[trans_in_acct].[ATID] as [t].[ATID]=(39) AND PROBE([Bitmap1015],[COREACQUIRE].[dbo].[trans_in_acct].[tran_id_index] as [t].[tran_id_index])
What on earth is this PROBE([Bitmap1015])?
I quickly "googled" it but really didn't come up with an explanation that made much sense to me. Can someone please shed some light?
This portion of the query is accounting for the majority of the reports execution time...and I'd like to get past this one :w00t:
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 7, 2012 at 12:15 am
I can't help you PROBE your BITMAP (sorry but I couldn't resist ;-)), but my first suggestion would be to remove the INDEX hint and see what that does to performance.
It could be the underlying data has changed since that hint was applied and SQL Server may be able to come up with a better plan if you remove the hint.
Make sure you take an accurate timing of the query with and without the HINT, and allow several runs to give SQL a chance to cache a new execution plan.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 7, 2012 at 12:36 am
Your query is using bitmap filters.
a bitmap filter is a way for sql server to eliminate rows earlier in the execution pipeline. This is a powerful performance optimization.
Bitmap filters will always show up in two places in your query plan, once for the build, once for the probe.
The most common case will include a hash join. On the build side of the hash join, you will find the build for the bitmap filter (just like a hash join, the bitmap filter must consume all imput before the probe side can start).
consider this contrived and grossly oversimplified example:
table cats (cat_id int...)
table books (book_id, cat_id ...)
select books.* from books b inner join cats c
on b.cat_id = c.cat_id where c.cat_id in (1,33,51,79,91)
Sql server may decide to scan the cats table, and build a bitmap filter.
Through SQL Server magic, the bitmap filter realizes that all the
values of cat_id are odd. When Sql server does a scan on the books
table, it tests each cat_id against the filter. Any values of cat_id that
are even numbers can't possibly match when we get to the hash join,
so we eliminate those rows before they even make it to the query pipeline.
bitmap filters are a good thing, but typically only show up in plans with large row counts (DW queries, etc).
August 7, 2012 at 5:58 am
dwain.c (8/7/2012)
I can't help you PROBE your BITMAP (sorry but I couldn't resist ;-)), but my first suggestion would be to remove the INDEX hint and see what that does to performance.It could be the underlying data has changed since that hint was applied and SQL Server may be able to come up with a better plan if you remove the hint.
Make sure you take an accurate timing of the query with and without the HINT, and allow several runs to give SQL a chance to cache a new execution plan.
LOL - yeah I chuckled when I just typed it ino the OP 🙂
All jokes aside 😉 when removing the index hint the optimizer automatically turns it into an index scan...so that's why I was trying to force it to use the index I had created specifically for it.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 7, 2012 at 6:00 am
@SpringTown :: Thanks for the explanation, much appreciated!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 7, 2012 at 8:49 am
It will be much more helpful, if you provide an Actual Execution Plan (in xml) for this slow query. If this is not a slow query by it self, but you think it is the slow part of more complex query, than it would be nice if you provide the actual query plan for all the complex query (including slow part), and a plan for it, without slow part (when you comment slow part, for example).
August 7, 2012 at 1:17 pm
MyDoggieJessie (8/7/2012)
All jokes aside 😉 when removing the index hint the optimizer automatically turns it into an index scan...so that's why I was trying to force it to use the index I had created specifically for it.
That's not always a bad thing... an Index Seek that involves Bookmark/RID lookups is generally worse than an index scan that doesn't.
Just as one example.
August 7, 2012 at 6:15 pm
mtassin (8/7/2012)
MyDoggieJessie (8/7/2012)
All jokes aside 😉 when removing the index hint the optimizer automatically turns it into an index scan...so that's why I was trying to force it to use the index I had created specifically for it.
That's not always a bad thing... an Index Seek that involves Bookmark/RID lookups is generally worse than an index scan that doesn't.
Just as one example.
This is kinda why I suggested he run a timing test with and without the INDEX hint.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 7, 2012 at 6:47 pm
Tried those suggestions and really didn't see any performance gain...have a few other things to try but ended up getting too busy to look at it today!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 7, 2012 at 6:59 pm
I'll make two other suggestions then, but for the first I must ask a question:
AND l.ARTxnBusinessDate <= '1/1/2011'--@DateFrom
1. Are you actualy comparing against a hard-coded date or a parameter you passed in named @DateFrom? If the latter, try adding OPTION (RECOMPILE) to the query and time the result.
AND InstitutionId IN
(SELECT childaid FROM #InstitutionChild)
2. Can you make this an INNER JOIN #InstitutionChild ON InstitutionID = childaid without impacting the row count?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 7, 2012 at 7:01 pm
Also, one other:
AND ISNULL(l.ExcludeFlag, '0') = '0'
Can you set a default value for ExcludeFlag, run a script to set all NULL values to '0' and then remover the ISNULL on that column? That would make the condition sargable.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 7, 2012 at 10:53 pm
dwain.c (8/7/2012)
I'll make two other suggestions then, but for the first I must ask a question:
AND l.ARTxnBusinessDate <= '1/1/2011'--@DateFrom
1. Are you actualy comparing against a hard-coded date or a parameter you passed in named @DateFrom? If the latter, try adding OPTION (RECOMPILE) to the query and time the result.
AND InstitutionId IN
(SELECT childaid FROM #InstitutionChild)
2. Can you make this an INNER JOIN #InstitutionChild ON InstitutionID = childaid without impacting the row count?
#1 It's a date parameter that's passed in, the hard-coded one was just for my testing (forgot to remove that)
#2 I will try that...hadn't even thought of it 🙂
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 8, 2012 at 12:20 am
Maybe it is worth trying, to create primary key on #InstitutionChild.childaid if there isn't.
In general, without plans, all that is pure guess work, imho.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply