CODEWITHSUNDEEP
paginationgoogle-bigquery
Aboriginal
Aboriginal

Reputation: 394

Bigquery search for limited records at each time

I am running below query in bigquery: 

SELECT othermovie1.title, 
                     (SUM(mymovie.rating*othermovie.rating) - (SUM(mymovie.rating) * SUM(othermovie.rating)) / COUNT(*)) / 
                      (SQRT((SUM(POW(mymovie.rating,2)) - POW(SUM(mymovie.rating),2) / COUNT(*)) * (SUM(POW(othermovie.rating,2)) - 
                      POW(SUM(othermovie.rating),2) / COUNT(*) ))) AS num_density
                    FROM [CFDB.CF] AS mymovie JOIN
                    [CFDB.CF] AS othermovie ON
                    mymovie.userId = othermovie.userId JOIN
                    [CFDB.CF] AS othermovie1 ON
                    othermovie.title = othermovie1.title JOIN
                    [CFDB.CF] AS mymovie1 ON
                    mymovie1.userId = othermovie1.userId
                    WHERE othermovie1.title != mymovie.title 
                    AND mymovie.title = 'Truth About Cats & Dogs, The (1996)'
                    GROUP BY othermovie1.title

But it is a while that bigquery is still processing. Is there a way to paginate the query and request to FETCH NEXT 10 ROWS ONLY WHERE othermovie1.title != mymovie.title AND num_density > 0 at each time?

Upvotes: 0

Views: 53

Answers (1)

Willian Fuks
Willian Fuks

Reputation: 11787

You won't find in BigQuery any concept related to paginating results so to increase processing performance.

Still, there's probably several things you can do to understand why it's taking so long and how to improve it.

For starters, I'd recommend using Standard SQL instead of Legacy as the former is capable of several optimizations plan that might help you in your case such as pushing down filters to happen before joins and not after, which is the case for you if you are using Legacy.

You can also make use of query plans explanation to diagnose more effectively where in your query the bottleneck is; lastly, make sure to follow the concepts discussed in best practices, it might help you to adapt your query to be more performative.

Upvotes: 1

Related Questions