|
|
|
SSC Veteran
 
Group: General Forum Members
Last Login: Monday, September 26, 2011 8:58 AM
Points: 216,
Visits: 187
|
|
I have a problem where the optimiser chooses a poor plan. The query is on a view and the problem is the choice of indexes in one of the tables in the view. Is there any way I can provide a hint in the query using the view to specify the index to use in the main query rather than within the view as there may be other queries that use the view when that hint would be unhelpful.
What I have is basically a list of cash transactions with an account_id and a ledger_date and there is an index on both of these. There is then a table of account groups which maps each group to a range of accounts. There are usually 3-10 accounts per group.
The query is looking for transactions for a single account group over a range of dates (the range can be quite large) and sometimes for no reason that I can fathom it seems to think it is a good idea to scan the range of dates and then filter by account rather than the other way around.
So I want to do something like this...(but the hint is ignored presumably because this index doesn't actually exist on the view)
select sum(amount)
from vwCashTransactions with (index (idx_account))
where groupaccountid = @P1 and ledgerdate between @P2 and @P3
Now I could do something hideous to get round it such as this but I though it would be clearer to other people what I was doing if I could do it with hints or something else.
select sum(amount)
from vwCashTransactions
where groupaccountid = @P1 and coalesce(ledgerdate,ledgerdate) between @P2 and @P3
Any other bright ideas?
Thanks
Ken
|
|
|
|
|
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 10:59 AM
Points: 4,222,
Visits: 6,728
|
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Monday, September 26, 2011 8:58 AM
Points: 216,
Visits: 187
|
|
Sorry should have mentioned , been there done that. It does kind of make a difference but only for a day at most and there isn't enough activity on the table to make day old statistics invalid. I find it odd that it takes a BETWEEN range condition over an equality one. It does seem to get much worse when we have some light days and the number of rows at the top end of the query date range are low, like it guesses that all days in the range have this small number of rows. I really don't want to have to go down the road of updating statistics every day on a table where the row distribution doesn't change that much 
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 11,884,
Visits: 22,833
|
|
You might not want to go down that road, and I don't blame you, but you might have to. Are you doing full scans on the statistics or just using the default sampling? A full scan might make a difference.
Just so you know, we have a couple of tables that we update statistics on several times a day. Partly this is because of bad design that we're not allowed to "fix" because it's a financial system and they'd would have to go through a ninety day test cycle. Partly it's because the data is just volatile enough and just odd enough, that it needs VERY good statistics. Most of the rest of our databases run just fine with statistics updated weekly.
Also, have you checked the index fragmentation levels?
----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for Red Gate Software
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Monday, September 26, 2011 8:58 AM
Points: 216,
Visits: 187
|
|
OK thanks, that surely can't be right though! We have a bit of bad design here and there too but this particular section looks OK and it isn't an unreasonable query. I'm not even sure it will help in all cases either. I have seen it happen where the end date in the range is today and if we have no transactions for today it does it by date even though the start date of the range is a month or more ago. No matter how many times I recalc the stats it won't make any difference until we actually have some transactions!
The whole thing just seems broken and I don't understand why I have to put so much work into something so simple!
Maybe I'll go with the coalesce hack for now after all 
Oh and I defragment every week too if it is more than 25% fragmented. Fragmentation doesn't make any difference to query plan choice though does it?
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 11,884,
Visits: 22,833
|
|
|
|
|
|
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 10:59 AM
Points: 4,222,
Visits: 6,728
|
|
Have you tried some kind of query refactoring to force the query optimizer to do what you expect?
Sometimes the same query with a different syntax takes a different query plan...
select sum(amount)
from (
select amount, ledgerdate
from vwCashTransactions
where groupaccountid = @P1
) as a
where ledgerdate between @P2 and @P3
Get your two-cent-answer quickly
The Spaghetti DBA
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Monday, September 26, 2011 8:58 AM
Points: 216,
Visits: 187
|
|
Actually no, but now that I've been forced to think about it in a logical way to explain it here, I might have been making too many assumption about what it is doing. I can rarely if ever replicate the problem and the only reason I know what it is doing is by pulling the query plan out of the DB. So actually the problem is likely that the query plan has aged out and the first person to call this statement does it with a date range in the future or just for one day with no transactions, this is then cached for the rest of the day unless I recalc the stats and a new plan is generated till tomorrow when the same thing happens!
So the question now boils down to is there a way to invalidate the query plan for one statement? (I can't go the recompile option because it isn't supported in our data access layer, don't ask!)
Thanks for listening, maybe I should start learning to talk to myself :D
Ken.
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 11,884,
Visits: 22,833
|
|
I talk to myself all the time, to the horror of all my co-workers.
OK. You could try a query hint in this case, the OPTIMIZE FOR hint might help out. Experiment with it. In 2008 you can also OPTIMIZE FOR UNKOWN, which can work really well.
----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for Red Gate Software
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 4:10 AM
Points: 2,348,
Visits: 3,228
|
|
It would help if you posted the view definition.
Maybe something is seriously wrong there?
SELECT SUM(Amount)
FROM vwCashTransactions
WHERE GroupAccountID = @P1
AND LedgerDate BETWEEN @P2 AND @P3
N 56°04'39.16"
E 12°55'05.25"
|
|
|
|
