question

Looking at the code I can see a lot of views joined together in this query. You really need to run the Profiler on this code and also the Query Analyzer to truely find out what is running slow. Possibly you are missing indexes on various tables referenced by the views that could speed things up. The Query Analyzer will suggest what you could do far better than the naked eye due to us not being able to see the full data model, amount of data and so on.

I reformatted your query so it was a bit more readable, as Ian mentioned, looking at the execution plan may help you find out why the query is performing slowly. One of the things that I noticed that would definitely have an impact is the number of functions and concatenations that are being used in both join criteria and the where clause. Using functions, forces the query to table scan since it must apply the function to every single row in the table, so you loose the benefit of an index seek.

Other than that, after looking at the execution plan, make sure that you have the appropriate indexes on the the tables, at least covering the join and filtering criteria of the query

CREATE VIEW [dbo].[viewAR_ST_Product]
AS  SELECT  1210 AS ArrangementTypeOID
          , 364 AS ObjectStatusOID
          , 539 AS ArrangementLifeCycleStatusTypeOID
          , 1010001005 AS SourceSystemOID
          , 1010001121 AS SourceFileOID
          , ( CAST(viewAR_ST_Product_Int.EFF_REKNR AS VARCHAR) + '/EFF/'
              + RTRIM(LTRIM(viewAR_ST_Product_Int.K_FONDS)) + '/'
              + ISNULL(viewARProductMap.IDAT, '') ) AS UniqueIdInSourceSystem
          ,

-- We are assuming here that xlsTOFFONDS will be populated. 1080001022 AS IdentifierTypeOID, -- To be changed as not available GETDATE() AS InitialPopulationTimestamp, GETDATE() AS LastPopulationTimestamp,
            CAST(viewAR_ST_Product_Int.CLIENT_NR AS VARCHAR) AS CustomerUniqueId
          , viewDWHCustomer.SourceSystemOID AS CustomerSourceSystemOID
          , viewDWHCustomer.SourceFileOID AS CustomerSourceFileOID
          , viewDWHCustomer.IdentifierTypeOID AS CustomerIdentifierTypeOID
          , ISNULL(viewDWHCustomer.InvolvedPartyOID, -1) AS CustomerOID
          , ( RTRIM(LTRIM(viewAR_ST_Product_Int.K_FONDS)) + '/'
              + ISNULL(viewARProductMap.IDAT, '') ) AS ProductUniqueId
          , viewARProductMap.SourceSystemOID AS ProductSourceSystemOID
          , viewARProductMap.SourceFileOID AS ProductSourceFileOID
          , viewARProductMap.IdentifierTypeOID AS ProductIdentifierTypeOID
          , ISNULL(viewARProductMap.ProductOID, -1) AS ProductOID
          , CAST(viewAR_ST_Product_Int.EFF_REKNR AS VARCHAR) + '/EFF' AS MasterProductArrangementUniqueId
          , 1010001005 AS MasterProductArrangementSourceSystemOID
          , 1010001121 AS MasterProductArrangementSourceFileOID
          , 1080001019 AS MasterProductArrangementIdentifierTypeOID
          , viewDWHArrangement.ArrangementOID AS MasterProductArrangementOID
          , 'ST' AS Securities_Type
    FROM    viewAR_ST_Product_Int
            LEFT OUTER JOIN (
                              SELECT    InvolvedPartyOID
                                      , UniqueIdInSourceSystem
                                      , SourceFileOID
                                      , SourceSystemOID
                                      , IdentifierTypeOID
                              FROM      viewDWHIP
                              WHERE     AABCustomerIndicatorOID = 616
                                        AND SourceFileOID = 1010001110
                                        AND SourceSystemOID = 1010001014
                                        AND IdentifierTypeOID IN ( 1080001004, 1080001024 )
                            ) AS viewDWHCustomer
                ON CAST(viewAR_ST_Product_Int.CLIENT_NR AS VARCHAR) = viewDWHCustomer.UniqueIdInSourceSystem
            LEFT OUTER JOIN (
                              SELECT    ArrangementOID
                                      , UniqueIdInSourceSystem
                              FROM      viewDWHArrangement
                              WHERE     SourceFileOID = 1010001121
                                        AND IdentifierTypeOID = 1080001019
                                        AND SourceSystemOID = 1010001005
                            ) AS viewDWHArrangement
                ON CAST(viewAR_ST_Product_Int.EFF_REKNR AS VARCHAR) + '/EFF' = viewDWHArrangement.UniqueIdInSourceSystem
            LEFT OUTER JOIN (
                              SELECT    xlsTOFFONDS.FONDSCODE AS FONDSCODE
                                      , xlsTOFFONDS.IDAT AS IDAT
                                      , viewDWHProduct.ProductOID AS ProductOID
                                      , viewDWHProduct.UniqueIdInSourceSystem AS UniqueIdInSourceSystem
                                      , viewDWHProduct.SourceFileOID AS SourceFileOID
                                      , viewDWHProduct.SourceSystemOID AS SourceSystemOID
                                      , viewDWHProduct.IdentifierTypeOID AS IdentifierTypeOID
                              FROM      xlsTOFFONDS
                                        INNER JOIN (
                                                     SELECT ProductOID
                                                          , UniqueIdInSourceSystem
                                                          , SourceFileOID
                                                          , SourceSystemOID
                                                          , IdentifierTypeOID
                                                     FROM   viewDWHProduct
                                                     WHERE  SourceFileOID = 1010001117
                                                            AND SourceSystemOID = 1010001005
                                                            AND IdentifierTypeOID = 1080001014
                                                   ) AS viewDWHProduct
                                            ON ( xlsTOFFONDS.FONDSCODE + '/'
                                                 + xlsTOFFONDS.IDAT ) = viewDWHProduct.UniqueIdInSourceSystem
                            ) AS viewARProductMap
                ON RTRIM(LTRIM(viewAR_ST_Product_Int.K_FONDS)) = viewARProductMap.FONDSCODE
    WHERE   ( CAST(viewAR_ST_Product_Int.EFF_REKNR AS VARCHAR)
              + RTRIM(LTRIM(viewAR_ST_Product_Int.K_FONDS)) ) NOT IN (
            SELECT  ( CAST(xlsAR_SecuritiesPositions.REKNR AS VARCHAR)
                      + RTRIM(LTRIM(xlsAR_SecuritiesPositions.FONDSCOD)) )
            FROM    xlsAR_SecuritiesPositions )

Without the execution plan, it's hard to know what might be going wrong, but a couple of things jump right out. First off, you're joining views to views. This can lead to major performance bottlenecks. Sometimes SQL Server can be very smart about how it deals with views, breaking down the view query itself and identifying which parts of it are actually applicable to the query you're working on. Other times, especially as things get more complex, as this query is, you'll simply see SQL Server treat the entire view as a unit and process every table referenced within the view, whether it's applicable to the query you're running against it or not. Execution plans will help here, but as a general rule, I don't recommend joining views to views if you can avoid it.

The next thing that jumps out are several instances of runnning functions against columns:

 RTRIM(LTRIM(viewAR_ST_Product_Int.K_FONDS)) = viewARProductMap.FONDSCODE WHERE        (                CAST(viewAR_ST_Product_Int.EFF_REKNR AS VARCHAR) +                RTRIM(LTRIM(viewAR_ST_Product_Int.K_FONDS))

This will lead to scans. You can't get good index seeks out of this code, regardless of how good your indexes may be. You need to either drop the functions, or clean up the data in the tables so that these functions are not needed. With the functions in place, you'll get scans and slow performance, no matter what else you do.

That's about all I can see or suggest without a look at the execution plans, your table structures, the views, indexes and some sample data.

I know it's an old post but the first thing I would do is read Grant's answer... especially the part about have forumlas in the columns in the WHERE clause and in the columns in the ON clause. The make any chance of high performance virtually non existant because they force scans instead of seeks just like Grant said.