x

How to make a query perform faster

This query takes more than 1 hour

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 )
more ▼

asked Feb 15 '10 at 09:05 AM in Default

wijnand-167459 gravatar image

wijnand-167459
11 1 1 1

Don't forget to indent code with four spaces to allow the system to format it correctly. Makes it easier for my poor eyes to read too! :-)
Feb 15 '10 at 09:07 AM Ian Roke
formatted the code, but still not nice - maybe Jay could add his nicer version.....
Feb 15 '10 at 10:26 AM Kev Riley ♦♦
Kev, I was originally going to try and edit the OP and update the code, but I couldn't seee how to edit the origianl post, which is why I posted the code in an answer - +1 for Red Gates SQL Refactor...
Feb 15 '10 at 12:54 PM Jay Bonk
Jay : ah sorry - you need 2000 rep to edit other peoples posts. I could lift it for you if you want?
Feb 15 '10 at 02:16 PM Kev Riley ♦♦
Sure, if you don't mind lifting it, I can help out editing occasionally.
Feb 15 '10 at 03:46 PM Jay Bonk
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.

more ▼

answered Feb 15 '10 at 09:59 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
91k 19 21 74

+1 So easy to do as a beginner though or even later on in your experience. I've done it so many time to run quick queries which then snowballed.
Feb 15 '10 at 10:34 AM Ian Roke
(comments are locked)
10|1200 characters needed characters left

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 ) 
more ▼

answered Feb 15 '10 at 09:29 AM

Jay Bonk gravatar image

Jay Bonk
1.4k 2

+1 Nicely formatted Jay! ;-)
Feb 15 '10 at 10:33 AM Ian Roke
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Feb 15 '10 at 09:12 AM

Ian Roke gravatar image

Ian Roke
1.7k 29 33 34

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 30 '10 at 10:44 PM

Jeff Moden gravatar image

Jeff Moden
1.7k 3 8

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x977
x242
x61

asked: Feb 15 '10 at 09:05 AM

Seen: 5008 times

Last Updated: Feb 15 '10 at 10:25 AM