Trying to learn something new every day!
Database
MySQL rand() benchmark, don’t use rand()
0572 days
The need to get a certain amount of data randomly to present on a website so that your audience can view that your website is alive and, in the most cases, to be aware of the variety of products you have to present. It’s very common as also the performance side effect.
There are so many approaches for rand() replacements: ‘rand() mixed soup variations’, using join’s, etc but I found that with queries over very large tables none of them actually ease you from the pain of having a lot of heavy queries over your database.
Rand is not efficient and if you need to retrieve random rows having several columns on the where clause it’s even worst.
MySQL: Easily delete all data and reseed all tables
0The need for a fresh start during the development stage is very frequent. This script allows to delete data from all tables and also to reseed all tables that have identity columns. I’ts clean and simple.
This is my version for MySQL (see previous article for MS SQL).
/* disable all constraints */
CALL usp_mysql_foreachtable('ALTER TABLE ? DISABLE KEYS');
SET FOREIGN_KEY_CHECKS=0;
/* delete data in all tables */
CALL usp_mysql_foreachtable('DELETE FROM ? WHERE Id > 0');
/* enable all constraints */
SET FOREIGN_KEY_CHECKS=1;
CALL usp_mysql_foreachtable('ALTER TABLE ? ENABLE KEYS');
/* reset auto-increment columns */
CALL usp_mysql_foreachtable('ALTER TABLE ? AUTO_INCREMENT = 1');
MS SQL: Easily delete all data and reseed all tables
0The need for a fresh start during the development stage is very frequent. This script allows to delete data from all tables and also to reseed all tables that have identity columns. I’ts clean and simple.
-- disable all constraints
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
-- delete data in all tables
EXEC sp_MSForEachTable 'DELETE FROM ?'
-- enable all constraints
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'
-- reseed identity columns
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
DBCC CHECKIDENT (''?'', RESEED, 0)
'
Updated [on April 19, 2011]: I’ve just added similar script for MySQL. View article.
Easy TRIM function for MS SQL
0I don’t know if you notice but MS SQL don’t have, natively, a TRIM() function. TRIM() is actually a conjunction of LTRIM() and RTRIM(), these are native. So it’s easy to add a TRIM() using an UDF (User Defined Function):
-- Add UDF to current database
CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(@string))
END
GO
-- Test it, the result would be 'i have some extra spaces'
SELECT dbo.TRIM(' i have some extra spaces ')
In MS SQL is very easy to add new UDF’s to satisfy several needs, use your imagination and google it all the way…
MySQL vs MSSQL – upsert stored procedure
2Stored procedures are a great way to keep things simple when we develop more complex systems with a lot of data entities that must me stored on a database. Personally as an MSSQL guy I’ve struggled these years to learn the most to take advantage of this import past of the database engines: stored procedures, or, routines in the MySQL terminology.
One way of reducing the written code for updates and inserts is to “merge” both operations using the SQL-like UPSERT. Imagine a simple task: update and insert rows to a “cron” entity that stores main information for cron jobs/tasks. Basically the difference between update/insert, DAL perspective, is the supplied id. For new records use 0 to define the behavior. This can be done with a simple stored procedure/routine:
(more…)
