Solved

SQL Stored Proc - Cross Apply Question

Posted on 2011-09-07
10
374 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

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 …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 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

23 Experts available now in Live!

Get 1:1 Help Now