SQL Server Performance

Help in checking a query

Discussion in 'T-SQL Performance Tuning for Developers' started by itsdeshpande, Jul 11, 2008.

  1. itsdeshpande New Member

    HI All,
    We have a Report which calls the below Query. I just checked and found that there are calls to two views more than once, but there is nothing like a self join. But I could not say that the query is wrong right away. So, I want help in checking this. Please have a look at this and let me know if I can remove the RED colored lines and it would make no difference. (and ofcourse change to CRSC2 to CRSC1 and CC2 to CC1 in the SELECT list; and also change CRSC2.TargetClassInstanceID to CRSC1.TargetClassInstanceID in the last of WHERE ).
    SELECT DISTINCT CRSC1.SourceClassInstanceKeyValue AS [TargetSite],
    CC1.FQDN AS [TargetDomainController],
    CRSC2.SourceClassInstanceKeyValue AS [SourceSite],
    CC2.FQDN AS [SourceDomainController],
    CRCO.[Transport Type] AS [TransportType],
    CRCO.[Manual Connection] AS ManualConnection
    FROM [SC_Class_Rel_Connection Object_View] CRCO,
    SC_ComputerDimension_View CD,
    [SC_Class_Rel_Site-Computer_View] CRSC1,
    [SC_Class_Rel_Site-Computer_View] CRSC2,
    SC_Class_Computer_View CC1,
    SC_Class_Computer_View CC2
    WHERE ((CRCO.SourceClassInstanceID = CD.ComputerID) OR (CRCO.TargetClassInstanceID = CD.ComputerID))
    AND CRCO.TargetClassInstanceID = CRSC1.TargetClassInstanceID
    AND CC1.ClassInstanceID = CRSC1.TargetClassInstanceID
    AND CRCO.SourceClassInstanceID = CRSC2.TargetClassInstanceID
    AND CC2.ClassInstanceID = CRSC2.TargetClassInstanceID

    And, I am not able to run both the queries and compare output, because if I keep these second references, a error is being thrown that the allowed number of table usage (260) is exceeded.
    Thanks a lot,
    Manoj Deshpande.
  2. Adriaan New Member

    It's not a self-join: the query retrieves different rows from the same tables/views for the "Source" and "Target" objects.
    Looks like each of those views contains a number of tables, and the views make you exceed the limit for the number of tables allowed in a single query. Could you bypass the views, and query
  3. Adriaan New Member

    It's not a self-join: the query retrieves different rows from the same tables/views for the "Source" and "Target" objects.
    Looks like each of those views contains a number of tables, and the views make you exceed the limit for the number of tables allowed in a single query. It's usually a bad idea to stack views ...
    Could you re-write the query to be based only the relevant tables from the view?
  4. FrankKalis Moderator

    Actually I would use the explicit JOIN syntax to avoid any accidentally occuring CROSS JOINs.
  5. itsdeshpande New Member

    Hi Adrian and Frank
    Thanks for the reply.
    If you see these lines:
    AND CC1.ClassInstanceID = CRSC1.TargetClassInstanceID
    AND CRCO.SourceClassInstanceID = CRSC2.TargetClassInstanceID
    AND CC2.ClassInstanceID = CRSC2.TargetClassInstanceID
    CRSC1.TargetClassInstanceID and CRSC2.TargetClassInstanceID is basically the same column of the same view and it is repeatedly being checked with ClassInstanceID of CC1 and CC2, which again the same column of the same another view.
    Don't you think this is a little redundant here?
    Manoj.
  6. itsdeshpande New Member

    Hey Adriaan,
    Actually I cannot re-write the view definition. Reason being that, these are the views generated by a dynamically generated script, which it does based on entries in a particular table. If we need to reduce the tables' usage, we will have to modify that table; That is difficult because, these are MOM (Microsoft Operations manager) tables and none of the content is compromized over there.
  7. Adriaan New Member

    The join is between different instances of the same view. The different instances mean that different rows can be matched against eachother. So there is definitely no redundancy. (Did I say it wasn't a self-join? My fault: it is a self-join.)
    I didn't suggest that you change the view definition, just that you copy the query statement, remove all irrelevant tables, then use this directly as your query (instead of basing your query on the views). --- The problem with that seems to be that these views are being changed all the time, so you have nothing permanent to work against.
    On top of that, it seems like perhaps there is a little too much normalization.

Share This Page