Query Execution Issue

wbodger · Jun 4, 2007

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

ptheriault · Jun 4, 2007

Can you post your query and query plan.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

wbodger · Jun 4, 2007

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.

ptheriault · Jun 4, 2007

To get the query plan in text format run this statement first.

set showplan_text on

Then run your query.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

wbodger · Jun 4, 2007

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

gmmastros · Jun 4, 2007

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

wbodger · Jun 4, 2007

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

wbodger · Jun 4, 2007

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

wbodger · Jun 4, 2007

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

wbodger · Jun 4, 2007

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

wbodger · Jun 4, 2007

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

ptheriault · Jun 4, 2007

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

- If at first you don't succeed, find out if the loser gets anything.

wbodger · Jun 5, 2007

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

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!

Query Execution Issue

wbodger

Programmer

ptheriault

IS-IT--Management

wbodger

Programmer

ptheriault

IS-IT--Management

wbodger

Programmer

gmmastros

Programmer

wbodger

Programmer

wbodger

Programmer

wbodger

Programmer

wbodger

Programmer

wbodger

Programmer

ptheriault

IS-IT--Management

wbodger

Programmer

Similar threads

Part and Inventory Search

Sponsor