Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

SQL Stored Proc - Cross Apply Question

Hello all,

I have a stored proc that when I run the proc with the following syntax the cross apply seems to hang the proc and it just runs and runs.  If I run this syntax in just a query window it returns no rows immediately.   Any idea why the proc would hang on the cross apply?  Not sure if it is permissions related but the proc has the the same granted user I am running the query under.

ALTER PROCEDURE dbo.HL7CCDMemberData
@cid              INT = NULL,
@fromDate         DATE,
@toDate                 DATE,
@sourceSystem     INT,
@sessionid        NVARCHAR(51) = NULL
AS
SET NOCOUNT ON


SELECT /*'Medications' AS section_name,*/
  'U' AS bucket,
  log.id,
  log.med_id,
  0 as inactive, -- Settin as 0. Once the medication is deleted, it cannot be reactivated. Hence all rows in log should be 0
  log.medical_date,
  log.dosage,
  log.ndc_id,
  log.drug_name,
  log.generic_name
FROM dbo.U_MEMBER_MEDICATION_LOG log WITH(NOLOCK)
INNER JOIN dbo.U_MEMBER_MEDICATION med WITH(NOLOCK) ON log.med_id = med.id
      AND CAST(med.medical_date AS DATE) NOT BETWEEN @fromDate AND @toDate
CROSS APPLY (SELECT MAX(id) as log_id
  FROM dbo.U_MEMBER_MEDICATION_LOG WITH(NOLOCK)
  WHERE med_id = log.med_id
  AND CAST(medical_date AS DATE) BETWEEN @fromDate AND @toDate) logFilter
WHERE CID = @cid
AND CAST(log.medical_date AS DATE) BETWEEN @fromDate AND @toDate
AND log.id = logFilter.log_id
0
sbornstein2
Asked:
sbornstein2
  • 5
  • 5
1 Solution
 
sbornstein2Author Commented:
Looks like the log table has 349 Million records so I need to prob reindex and defrag the table.  Need to find out the best way.
0
 
JestersGrindCommented:
To rebuild all indexes on the log table run this.

ALTER INDEX ALL ON LogTable
REBUILD
GO

Greg

0
 
JestersGrindCommented:
If this table is being used by anything else (i.e. in production)  you can do this.  Otherwise the table will be unavailable during the rebuild.

ALTER INDEX ALL ON LogTable
REBUILD WITH(ONLINE = ON)
GO

Greg
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sbornstein2Author Commented:
why would a query run fast in a new query but the stored proc with in it takes forever
0
 
JestersGrindCommented:
They must be using different execution plans.  You can test this if you like by turning on show execution plan and comparing the two after you execute each one.  Whenever I've run into this issue, the easiest fix is to recompile the stored procedure which forces SQL to generate a new execution plan.

Greg

0
 
sbornstein2Author Commented:
Hey Jest,

That actually worked the first time I ran a sp_recompile but the past few times I run:

exec sp_recompile [procname] it says marked for recompilation but it still hangs.  The first time I did this it did fix the proc returning.  Any idea?
0
 
JestersGrindCommented:
That procedure, sp_recompile, just clears the procedure cache and forces SQL to generate a new execution plan.  I'm not sure exactly why it would hang.  Maybe the procedure is currently executing, so the cache can't be cleared right away?  If it's generating a bad execution plan, you might need to update statistics so that SQL has better information to choose the right execution plan.  To do that just run:

UPDATE STATISTICS YourTableName

Greg

0
 
sbornstein2Author Commented:
It was parameter sniffing.  Yup sniff sniff.  Now I need to understand better why that happens overall.  I had to create local variables to fix this issue.
0
 
JestersGrindCommented:
Parameter sniffing is generally a good thing.  SQL creates an execution plan based on the first parameters that are passed to it.  It assumes that those parameters are typical for the stored procedure.  If the range of values vary greatly, the execution plan may not be optimal for all values.  Creating local variables is a good way to disable it.  Here is a very good blog post that talks about it.

http://blogs.msdn.com/b/queryoptteam/archive/2006/03/31/565991.aspx

Greg

0
 
sbornstein2Author Commented:
Your other posts were also important as well though in fixing this.  Thanks again.  Interesting stuff.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now