Stored Procedure Running Slow

I have seen this in odd circumstances before.  Sometimes, it seems to 'get better' if you actually Drop, then recreate, the sproc.

Also, try creating a duplicate of the sproc with a different name, but the same code.  Is it also slow?  If so, the above will more frequently work.

Finally, if the new version is fast and the old one remains slow, try dropping the old one and renaming the new one.

Please post Query.....


Also take a look at the Execution Plan and see if you are loosing time some where specific.

This is How>> Befor you run your query on Query analyzer press   Ctrl-K  and then run the query.
At the bottom left of the Query analyzer there will be a Execution plan tab.

It also depend on the traffic of your server at the particular time you are run the SP.

agree -  Drop, then recreate the SP some time help with SQL 2000, but never see anything different in SQL 7.0

I have tried dropping and recreating, running it under a different name and creating and altering WITH RECOMPILE but that hasn't helped. I have run it at the same time as running the query through Query Anl but the stored procedure is 10 times slower. I believe that there are some server resource issues however, I would still like to understand the time differences.

Here is the query:

CREATE       PROCEDURE dbo.ProductsGet_CS
      @JDEORGID as nvarchar(255),
      @CATEGORYCODE as nvarchar(255)
WITH RECOMPILE
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN

SELECT DISTINCT
      [Company_en-US_Catalog].Name AS Name,
      'Company' AS BaseCatalogName,
      Company_CatalogProducts.oid,
      Company_CatalogProducts.DefinitionName,
      Company_CatalogProducts.IsSearchable,
      Company_CatalogProducts.cy_list_price,
      Company_CatalogProducts.UseCategoryPricing,
      Company_CatalogProducts.[TimeStamp],
      Company_CatalogProducts.OriginalPrice,
      Company_CatalogProducts.i_ClassType,
      Company_CatalogProducts.ParentOID,
      Company_CatalogProducts.ProductID + '(Company)' AS ProductID,
      Company_CatalogProducts.VariantID,
      Company_CatalogProducts.ProductVariantCode,
      Company_CatalogProducts.LastModified,
      Company_CatalogProducts.CatalogName,
      Company_CatalogProducts.PrimaryParentCategory,
      Company_CatalogProducts.[#CategoryDisplayName],
      Company_CatalogProducts.ProductCode,
      Company_CatalogProducts.ApprovalCode,
      Company_CatalogProducts.CycleCountCategory,
      Company_CatalogProducts.EffectiveDate,
      Company_CatalogProducts.ExpirationDate,
      Company_CatalogProducts.GLCategory,
      Company_CatalogProducts.InventoryItemFlag,
      Company_CatalogProducts.ItemType,
      Company_CatalogProducts.JDEGenericItemID,
      Company_CatalogProducts.JdeItemId,
      Company_CatalogProducts.JDEItemMasterLastUpdated,
      Company_CatalogProducts.JDEItemMasterLastUpdateID,
      Company_CatalogProducts.JDEItemXRefLastUpdated,
      Company_CatalogProducts.JDEItemXRefLastUpdateID,
      Company_CatalogProducts.JDESupplierCatalogLastUpdated,
      Company_CatalogProducts.JDESupplierCatalogLastUpdateID,
      Company_CatalogProducts.JDEUOMConversionLastUpdated,
      Company_CatalogProducts.JDEUOMConversionLastUpdateID,
      Company_CatalogProducts.JDEVendorID,
      Company_CatalogProducts.JDEVendorItemID,
      Company_CatalogProducts.LineType,
      Company_CatalogProducts.MarketCostFlag,
      Company_CatalogProducts.ParentCategoryCode,
      ParentCategoryHierarchy.CategoryDisplayName AS ParentCategoryName,
      tblCategoryHierarchy.CategoryDisplayName AS CategoryName,
      Company_CatalogProducts.PriceLastUpdated,
      Company_CatalogProducts.PrimaryLastVendorNo,
      Company_CatalogProducts.ProductRecordType,
      Company_CatalogProducts.PurchaseUOM,
      Company_CatalogProducts.SerialNumberRequired,
      Company_CatalogProducts.StockingType,
      ISNULL(Company_CatalogProducts.CountingUOM,
      Company_CatalogProducts.UOM) AS UOM,
      Company_CatalogProducts.Image_filename,
      Company_CatalogProducts.Image_height,
      Company_CatalogProducts.Image_width,
      Company_CatalogProducts.DisplayProductFlag,
      ISNULL(Company_CatalogProducts.CountingUOM,
      Company_CatalogProducts.UOM) AS CountingUOM,
      ISNULL(Company_CatalogProducts.UOMtoCountingUOMConv, 1) AS UOMtoCountingUOMConv,
      1 AS PrimaryVendorFlag,
      ISNULL(tblOrgProducts.favoritesflag, 0) AS FavoritesFlag,
      tblOrgProducts.PurchasePrice,
      ISNULL(tblOrgProducts.UOMtoPurchaseUOMConv, 1) / ISNULL(Company_CatalogProducts.UOMtoCountingUOMConv, 1) AS UOMtoPurchaseUOMConv,
      ISNULL(tblOrgProducts.ReorderLevel, 0) / ISNULL(Company_CatalogProducts.UOMtoCountingUOMConv, 1) AS ReorderLevel,
      ISNULL(tblOrgProducts.typicalReorderquantity, 0) AS typicalReorderquantity,
      (ISNULL(tblOrgProducts.ReorderLevel, 1) / ISNULL(Company_CatalogProducts.UOMtoCountingUOMConv, 1) * ISNULL(tblOrgProducts.UOMtoPurchaseUOMConv, 1)) AS ReorderLevelUnits,
      CASE tblOrgProducts.MinimumOrderQuantity
            WHEN NULL THEN NULL
            ELSE tblOrgProducts.MinimumOrderQuantity / ISNULL(Company_CatalogProducts.UOMtoCountingUOMConv, 1)
      END AS MinimumOrderQuantity,
      CASE tblOrgProducts.MaximumOrderQuantity
            WHEN NULL THEN NULL
            ELSE tblOrgProducts.MaximumOrderQuantity / ISNULL(Company_CatalogProducts.UOMtoCountingUOMConv, 1)
      END AS MaximumOrderQuantity,
      ISNULL(QOH.QtyOnHand, 0) / ISNULL(Company_CatalogProducts.UOMtoCountingUOMConv, 1) AS QuantityOnHand,
      (ISNULL(QOH.QtyOnHand, 0) --* ISNULL(UOMtoCountingUOMConv, 1)
            / ISNULL(CASE tblOrgProducts.UOMtoPurchaseUOMConv
                              WHEN 0 THEN 1
                              ELSE tblOrgProducts.UOMtoPurchaseUOMConv
                        END, 1))
      AS PurchaseUnitsOnHand,
      CASE
            WHEN ISNULL(UOMtoCountingUOMConv, 1) = 1 THEN
                  ISNULL(tblOrgProducts.quantityOnOrder, 0) / ISNULL(Company_CatalogProducts.UOMtoCountingUOMConv, 1)
            ELSE
                  CAST( ISNULL(tblOrgProducts.quantityOnOrder, 0) / ISNULL(Company_CatalogProducts.UOMtoCountingUOMConv, 1) AS numeric(18, 2))
      END AS quantityOnOrder,
      tblOrgProducts.ActiveItemFlag,
      Company_CatalogProducts.LoadStatus,
      CASE tblOrgProducts.taxableitemflag
            WHEN 1 THEN 'Y'
            WHEN 0 THEN 'N'
      END AS TaxableItemFlag,
      CASE Company_CatalogProducts.ProductRecordType
            WHEN 'THEATERMRS' THEN NULL
            WHEN 'HOMRS' THEN NULL
            ELSE tblVendor.Vendor_ID
      END AS tblVendorID,
      CASE Company_CatalogProducts.ProductRecordType
            WHEN 'THEATERMRS' THEN NULL
            WHEN 'HOMRS' THEN NULL
            ELSE tblVendor.Name
      END AS VendorName,
      CASE Company_CatalogProducts.ProductRecordType
            WHEN 'THEATERMRS' THEN 1
            WHEN 'HOMRS' THEN 1
            ELSE tblVendor.JDEStatus
      END AS ValidVendorCount,
      NULL AS BudgetedMRSID,
      NULL AS BudgetedMRSDetailID,
      NULL AS CapExID,
      NULL AS ProjectOID,
      NULL AS ProjectDetailID
FROM         Company_CatalogProducts INNER JOIN
                      [Company_en-US_Catalog] ON Company_CatalogProducts.oid = [Company_en-US_Catalog].[#Catalog_Lang_Oid] INNER JOIN
                      tblOrgProducts ON Company_CatalogProducts.oid = tblOrgProducts.oid INNER JOIN
                      OrganizationObject ON tblOrgProducts.JDEOrgID = OrganizationObject.JDE_ID INNER JOIN
                      tblVendor ON Company_CatalogProducts.JDEVendorID = tblVendor.JDE_ID LEFT JOIN
                  (SELECT JDEOrgID, JDEItemID, SUM(QtyOnHand) AS QtyOnHand FROM InvCurrentCounts
                        WHERE InvCurrentCounts.BusinessDate =
                        (SELECT BusinessDate FROM InvTheater WHERE JDEOrgID = @JDEORGID)
                        GROUP BY JDEOrgID, JDEItemID
                  ) QOH ON convert(varchar, QOH.JDEOrgID) = OrganizationObject.JDE_ID AND
                  Company_CatalogProducts.JDEItemID = QOH.JDEItemID INNER JOIN
                  tblCategoryHierarchy ON tblCategoryHierarchy.CategoryName LIKE Company_CatalogProducts.ParentCategoryCode INNER JOIN
                  tblCategoryHierarchy ParentCategoryHierarchy ON tblCategoryHierarchy.ParentCategoryName LIKE ParentCategoryHierarchy.CategoryName
WHERE     (OrganizationObject.JDE_ID = @JDEORGID) AND (Company_CatalogProducts.ParentCategoryCode = @CATEGORYCODE OR
                      (tblOrgProducts.FavoritesFlag = 1 AND Company_CatalogProducts.ProductRecordType  IN
                  (SELECT ProductRecordType FROM tblCategoryHierarchy
                        WHERE CategoryName = @CATEGORYCODE AND ParentCategoryName IS NULL)))
      AND (Company_CatalogProducts.DisplayProductFlag = 1)
      AND (
            (Company_CatalogProducts.JDEVendorID <> '' AND tblOrgProducts.PrimaryVendorFlag >= 0) OR
                            (Company_CatalogProducts.ProductRecordType IN ('THEATERMRS', 'HOMRS'))
      )
ORDER BY             
      ParentCategoryHierarchy.CategoryDisplayName,
      tblCategoryHierarchy.CategoryDisplayName,
      CASE Company_CatalogProducts.ProductRecordType
            WHEN 'THEATERMRS' THEN NULL
            WHEN 'HOMRS' THEN NULL
            ELSE tblVendor.Name
      END,      
      [Company_en-US_Catalog].Name
END

GO

If you run it two times in a row, is the second run faster ?

What about Executing the SP from Query Analyzer?? have you tried that??

Exec dbo.ProductsGet_CS   "WIth the variables"

Is this fast or Slow?

If this is fast I can tell you that your SP is good.

My next question is how are you Executing the SP?? is it through a ASP page or some kind of an online page??

I have executed it by right-clicking and click open and fill in the variables. I have executed it by doing EXEC dbo.ProductsGet_CS with the variables. I have executed it with EXEC dbo.ProductsGet_CS with the variables WITH RECOMPILE. I have run the SP mulitple times and it is always slow.

Now you're understanding the 500 points available?

What's the difference between running the query in QA or running the query in the stored procedure in QA? That's the question.

Can you try to se the Execution Plan by pressing ctl + L in query analyzer for both Query and SP. See if you can make out any difference?

Also can you hardcode the parameters passing and see if it makes a difference.

Can you remove the order By and se if it helps?

Please let us know the results.

I am thinking it could be the way the Execution plan was prepared for Query and SP.

Don't know how you're running the query in QA.
But ff you're running the query in QA with out the (SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED) and both criteria JDEORGID and CATEGORYCODE straight in the where clause, of course this would run much faster.  For a accurate comparison,
you might want to run both in QA as follow:

Exec ProductsGet_CS (with variables to pass into the SP)

---AND

Declare @JDEORGID as nvarchar(255),@CATEGORYCODE as nvarchar(255)

Set @JDEORGID = 'yourdata'
Set @CATEGORYCODE  = 'yourdata'

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN

SELECT DISTINCT
     [Company_en-US_Catalog].Name AS Name,
     'Company' AS BaseCatalogName,
     Company_CatalogProducts.oid,
     Company_CatalogProducts.DefinitionName,
     Company_CatalogProducts.IsSearchable,
     Company_CatalogProducts.cy_list_price,
     Company_CatalogProducts.UseCategoryPricing,
     Company_CatalogProducts.[TimeStamp],
     Company_CatalogProducts.OriginalPrice,
     Company_CatalogProducts.i_ClassType,
     Company_CatalogProducts.ParentOID,
     Company_CatalogProducts.ProductID + '(Company)' AS ProductID,
     Company_CatalogProducts.VariantID,
     Company_CatalogProducts.ProductVariantCode,
     Company_CatalogProducts.LastModified,
     Company_CatalogProducts.CatalogName,
     Company_CatalogProducts.PrimaryParentCategory,
     Company_CatalogProducts.[#CategoryDisplayName],
     Company_CatalogProducts.ProductCode,
     Company_CatalogProducts.ApprovalCode,
     Company_CatalogProducts.CycleCountCategory,
     Company_CatalogProducts.EffectiveDate,
     Company_CatalogProducts.ExpirationDate,
     Company_CatalogProducts.GLCategory,
     Company_CatalogProducts.InventoryItemFlag,
     Company_CatalogProducts.ItemType,
     Company_CatalogProducts.JDEGenericItemID,
     Company_CatalogProducts.JdeItemId,
     Company_CatalogProducts.JDEItemMasterLastUpdated,
     Company_CatalogProducts.JDEItemMasterLastUpdateID,
     Company_CatalogProducts.JDEItemXRefLastUpdated,
     Company_CatalogProducts.JDEItemXRefLastUpdateID,
     Company_CatalogProducts.JDESupplierCatalogLastUpdated,
     Company_CatalogProducts.JDESupplierCatalogLastUpdateID,
     Company_CatalogProducts.JDEUOMConversionLastUpdated,
     Company_CatalogProducts.JDEUOMConversionLastUpdateID,
     Company_CatalogProducts.JDEVendorID,
     Company_CatalogProducts.JDEVendorItemID,
     Company_CatalogProducts.LineType,
     Company_CatalogProducts.MarketCostFlag,
     Company_CatalogProducts.ParentCategoryCode,
     ParentCategoryHierarchy.CategoryDisplayName AS ParentCategoryName,
     tblCategoryHierarchy.CategoryDisplayName AS CategoryName,
     Company_CatalogProducts.PriceLastUpdated,
     Company_CatalogProducts.PrimaryLastVendorNo,
     Company_CatalogProducts.ProductRecordType,
     Company_CatalogProducts.PurchaseUOM,
     Company_CatalogProducts.SerialNumberRequired,
     Company_CatalogProducts.StockingType,
     ISNULL(Company_CatalogProducts.CountingUOM,
     Company_CatalogProducts.UOM) AS UOM,
     Company_CatalogProducts.Image_filename,
     Company_CatalogProducts.Image_height,
     Company_CatalogProducts.Image_width,
     Company_CatalogProducts.DisplayProductFlag,
     ISNULL(Company_CatalogProducts.CountingUOM,
     Company_CatalogProducts.UOM) AS CountingUOM,
     ISNULL(Company_CatalogProducts.UOMtoCountingUOMConv, 1) AS UOMtoCountingUOMConv,
     1 AS PrimaryVendorFlag,
     ISNULL(tblOrgProducts.favoritesflag, 0) AS FavoritesFlag,
     tblOrgProducts.PurchasePrice,
     ISNULL(tblOrgProducts.UOMtoPurchaseUOMConv, 1) / ISNULL(Company_CatalogProducts.UOMtoCountingUOMConv, 1) AS UOMtoPurchaseUOMConv,
     ISNULL(tblOrgProducts.ReorderLevel, 0) / ISNULL(Company_CatalogProducts.UOMtoCountingUOMConv, 1) AS ReorderLevel,
     ISNULL(tblOrgProducts.typicalReorderquantity, 0) AS typicalReorderquantity,
     (ISNULL(tblOrgProducts.ReorderLevel, 1) / ISNULL(Company_CatalogProducts.UOMtoCountingUOMConv, 1) * ISNULL(tblOrgProducts.UOMtoPurchaseUOMConv, 1)) AS ReorderLevelUnits,
     CASE tblOrgProducts.MinimumOrderQuantity
          WHEN NULL THEN NULL
          ELSE tblOrgProducts.MinimumOrderQuantity / ISNULL(Company_CatalogProducts.UOMtoCountingUOMConv, 1)
     END AS MinimumOrderQuantity,
     CASE tblOrgProducts.MaximumOrderQuantity
          WHEN NULL THEN NULL
          ELSE tblOrgProducts.MaximumOrderQuantity / ISNULL(Company_CatalogProducts.UOMtoCountingUOMConv, 1)
     END AS MaximumOrderQuantity,
     ISNULL(QOH.QtyOnHand, 0) / ISNULL(Company_CatalogProducts.UOMtoCountingUOMConv, 1) AS QuantityOnHand,
     (ISNULL(QOH.QtyOnHand, 0) --* ISNULL(UOMtoCountingUOMConv, 1)
          / ISNULL(CASE tblOrgProducts.UOMtoPurchaseUOMConv
                         WHEN 0 THEN 1
                         ELSE tblOrgProducts.UOMtoPurchaseUOMConv
                    END, 1))
     AS PurchaseUnitsOnHand,
     CASE
          WHEN ISNULL(UOMtoCountingUOMConv, 1) = 1 THEN
               ISNULL(tblOrgProducts.quantityOnOrder, 0) / ISNULL(Company_CatalogProducts.UOMtoCountingUOMConv, 1)
          ELSE
               CAST( ISNULL(tblOrgProducts.quantityOnOrder, 0) / ISNULL(Company_CatalogProducts.UOMtoCountingUOMConv, 1) AS numeric(18, 2))
     END AS quantityOnOrder,
     tblOrgProducts.ActiveItemFlag,
     Company_CatalogProducts.LoadStatus,
     CASE tblOrgProducts.taxableitemflag
          WHEN 1 THEN 'Y'
          WHEN 0 THEN 'N'
     END AS TaxableItemFlag,
     CASE Company_CatalogProducts.ProductRecordType
          WHEN 'THEATERMRS' THEN NULL
          WHEN 'HOMRS' THEN NULL
          ELSE tblVendor.Vendor_ID
     END AS tblVendorID,
     CASE Company_CatalogProducts.ProductRecordType
          WHEN 'THEATERMRS' THEN NULL
          WHEN 'HOMRS' THEN NULL
          ELSE tblVendor.Name
     END AS VendorName,
     CASE Company_CatalogProducts.ProductRecordType
          WHEN 'THEATERMRS' THEN 1
          WHEN 'HOMRS' THEN 1
          ELSE tblVendor.JDEStatus
     END AS ValidVendorCount,
     NULL AS BudgetedMRSID,
     NULL AS BudgetedMRSDetailID,
     NULL AS CapExID,
     NULL AS ProjectOID,
     NULL AS ProjectDetailID
FROM         Company_CatalogProducts INNER JOIN
                      [Company_en-US_Catalog] ON Company_CatalogProducts.oid = [Company_en-US_Catalog].[#Catalog_Lang_Oid] INNER JOIN
                      tblOrgProducts ON Company_CatalogProducts.oid = tblOrgProducts.oid INNER JOIN
                      OrganizationObject ON tblOrgProducts.JDEOrgID = OrganizationObject.JDE_ID INNER JOIN
                      tblVendor ON Company_CatalogProducts.JDEVendorID = tblVendor.JDE_ID LEFT JOIN
                (SELECT JDEOrgID, JDEItemID, SUM(QtyOnHand) AS QtyOnHand FROM InvCurrentCounts
                    WHERE InvCurrentCounts.BusinessDate =
                    (SELECT BusinessDate FROM InvTheater WHERE JDEOrgID = @JDEORGID)
                    GROUP BY JDEOrgID, JDEItemID
               ) QOH ON convert(varchar, QOH.JDEOrgID) = OrganizationObject.JDE_ID AND
               Company_CatalogProducts.JDEItemID = QOH.JDEItemID INNER JOIN
               tblCategoryHierarchy ON tblCategoryHierarchy.CategoryName LIKE Company_CatalogProducts.ParentCategoryCode INNER JOIN
               tblCategoryHierarchy ParentCategoryHierarchy ON tblCategoryHierarchy.ParentCategoryName LIKE ParentCategoryHierarchy.CategoryName
WHERE     (OrganizationObject.JDE_ID = @JDEORGID) AND (Company_CatalogProducts.ParentCategoryCode = @CATEGORYCODE OR
                      (tblOrgProducts.FavoritesFlag = 1 AND Company_CatalogProducts.ProductRecordType  IN
               (SELECT ProductRecordType FROM tblCategoryHierarchy
                    WHERE CategoryName = @CATEGORYCODE AND ParentCategoryName IS NULL)))
     AND (Company_CatalogProducts.DisplayProductFlag = 1)
     AND (
          (Company_CatalogProducts.JDEVendorID <> '' AND tblOrgProducts.PrimaryVendorFlag >= 0) OR
                           (Company_CatalogProducts.ProductRecordType IN ('THEATERMRS', 'HOMRS'))
     )
ORDER BY          
     ParentCategoryHierarchy.CategoryDisplayName,
     tblCategoryHierarchy.CategoryDisplayName,
     CASE Company_CatalogProducts.ProductRecordType
          WHEN 'THEATERMRS' THEN NULL
          WHEN 'HOMRS' THEN NULL
          ELSE tblVendor.Name
     END,    
     [Company_en-US_Catalog].Name
END

GO

Some indexing on your tables should help if they are not Indexed yet.

CREATE TRIGGER NameMe
ON YourTable
FOR insert, UPDATE
AS
BEGIN
--inserted and deleted tables will have the changes for updates.

--You can set your timestamps here
END
--

Execution plan is identical for both. Hard coding parameters makes no difference.

Indexing tables won't help. (query runs fast/sp runs slow)

What is the trigger about? (misposted?)

One thing to look at is the ANSI_NULL and Quoted_identifier values.  Please run:

  select objectproperty(object_id('ProductsGet_CS'), 'ExecIsAnsiNullsOn')
  select objectproperty(object_id('ProductsGet_CS'), 'ExecIsQuotedIdentOn')

If these are not on, try setting them in your Create Procedure code:

Set ANSI_NULLS ON
Set QUOTED_IDENTIFIER ON

GO

CREATE       PROCEDURE dbo.ProductsGet_CS
     @JDEORGID as nvarchar(255),
     @CATEGORYCODE as nvarchar(255)
WITH RECOMPILE
AS
....

Then test - do things improve?

(Reference:  The below Google Groups thread:

http://groups.google.com/groups?q=Microsoft+SQL+Server+stored+procedure+slow&hl=en&lr=&safe=off&selm=3dcfb867%240%2485006%24e4fe514c%40dreader7.news.xs4all.nl&rnum=8

)

both are set to ON.

Some ideas. Try using

SET STATISTICS IO ON

Then run both ways, and see if any table has an increased IO (the output appears in the "messages" tab in QA. If all looks the same, try putting

SELECT GETDATE()

before and after the main select in the SP to *confirm* that the time is actually being lost running the select. If the difference between those times is about how long it takes in QA, then the time is being lost elsewhere (parse and compile, maybe).

BTW, you should specify the schema (usually dbo) before each table and stored prodecure.

e.g

exec dbo.ProductsGet_CS

 and

FROM         dbo.Company_CatalogProducts INNER JOIN

I see that many people have tried to help you out in vairous ways, so I thought I woul add my 2 cents worth.....

y not break you query up into multiple queries. i.e. perform 2-3 smaller queries and then join them all with 1 big SELECT, joining them together....? You could even try removing the the sections that are doing the divisions into a seperate query, then join it on... I honestly feel like you are trying to do too much in one query.....

indexing on certain columns should also help your cause.

Ok, arbert, it is parameter sniffing. I have literally 100s of SPs that hold the same form. Do I need to change them all?

If you can verify that each proc experiences the same behaviour and there is a lot of overhead when the proc runs, I would try and fix it.  Otherwise, I wouldn't worry about it (SQL2005 addresses the issue a little bit with better plan caching).