Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query Execution Issue

Status
Not open for further replies.
wbodger

wbodger

Programmer
Joined
Apr 23, 2007
Messages
769
Location
US
So, I've got a query that uses lots of left outer joins and views to exclude multiple populations and it is sucking the lifeblood out of my server. So... I took a look at it with the estimated execution plan and it meant next to nothing to me. Bummer. Any hints or tips from you wonderful folks out there as to where best to start tracking down execution issues? I tried running this query and it brought my server to its knees.

Willie

Not sure if you want to look at the code or just give me a direction for solving this issue, so I didn't want to post the code yet. However, it is SQL 2000 SP4 running on Windows .Server 2003
 
Sort by date Sort by votes
Can you post your query and query plan.

- Paul
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Here is the query:

Code: 
SELECT Distinct vONYX_Individual_PrimaryEmailOnly.iEntityID, 368 AS iListID
FROM vONYX_Individual_PrimaryEmailOnly LEFT OUTER JOIN
     vOnyx_Products_SimplyAccounting_Only ON 
     vONYX_Individual_PrimaryEmailOnly.iIndividualId = vOnyx_Products_SimplyAccounting_Only.iOwnerId LEFT OUTER JOIN vOnyx_Products_Nova_Only ON 
     vONYX_Individual_PrimaryEmailOnly.iIndividualId = vOnyx_Products_Nova_Only.iOwnerId LEFT OUTER JOIN temp_rejected tr ON
     vONYX_Individual_PrimaryEmailOnly.vchEmailAddress = tr.vchEmailAddress LEFT OUTER JOIN vOnyx_Products_PCsync50_Only ON 
     vONYX_Individual_PrimaryEmailOnly.iIndividualId = vOnyx_Products_PCsync50_Only.iOwnerId LEFT OUTER JOIN vOnyx_Products_FileMover50_Only ON 
     vONYX_Individual_PrimaryEmailOnly.iIndividualId = vOnyx_Products_FileMover50_Only.iOwnerId LEFT OUTER JOIN 
     (SELECT * FROM vONYX_Products_PCmover WHERE DateDiff(day, vONYX_Products_PCmover.dtPurchaseDate, getdate())<'90')pcm ON 
     vONYX_Individual_PrimaryEmailOnly.iIndividualId = pcm.iOwnerID LEFT OUTER JOIN 
     (SELECT * FROM vONYX_Products_PCsync WHERE DateDiff(day, vONYX_Products_PCsync.dtPurchaseDate, getdate())<'90')pcs ON 
     vONYX_Individual_PrimaryEmailOnly.iIndividualId = pcs.iOwnerID
WHERE (vOnyx_Products_SimplyAccounting_Only.iOwnerId IS NULL) AND 
      (vOnyx_Products_Nova_Only.iOwnerId IS NULL)  AND
      (vONYX_Individual_PrimaryEmailOnly.chLanguageCode='ENG') AND
      (vONYX_Individual_PrimaryEmailOnly.bNewsletter = 0) AND
      (vONYX_Individual_PrimaryEmailOnly.bNoContact = 0) AND
      (tr.vchEmailAddress IS NULL) AND
      (vOnyx_Products_PCsync50_Only.iOwnerId IS NULL)  AND
      (vOnyx_Products_FileMover50_Only.iOwnerId IS NULL)  AND
      (pcm.iOwnerID IS NULL) AND
      (pcs.iOwnerID IS NULL)

However, I'm not sure how to post the estimated execution plan. The only view I can see is the graphical view and it won't let me copy it.
 
Upvote 0 Downvote
To get the query plan in text format run this statement first.

set showplan_text on


Then run your query.


- Paul
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Well, here is the estimated execution plan text, though it is cutoff at the end because it was in SQL Server as well. Did I miss something?

Code: 
  |--Parallelism(Gather Streams)
       |--Hash Match(Aggregate, HASH:([SPAM_Preferences].[iEntityID]) DEFINE:([Expr1087]=ANY(368)))
            |--Filter(WHERE:([CustomerProduct].[iOwnerId]=NULL))
                 |--Parallelism(Repartition Streams, PARTITION COLUMNS:([SPAM_Preferences].[iEntityID]))
                      |--Hash Match(Right Outer Join, HASH:([CustomerProduct].[iOwnerId])=([Individual].[iIndividualId]))
                           |--Parallelism(Repartition Streams, PARTITION COLUMNS:([CustomerProduct].[iOwnerId]))
                           |    |--Hash Match(Inner Join, HASH:([ProductMaster_Extended].[chProductNumber], [ProductMaster_Extended].[iSiteID])=([CustomerProduct].[chProductNumber], [CustomerProduct].[iSiteId]), RESIDUAL:([CustomerProduct].[chProductNumber
                           |         |--Parallelism(Broadcast)
                           |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductMaster].[chProductNumber], [ProductHierarchy].[iSiteId]) WITH PREFETCH)
                           |         |         |--Parallelism(Gather Streams)
                           |         |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductMaster].[iSiteId]))
                           |         |         |         |--Clustered Index Scan(OBJECT:([Onyx].[dbo].[ProductMaster].[PKCUCProductMaster]), WHERE:([ProductMaster].[iHierarchyId]=268))
                           |         |         |         |--Index Seek(OBJECT:([Onyx].[dbo].[ProductHierarchy].[PKNUCProductHierarchy]), SEEK:([ProductHierarchy].[iHierarchyId]=268 AND [ProductHierarchy].[iSiteId]=[ProductMaster].[iSiteId]) ORDERED FORWARD
                           |         |         |--Index Seek(OBJECT:([Onyx].[dbo].[ProductMaster_Extended].[IX_ProductMaster_Extended_Uniq_Prodnum]), SEEK:([ProductMaster_Extended].[chProductNumber]=[ProductMaster].[chProductNumber] AND [ProductMaster_Exte
                           |         |--Clustered Index Scan(OBJECT:([Onyx].[dbo].[CustomerProduct].[PKNUCCustomerProduct]), WHERE:(datediff(day, [CustomerProduct].[dtPurchaseDate], getdate())<90))
                           |--Filter(WHERE:([CustomerProduct].[iOwnerId]=NULL))
                                |--Hash Match(Right Outer Join, HASH:([CustomerProduct].[iOwnerId])=([Individual].[iIndividualId]))
                                     |--Parallelism(Repartition Streams, PARTITION COLUMNS:([CustomerProduct].[iOwnerId]))
                                     |    |--Hash Match(Inner Join, HASH:([ProductMaster_Extended].[chProductNumber], [ProductMaster_Extended].[iSiteID])=([CustomerProduct].[chProductNumber], [CustomerProduct].[iSiteId]), RESIDUAL:([CustomerProduct].[chPro
                                     |         |--Bitmap(HASH:([ProductMaster_Extended].[chProductNumber], [ProductMaster_Extended].[iSiteID]), DEFINE:([Bitmap1093]))
                                     |         |    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([ProductMaster_Extended].[chProductNumber], [ProductMaster_Extended].[iSiteID]))
                                     |         |         |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductMaster].[chProductNumber], [ProductHierarchy].[iSiteId]) WITH PREFETCH)
                                     |         |              |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductMaster].[iSiteId], [ProductMaster].[iHierarchyId]))
                                     |         |              |    |--Clustered Index Scan(OBJECT:([Onyx].[dbo].[ProductMaster].[PKCUCProductMaster]),  WHERE:([ProductMaster].[iHierarchyId]=272 OR [ProductMaster].[iHierarchyId]=199) ORDERED FORWARD)
                                     |         |              |    |--Index Seek(OBJECT:([Onyx].[dbo].[ProductHierarchy].[PKNUCProductHierarchy]), SEEK:([ProductHierarchy].[iHierarchyId]=[ProductMaster].[iHierarchyId] AND [ProductHierarchy].[iSiteId]=[Prod
                                     |         |              |--Index Seek(OBJECT:([Onyx].[dbo].[ProductMaster_Extended].[IX_ProductMaster_Extended_Uniq_Prodnum]), SEEK:([ProductMaster_Extended].[chProductNumber]=[ProductMaster].[chProductNumber] AND [Pro
                                     |         |--Parallelism(Repartition Streams, PARTITION COLUMNS:([CustomerProduct].[chProductNumber], [CustomerProduct].[iSiteId]), WHERE:(PROBE([Bitmap1093])=TRUE))
                                     |              |--Clustered Index Scan(OBJECT:([Onyx].[dbo].[CustomerProduct].[PKNUCCustomerProduct]), WHERE:(datediff(day, [CustomerProduct].[dtPurchaseDate], getdate())<90))
                                     |--Parallelism(Repartition Streams, PARTITION COLUMNS:([Individual].[iIndividualId]))
                                          |--Hash Match(Inner Join, HASH:([Country].[chCountryCode])=([Individual].[chCountryCode]), RESIDUAL:([Individual].[chCountryCode]=[Country].[chCountryCode]))
                                               |--Parallelism(Distribute Streams, PARTITION COLUMNS:([Country].[chCountryCode]))
                                               |    |--Index Scan(OBJECT:([Onyx].[dbo].[Country].[IX_Country_Currency]))
                                               |--Filter(WHERE:([CustomerProduct].[iOwnerId]=NULL))
                                                    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([Individual].[chCountryCode]))
                                                         |--Nested Loops(Left Outer Join, WHERE:([Individual].[iIndividualId]=[CustomerProduct].[iOwnerId]))
                                                              |--Filter(WHERE:([CustomerProduct].[iOwnerId]=NULL))
                                                              |    |--Nested Loops(Left Outer Join, WHERE:([Individual].[iIndividualId]=[CustomerProduct].[iOwnerId]))
                                                              |         |--Filter(WHERE:([tr].[vchemailaddress]=NULL))
                                                              |         |    |--Hash Match(Right Outer Join, HASH:([tr].[vchemailaddress])=([Expr1092]), RESIDUAL:([Expr1092]=[tr].[vchemailaddress]))
                                                              |         |         |--Parallelism(Repartition Streams, PARTITION COLUMNS:([tr].[vchemailaddress]))
                                                              |         |         |    |--Table Scan(OBJECT:([EmailBlasts].[dbo].[temp_rejected] AS [tr]))
                                                              |         |         |--Parallelism(Repartition Streams, PARTITION COLUMNS:([Expr1092]))
                                                              |         |              |--Compute Scalar(DEFINE:([Expr1092]=Convert([Individual].[vchEmailAddress])))
                                                              |         |                   |--Filter(WHERE:([CustomerProduct].[iOwnerId]=NULL))
                                                              |         |                        |--Nested Loops(Left Outer Join, WHERE:([Individual].[iIndividualId]=[CustomerProduct].[iOwnerId]))
                                                              |         |                             |--Filter(WHERE:([CustomerProduct].[iOwnerId]=NULL))
                                                              |         |                             |    |--Nested Loops(Left Outer Join, WHERE:([Individual].[iIndividualId]=[CustomerProduct].[iOwnerId]))
                                                              |         |                             |         |--Hash Match(Inner Join, HASH:([SPAM_Preferences].[iOwnerID], [SPAM_Preferences].[vchEmailAddress])=([Individual].[iIndividualId], [Individual]
                                                              |         |                             |         |    |--Bitmap(HASH:([SPAM_Preferences].[iOwnerID], [SPAM_Preferences].[vchEmailAddress]), DEFINE:([Bitmap1094]))
                                                              |         |                             |         |    |    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([SPAM_Preferences].[iOwnerID], [SPAM_Preferences].[vchEmailAddress]))
                                                              |         |                             |         |    |         |--Clustered Index Scan(OBJECT:([Onyx].[dbo].[SPAM_Preferences].[PK_SPAM_Preferences]), WHERE:(((((((Convert([SPAM_Preferences].[
                                                              |         |                             |         |    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([Individual].[iIndividualId], [Individual].[vchEmailAddress]), WHERE:(PROBE([Bitmap1
                                                              |         |                             |         |         |--Clustered Index Scan(OBJECT:([Onyx].[dbo].[Individual].[IX_Individual_email_iid]), WHERE:([Individual].[chLanguageCode]='ENG'))
                                                              |         |                             |         |--Table Spool
                                                              |         |                             |              |--Filter(WHERE:([CustomerProduct].[iOwnerId]=NULL))
                                                              |         |                             |                   |--Nested Loops(Left Outer Join, OUTER REFERENCES:([CustomerProduct].[iOwnerId]))
                                                              |         |                             |                        |--Sort(ORDER BY:([CustomerProduct].[iOwnerId] ASC))
                                                              |         |                             |                        |    |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([Onyx].[dbo].[CustomerProduct]) WITH PREFETCH)
                                                              |         |                             |                        |         |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductMaster_Extended].[iSiteID], [ProductMaster_Extended].[chProductNu
                                                              |         |                             |                        |              |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductMaster].[chProductNumber], [ProductHierarchy].[iSiteId]))
                                                              |         |                             |                        |              |    |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductMaster].[iSiteId], [ProductMaster].[iHierarchyId]))
Clustered Index Scan(OBJECT:([Onyx].[dbo].[ProductMaster].[PKCUCProductMaster]), WHERE:([ProductMaster].[iHierarchyId]=231 OR [ProductMaster].[iHierarchyId]=229))
Index Seek(OBJECT:([Onyx].[dbo].[ProductHierarchy].[PKNUCProductHierarchy]), SEEK:([ProductHierarchy].[iHierarchyId]=[ProductMaster].[iHierarchyId] AND [ProductHierarchy].[iSiteId]=[ProductMaster].[iSiteId]) ORDERED FORWARD)
                                                              |         |                             |                        |              |    |--Index Seek(OBJECT:([Onyx].[dbo].[ProductMaster_Extended].[IX_ProductMaster_Extended_Uniq_Prodnum]), SEEK:(
                                                              |         |                             |                        |              |--Index Seek(OBJECT:([Onyx].[dbo].[CustomerProduct].[customerproduct_Site_ProductNumber]), SEEK:([CustomerProduct
                                                              |         |                             |                        |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductMaster].[chProductNumber], [ProductHierarchy].[iSiteId]))
                                                              |         |                             |                             |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductMaster].[iSiteId], [ProductMaster].[iHierarchyId]))
                                                              |         |                             |                             |    |--Nested Loops(Inner Join, OUTER REFERENCES:([CustomerProduct].[iSiteId], [CustomerProduct].[chProductNumber]))
                                                              |         |                             |                             |    |    |--Bookmark Lookup(BOOKMARK:([Bmk1021]), OBJECT:([Onyx].[dbo].[CustomerProduct]))
                                                              |         |                             |                             |    |    |    |--Index Seek(OBJECT:([Onyx].[dbo].[CustomerProduct].[NNXcustomerproduct_ownerid]), SEEK:([CustomerProduct].[
                                                              |         |                             |                             |    |    |--Clustered Index Seek(OBJECT:([Onyx].[dbo].[ProductMaster].[PKCUCProductMaster]), SEEK:([ProductMaster].[chProdu
                                                              |         |                             |                             |    |--Index Seek(OBJECT:([Onyx].[dbo].[ProductHierarchy].[PKNUCProductHierarchy]), SEEK:([ProductHierarchy].[iHierarchyId]
                                                              |         |                             |                             |--Index Seek(OBJECT:([Onyx].[dbo].[ProductMaster_Extended].[IX_ProductMaster_Extended_Uniq_Prodnum]), SEEK:([ProductMaster_
                                                              |         |                             |--Table Spool
                                                              |         |                                  |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductMaster].[chProductNumber], [ProductHierarchy].[iSiteId]))
                                                              |         |                                       |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductMaster].[iHierarchyId], [ProductMaster].[iSiteId]))
                                                              |         |                                       |    |--Nested Loops(Inner Join, OUTER REFERENCES:([CustomerProduct].[iSiteId], [CustomerProduct].[chProductNumber]))
                                                              |         |                                       |    |    |--Filter(WHERE:([CustomerProduct].[iOwnerId]=NULL))
                                                              |         |                                       |    |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([CustomerProduct].[iOwnerId]))
                                                              |         |                                       |    |    |         |--Bookmark Lookup(BOOKMARK:([Bmk1004]), OBJECT:([Onyx].[dbo].[CustomerProduct]) WITH PREFETCH)
                                                              |         |                                       |    |    |         |    |--Index Seek(OBJECT:([Onyx].[dbo].[CustomerProduct].[NNXcustomerproduct_prodnumber]), SEEK:([CustomerProduct].[chProdu
                                                              |         |                                       |    |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductMaster].[iHierarchyId], [ProductMaster_Extended].[iSiteID]))
                                                              |         |                                       |    |    |              |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductMaster].[iSiteId], [ProductMaster].[chProductNumber]))
                                                              |         |                                       |    |    |              |    |--Nested Loops(Inner Join, OUTER REFERENCES:([CustomerProduct].[iSiteId], [CustomerProduct].[chProductNumber]))
                                                              |         |                                       |    |    |              |    |    |--Filter(WHERE:(([CustomerProduct].[chProductNumber]<'PAFGTYPC02000P0EVDEN' OR [CustomerProduct].[chProductN
Bookmark Lookup(BOOKMARK:([Bmk1029]), OBJECT:([Onyx].[dbo].[CustomerProduct]))
Index Seek(OBJECT:([Onyx].[dbo].[CustomerProduct].[NNXcustomerproduct_ownerid]), SEEK:([CustomerProduct].[iOwnerId]=[CustomerProduct].[iOwnerId]) ORDERED FORWARD)
                                                              |         |                                       |    |    |              |    |    |--Clustered Index Seek(OBJECT:([Onyx].[dbo].[ProductMaster].[PKCUCProductMaster]), SEEK:([ProductMaster].[ch
                                                              |         |                                       |    |    |              |    |--Index Seek(OBJECT:([Onyx].[dbo].[ProductMaster_Extended].[IX_ProductMaster_Extended_Uniq_Prodnum]), SEEK:([Prod
                                                              |         |                                       |    |    |              |--Index Seek(OBJECT:([Onyx].[dbo].[ProductHierarchy].[PKNUCProductHierarchy]), SEEK:([ProductHierarchy].[iHierarchyId]
                                                              |         |                                       |    |    |--Clustered Index Seek(OBJECT:([Onyx].[dbo].[ProductMaster].[PKCUCProductMaster]), SEEK:([ProductMaster].[chProductNumber]=[CustomerP
                                                              |         |                                       |    |--Index Seek(OBJECT:([Onyx].[dbo].[ProductHierarchy].[PKNUCProductHierarchy]), SEEK:([ProductHierarchy].[iHierarchyId]=[ProductMaster].[iH
                                                              |         |                                       |--Index Seek(OBJECT:([Onyx].[dbo].[ProductMaster_Extended].[IX_ProductMaster_Extended_Uniq_Prodnum]), SEEK:([ProductMaster_Extended].[chProduct
                                                              |         |--Table Spool
                                                              |              |--Filter(WHERE:([CustomerProduct].[iOwnerId]=NULL))
                                                              |                   |--Nested Loops(Left Outer Join, OUTER REFERENCES:([CustomerProduct].[iOwnerId]))
                                                              |                        |--Bookmark Lookup(BOOKMARK:([Bmk1007]), OBJECT:([Onyx].[dbo].[CustomerProduct]) WITH PREFETCH)
                                                              |                        |    |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductMaster_Extended].[iSiteID]))
                                                              |                        |         |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductMaster].[iHierarchyId], [ProductMaster_Extended].[iSiteID]))
                                                              |                        |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductMaster_Extended].[iSiteID]))
                                                              |                        |         |    |    |--Index Seek(OBJECT:([Onyx].[dbo].[ProductMaster_Extended].[IX_ProductMaster_Extended_Uniq_Prodnum]), SEEK:([ProductMaster_Extended].[chProductNumbe
                                                              |                        |         |    |    |--Clustered Index Seek(OBJECT:([Onyx].[dbo].[ProductMaster].[PKCUCProductMaster]), SEEK:([ProductMaster].[chProductNumber]='PAFGPCSY05000POEMPML' AN
                                                              |                        |         |    |--Index Seek(OBJECT:([Onyx].[dbo].[ProductHierarchy].[PKNUCProductHierarchy]), SEEK:([ProductHierarchy].[iHierarchyId]=[ProductMaster].[iHierarchyId] AND
                                                              |                        |         |--Index Seek(OBJECT:([Onyx].[dbo].[CustomerProduct].[customerproduct_Site_ProductNumber]), SEEK:([CustomerProduct].[iSiteId]=[ProductMaster_Extended].[iSiteID
                                                              |                        |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductMaster].[iHierarchyId], [ProductMaster_Extended].[iSiteID]))
                                                              |                             |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductMaster].[iSiteId], [ProductMaster].[chProductNumber]))
                                                              |                             |    |--Nested Loops(Inner Join, OUTER REFERENCES:([CustomerProduct].[iSiteId], [CustomerProduct].[chProductNumber]))
                                                              |                             |    |    |--Filter(WHERE:([CustomerProduct].[chProductNumber]<'PAFGPCSY05000POEMPML' OR [CustomerProduct].[chProductNumber]>'PAFGPCSY05000POEMPML'))
                                                              |                             |    |    |    |--Bookmark Lookup(BOOKMARK:([Bmk1037]), OBJECT:([Onyx].[dbo].[CustomerProduct]))
                                                              |                             |    |    |         |--Index Seek(OBJECT:([Onyx].[dbo].[CustomerProduct].[NNXcustomerproduct_ownerid]), SEEK:([CustomerProduct].[iOwnerId]=[CustomerProduct].[iOwner
                                                              |                             |    |    |--Clustered Index Seek(OBJECT:([Onyx].[dbo].[ProductMaster].[PKCUCProductMaster]), SEEK:([ProductMaster].[chProductNumber]=[CustomerProduct].[chProductNu
                                                              |                             |    |--Index Seek(OBJECT:([Onyx].[dbo].[ProductMaster_Extended].[IX_ProductMaster_Extended_Uniq_Prodnum]), SEEK:([ProductMaster_Extended].[chProductNumber]=[Produc
                                                              |                             |--Index Seek(OBJECT:([Onyx].[dbo].[ProductHierarchy].[PKNUCProductHierarchy]), SEEK:([ProductHierarchy].[iHierarchyId]=[ProductMaster].[iHierarchyId] AND [ProductH
                                                              |--Table Spool
                                                                   |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductHierarchy].[iSiteId]))
                                                                        |--Filter(WHERE:([CustomerProduct].[iOwnerId]=NULL))
                                                                        |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([CustomerProduct].[iOwnerId]))
                                                                        |         |--Bookmark Lookup(BOOKMARK:([Bmk1009]), OBJECT:([Onyx].[dbo].[CustomerProduct]) WITH PREFETCH)
                                                                        |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductHierarchy].[iSiteId]))
                                                                        |         |         |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductMaster].[iHierarchyId], [ProductMaster].[iSiteId]))
                                                                        |         |         |    |--Clustered Index Seek(OBJECT:([Onyx].[dbo].[ProductMaster].[PKCUCProductMaster]), SEEK:([ProductMaster].[chProductNumber]='PAFGFLMV05000POEMPML') ORDERED FOR
                                                                        |         |         |    |--Index Seek(OBJECT:([Onyx].[dbo].[ProductHierarchy].[PKNUCProductHierarchy]), SEEK:([ProductHierarchy].[iHierarchyId]=[ProductMaster].[iHierarchyId] AND [Pro
                                                                        |         |         |--Index Seek(OBJECT:([Onyx].[dbo].[CustomerProduct].[customerproduct_Site_ProductNumber]), SEEK:([CustomerProduct].[iSiteId]=[ProductHierarchy].[iSiteId] AND [Cust
                                                                        |         |--Table Spool
                                                                        |              |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductMaster].[iHierarchyId], [ProductMaster_Extended].[iSiteID]))
                                                                        |                   |--Nested Loops(Inner Join, OUTER REFERENCES:([ProductMaster].[iSiteId], [ProductMaster].[chProductNumber]))
                                                                        |                   |    |--Nested Loops(Inner Join, OUTER REFERENCES:([CustomerProduct].[iSiteId], [CustomerProduct].[chProductNumber]))
                                                                        |                   |    |    |--Filter(WHERE:([CustomerProduct].[chProductNumber]<'PAFGFLMV05000POEMPML' OR [CustomerProduct].[chProductNumber]>'PAFGFLMV05000POEMPML'))
                                                                        |                   |    |    |    |--Bookmark Lookup(BOOKMARK:([Bmk1045]), OBJECT:([Onyx].[dbo].[CustomerProduct]))
                                                                        |                   |    |    |         |--Index Seek(OBJECT:([Onyx].[dbo].[CustomerProduct].[NNXcustomerproduct_ownerid]), SEEK:([CustomerProduct].[iOwnerId]=[CustomerProduct].[iOwner
                                                                        |                   |    |    |--Clustered Index Seek(OBJECT:([Onyx].[dbo].[ProductMaster].[PKCUCProductMaster]), SEEK:([ProductMaster].[chProductNumber]=[CustomerProduct].[chProductNu
                                                                        |                   |    |--Index Seek(OBJECT:([Onyx].[dbo].[ProductMaster_Extended].[IX_ProductMaster_Extended_Uniq_Prodnum]), SEEK:([ProductMaster_Extended].[chProductNumber]=[Produc
                                                                        |                   |--Index Seek(OBJECT:([Onyx].[dbo].[ProductHierarchy].[PKNUCProductHierarchy]), SEEK:([ProductHierarchy].[iHierarchyId]=[ProductMaster].[iHierarchyId] AND [ProductH
                                                                        |--Index Seek(OBJECT:([Onyx].[dbo].[ProductMaster_Extended].[IX_ProductMaster_Extended_Uniq_Prodnum]), SEEK:([ProductMaster_Extended].[chProductNumber]='PAFGFLMV05000POEMPML' AND [Prod
 
Upvote 0 Downvote
I have a couple suggestions (all dealing with the
subqueries).

You have this...

[tt][blue]SELECT * FROM vONYX_Products_PCmover WHERE DateDiff(day, vONYX_Products_PCmover.dtPurchaseDate, getdate())<'90'[/blue][/tt]

This is bad for a couple reasons. First, you are
returning all of the columns from this table
but appear to only use 1 column. Replace the *
with the column name(s).

Next, the way you are limiting the dates will
cause performance issues because any index you
have on the dtPurchaseDate column will not
be used. This happens because you are using
the datediff function on the column.

This would be better:
[tt][blue]SELECT iOwnerId FROM vONYX_Products_PCmover WHERE dtPurchaseDate >= GetDate() - 90[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Those make sense, unfortunately, there is no index on the dtPurchaseDate field. And now as I look further there are NO indexes on those views. I guess I should have done a little more poking about before I asked for help. Now I'll go see what I can do about creating indexes on those views.

WB
 
Upvote 0 Downvote
So, if my view is as simple as:
Code: 
CREATE VIEW dbo.vONYX_Individual_PrimaryEmailOnly
AS
SELECT     dbo.Spam_Preferences.iEntityID, dbo.vOnyx_Individual.iIndividualId, dbo.vOnyx_Individual.chCountryCode, dbo.vOnyx_Individual.vchEmailAddress, 
                      dbo.vOnyx_Individual.iCompanyId, dbo.vOnyx_Individual.vchCompanyName, dbo.vOnyx_Individual.chLanguageCode, 
                      dbo.country.chCurrencyCode, dbo.SPAM_Preferences.bLaplink_bPrivate, dbo.SPAM_Preferences.bNewsLetter, dbo.SPAM_Preferences.bThirdParty, dbo.SPAM_Preferences.bTipsTricks, dbo.SPAM_Preferences.bNocontact
FROM         dbo.vOnyx_Individual INNER JOIN
                      dbo.Spam_Preferences ON dbo.vOnyx_Individual.iIndividualId = dbo.Spam_Preferences.iOwnerID AND 
                      dbo.vOnyx_Individual.vchEmailAddress = dbo.Spam_Preferences.vchEmailAddress INNER JOIN
                      dbo.country ON dbo.vOnyx_Individual.chCountryCode = dbo.country.chCountryCode
WHERE     (dbo.Spam_Preferences.bIsInvalid = 0) AND  (dbo.Spam_Preferences.bIsPress = 0)  AND  (dbo.Spam_Preferences.bNoContact = 0)

How would I go about creating indexes on iOwnerID, countrycoe, languagecode and emailaddress? Is there anything special about it?

WB
 
Upvote 0 Downvote
And, now I see that the views are nested. There are a few main views and then smaller views are created from those views. It looks to me like I cannot index those nested views, so would it be better to create them differently by creating them directly from the main tables?

WB
 
Upvote 0 Downvote
All kinds of problems...

I am creating the views in database B using data from database A. Does this scenario mean that I cannot use schemabinding and therefore no indexes on the views? Then, if I create an index on the underlying table, will that translate into a positive for the query on the view if I cannot create an index on the view?

WB
 
Upvote 0 Downvote
So, this:
Code: 
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

CREATE VIEW dbo.vOnyx_Products
WITH SCHEMABINDING
AS
SELECT     onyx.dbo.CustomerProduct.iProductId, onyx.dbo.CustomerProduct.iOwnerId, onyx.dbo.CustomerProduct.chProductNumber, 
                      onyx.dbo.CustomerProduct.vchSerialNumber, onyx.dbo.CustomerProduct.dtPurchaseDate, 
                      onyx.dbo.ProductMaster.vchDescription, onyx.dbo.ProductHierarchy.vchHierarchyDesc, onyx.dbo.ProductMaster.iHierarchyId
FROM         onyx.dbo.CustomerProduct INNER JOIN
                      onyx.dbo.ProductMaster ON onyx.dbo.CustomerProduct.chProductNumber = onyx.dbo.ProductMaster.chProductNumber AND 
                      onyx.dbo.CustomerProduct.iSiteId = onyx.dbo.ProductMaster.iSiteId INNER JOIN
                      onyx.dbo.ProductHierarchy ON onyx.dbo.ProductMaster.iSiteId = onyx.dbo.ProductHierarchy.iSiteId AND 
                      onyx.dbo.ProductMaster.iHierarchyId = onyx.dbo.ProductHierarchy.iHierarchyId INNER JOIN
                      onyx.dbo.ProductMaster_Extended ON onyx.dbo.ProductMaster.chProductNumber = onyx.dbo.ProductMaster_Extended.chProductNumber AND 
                      onyx.dbo.ProductMaster.iSiteId = onyx.dbo.ProductMaster_Extended.iSiteID

Gives me this:

Code: 
Server: Msg 4512, Level 16, State 3, Procedure vOnyx_Products, Line 5
Cannot schema bind view 'dbo.vOnyx_Products' because name 'onyx.dbo.CustomerProduct' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Is this error because I have included the database name (Onyx) since it is not the same as the database I am creating the view in?

WB
 
Upvote 0 Downvote
You can only use schema binding on view that have simple joins. No OUTER JOIN, SELF JOINS, 3 Part names... There is a long list. But George's suggestions are good ones. I would start there and check performance.
Also, you don't need all the parens in your where clause.

- Paul
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
So, I am re-working this whole 'idea', moving it to another server etc. and making more of a 'reporting' database (which is what it is). Thank you all for your help and insights, they are leading me to a much better solution.

WB
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

CodeWell
  • Locked
  • Question Question
SQL Server 2017 Install Issue - Database Not in Configuration Manager
Replies
0
Views
882
CodeWell
CodeWell
dgillz
  • Locked
  • Question Question
SQL View not available to Excel Power Query
Replies
1
Views
770
dgillz
dgillz
intel233
  • Locked
  • Question Question
Backup/Restore DB from SQL issues
Replies
2
Views
462
intel233
intel233
dd2maker
  • Locked
  • Question Question
SQL server 2008 R2 backup restore issue
Replies
2
Views
413
dd2maker
dd2maker
niall5098
  • Locked
  • Question Question
linked server query causes sqlsrver to crash
Replies
1
Views
400
yelworcm
yelworcm

Part and Inventory Search

Sponsor

Back
Top