May 22, 2014 at 6:15 am
how can I optimize this query please:
select [Collateral ID], [DataDate],
[Cif], [Collateral Category],
[Expiry Date], [Realizable Amount], [Currency],
[Classification of Property], [Market Value],
[Country],[CB_Guarantor], [CB_Guarantee]
from v0_AccountSecuritiesBASEL_III
WHERE
((DataDate)='2014-04-30')
and [Collateral ID] NOT IN (Select [Collateral ID] from [dbo].[v1_AccountSecuritiesBASEL_III])
If I run it with only the date restriction it’s pretty fast (1-2 seconds), but the restriction on the [Collateral ID] from the view `v1_AccountSecuritiesBASEL_III` is killing it.
May 22, 2014 at 6:21 am
Please post table definitions, index definitions and execution plan as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
The view definitions will also be of great help
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 22, 2014 at 3:31 pm
Just in general, if you (almost) always query table* "v0_AccountSecuritiesBASEL_III" by DataDate, then you should strongly consider clustering the table on that date first, rather than on -- my best guess -- [Collateral ID] -- alone.
*) Assuming it is a table, and not a view.
Also, make sure table "v1_AccountSecuritiesBASEL_III" has an index with [Collateral ID] in it, if it's a large table.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
May 23, 2014 at 2:20 am
thanks.
They're both views but for the base table, BaselCollateralData I'll try adding a clustered index on the DataDate column and a non-clustered index on the Collateral ID.
May 23, 2014 at 7:57 am
Joining view to view like that is a very common code smell that can lead to serious performance issues. In most cases you're much better off querying the data directly from the tables in question rather than relying on the views. There's only so much the optimizer can do for you during the simplification process.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply