Database

Solved, but... [modified]

27-Feb-10 13:48

I have the following Table Structure in a SQL 2008 database:

ProductCategory
ProdCat_ID  Int PK
Name VarChar(30)

Product
Product_ID Int PK
ProdCat_ID Int FK
...

WorkOrder
WO_ID Int PK
Product_ID Int FK
...

Production
Prodn_ID Int PK
WO_ID Int FK
...

MaterialTransaction
MatTrans_ID Int PK,
TransDate Date,
Prodn_ID FK,
Qty Decimal(18,7)
...

I want to retrieve Qty data for specified Category Names on each date in the query.

If I do the following:

SELECT Distinct(mt.TransDate) As [Date],
         CASE WHEN pc.Name IN('Hadware','Custom')
              THEN SUM(mt.Qty)
              ELSE 0 END AS 'Hardware',
         CASE WHEN pc.Name LIKE 'DS %'
              THEN SUM(mt.Qty)
              ELSE 0 End As 'DataStrip'
              
  From MaterialTransaction mt
    JOIN Production pr
      ON mt.Prodn_ID = pr.Prodn_ID
    JOIN WorkOrder wo
      ON pr.WO_ID = wo.WO_ID
    JOIN Product p
      ON wo.Product_ID = p.ID
    JOIN ProductCategory pc
      ON p.ProdCat_ID = pc.ID
    WHERE Date Between '11/24/2009' And '11/25/2009'

  GROUP BY mt.TransDate, pc.Name

I end up with results similar to:

OUTPUT:

Date                Hardware        DataStrip
2009-11-24 00:00:00 0.0000000000    560.4080000
2009-11-24 00:00:00 2786.3100000    0.0000000
2009-11-25 00:00:00 0.0000000       125.5415000

What I require however is:

Date                Hardware        DataStrip
2009-11-24 00:00:00 2786.310000000  560.4080000
2009-11-25 00:00:00 0.0000000       125.4150000

Any pointers are most appreciated.

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

Andy_L_J27-Feb-10 14:55

27-Feb-10 14:55

I have used the following correlated query:

SELECT mt.TransDate, CASE ISNULL(h.Hardware,0)
                      WHEN 0 Then 0
                      ELSE h.Hardware
                      End As Hardware, 
                     CASE ISNULL(d.Datastrip, 0)
                      WHEN 0 Then 0
                      ELSE d.Datastrip
                      END AS Datastrip
  FROM MaterialTransaction mt
    LEFT JOIN (SELECT mtt.TransDate,SUM(mtt.Qty) As Hardware
            FROM MaterialTransaction mtt
              JOIN Production prr
                ON mtt.Prodn_ID = prr.Prodn_ID
              JOIN WorkOrder woo
                ON prr.WO_ID = woo.WO_ID
              JOIN Product pp
                ON woo.Product_ID = pp.ID
              JOIN ProductCategory pcc
                ON pp.ProdCat_ID = pcc.ID
            WHERE pcc.Name In ('Hardware','Custom', 'Cellular')    
            GROUP BY mtt.TransDate) h
      ON mt.TransDate = h.TransDate
    LEFT JOIN (SELECT mtt.TransDate, SUM(mtt.Qty) As Datastrip
            FROM MaterialTransaction mtt
              JOIN Production prr
                ON mtt.Prodn_ID = prr.Prodn_ID
              JOIN WorkOrder woo
                ON prr.WO_ID = woo.WO_ID
              JOIN Product pp
                ON woo.Product_ID = pp.ID
              JOIN ProductCategory pcc
                ON pp.ProdCat_ID = pcc.ID
            WHERE pcc.Name In ('DS PVC','DS PETG')    
            GROUP BY mtt.TransDate) d
          ON h.TransDate = d.TransDate
    WHERE mt.TransDate BETWEEN '11/24/2009' And '11/25/2009'
    GROUP BY mt.TransDate, h.Hardware, d.Datastrip

It works as required, but boy is it ugly. Can this be optimized further?

<edit> The final query (with 8 sub-queries) was running rather slowly - ~8 secs to retreive 30 rows from around 4000. An nonclustered index on MaterialTransaction.TransDate sped this up to ~1 sec <edit>

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

modified on Saturday, February 27, 2010 11:33 PM

Luc Pattyn27-Feb-10 15:01

Luc Pattyn

27-Feb-10 15:01

AFAIK joins don't need explicit "ON" clauses if the corresponding fields carry the same names, so ON prr.WO_ID = woo.WO_ID could be omitted, and others after a field name change too.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.
All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.

Mycroft Holmes27-Feb-10 17:16

27-Feb-10 17:16

Luc Pattyn wrote:
AFAIK joins don't need explicit "ON" clauses if the corresponding fields carry the same names

Erk! what happens if you have fields of the same name and you don't want them joined? Or am I misunderstanding your point.

Never underestimate the power of human stupidity
RAH

Mycroft Holmes27-Feb-10 17:22

27-Feb-10 17:22

Andy_L_J wrote:
Can this be optimized further?

Probably but not by the query structure, I have these regularly, as long as it is a transactional type system (as opposed to a batch system)and you are not dealing with 1000s of results there should not be too much pain.

Ugly, Pfft, its less than 40 line, has only 2 sub selects, doesn't qualify for ugly.

Never underestimate the power of human stupidity
RAH

Andy_L_J27-Feb-10 17:41

Re: How to write this query?

27-Feb-10 17:41

Thanks Mycroft. Thumbs Up | :thumbsup:

The final query has 8 subs and does the job. Initially a little slowly until I placed an index on the TransDate field. Blush | :O

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

i.j.russell27-Feb-10 23:41

Re: How to write this query?

27-Feb-10 23:41

SELECT mt.TransDate,
         SUM(CASE WHEN pc.Name IN('Hadware','Custom')
              THEN mt.Qty
              ELSE 0 END) AS 'Hardware',
         SUM(CASE WHEN pc.Name LIKE 'DS %'
              THEN mt.Qty
              ELSE 0 End) As 'DataStrip'
  From MaterialTransaction mt
    JOIN Production pr
      ON mt.Prodn_ID = pr.Prodn_ID
    JOIN WorkOrder wo
      ON pr.WO_ID = wo.WO_ID
    JOIN Product p
      ON wo.Product_ID = p.ID
    JOIN ProductCategory pc
      ON p.ProdCat_ID = pc.ID
    WHERE Date Between '11/24/2009' And '11/25/2009'
  GROUP BY mt.TransDate

Andy_L_J28-Feb-10 0:15

Advice for product manager

28-Feb-10 0:15

Cheers - Nice Thumbs Up | :thumbsup:

I am testing for speed against the other query.

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

treefirmy27-Feb-10 12:34

27-Feb-10 12:34

Would anyone happen to know of any public web pages or documents, by respected authorities in the field that would definitively convince a product owner that creating tens of thousands of databases in a single SQL Server is not a reasonable thing to do? In addition, having Joe-blow users creating and deleting databases is not a good idea, i.e. these are administrator only tasks.

The product in question is an update of a previous product where there was a single ‘database’ per profile. Only in the old version, the ‘database’ was just a single file with no constraint checking.

Mycroft Holmes27-Feb-10 17:27

27-Feb-10 17:27

I ran across this type of system in the 90s, app was build on system5 or some such crap. The owner wanted exactly the same thing written in Access or SQL server, same functionality and everything. When I flatly refused to even discuss the solution and offered to walk away from the contract he saw the light.

Seriously if you cannot move him off this idiotic design, quit, move on your stress levels will appreciate it.

Never underestimate the power of human stupidity
RAH

i.j.russell27-Feb-10 23:48

27-Feb-10 23:48

Try this article from Microsoft.

http://msdn.microsoft.com/en-us/library/aa479086.aspx[^]

treefirmy28-Feb-10 4:14

28-Feb-10 4:14

Thanks for the link to the great article i.j.russell, I'm still reading and learning.

Ah, I see, my description of the question wasn't very good. I should expand a bit on what I mean by profile. A better description would be an item where multiple, think thousands or tens of thousands, of these items are associated with a single tenant.

The new software being developed copied a lot of code from the old version where an item, single file, was represented as a database using an API provided by an antiquated database product.

The new software is trying to use this same scheme in SQL Server, one database per item. Since there can be tens of thousands of items, there will be tens of thousands of databases. I can hear the murderous laughter of database administrators everywhere.

I think this scheme breaks a number of well-established guidelines but I’d like to give the product owner ammunition, concrete definitive documentation, to force the developers to change the product to something more reasonable. (Oh, and I’m one of the developers – just showing up late in the development cycle of the product.) The product owner is not very technical and so may have been convinced for some reason or other that this was an okay solution.

i.j.russell28-Feb-10 5:33

28-Feb-10 5:33

The other option to consider is going the NoSql route and using something like MongoDb, CouchDb or even cloud-based storage like Microsoft Azure Table Storage or Amazon SimpleDb.

treefirmy28-Feb-10 5:40

28-Feb-10 5:40

Good options, though I'm pretty sure they are going to use SQL Server no matter what. The customers of the product are typically Microsoft shops so they really want SQL Server.

I just can't imagine how the developers convinced the product owner that creating tens of thousands of databases would be okay.

i.j.russell28-Feb-10 5:58

28-Feb-10 5:58

Sql Server will actually do what they want as it supports up to 32,767 databases per server instance.

http://msdn.microsoft.com/en-us/library/ms143432%28SQL.90%29.aspx[^]

However, I would love to see the look on the first DBAs that are asked to support this application; I am fairly certain that it will not be pretty!

treefirmy28-Feb-10 6:13

28-Feb-10 6:13

Exactly, 'technically possible' but a horrendous idea. I'm guessing this is how development 'convinced' the product owner.

Any good web pages to convince them that this is a ridiculous idea?

i.j.russell28-Feb-10 7:08

28-Feb-10 7:08

I can't find any sites that would help, but that in itself is helpfull because it means that the concept is either uber-cutting edge or plain stupid. Since Sql Server has been around for well over a decade, I am going for the latter.

From a technical perspective, I can think of a few issues;

1. Your hardware requirements are going to be well in excess of what your application data should need. Your data should be easily handled by a single Sql Server Express database.
2. Connection pooling is going to be interesting as each database will have a different connection string. Performance will be adversely affected as new connections are created.
3. Whilst you can carry out DDL in a stored procedure to create a database, table(s) and indexes, this will take a few seconds each time.

Mycroft Holmes28-Feb-10 10:58

28-Feb-10 10:58

I'd go with i.j's plain stupid scenario, this is so far outside sensible that it would not be entertained by any reasonable designer.

Consider a code base change to the database, maintenance will be an absolute nightmare and the cost of maintaining an app is much higher than developing the app.

Never underestimate the power of human stupidity
RAH

question in regard to updating using mssql

jgrogan17-Dec-10 3:34

jgrogan

17-Dec-10 3:34

The key to your argument should be about cost; each cost can then have a technical answer to the question - for example, annual maintenance cost is $500k as we need to employ a team of DBA's to manage 1000's of databases

Give your suggested solution with a cost, give his solution with it's associated costs. You can always ham it up a bit to ensure that your solution looks better.

This will get his interest far more than technical discussions on the merits or otherwise of solutions.

tonyonlinux26-Feb-10 18:19

tonyonlinux

26-Feb-10 18:19

i have a database model that has an isbn as the primary key
The isbn is input by the user
it is the primary key for book and book is linked to book_by_authors on the isbn number
and it is also linked to bookcategory by the isbn and so forth.

How in the the world could I update the isbn easily? I keep getting constraint issues with the foreign keys. Should I take and create a method that stores the existing values and then deletes the record then turns around and adds the record back with the corrected data? just wondering what should be done in this case. thanks

Re: question in regard to updating using mssql

R. Giskard Reventlov26-Feb-10 19:54

R. Giskard Reventlov

26-Feb-10 19:54

Perhaps you'd be better using a surrogate key rather than the ISBN if you want to allow users to edit them (you'd still have to check for collisions if they can manually alter an ISBN).

Tychotics: take us back to the moon

"Life, for ever dying to be born afresh, for ever young and eager, will presently stand upon this earth as upon a footstool, and stretch out its realm amidst the stars."
H. G. Wells

Re: question in regard to updating using mssql

Mycroft Holmes26-Feb-10 23:28