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://www.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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply