Does (code) size matter?!?

[Artie]

The age-old question: does size really matter? I mean code size - get your
minds out of the gutter!

I have a table to store customer discounts (SQL 2000). The table has up to
10 possible levels of minimum qty and discount. Then there are 8 possible
discount types.
I created a proc to look for the best discount available. It has to go
though each of the 8 types and 10 levels of discounts per type.

The 8 types have different where clauses so I did not see a way around
having 8 separate sets of syntax.
For the 10 discount levels, I used an int variable (@disclvl) to increment
1 - 10.

minqty1 disc1 minqty2 disc2 .... minqty10
disc10

The proc uses dynamic sql like:
where 'minqty' + @disclvl + ' = blahblah'

Question: Is using dynamic sql and variables like this any more or less
efficient than simply repeating this code 10 times per discount type?

If I simply coded the 10 discount levels I could use a table variable
instead of the temp table I am using with dynamic sql.

[Roy Harvey (SQL Server MVP)]

To me it sounds like the ten discount levels should be in a table, and
dealt with my joining to the table. Or perhaps I just don't
understand what you are doing.

Roy Harvey
Beacon Falls, CT

On Thu, 16 Oct 2008 15:56:05 -0700, "Artie" <ar*******@yahoo.com>
wrote:

>The age-old question: does size really matter? I mean code size - get your
minds out of the gutter!

I have a table to store customer discounts (SQL 2000). The table has up to
10 possible levels of minimum qty and discount. Then there are 8 possible
discount types.
I created a proc to look for the best discount available. It has to go
though each of the 8 types and 10 levels of discounts per type.

The 8 types have different where clauses so I did not see a way around
having 8 separate sets of syntax.
For the 10 discount levels, I used an int variable (@disclvl) to increment
1 - 10.

minqty1 disc1 minqty2 disc2 .... minqty10
disc10

The proc uses dynamic sql like:
where 'minqty' + @disclvl + ' = blahblah'

Question: Is using dynamic sql and variables like this any more or less
efficient than simply repeating this code 10 times per discount type?

If I simply coded the 10 discount levels I could use a table variable
instead of the temp table I am using with dynamic sql.

[Dan Guzman]

I have a table to store customer discounts (SQL 2000). The table has up

to 10 possible levels of minimum qty and discount. Then there are 8
possible discount types.
I created a proc to look for the best discount available. It has to go
though each of the 8 types and 10 levels of discounts per type.

The 8 types have different where clauses so I did not see a way around
having 8 separate sets of syntax.
For the 10 discount levels, I used an int variable (@disclvl) to increment
1 - 10.

minqty1 disc1 minqty2 disc2 .... minqty10
disc10

The proc uses dynamic sql like:
where 'minqty' + @disclvl + ' = blahblah'

I believe this task would be much simpler with a normalized database design
(no repeating groups). I have no idea what a discount type is nor your
business rules for determining the best available discount but the example
below should get you started:

CREATE TABLE dbo.discount_types
(
discount_type int NOT NULL
CONSTRAINT PK_discount_types PRIMARY KEY
description varchar(100) NOT NULL
)

CREATE TABLE dbo.discounts
(
discount int NOT NULL
CONSTRAINT PK_discounts PRIMARY KEY,
discount_type int NOT NULL
CONSTRAINT FK_discounts_discount_type FOREIGN KEY
REFERENCES dbo.discount_types(discount_type),
minqty int NOT NULL
);

--set-based query to determine best discount
SELECT MAX(Discount)
FROM dbo.discounts
WHERE minqty < @order_quantity;

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Artie" <ar*******@yahoo.comwrote in message
news:ND****************@nlpi070.nbdc.sbc.com...

The age-old question: does size really matter? I mean code size - get
your minds out of the gutter!

I have a table to store customer discounts (SQL 2000). The table has up
to 10 possible levels of minimum qty and discount. Then there are 8
possible discount types.
I created a proc to look for the best discount available. It has to go
though each of the 8 types and 10 levels of discounts per type.

The 8 types have different where clauses so I did not see a way around
having 8 separate sets of syntax.
For the 10 discount levels, I used an int variable (@disclvl) to increment
1 - 10.

minqty1 disc1 minqty2 disc2 .... minqty10
disc10

The proc uses dynamic sql like:
where 'minqty' + @disclvl + ' = blahblah'

Question: Is using dynamic sql and variables like this any more or less
efficient than simply repeating this code 10 times per discount type?

If I simply coded the 10 discount levels I could use a table variable
instead of the temp table I am using with dynamic sql.

[Artie]

I certainly agree with normalizing the db but unfortunately that is not an
option. This is for an application so I need to work with what is there.

Maybe I answered my own question with the table variable vs temp table. If
I code each of the 10 discount level checks, I can use a table variable. If
I use dynamic sql then I need to use a temp table.
"Dan Guzman" <gu******@nospam-online.sbcglobal.netwrote in message
news:%E***************@flpi147.ffdc.sbc.com...

>I have a table to store customer discounts (SQL 2000). The table has up
to 10 possible levels of minimum qty and discount. Then there are 8
possible discount types.
I created a proc to look for the best discount available. It has to go
though each of the 8 types and 10 levels of discounts per type.

The 8 types have different where clauses so I did not see a way around
having 8 separate sets of syntax.
For the 10 discount levels, I used an int variable (@disclvl) to
increment 1 - 10.

minqty1 disc1 minqty2 disc2 ....
minqty10 disc10

The proc uses dynamic sql like:
where 'minqty' + @disclvl + ' = blahblah'

I believe this task would be much simpler with a normalized database
design (no repeating groups). I have no idea what a discount type is nor
your business rules for determining the best available discount but the
example below should get you started:

CREATE TABLE dbo.discount_types
(
discount_type int NOT NULL
CONSTRAINT PK_discount_types PRIMARY KEY
description varchar(100) NOT NULL
)

CREATE TABLE dbo.discounts
(
discount int NOT NULL
CONSTRAINT PK_discounts PRIMARY KEY,
discount_type int NOT NULL
CONSTRAINT FK_discounts_discount_type FOREIGN KEY
REFERENCES dbo.discount_types(discount_type),
minqty int NOT NULL
);

--set-based query to determine best discount
SELECT MAX(Discount)
FROM dbo.discounts
WHERE minqty < @order_quantity;

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Artie" <ar*******@yahoo.comwrote in message
news:ND****************@nlpi070.nbdc.sbc.com...

>The age-old question: does size really matter? I mean code size - get
your minds out of the gutter!

I have a table to store customer discounts (SQL 2000). The table has up
to 10 possible levels of minimum qty and discount. Then there are 8
possible discount types.
I created a proc to look for the best discount available. It has to go
though each of the 8 types and 10 levels of discounts per type.

The 8 types have different where clauses so I did not see a way around
having 8 separate sets of syntax.
For the 10 discount levels, I used an int variable (@disclvl) to
increment 1 - 10.

minqty1 disc1 minqty2 disc2 ....
minqty10 disc10

The proc uses dynamic sql like:
where 'minqty' + @disclvl + ' = blahblah'

Question: Is using dynamic sql and variables like this any more or less
efficient than simply repeating this code 10 times per discount type?

If I simply coded the 10 discount levels I could use a table variable
instead of the temp table I am using with dynamic sql.

[Ed Murphy]

Artie wrote:

I certainly agree with normalizing the db but unfortunately that is not an
option. This is for an application so I need to work with what is there.

Can you fake it by adding normalized views?

[Erland Sommarskog]

Artie (ar*******@yahoo.com) writes:

I certainly agree with normalizing the db but unfortunately that is not an
option. This is for an application so I need to work with what is there.

One approach would be to first unpivot this beast into temp table:

SELECT whatid, disclvl,
CASE disclvl WHEN 1 THEN minqty1
THEN 2 THEN minqty 2
..
FROM discounts
CROSS JOIN (SELECT disclvl = 1
UNION ALL
SELECT 2
...
SELECT 10) AS d

And then you can work with table in a more civilised way.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

bill (bi**********@gmail.com) writes:

I am bumping this thread, becuase I'd like someone who is more
familiar with the insides of the SQL Server engine to tell me how the
idea I outlined below will perform. I am thinking of doing this for a
similar problem that I face, and want to know if there will be any
issues. For the reasons I outline near the end of the message
(paragraph right before my signature) I think things will work fine,
but I'd like to hear from someone else.

An alternative is is a query that goes:

SELECT part_no, n,
CASE n WHEN 1 THEN disc_qt_01
WHEN 2 THEN disc_qt_02
WHEN 3 THEN disc_qt_03
WHEN 4 THEN disc_qt_04
WHEN 5 THEN disc_qt_05
END,
CASE n WHEN 1 THEN disc_price_01
WHEN 2 THEN disc_price_02
WHEN 3 THEN disc_price_03
WHEN 4 THEN disc_price_04
WHEN 5 THEN disc_price_05
END
FROM discount_flat
CROSS JOIN (SELECT n = 1 UNION ALL SELECT 2 UNION ALL 3
SELECT 4 UNION ALL SELECT 5) AS n

I would expect this to perform better than your query, which is likely
to access the table once per term in the UNION. But, as always, you
need to benchmark to find out.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[bill]

I like Erland's query better too. Because everything resolves to one
row, the view I proposed may (or may not) perform as well as Erland's
idea. However, even if view performs as well, Erland's has the
advantage of not requiring one to make a view. From a practical
standpoint, it is often the case that the developer doesn't have the
permissions to create views. From an elegance standpoint, other
things being equal, fewer permanent objects in the database the
better.

Thanks,

Bill

On Oct 31, 3:18*pm, Erland Sommarskog <[e-mail]:

An alternative is is a query that goes:

* SELECT part_no, n,
* * * * *CASE n WHEN 1 THEN disc_qt_01
* * * * * * * * WHEN 2 THEN disc_qt_02
* * * * * * * * WHEN 3 THEN disc_qt_03
* * * * * * * * WHEN 4 THEN disc_qt_04
* * * * * * * * WHEN 5 THEN disc_qt_05
* * * * *END,
* * * * *CASE n WHEN 1 THEN disc_price_01
* * * * * * * * WHEN 2 THEN disc_price_02
* * * * * * * * WHEN 3 THEN disc_price_03
* * * * * * * * WHEN 4 THEN disc_price_04
* * * * * * * * WHEN 5 THEN disc_price_05
* * * * *END
* FROM * discount_flat
* CROSS JOIN (SELECT n = 1 UNION ALL SELECT 2 UNION ALL 3
* * * * * * * SELECT 4 UNION ALL SELECT 5) AS n

I would expect this to perform better than your query, which is likely
to access the table once per term in the UNION. But, as always, you
need to benchmark to find out.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinf...ons/books.mspx