Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Stored Proc - Cross Apply Question

Posted on 2011-09-07
10
378 Views
Last Modified: 2012-05-12
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
Comment
Question by:sbornstein2
  • 5
  • 5
10 Comments
 

Author Comment

by:sbornstein2
ID: 36498086
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
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36498241
To rebuild all indexes on the log table run this.

ALTER INDEX ALL ON LogTable
REBUILD
GO

Greg

0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36498270
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:sbornstein2
ID: 36500661
why would a query run fast in a new query but the stored proc with in it takes forever
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36502186
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
 

Author Comment

by:sbornstein2
ID: 36503232
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
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36503301
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
 

Author Comment

by:sbornstein2
ID: 36503436
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
 
LVL 21

Accepted Solution

by:
JestersGrind earned 250 total points
ID: 36503536
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
 

Author Closing Comment

by:sbornstein2
ID: 36503566
Your other posts were also important as well though in fixing this.  Thanks again.  Interesting stuff.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

837 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