Solved

Stored Procedure Running Slow

Posted on 2004-10-14
19
5,344 Views
Last Modified: 2008-01-09
I have a simple stored procedure that accepts two arguments. The procedure has been running fine and then all of a sudden, it is now taking over 30 seconds to return (in Query Analyzer). I take the query out of the SP and run it through Query Analyzer and it runs in less than 3 seconds.

I have tried recompiling but with no change.

Thoughts?
0
Comment
Question by:r_i_x
  • 5
  • 3
  • 2
  • +6
19 Comments
 
LVL 32

Expert Comment

by:bhess1
Comment Utility
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.
0
 
LVL 9

Expert Comment

by:apirnia
Comment Utility
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.
0
 
LVL 8

Expert Comment

by:MartinCMS
Comment Utility
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
0
 
LVL 1

Author Comment

by:r_i_x
Comment Utility
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
0
 
LVL 8

Expert Comment

by:SNilsson
Comment Utility

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

Expert Comment

by:apirnia
Comment Utility
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??
0
 
LVL 1

Author Comment

by:r_i_x
Comment Utility
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.
0
 
LVL 2

Expert Comment

by:praveen_ms
Comment Utility
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.
0
 
LVL 8

Expert Comment

by:MartinCMS
Comment Utility
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







0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 9

Expert Comment

by:apirnia
Comment Utility
Some indexing on your tables should help if they are not Indexed yet.
0
 
LVL 2

Expert Comment

by:praveen_ms
Comment Utility

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
--
0
 
LVL 1

Author Comment

by:r_i_x
Comment Utility
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?)
0
 
LVL 32

Expert Comment

by:bhess1
Comment Utility
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

)
0
 
LVL 1

Author Comment

by:r_i_x
Comment Utility
both are set to ON.
0
 
LVL 3

Expert Comment

by:AlphaMan74
Comment Utility
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

0
 
LVL 34

Accepted Solution

by:
arbert earned 500 total points
Comment Utility
I;m still not totally positive how you tested in QA, but this really sounds  a lot like parameter sniffing to me.  It's a large topic--take a look at this previous post:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21048065.html
0
 
LVL 1

Expert Comment

by:AntonG
Comment Utility
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.
0
 
LVL 1

Author Comment

by:r_i_x
Comment Utility
Ok, arbert, it is parameter sniffing. I have literally 100s of SPs that hold the same form. Do I need to change them all?
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
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).
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now