Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Recursive query with different where clauses - very slow Expand / Collapse
Author
Message
Posted Friday, February 29, 2008 9:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 2, 2009 2:50 AM
Points: 13, Visits: 51
I was wondering which is the fastest method of creating a dynamic SQL statement which references the same table recursively but with differing where clauses e.g.

I wish to query a patient table which attempts to find as many previous patterns for a given blood pressure reading:

An exmaple pattern is:

Today: [Open%] BETWEEN -1.61 AND -0.61 [High%] BETWEEN -0.28 AND 0.72 [Low%] BETWEEN -1.61 AND -0.61 [Close%] BETWEEN -1.39 AND -0.39
Previous Day: [Open%] BETWEEN 1.07 AND 2.07 [High%] BETWEEN 1.29 AND 2.29 [Low%] BETWEEN -0.5 AND 0.5 [Close%] BETWEEN 1.29 AND 2.29)
Previous Day: [Open%] BETWEEN -0.5 AND 0.5 AND [High%] BETWEEN -0.5 AND 0.5 AND [Low%] BETWEEN -1.16 AND -0.16 AND [Close%] BETWEEN -0.5 AND 0.5
Previous Day: etc.
Previous Day: etc.
Previous Day: etc.
Previous Day: etc.

Unfortunately, both T-SQL statements I have used seem very slow i.e.

Nested Query:

SELECT [Patient Data].*
FROM [Patient Data] WHERE [Patient Data].PreviousDayID In
( SELECT TodayID FROM [Patient Data] WHERE PreviousDayID In
( SELECT TodayID FROM [Patient Data] WHERE PreviousDayID In
( SELECT TodayID FROM [Patient Data] WHERE PreviousDayID In
( SELECT TodayID FROM [Patient Data] WHERE [Open%] BETWEEN -1.61 AND -0.61 AND [High%] BETWEEN -0.28 AND 0.72 AND [Low%] BETWEEN -1.61 AND -0.61 AND [Close%] BETWEEN -1.39 AND -0.39 ) AND
[Open%] BETWEEN 1.07 AND 2.07 AND [High%] BETWEEN 1.29 AND 2.29 AND [Low%] BETWEEN -0.5 AND 0.5 AND [Close%] BETWEEN 1.29 AND 2.29) AND
[Open%] BETWEEN -0.5 AND 0.5 AND [High%] BETWEEN -0.5 AND 0.5 AND [Low%] BETWEEN -1.16 AND -0.16 AND [Close%] BETWEEN -0.5 AND 0.5) AND
[Open%] BETWEEN -0.5 AND 0.5 AND [High%] BETWEEN -0.06 AND 0.94 AND [Low%] BETWEEN -0.94 AND 0.06 AND [Close%] BETWEEN -0.5 AND 0.5) AND
[Open%] BETWEEN -0.94 AND 0.06 AND [High%] BETWEEN 0.38 AND 1.38 AND [Low%] BETWEEN -0.94 AND 0.06 AND [Close%] BETWEEN -0.5 AND 0.5 Order By [Date] DESC

and

Inner Join Query:

SELECT [Patient Data].* FROM [Patient Data] INNER JOIN [Patient Data] AS [Patient Data_1] ON [Patient Data].PreMarketID = [Patient Data_1].TodayID INNER JOIN [Patient Data] AS [Patient Data_2] ON [Patient Data_1].PreMarketID = [Patient Data_2].TodayID INNER JOIN [Patient Data] AS [Patient Data_3] ON [Patient Data_2].PreviousDayID = [Patient Data_3].TodayID INNER JOIN [Patient Data] AS [Patient Data_4] ON [Patient Data_3].PreviousDayID = [Patient Data_4].TodayID WHERE [Patient Data].[Close%]>=-0.5 AND [Patient Data].[Close%] =-1.61 AND [Patient Data_4].[Low%]<=-0.61


I wish to know the most efficient and fastest way of achieving this query e.g. SELECT Statements, indexing, stored procedures, etc.etc.

Kind regards

Laurence
Post #462545
Posted Friday, February 29, 2008 2:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:34 PM
Points: 35,534, Visits: 32,117
I don't think this is a terribly difficult problem... but without data or knwing what the table it's stored in looks like, its just not possible. Click on the URL in the last line of my signature below, please...

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #462675
Posted Monday, March 3, 2008 4:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 2, 2009 2:50 AM
Points: 13, Visits: 51
Hello Jeff,

Thank you for your reply and thanks for explaining best practises. Sorry I havent got back to your earlier, but I have been away from work. Please find the following data based upon your "Correct Way to Post Data".

As I stated earlier my problem is not getting the query to work, but the speed of the query. I have developed a vb6 front end and the intention is to allow to end-user to enter the number of days they wish to scan for a matching pattern and then to loop through all current patients to see if each patient has a matching pattern in the entire table ( currently over 4 millions records ). However, just finding a matching pattern for single patient can take 15 to 20 seconds. I also intended that once one or more patterns have been found that the/a query would then look at the following day results and see if this could forecast the patients blood pressure for the next day.

I hope this isn't too confusing and that you will let me know if you require any further information.

Kind regards

Laurence


--===== If the test table already exists, drop it
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Patient Data]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Patient Data]
GO

--===== Create the test table with
CREATE TABLE [dbo].[Patient Data] (
[TodayID] [int] IDENTITY (1, 1) NOT NULL ,
[PreviousDayID] [int] NULL ,
[PatientID] [int] NULL ,
[Date] [datetime] NULL ,
[Open%] [float] NULL ,
[High%] [float] NULL ,
[Low%] [float] NULL ,
[Close%] [float] NULL
) ON [PRIMARY]
GO


--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT [dbo].[Patient Data] ON

--===== Insert the test data into the test table
INSERT INTO [dbo].[Patient Data]
([TodayID], [PreviousDayID], [PatientID], [Date], [Open%], [High%], [Low%], [Close%])
SELECT '4629170', '4624881', '12', '29/02/2008', -1.12, 1.12, -1.12, 1.12 UNION ALL
SELECT '4624881', '4620598', '12', '28/02/2008', -3.84, -3.84, -3.84, -3.84 UNION ALL
SELECT '4620598', '4615809', '12', '27/02/2008', -1.74, 0.6, -2.13, 0.6 UNION ALL
SELECT '4615809', '4611052', '12', '26/02/2008', -0.44, 1.77, -0.44, 1.77 UNION ALL
SELECT '4611052', '4606819', '12', '25/02/2008', 0, 0.44, 0, 0.44 UNION ALL
SELECT '4606819', '4602038', '12', '22/02/2008', -1.79, -1.79, -1.79, -1.79 UNION ALL
SELECT '4602038', '4597285', '12', '21/02/2008', -0.39, -0.39, -0.39, -0.39 UNION ALL
SELECT '4597285', '4592909', '12', '20/02/2008', 2.09, 2.09, -2.13, -2.13 UNION ALL
SELECT '4592909', '4588286', '12', '19/02/2008', 3.3, 3.3, 3.3, 3.3 UNION ALL
SELECT '4588286', '4583621', '12', '18/02/2008', -0.02, -0.02, -1.11, -1.11 UNION ALL
SELECT '4583621', '4578897', '12', '15/02/2008', -2.56, -2.56, -2.56, -2.56 UNION ALL
SELECT '4578897', '4574160', '12', '14/02/2008', 6.26, 6.26, 2.65, 2.65 UNION ALL
SELECT '4574160', '4569529', '12', '13/02/2008', -4.17, -4.17, -4.17, -4.17 UNION ALL
SELECT '4569529', '4564912', '12', '12/02/2008', -3.93, 0.25, -3.93, 0.25 UNION ALL
SELECT '4564912', '4560435', '12', '11/02/2008', 2.49, 6.4, 2.49, 6.4 UNION ALL
SELECT '4560435', '4555994', '12', '08/02/2008', -1.45, -1.45, -6.64, -6.64 UNION ALL
SELECT '4555994', '4551511', '12', '07/02/2008', -5.88, -5.49, -5.88, -5.49 UNION ALL
SELECT '4551511', '4547020', '12', '06/02/2008', 0, 2, -0.76, 2 UNION ALL
SELECT '4626564', '4622254', '14', '29/02/2008', 0.67, 0.67, 0.67, 0.67 UNION ALL
SELECT '4622254', '4617575', '14', '28/02/2008', 0.99, 1.97, 0, 1.48 UNION ALL
SELECT '4617575', '4612732', '14', '27/02/2008', 0.5, 1.93, 0.5, 0.5 UNION ALL
SELECT '4612732', '4608220', '14', '26/02/2008', 0.25, 1.17, -0.74, -0.74 UNION ALL
SELECT '4608220', '4603782', '14', '25/02/2008', 0.29, 0.73, -0.44, -0.44 UNION ALL
SELECT '4603782', '4599017', '14', '22/02/2008', 0.25, 0.44, -0.25, 0.44 UNION ALL
SELECT '4599017', '4594539', '14', '21/02/2008', 1.01, 2.51, 1.01, 2.26 UNION ALL
SELECT '4594539', '4589944', '14', '20/02/2008', 0.25, 0.25, -1.26, 0.25 UNION ALL
SELECT '4589944', '4585335', '14', '19/02/2008', -0.88, 0.63, -0.88, 0.25 UNION ALL
SELECT '4585335', '4580641', '14', '18/02/2008', 0, 0.76, -0.76, 0.76 UNION ALL
SELECT '4580641', '4575900', '14', '15/02/2008', -1.5, -0.05, -1.9, -1.75 UNION ALL
SELECT '4575900', '4571181', '14', '14/02/2008', 1.98, 2.5, 0.25, 2.5 UNION ALL
SELECT '4571181', '4566589', '14', '13/02/2008', 0.13, 1.1, -0.32, -0.32 UNION ALL
SELECT '4566589', '4562080', '14', '12/02/2008', 1.84, 3.29, 1.31, 3.29 UNION ALL
SELECT '4562080', '4557661', '14', '11/02/2008', 0, 0, -2.07, -1.8 UNION ALL
SELECT '4557661', '4553190', '14', '08/02/2008', 1.06, 2.12, 0.53, 2.12 UNION ALL
SELECT '4553190', '4548718', '14', '07/02/2008', -1.05, -0.31, -1.05, -1.05 UNION ALL
SELECT '4548718', '4544184', '14', '06/02/2008', -2.08, -0.78, -2.08, -0.78 UNION ALL
SELECT '4544184', '4539679', '14', '05/02/2008', 1.61, 4.3, 1.61, 3.49 UNION ALL
SELECT '4539679', '4535235', '14', '04/02/2008', 3.01, 3.56, 1.92, 1.92

--===== Set the identity insert back to normal
SET IDENTITY_INSERT [dbo].[Patient Data] OFF
Post #463050
Posted Monday, March 3, 2008 6:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:34 PM
Points: 35,534, Visits: 32,117
Thanks. I'll give this a whirl after work, today.

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #463107
Posted Monday, March 3, 2008 5:21 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:34 PM
Points: 35,534, Visits: 32,117
It seems to me that a bit of divide and conquer will make this run much faster... when you're all done, look for PatientID's that have a Parm of 5. Dunno if I put your parameters in the correct order or not...

 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

INSERT INTO #TmpTable
(TodayID,PreviousDayID,PatientID,Parm)
SELECT pd.TodayID,pd.PreviousDayID,pd.PatientID,2 AS Parm
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)
SELECT pd.TodayID,pd.PreviousDayID,pd.PatientID,3 AS Parm
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)
SELECT pd.TodayID,pd.PreviousDayID,pd.PatientID,4 AS Parm
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)
SELECT pd.TodayID,pd.PreviousDayID,pd.PatientID,5 AS Parm
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



--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #463500
Posted Tuesday, March 4, 2008 2:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 2, 2009 2:50 AM
Points: 13, Visits: 51
Thanks very much Jeff. I'll give that a go later today !!!
Post #463624
Posted Tuesday, March 4, 2008 3:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 2, 2009 2:50 AM
Points: 13, Visits: 51
Hi Jeff,

Sorry for being a bit thick, but I ran the query in a View and couldn't find the tmptable it created. I then ran the query in the query analyzer and it was still running 2 minutes later so decided to terminate the execution. Also, ( again me being a bit thick ) I couldn't read in the code where the recursion is happening i.e. looking for a pattern within each patients records that are in a consecutive days.

Kind regards ( and sorry for being pain ).

Laurence
Post #463645
Posted Tuesday, March 4, 2008 6:18 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:34 PM
Points: 35,534, Visits: 32,117
Not a problem... First, it can't be done in a view because of the temp table.

I'm not really using any form of recurrsion... I only stuff that meets the first day's measurments. Then I load the second day's measurements but only if the previous day is present, etc, etc. Only patients with the 5 day loaded have all 5 an in the correct order. It should be much faster than having cascaded joins on 4 million rows 5 times.

I suspect that the only way you'll get any decent performance out of these queries is to build a full blown covering index on you main table.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #463714
Posted Tuesday, March 4, 2008 7:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 2, 2009 2:50 AM
Points: 13, Visits: 51
Hi Jeff,

Thanks for your reply. Still not too sure where the tmptable is stored/held? And, when you suggest building a full blown covering index on you main table, how and what would you suggest? Once again, thanks very much for your help and time.

regards

Laurence
Post #463774
Posted Tuesday, March 4, 2008 7:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:34 PM
Points: 35,534, Visits: 32,117
The temp table is built in the first of the 5 queries I posted. Temp tables are session sensitive... they only "live" as long as the current session does. You can make it a permanent table just by removing the # sign from before the name. Of course, then you'll need to make sure you drop it when you're done with it. Look up "temporary tables, Creating and Modifying a Table" in Books Online and see the section on temporary tables for more information on them.

A "covering index" has all of the columns in the criteria followed by all of the columns in the SELECT list. They can be very fast because all of the information is contained in the index... it never has to go back to the table to find anything.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #463782
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse