Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: looking to performance tune this qry (generate range bar stock charts from tick data)

    I have 5 jobs running that is utilizing a lot of CPU.

    The jobs call 1 stored procedure with different parameters. The parameters tell the stored procedure which table to act on.

    Here is an example table schema:
    Code:
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[myTable](
    	[Id] [bigint] IDENTITY(1,1) NOT NULL,
    	[Time] [datetime] NOT NULL,
    	[price] [money] NOT NULL,
     CONSTRAINT [PK_myTable] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    CREATE NONCLUSTERED INDEX [IDX_myTable_1] ON [dbo].[myTable] 
    (
    	[Id] ASC,
    	[Time] ASC
    )
    INCLUDE ( [price]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    GO
    Here is the query that profile trace is showing as having highest read/duration/cpu:
    Code:
    			SELECT MIN(t2.Id)
    			FROM
    					dbo.myTable t1 
    			JOIN	dbo.myTable t2 
    				ON	t1.Id < t2.Id
    				AND
    				(
    					(
    						t2.price >= t1.price + 2.50
    					AND	t2.price > t1.price
    					)
    					OR
    					(
    						t1.price >= t2.price + 2.50
    					AND	t1.price > t2.price
    					)	
    
    					OR
    
    					(
    						t2.price >= 2732.75 + 2.50
    					AND	t2.price > 2732.75
    					)
    					OR
    					(
    						t2.price <= 2732.75 - 2.50
    					AND	t2.price < 2732.75
    					)													
    				)
    			WHERE
    				t1.Id between 8224 and 9867
    			AND t2.Time > '03/21/2012 15:59:21'
    The query was originally using ABS() in the form of:
    Code:
    			SET @sql = 
    			'
    			-- @r4Id
    			SELECT 
    				MIN(r2.Id)
    			FROM
    					dbo.'+@myTable+' r1 
    			JOIN	dbo.'+@myTable+' r2 
    				ON	r1.Id < r2.Id
    				AND	(ABS(r1.price - r2.price) >= '+CAST(@Diff AS VARCHAR(20))+'
    				OR ABS('+CAST(@r1price AS VARCHAR(20))+' - r2.price) >= '+CAST(@Diff AS VARCHAR(20))+'
    				)
    			WHERE
    				r1.Id between '+CAST(@r1Id AS VARCHAR(20))+' and '+CAST(@r2Id AS VARCHAR(20))+'
    			AND r2.Time > '''+@stR4Date+''' 
    			'
    I assumed the ABS was causing problems with the query optimizer so I rewrote the code as you can see in the 2nd code snippet above.

    In some instances the profiler trace is showing:
    cpu: 46909
    reads: 568705
    duration: 5382

    the myTable table does not have any more than 25k rows at any given time and those performance metrics seem pretty high to me given the query and table size.
    Last edited by Gagnon; 03-28-12 at 20:54.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    	t2.price >= t1.price + 2.50
    		AND	t2.price > t1.price
    Can you give an example of a value of t2.price, where t2.price >= t1.price + 2.50 is true and t2.price > t1.price is not true? Like 12.5 >= 10 + 2.5, but 12.5 is not > 10, but then with a better value than 12.5.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jun 2005
    Posts
    319
    Quote Originally Posted by Wim View Post
    Code:
    	t2.price >= t1.price + 2.50
    		AND	t2.price > t1.price
    Can you give an example of a value of t2.price, where t2.price >= t1.price + 2.50 is true and t2.price > t1.price is not true? Like 12.5 >= 10 + 2.5, but 12.5 is not > 10, but then with a better value than 12.5.
    Case 1:
    t2.price = 5
    t1.price = 4
    will fail

    Case 2:
    t2.price = 7
    t1.price = 4
    will succeed

  4. #4
    Join Date
    Jun 2005
    Posts
    319
    If it helps any I am trying to generate range bar charts from stock tick prices, in this example a tick is 0.25 and the range is 10 ticks (2.50 total).

  5. #5
    Join Date
    Jun 2005
    Posts
    319
    actually I think you are on to something, let me make some changes based on that assumption

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •