Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2013
    Posts
    16

    Unanswered: SQL Cursor Alternative

    Hello all,

    At my company we're looking for a more efficient way to track movie popularity through SQL queries.

    We're working with 2 tables: one with a list of every movie that exists on our cable system (Table A), and another with the request log of each movie (Table B). I've written a SQL Server while loop (Cursor) that iterates through each distinct movie name in Table A, and records the number of times each movie was requested into Table C.

    I've noticed each while loop iteration takes a long time to run while using little CPU power, so I'd like to instead run the code once with a combination of UPDATE/JOINS/etc. However, I'm running into problems calculating the summation and was wondering if the community here could offer some help!


    Here's my original cursor code:

    Code:
    DECLARE
    _Cursor CURSOR FOR SELECT DISTINCT MovieName FROM TABLE_A ORDER BY MovieName ASC
    OPEN _Cursor
    FETCH NEXT FROM _Cursor INTO @movie_name
    WHILE @@fetch_status = 0
    BEGIN
    	SET @movie_title = ''
    	SET @provider = ''
    	SET @format = 0
    	SET @request_count = 0
    
    	SELECT @moive_title = MovieTitle FROM TABLE_A WHERE MovieName = @movie_name
    	SELECT @provider = Provider FROM TABLE_A WHERE MovieName = @movie_name
    	SELECT @format = FORMAT FROM TABLE_A WHERE MovieName = @movie_name
    	SELECT @request_count = COUNT(MovieName) FROM TABLE_B_FRIDAY WHERE MovieName = @movie_name
    	SELECT @asset_sd_hours = AssetSDHours FROM TABLE_A WHERE MovieName = @movie_name
    
    	INSERT INTO TABLE_C VALUES (@movie_title, @movie_name, @provider, @format, @request_count)
    		
    	FETCH NEXT FROM _Cursor INTO @movie_name
    END
    CLOSE _Cursor
    DEALLOCATE _Cursor

    Code with errors:

    Code:
    CREATE TABLE TABLE_C (MovieTitle varchar(255), MovieName varchar(255), Provider varchar(255), Format nvarchar(255), StreamCount int)
    
    INSERT INTO  TABLE_C(MovieName)
    SELECT DISTINCT MovieName FROM TABLE_A ORDER BY MovieName ASC
    
    UPDATE TABLE_C
    SET TABLE_C.MovieTitle = TABLE_A.MovieTitle,
    	TABLE_C.Provider = TABLE_A.Provider,
    	TABLE_C.Format = TABLE_A.Format,
    	TABLE_C.RequestCount = (SELECT COUNT(TABLE_B_FRIDAY.MovieName) WHERE TABLE_B_FRIDAY.MovieName = TABLE_C.MovieName),
    FROM TABLE_A, TABLE_B_FRIDAY
    WHERE TABLE_A.MovieName = TABLE_C.MovieName

    Below is what the code should do using an example of 5 movies:


    Table A - Master List of All Movies on System

    ==================================================
    MovieTitle | MovieName | Provider | Format | Location
    ==================================================
    Title 1 | Movie 1 | HBO | HD | East Coast
    Title 1 | Movie 1 | HBO | HD | West Coast
    Title 2 | Movie 2 | CINEMAX | HD | East Coast
    Title 2 | Movie 2 | CINEMAX | HD | West Coast
    Title 3 | Movie 3 | STARZ | HD | East Coast
    Title 3 | Movie 3 | STARZ | HD | West Coast
    Title 4 | Movie 4 | ENCORE | HD | East Coast
    Title 4 | Movie 4 | ENCORE | HD | West Coast
    Title 5 | Movie 5 | BRAVO | HD | East Coast
    Title 5 | Movie 5 | BRAVO | HD | West Coast



    Table B - Request Log of Each Movie

    ==================================================
    MovieName | StartTime | CompleteTime | Location
    ==================================================
    Movie 1 | 20:00 | 20:30 | East Coast
    Movie 1 | 20:00 | 20:30 | East Coast
    Movie 1 | 20:00 | 20:30 | East Coast
    Movie 1 | 20:00 | 20:30 | East Coast
    Movie 1 | 20:05 | 20:35 | West Coast
    Movie 2 | 20:10 | 20:40 | West Coast
    Movie 2 | 20:10 | 20:40 | West Coast
    Movie 2 | 20:10 | 20:40 | East Coast
    Movie 4 | 20:30 | 21:00 | East Coast
    Movie 5 | 20:30 | 21:00 | West Coast



    Table C - Newly Created Table Showing the Request Count of Each Movie

    ==================================================
    MovieTitle | MovieName | Provider | Format | RequestCount
    Title 1 | Movie 1 | HBO | HD | 5
    Title 2 | Movie 2 | CINEMAX | HD | 3
    Title 3 | Movie 3 | STARZ | HD | 0
    Title 4 | Movie 4 | ENCORE | HD | 1
    Title 5 | Movie 5 | BRAVO | HD | 1


    Thanks!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    No cursor and no new table required; just a SELECT statement:
    Code:
    SELECT movies.movietitle
         , movies.moviename
         , movies.provider
         , movies.format
         , Count(movie_log.moviename) As requestcount
    FROM   movies
     LEFT
      JOIN movie_log
        ON movie_log.moviename = movies.moviename
       AND movie_log.location  = movies.location
    GROUP
        BY movies.movietitle
         , movies.moviename
         , movies.provider
         , movies.format
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2013
    Posts
    16
    Thanks gvee. My code no doubt went from an execution time of 1 hour & 9 seconds to 18 seconds!

    For some reason I'm getting a much higher request count from what I had using my cursor code. One way I've checked is by executing:

    Code:
    SELECT COUNT(moviename) FROM movie_log WHERE moviename = '***'
    Which is different from what your suggestion outputs.

  4. #4
    Join Date
    Sep 2013
    Posts
    16
    I see what's going on. In my while loop, I iterated through a distinct list from the movies.moviename column, since the movies table has repeated movie names within it.

    For ex:
    MovieName 1 | West Coast
    MovieName 1 | East Coast

    How would I incorporate a SELECT DISTINCT movies.moviename while keeping everything the same?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by keennay View Post
    Thanks gvee. My code no doubt went from an execution time of 1 hour & 9 seconds to 18 seconds!
    18 seconds?
    That's still too long. Do you have indexes on these tables? Primary keys?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Sep 2013
    Posts
    16
    Quote Originally Posted by blindman View Post
    18 seconds?
    That's still too long. Do you have indexes on these tables? Primary keys?
    The example I used was for 5 movies. My real request log has a million rows of data. No primary keys.

  7. #7
    Join Date
    Sep 2013
    Posts
    16
    Nevermind guys, I figured it out.

    Consider this resolved!

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Care to share your resolution?
    George
    Home | Blog

  9. #9
    Join Date
    Sep 2013
    Posts
    16
    Quote Originally Posted by gvee View Post
    Care to share your resolution?
    I forgot to join the movies.location column as well:

    Code:
     LEFT
      JOIN movie_log
        ON movie_log.moviename = movies.moviename
       AND movie_log.location  = movies.location

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hehe, that was in my original answer
    George
    Home | Blog

  11. #11
    Join Date
    Sep 2013
    Posts
    16
    Figured question out, so please delete.
    Last edited by keennay; 09-18-13 at 13:12.

Tags for this Thread

Posting Permissions

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