1

I am very new to SQL Server and finally got my code to run, but its very slow. My database is a bit on the big side (1.22mill rows, 3 columns) and I have some while loops, which I know isn't ideal but I couldn't find a way around it.

Any help would be greatly appreciated!!!

declare @b float
declare @c float
declare @dateloopfora datetime
declare @dateloopforc datetime

BEGIN 
    SET @MYCURSOR = CURSOR FOR  
        SELECT DISTINCT [STOCKS] FROM [tsxvPrices].[dbo].[3coldata] 

    OPEN @MYCURSOR

    FETCH NEXT FROM @MYCURSOR INTO @STOCK

    set @end = '12/30/2012'

    WHILE @@FETCH_STATUS = 0
    BEGIN 
        print ('Stockname restart: ' + @stock)
        SET @dateloop = '01/01/2012'

        WHILE (@dateloop <= @end)
        BEGIN
            set @datedelta = 0
            SET @dateloop = dateadd(day,1,@dateloop)
            set @b = (SELECT [val] FROM [tsxvPrices].[dbo].[3coldata] WHERE( [dates] = @dateloop and [stocks] = @stock))

            WHILE (@datedelta < 4) 
            BEGIN
                set @datedelta = @datedelta + 1

                if (@b is null)
                begin
                    SET @dateloop = dateadd(day,1,@dateloop)
                    set @b = (SELECT [val] FROM [tsxvPrices].[dbo].[3coldata] WHERE( [dates] = @dateloop and [stocks] = @STOCK))
                    /*print ('b= ' + cast( @b as varchar(10)) + ' dateadd1=' + cast( @datedelta as varchar(10))) */
                end
            END

        SET @datedelta = 0
        set @a = (SELECT [val] FROM [tsxvPrices].[dbo].[3coldata] WHERE( [dates] = dateadd(day,-1,@dateloop) and [stocks] = @stock))
        set @c = (SELECT [val] FROM [tsxvPrices].[dbo].[3coldata] WHERE( [dates] = dateadd(day,+96,@dateloop) and [stocks] = @stock))

        if @b/@a < 0.8 
        begin
            WHILE (@datedelta < 4)
            BEGIN
                set @datedelta = @datedelta + 1

                if (@a is null)
                begin
                    SET @dateloopfora = dateadd(day,-@datedelta,@dateloop)
                    set @a = (SELECT [val] FROM [tsxvPrices].[dbo].[3coldata] WHERE( [dates] = @dateloopfora and [stocks] = @STOCK))
                    /*print ('b= ' + cast( @b as varchar(10)) + ' dateadd1=' + cast( @datedelta as varchar(10))) */
                end

                if (@c is null)
                begin
                    SET @dateloopforc = dateadd(day,96+@datedelta,@dateloop)
                    set @c = (SELECT [val] FROM [tsxvPrices].[dbo].[3coldata] WHERE( [dates] = @dateloopforc and [stocks] = @STOCK))
                    /*print ('b= ' + cast( @b as varchar(10)) + ' dateadd1=' + cast( @datedelta as varchar(10))) */
                end
            END

            /*print ('Stockname: ' + @stock)
            print @dateloop
            print('daily')
            print @b/@a 
            print ('quarterly')*/
            print @c/@b
            /*print ('======================')*/
        end
    END

    FETCH NEXT FROM @MYCURSOR INTO @STOCK
END

CLOSE @MYCURSOR
DEALLOCATE @MYCURSOR
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jordan
  • 11
  • 2
  • 5
    you have a nested loop of O(N^3). This will have very poor performance. I recommend using a set based approach. If you supply sample data and what the desired output will be that would be great. – Fuzzy Feb 24 '16 at 17:59
  • 1
    You're writing SQL like a it's an object oriented programming language instead of a set-based query language. I'd suggest you start by stating the requirement (what result set you're expecting) with the list of tables you have. Then again, stack overflow isn't really a please-write-my-code for me forum. But, I'm not really sure where to start with what you posted. I think if I was a moderator, I'd vote to close. – maplemale Feb 24 '16 at 18:01
  • 1
    You are giving no chances to sql server to optimize anything because you are doing everything step-by-step. – Ivan Starostin Feb 24 '16 at 18:07

1 Answers1

0

I can see one simple problem straight away, you're using a Cursor. This is probably one of the worst ways to process SQL, it's very slow and it's forcing the SQL engine to be very inefficient.

You're new to SQL so I'll forgo a technical explanation, but please have a look at this other StackOverflow question:

Why is it considered bad practice to use cursors in SQL Server?

I may be able to offer further advice, What version of SQL are you using?

Community
  • 1
  • 1
Russ Clarke
  • 17,511
  • 4
  • 41
  • 45
  • I'm using MS sql server 2014. originally I was planning on using a loop instead of a cursor, but due to that data being stock names I started looking for a loop that would work and I found Cursor. – Jordan Feb 24 '16 at 18:38
  • Yeah, it's an easy trap for people new to SQL, perhaps you can do as maplemale commented on your question and post a small sample of the data that makes sense for us to analyse. – Russ Clarke Feb 24 '16 at 21:51