Welcome Guest, Not a member yet? Register   Sign In
I thought this stored procedure would be faster than plain selects
#1

I just spent all morning working on a stored procedure that recursively selects categories from a table that has a parent/child relationship. I knew I could do this in PHP/CodeIgniter, but I thought I would gain some sort of performance advantage by going with the stored procedure. It turned out that the stored procedure was adding what seemed like a half second to the operation. In cases where the stored procedure was called multiple times, it was about 2+ seconds.

Then I just did it in PHP, and it's so fast I can't even tell there is any lag at all.

So, now I'm wondering why it's so slow. Is it slow because the creation of the temporary table makes it slow? Check it out:


Code:
DELIMITER $$

DROP PROCEDURE IF EXISTS get_ancestry $$

CREATE PROCEDURE get_ancestry( IN selected_cat_id int )
BEGIN

DECLARE parent_id int;
DECLARE child_id int;
SET child_id = selected_cat_id;
SET parent_id = 0;

-- Create temp table
CREATE TEMPORARY TABLE IF NOT EXISTS temp_table AS
    ( SELECT * FROM categories WHERE 1 = 0 );
TRUNCATE TABLE temp_table;

-- Add the selected category to the results
INSERT INTO temp_table
    SELECT * FROM categories
    WHERE id = selected_cat_id;

-- Get the parent of selected_cat_id
SELECT parent INTO parent_id
FROM temp_table WHERE id = selected_cat_id;

WHILE parent_id != 0 DO

  -- Add this category to the temp table
  INSERT INTO temp_table
      SELECT * FROM categories
      WHERE id = parent_id;
 
  SET child_id = parent_id;
  SET parent_id = 0;

  -- This will be the next ancestor in the loop
  SELECT parent into parent_id
  FROM categories WHERE id = child_id;

END WHILE;

-- Return the complete ancestry
SELECT * FROM temp_table;

END $$

DELIMITER ;
Reply
#2

Could be the SELECT * try it with the column names instead and see if it speeds up.
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#3

(03-11-2018, 03:24 AM)InsiteFX Wrote: Could be the SELECT * try it with the column names instead and see if it speeds up.

Indeed, the following in particular looks bad:

(03-10-2018, 01:58 PM)skunkbad Wrote:
Code:
SELECT * FROM categories WHERE 1 = 0

Because you're doing a SELECT on a table, even it is obvious that no data would actually be selected (as nothing will satisfy 1 = 0), this likely results in a rowscan of the entire table.

And of course, if your PHP solution doesn't involve a temporary table, that's another thing that adds overhead.
Reply
#4

(03-11-2018, 10:58 AM)Narf Wrote: Because you're doing a SELECT on a table, even it is obvious that no data would actually be selected (as nothing will satisfy 1 = 0), this likely results in a rowscan of the entire table.

And of course, if your PHP solution doesn't involve a temporary table, that's another thing that adds overhead.

At the moment the table only has 150 rows or so. I'm thinking the main issue is the temp table. Do you think there is a MySQL solution that is just as fast as my PHP solution (which is just a recursive function looking for parents) ??

And, you're probably wondering why I'd be doing this for 150 categories. I was told that the category count may increase to ~600K.
Reply
#5

Relational databases aren't good at this. Unless you denormalize the data (i.e. store the already computed results), I don't see how you could get better results with pure SQL compared to something involving PHP.

Though I don't see why you need a temporary table even for a million records. As long as you're doing index searches it should be just fine to do it directly.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB