Recursive query with different where clauses - very slow

Question

Recursive query with different where clauses - very slow

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Laurie Thompson SSC Enthusiast Points: 165 More actions · Answer 1

Hi Matt,

Thanks for the reply. When you say: "...On the temp table - you would create the index immediately after the temp table was created (after the SELECT...INTO)", what is the syntax for this?

INTO #TmpTable

---TodayID,PatientID, Parm ???

FROM [Patient Data] pd

Kind regards

Laurie Thompson SSC Enthusiast Points: 165 More actions · Answer 2

Hi Matt,

Thanks very much for your input. I indexed as you said and it does run quicker. However, when I ran the "Index Tuning Wizard" it recommened:

[Open%] ,[High%], [Low%], [Close%], TodayID, PreviousDayID, PatientID

This seemed to run the query even quicker?

Also, you suggested that the temp table could be indexed to speed up the query by indexing on the columns: TodayID, PatientID, Parm.

How do you do that with the INTO Clause?

Kind regards

Laurence

With a fill factor around 70%.

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 3

The tuning wizard is recommending a full blown "Covering Index" and it's a good thing to have for a heavy duty set of SELECT's like this. It will, however slow down INSERTS, UPDATES, and DELETES.

As for how to create an index in the presence of an INTO... you have to follow the table creation/population with a CREATE INDEX statement. See Books Online for the proper syntax...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Laurie Thompson (3/6/2008)
Hi Matt,
Thanks very much for your input. I indexed as you said and it does run quicker. However, when I ran the "Index Tuning Wizard" it recommened:
[Open%] ,[High%], [Low%], [Close%], TodayID, PreviousDayID, PatientID
This seemed to run the query even quicker?
Also, you suggested that the temp table could be indexed to speed up the query by indexing on the columns: TodayID, PatientID, Parm.
How do you do that with the INTO Clause?
Kind regards
Laurence
With a fill factor around 70%.

Sorry - our little timezone thing made this one hard to find for a while.

You're wanting this document:

http://msdn2.microsoft.com/en-us/library/ms188783.aspx

Your syntax will look something like:

--The top of Jeff's script from previously

SELECT CAST(pd.TodayID AS INT) AS TodayID,pd.PreviousDayID,pd.PatientID,1 AS Parm

INTO #TmpTable

FROM [Patient Data] pd

WHERE pd.[Open%] BETWEEN -1.61 AND -0.61

AND pd.[High%] BETWEEN -0.28 AND 0.72

AND pd.[Low%] BETWEEN -1.61 AND -0.61

AND pd.[Close%] BETWEEN -1.39 AND -0.39

--new index goes after the SELECT...INTO

Create index MyIndexName on #temp(TodayID, PatientID, Parm) WITH (FILLFACTOR=70)

--rest of the script goes here

Also - like Jeff mentioned before - the index tuning wizard doesn't always give you all of the facts. For example - (at least from the scripts used here) - there's no reason to have the TodayID in the "main" columns - it should actually be in the "included" columns.

Of course - it's also basing its assessment on ALL of the activity going on, and not just this script, so there may be other activity playing into its choice.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Laurie Thompson SSC Enthusiast Points: 165 More actions · Answer 5

Hi Jeff & Matt,

Thanks very much for all your advise. I wonder if I could pick your brains once more? How you would add to your query to output 3 days forward for all selected data? For example, this is the first pattern found:

TodayId, PreviosuDayID, Patient Date

123, 121, 23 05/19/07

121, 111, 23 05/18/07

111, 109, 23 05/17/07

109, 89, 23 05/16/07

89, 77, 23 05/15/07

Now, I'd like to display ( or input into a table ), the following 3 days of open% High% Low% Close% blood pressures e.g.

05/20/07 open% High% Low% Close%

05/21/07 open% High% Low% Close%

05/22/07 open% High% Low% Close%

Kind regards

Laurnce

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

hmm - the twist that changes the problem around:)

I'd modify Jeff's script a little, to bring over the date as well.

drop table #tmptable

SELECT CAST(pd.TodayID AS INT) AS TodayID,pd.PreviousDayID,pd.PatientID,1 AS Parm,[date] as Dte

INTO #TmpTable

FROM [Patient Data] pd

WHERE pd.[Open%] BETWEEN -1.61 AND -0.61

AND pd.[High%] BETWEEN -0.28 AND 0.72

AND pd.[Low%] BETWEEN -1.61 AND -0.61

AND pd.[Close%] BETWEEN -1.39 AND 0.39

INSERT INTO #TmpTable

(TodayID,PreviousDayID,PatientID,Parm,dte)

SELECT pd.TodayID,pd.PreviousDayID,pd.PatientID,2 AS Parm,[date]

FROM [Patient Data] pd,

#TmpTable t

WHERE t.TodayID = pd.PreviousDayID

AND t.PatientID = pd.PatientID

AND t.Parm = 1

AND pd.[Open%] BETWEEN 1.07 AND 2.07

AND pd.[High%] BETWEEN 1.29 AND 2.29

AND pd.[Low%] BETWEEN -0.5 AND 0.5

AND pd.[Close%] BETWEEN 1.29 AND 2.29

INSERT INTO #TmpTable

(TodayID,PreviousDayID,PatientID,Parm,dte)

SELECT pd.TodayID,pd.PreviousDayID,pd.PatientID,3 AS Parm,[date]

FROM [Patient Data] pd,

#TmpTable t

WHERE t.TodayID = pd.PreviousDayID

AND t.PatientID = pd.PatientID

AND t.Parm = 2

AND pd.[Open%] BETWEEN -0.5 AND 0.5

AND pd.[High%] BETWEEN -0.5 AND 0.5

AND pd.[Low%] BETWEEN -1.16 AND -0.16

AND pd.[Close%] BETWEEN -0.5 AND 0.5

INSERT INTO #TmpTable

(TodayID,PreviousDayID,PatientID,Parm,Dte)

SELECT pd.TodayID,pd.PreviousDayID,pd.PatientID,4 AS Parm,[date]

FROM [Patient Data] pd,

#TmpTable t

WHERE t.TodayID = pd.PreviousDayID

AND t.PatientID = pd.PatientID

AND t.Parm = 3

AND pd.[Open%] BETWEEN -0.5 AND 0.5

AND pd.[High%] BETWEEN -0.06 AND 0.94

AND pd.[Low%] BETWEEN -0.94 AND 0.06

AND pd.[Close%] BETWEEN -0.5 AND 0.5

INSERT INTO #TmpTable

(TodayID,PreviousDayID,PatientID,Parm,dte)

SELECT pd.TodayID,pd.PreviousDayID,pd.PatientID,5 AS Parm,[date]

FROM [Patient Data] pd,

#TmpTable t

WHERE t.TodayID = pd.PreviousDayID

AND t.PatientID = pd.PatientID

AND t.Parm = 4

AND pd.[Open%] BETWEEN -0.94 AND 0.06

AND pd.[High%] BETWEEN 0.38 AND 1.38

AND pd.[Low%] BETWEEN -0.94 AND 0.06

AND pd.[Close%] BETWEEN -0.5 AND 0.5

--NEW STUFF - added to get the extra values

select *

from [patient data] pt

inner join (

selectpatientID,

dte

from #tmptable

where parm=5

) tmp on pt.patientid=tmp.patientID

and pt.[date]>dateadd(day,-5,tmp.dte)

and pt.[date]<=dateadd(day,3,tmp.dte

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?