Solved

SQL Stored Proc - Cross Apply Question

Posted on 2011-09-07
10
382 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
[X]
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
  • 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

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…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

710 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