• 0

mySql Temporary tables


Question

Looking at some of my recent posts, you can read that I am working on some MySQL that's pushing me, so please bare with me if I seem noobish...

I am wanting to employ some best practises here, and have been advised that temporary tables are a bad idea - they put extra strain on the server etc.

However, I need to query the same subset data quite a few times, and a temp table would suit me well here.

Are they a "bad thing" or do you have other opinions.

Also, I have written a stored proc to turn "a,b,d,e" into a table:

|---|-----|

|ID|VAL|

|---|-----|

|1 |a |

|2 |b |

|3 |d |

|4 |e |

|---|-----|

This creates a temp table and then throws it back, is that valid?

Code:

BEGIN

DECLARE output VARCHAR(255);
DECLARE thisvalue VARCHAR(255);
DECLARE curpos INT;
DECLARE countdelimiters INT;

SET output = '';
SET curpos = 0;
SET countdelimiters = CHAR_LENGTH(inString) - CHAR_LENGTH(REPLACE(inString, inDelim, ''));

CREATE TEMPORARY TABLE splitValues (
	splitval_Num INT NOT NULL DEFAULT 0,
	splitval_Val VARCHAR(50)
);

WHILE(curpos < countdelimiters) DO
	SET thisvalue = SUBSTRING(inString FROM 1 FOR LOCATE(inDelim,inString)-1);
	SET inString = SUBSTRING(inString FROM LOCATE(inDelim,inString)+1 FOR 100);
	INSERT INTO splitValues VALUES (curpos, thisvalue);
	SET curpos = curpos+1;
	SET output = CONCAT(output,thisvalue,'|');
END WHILE;
INSERT INTO splitValues VALUES (curpos+1, inString);
SET output = CONCAT(output,inString);

SELECT * FROM splitValues;
DROP TABLE splitValues;

END

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

There's nothing wrong with using temp tables if you cannot obtain the desired result by other means.

However....

If this is in relation to your other post where you pass a comma del. string of IDs and ONLY desire output a result if ALL IDs have the same value then no, you don't need to do it this way. What I would do is remove the temp table, declare OUTPUT as int. Begin tearing apart your string, set OUTPUT = value for first ID. As you proceed to the remaining IDs, compare its value to OUTPUT. If it matches, keep going. If it does not match, set OUTPUT to nothing or -1 and exit the while statement. send back the OUTPUT value.

If you wanted ALL the values, regardless if they match, then you did good.

Link to comment
Share on other sites

  • 0

I will look into the speed of my multiple stored procs as against one that returns multiple recordsets. It just feels somehow "cleaner" to be able to perform all my lookups via one procedure.

Then again, it does make the management of the procedure more taskworthy, say something changes. Also, as I am using persistant connections, it's not like I am always opening and closing them. I will be intrigued to see what benefits I can claim here.

Edited by njlouch
Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.