SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can I push an Index hint down into a base tables from a view


Can I push an Index hint down into a base tables from a view

Author
Message
Ken Gaul
Ken Gaul
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1200 Visits: 194
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
Gianluca Sartori
Gianluca Sartori
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99935 Visits: 13373
I would never mess with index hints, let MSSQL choose the best plan for you.
Try updating statistics: poor query plans are often due to outdated statistics or bad indexes.

-- Gianluca Sartori
How to post T-SQL questions
spaghettidba.com
@spaghettidba
Ken Gaul
Ken Gaul
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1200 Visits: 194
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 Crazy
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)

Group: General Forum Members
Points: 375231 Visits: 34636
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 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
Ken Gaul
Ken Gaul
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1200 Visits: 194
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 Unsure

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?
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)

Group: General Forum Members
Points: 375231 Visits: 34636
You could post the execution plan & query to see if anyone can spot something. I'd do that in a seperate post though so you get more eyeballs.

----------------------------------------------------
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 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
Gianluca Sartori
Gianluca Sartori
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99935 Visits: 13373
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



-- Gianluca Sartori
How to post T-SQL questions
spaghettidba.com
@spaghettidba
Ken Gaul
Ken Gaul
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1200 Visits: 194
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 BigGrin

Ken.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)

Group: General Forum Members
Points: 375231 Visits: 34636
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 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
SwePeso
SwePeso
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37613 Visits: 3447
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"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search