Payed by LOC??

coentje

Someone asked me to find a problem that should be located in this view...

I snipped some (about 3000) lines

SELECT 	
		/* snip about 100 lines */
FROM Live2007.ORDERSET T1 
INNER JOIN Live2007.SITE T2
ON		T2.STAMP1 = T1.STAMP1 

LEFT OUTER JOIN Live2007.SITE T4
ON		T1.GROSID_1 = T4.ID

LEFT OUTER JOIN Live2007.SITE T5
ON 		T1.ARTNR1 = T5.ARTIKELNUMMER
AND		T5.CATEGORIE = 'ARTIKEL'

WHERE	T1.SENT_TO_GR = 'N'
AND		T1.DELIVER_REQ = 'Y'
AND		T1.OTYPE1 <> 'MO'
AND		T1.LEVERING_EDI = 'Y'

UNION ALL

SELECT 	
		/* snip about 100 lines */			
FROM Live2007.ORDERSET T1 
INNER JOIN Live2007.SITE T2
ON		T2.STAMP1 = T1.STAMP1 

LEFT OUTER JOIN Live2007.SITE T4
ON		T1.GROSID_2 = T4.ID

LEFT OUTER JOIN Live2007.SITE T5
ON 		T1.ARTNR2 = T5.ARTIKELNUMMER
AND		T5.CATEGORIE = 'ARTIKEL'

WHERE	T1.SENT_TO_GR = 'N'
AND		T1.DELIVER_REQ = 'Y'
AND		T1.OTYPE2 <> 'MO'
AND		T1.LEVERING_EDI = 'Y'

UNION ALL
	/* snip 25 unions almost identical to the 4 left here. */
UNION ALL

SELECT 	
	/* snip about 100 lines */
FROM Live2007.ORDERSET T1 
INNER JOIN Live2007.SITE T2
ON		T2.STAMP1 = T1.STAMP1 

LEFT OUTER JOIN Live2007.SITE T4
ON		T1.GROSID_29 = T4.ID

LEFT OUTER JOIN Live2007.SITE T5
ON 		T1.ARTNR29 = T5.ARTIKELNUMMER
AND		T5.CATEGORIE = 'ARTIKEL'

WHERE	T1.SENT_TO_GR = 'N'
AND		T1.DELIVER_REQ = 'Y'
AND		T1.OTYPE29 <> 'MO'
AND		T1.LEVERING_EDI = 'Y'

UNION ALL

SELECT 	
	/* snip about 100 lines */
FROM Live2007.ORDERSET T1 
INNER JOIN Live2007.SITE T2
ON		T2.STAMP1 = T1.STAMP1 

LEFT OUTER JOIN Live2007.SITE T4
ON		T1.GROSID_30 = T4.ID

LEFT OUTER JOIN Live2007.SITE T5
ON 		T1.ARTNR30 = T5.ARTIKELNUMMER
AND		T5.CATEGORIE = 'ARTIKEL'

WHERE	T1.SENT_TO_GR = 'N'
AND		T1.DELIVER_REQ = 'Y'
AND		T1.OTYPE30 <> 'MO'
AND		T1.LEVERING_EDI = 'Y'

GO

Anyone interested in the whole view can find it here: http://www.4shared.com/document/O-poA33U/Anyway.htm

apaq11

Ah Union All . Everytime I see it used the query looks almost identical to the code you posted...

boomzilla

@apaq11 said:

Ah Union All . Everytime I see it used the query looks almost identical to the code you posted...

Ug. I use UNION ALL a fair amount, but never like this. It's usually the result of entirely separate tables, so I know there are no dupes, so there's just no point in making the DB reconfirm it.

OTOH, I see stuff where instead of a view, the coder just runs them all as separate queries. And then wonders why it takes so long. At least, I wish they'd wonder that. Since it keeps happening, I guess they probably never do.

badcaseofspace

Therez a dutchman in ur codez feasting on ur cpu

I wonder how that problem managed to find its way out of this code. I'd get lost before dark.

apaq11

@boomzilla said:

Ug. I use UNION ALL a fair amount, but never like this. It's usually the result of entirely separate tables, so I know there are no dupes, so there's just no point in making the DB reconfirm it.

OTOH, I see stuff where instead of a view, the coder just runs them all as separate queries. And then wonders why it takes so long. At least, I wish they'd wonder that. Since it keeps happening, I guess they probably never do.

That would be the proper way to use it. The way I always seen it used is of the form

[Some select statement]

[Some where clause]

union all

[Same select statement]

[Slightly different where clause]

union all

[repeat pattern]

What's frustrating is usually the where statements are identical except for one where blah = foo which could be combined into where blah in (foo,foo2)

mihi

Is that Orrible?

I know of a few situations where the query optimizer barfed (gave an execution plan with several full table scans) on

select ... where foo in (1,2)

but worked well (gave a reasonable execution plan with reasonable execution time) with

select ... where foo = 1
union all
select ... where foo = 2

(where ... is a subselect with several more subselects of approximately 2KB text length).

Instead of looking why the optimized barfed (invalid statistics on table), just replace the 2KB query by the outlined 4KB replacement, and later when more cases were needed just add more to the end (cargo cult programming anyone?) to build a monster SQL with approx 20 KB text length.

Anyway, years after that, the real problem was found (by accident), and everything was cleaned up again.

Lesson learned: If you see such a SQL on Orrible (and your company does not have a dedicated DBA or an incompetent one), check the query optimizer result. If it is way off, check the statistics manually for plausability and rebuilt all the nonsense ones.

TGV

Atrocious. How come people that cannot combine conditions are allowed anywhere near a database? Let me guess: they are expert-black-belt-guru-dba-tzars from a software house that bills by the hour?

blakeyrat

@mihi said:

Lesson learned: If you see such a SQL on Orrible (and your company does not have a dedicated DBA or an incompetent one), check the query optimizer result. If it is way off, check the statistics manually for plausability and rebuilt all the nonsense ones.

Probably a stupid question, but doesn't Orrible periodically rebuild its own statistics when it's not busy?

boomzilla

@blakeyrat said:

Probably a stupid question, but doesn't Orrible periodically rebuild its own statistics when it's not busy?

I think this is configurable. I believe ours are set to do this over night, but I think it only looks at tables that cross some threshold as far as the change in the number of rows or something.

mihi

@blakeyrat said:

@mihi said:
Lesson learned: If you see such a SQL on Orrible (and your company does not have a dedicated DBA or an incompetent one), check the query optimizer result. If it is way off, check the statistics manually for plausability and rebuilt all the nonsense ones.

Probably a stupid question, but doesn't Orrible periodically rebuild its own statistics when it's not busy?

It might have. But

a) I doubt there were any times the DB was not busy at all (there was a steady stream of background jobs that ran over hours, partly *because* of that broken statistics...)

b) The statistics showed as last analyzed a few days ago. Apparently Orrible can build statistics incrementally, but if you hose the base data somehow (merging tablespaces of different dbs without rebuild, having inconsistent indices for weeks, or whatever), it will update inconsistent statistics with inconsistent data, resulting in even more inconsistent statistics. Only dropping the stats and letting it gather new ones helped to fix it.

c) Perhaps the configuration was just hosed (or still is...)

d) Might be better with recent DB versions. There are ppl who think they can save money by buying Oracle 10 something without a support contract (no idea if the number of licenses matches the numbers of servers either) and then never update it...

RTapeLoadingError

Looks like the specs for the view were...

- All the records that match these criteria; and
- All the records that match these slightly different criteria; and
- All the records that match these criteria which are slightly different again

etc, etc.

The original developer then went off and created (and hopefully tested) each SELECT statement in isolation before joining them all together with UNIONs. I think this approach is reasonable as it's clear from the view definition what logic is used to return each subset of records.

If the logic were all rolled together it would be a monster which would probably be a lot more difficult to understand than this. If any problems are identified then each logical block can be tested separately rather than trying to find the problematic part of the huge WHERE clause packed with ANDs, ORs and INs.

Also, if business rules are added, removed or changed the above definition would be fairly easy to modify.

I would have liked to have see comments above each SELECT block which describes which records are meant to be returned (e.g. the text from the spec which might be along the lines of: "All the non-foo articles that do not require delivery").

@mihi said:

I know of a few situations where the query optimizer barfed (gave an execution plan with several full table scans) on
select ... where foo in (1,2)
but worked well (gave a reasonable execution plan with reasonable execution time) with
select ... where foo = 1
union all
select ... where foo = 2

Even even when trying to avoid using 'in' for some reason, there's also still

select ... where foo = 1 or foo = 2

so that's no excuse :D

Jonathan

The problem here is the horrid database design, not this query. Union all is the best way to select this data without redesigning the database. There is no equivalent shorter query.

mihi

@Anonymouse said:

@mihi said:
I know of a few situations where the query optimizer barfed (gave an execution plan with several full table scans) on
select ... where foo in (1,2)
but worked well (gave a reasonable execution plan with reasonable execution time) with
select ... where foo = 1
union all
select ... where foo = 2
Even even when trying to avoid using 'in' for some reason, there's also still
select ... where foo = 1 or foo = 2 
so that's no excuse :D

that where foo = 1 or foo = 2 will result in the same "Index Skip Scan" (or an ignored index if the statistics are hosed) as the where foo in (1,2). Orrible may be stupid, but not *that* stupid. The separate where clauses might (given sufficient complexity of the inner parts) result in two separate "Index Range Scans" combind with a "Fast Union". Of course, if Orrible optimizes your union away (which can happen) you did not win anything writing it either way (which is the point when you start cluttering your SQL with optimizer hints...).

[Ok, in case foo is declared as an number type with no decimals, foo=1 or foo = 2 might also be optimized to foo between 1 and 2, which can be done with a single "Index Range Scan" again. So I should have better written 1 and 3.]

Scarlet_Manuka

@Jonathan said:

The problem here is the horrid database design, not this query. Union all is the best way to select this data without redesigning the database. There is no equivalent shorter query.

This. If you design a table with 30 foreign keys called "ArtNr1" up to "ArtNr30", another 30 foreign keys called "GrosID1" to "GrosID30", and 30 fields called "OType1" to "OType30", you need to learn about intersection tables.

danixdefcon5

Ah.

I present you with my fabulous Query:

select fecha, c.nombre as servidor, '% RAM' as countername, 100-100*(avg(b.countervalue)/c.ram_mb) valor
FROM counterdetails a
INNER JOIN contadoreshistorial b ON a.counterid = b.counterid
INNER JOIN servidor c ON b.GUID = c.guid
WHERE 
a.CounterID IN (select counterid from CounterDetails WHERE ObjectName = 'Memory' AND CounterName = 'Available MBytes')
AND fecha BETWEEN @fechaini AND @fechafin
GROUP BY c.nombre, ram_mb, fecha

UNION
select fecha, c.nombre as servidor, a.countername, avg(b.countervalue) valor
FROM counterdetails a
INNER JOIN contadoreshistorial b ON a.counterid = b.counterid
INNER JOIN servidor c ON b.GUID = c.guid
WHERE 
a.CounterID IN (select counterid from CounterDetails WHERE ObjectName = 'Processor' AND CounterName = '% Processor Time' AND InstanceName = '_Total')
AND fecha BETWEEN @fechaini AND @fechafin
GROUP BY a.countername, c.nombre, ram_mb, fecha

UNION
select fecha, c.nombre as servidor, a.countername/*+' '+a.InstanceName*/ as countername, avg(b.countervalue) valor
FROM counterdetails a
INNER JOIN contadoreshistorial b ON a.counterid = b.counterid
INNER JOIN servidor c ON b.GUID = c.guid
WHERE 
a.CounterID IN (8844,8845,8846)
AND fecha BETWEEN @fechaini AND @fechafin
GROUP BY a.countername/*, a.InstanceName*/, c.nombre, ram_mb, fecha

ORDER BY servidor,countername,fecha

I truly wanted to avoid UNION, but the varying GROUP BY stuff wouldn't let me do so.