I have a table which contains "options" - things that the application needs to be able to do "This way or That way". For example, number of products on a page; default sort order; that sort of thing.CREATE TABLE MyOptions( MyKey varchar(10), -- PK MyValue varchar(100))INSERT INTO MyOptions SELECT 'OPT1', '6' UNION ALLSELECT 'OPT2', 'Price'
Now then, in an SProc lets say I want two options, what's the best way to get them?SELECT @Opt1 = CASE WHEN MyKey = 'OPT1' THEN MyValue ELSE @Opt1 END, @Opt2 = CASE WHEN MyKey = 'OPT2' THEN MyValue ELSE @Opt2 ENDFROM MypOtionsWHERE MyKey IN ('OPT1', 'OPT2')-- PK = Table 'MyOptions'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0.-- NoPK = Table 'MyOptions'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.orSELECT @Opt1 = O1.MyValue, @Opt2 = O2.MyValueFROM MyOptions O1, MyOptions O2WHERE O1.MyKey = 'OPT1' AND O2.MyKey = 'OPT2'-- PK = Table 'MyOptions'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0.-- NoPK = Table 'MyOptions'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0.or something else? (Such as selecting the appropriate rows into a @TableVar and JOINing them to subsequent queries.Tests were done with 100 rows in the MyOptions table. It looks like the no Primary Key and Query 1 are the best - but it kinda goes against the grain!Kristen
Using Configurable Option in SProc
Posted - 2004-09-27 : 06:58:59
