January 23, 2009 at 2:48 pm
>>Ditto ISO standard data. If there is an international standard use it.
If there is no international standard conform to an industry standard
>>
I will take the contrary position here. Some (many??) standards those such as Joe would have us adhere to are incredibly onerous and complicated, which would require lots of lost time and productivity to learn and then follow. What about older code that must be maintained - should we convert it as we have to touch it? Doesn't that increase error chances?
Oh, and standards are NEVER that - take ANSI SQL and Microsoft SQL Server. Which flavor? What level of compatability is baked into which version of which RDBMS your product must operate on??
Which standards do you adopt?? There are plenty out there. All of them, and if not why would one win and another lose?? What about conflicts between standards?
Yada-yada-yada
Sorry, 80+% of the dev shops out there have no need nor desire and would in my opinion suffer much more than than would gain from trying to implement standards across the board. This is yet another thing those of us on the forum will just have to agree to disagree about. There is nothing I can say to convince Joe et al that his standards would cause more harm than good if they were forced upon all and nothing he et al can say to convince me otherwise. :blink:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 23, 2009 at 9:38 pm
TheSQLGuru (1/23/2009)
>>Ditto ISO standard data. If there is an international standard use it.If there is no international standard conform to an industry standard
>>
I will take the contrary position here. Some (many??) standards those such as Joe would have us adhere to are incredibly onerous and complicated, which would require lots of lost time and productivity to learn and then follow. What about older code that must be maintained - should we convert it as we have to touch it? Doesn't that increase error chances?
Oh, and standards are NEVER that - take ANSI SQL and Microsoft SQL Server. Which flavor? What level of compatability is baked into which version of which RDBMS your product must operate on??
Which standards do you adopt?? There are plenty out there. All of them, and if not why would one win and another lose?? What about conflicts between standards?
Yada-yada-yada
Sorry, 80+% of the dev shops out there have no need nor desire and would in my opinion suffer much more than than would gain from trying to implement standards across the board. This is yet another thing those of us on the forum will just have to agree to disagree about. There is nothing I can say to convince Joe et al that his standards would cause more harm than good if they were forced upon all and nothing he et al can say to convince me otherwise. :blink:
On this, I will agree with you.
January 26, 2009 at 12:18 am
So, what do you do without standards ?
Some won't do business with you if you're not certified ISO...
Which one to pick is up to the company.
IMO things like SOx or Hippa are standards that have generated billions of $$ of investment to accomplish what ?
Well, coding standards are a start.
Having good guidelines and documented exceptions will help out in the (near) future.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 26, 2009 at 8:15 am
I guess that I got bit by the standards bug. The other day I wrote a check to our food delivery driver. He claimed that I fouled up the date on it. Nope, it was right. 20090115 just happens to be ISO-8601. I threw a hissy when they published a new schema layout that had a UPC column that was NVARCHAR(10). I guess "Sunrise 2005" was a waste. I objected to 10 and calling the column "UPC".
I'm old enough to have used a ton of serial connections. I still have NULL MODEM cable with 25 to 9 adapters. We used to laugh at RS-232 knowing that RS means Recommended Standard. Not even an adopted standard. Just one that had been recommended.
ATBCharles Kincaid
January 26, 2009 at 10:44 am
My absolute favorite standard is "MIL-TP-41".... "Make It Like The Print for once". 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
January 26, 2009 at 12:42 pm
I work for a company that sources its data from many hundred different data providers and supplies it to many different data consumers.
We could be arrogant and say "Ignorant prole, you should be grateful we allow you to buy from us, you and your ugly kids will take we can be bothered to offer" , but I suspect we wouldn't sell much with that attitude (unless we were, oh I don't know, a large international bank).
For us it makes sense to say, look guys, our data conforms to the the following published standards. It wouldn't be profitable for us to try and provide our customers with data in their format and it actually makes things easier for our customers in the medium to long term because they don't have to source their data with us. It opens up a whole new range of possibilities for them.
Yes, standards change but they are still standards. One of the ANSI-SQL standards had a 30 character limit on object names. Fantastic, a bone fide excuse to kill the ridiculous practice of putting tbl in front of tables and vw in front of views.
The next ANSI standard increased the limit to 128 characters. Again, this was beneficial for databases with huge numbers of objects because we weren't stuck trying to abbreviate a meaningful object name into text speak.
When it comes to sharing data adhering to standards is a bit like marriage. Marriage has its trials but celibacy has no joys.
January 26, 2009 at 1:07 pm
David.Poole (1/23/2009)
I'm just wondering what situation exists where you have 1 billion records with a tiny proportion having a bit field set to 1 AND that field being important enough to have an index on it.Doesn't imply that the tiny proportion of records should exist in their own table?
On the subject of standards guys, look at the world around you. We have the 3 Abrahamic faiths perpared to commit satanic acts against each other to prove their loyalty to exactly the same God where 99% of the key teachings of their religions agree.
In a world in which such craziness achieves nothing other than to prove that stupidity is the only renewable resource an international standard has been agreed. Don't look a gift horse in the mouth!
If the proprietary and standard way do exactly the same thing with no detriment then you might as well use the standard way.
Ditto ISO standard data. If there is an international standard use it.
If there is no international standard conform to an industry standard
I'm sick of seeing CountryID as an proprietary int field and having external companies kick back because they expect the ISO standard country code.
Once a company grows beyond a certain point it has to talk to the outside world and proprietary stuff just gets in the way.
I know this is a late answer to your question, but at a previous employer we had an Invoice Header table with close to 500,000 records. In the header was a bit field, Historical. If it was 0 the invoice was current, and if 1 it was historical. More than 90% of the queries I ran were for records that were current. An index on the bit field definitely enhanced the performance of my queries considering there were only about 20,000 current invoices.
January 26, 2009 at 4:31 pm
ASCII silly question, get a silly ANSI.
Able: "Why do the Britts always say 'Ten minutes Time'? Are there any other kind of Minutes?"
Cain: "Define the word 'parsec'."
Able: "Ah, now I get it."
ATBCharles Kincaid
January 27, 2009 at 2:14 am
Good article, as usual, Jeff 🙂
You sure know how create these long threads 😉
p.s. Keep banging that drum, they'll either get it or go deaf :hehe:
Far away is close at hand in the images of elsewhere.
Anon.
January 27, 2009 at 6:24 am
Thanks David... the article was first published over a year ago. Seems like some different folks jumped in this time and dang near doubled the length of the comments. Heh... odd part is, this second wave of comments started because of a totally different article I also wrote about a year ago. Then, the ANSI folks jumped in and all hell has broken loose. Some pretty good conversations going on, so far... just a little flame here and there.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 27, 2009 at 7:09 am
This is a nice case:
Someone called the fire depatement to help on this situation:
They have a nice x64 4proc (each 4 cores) going totally somewhere I didn't want it to go, because of RBAR queries.
Bizar thing about it is that the same lovely query (5 unions joining 4 to 5 tables each) sometimes completed in 6 minutes, and sometimes needed more than 3 hours. (isolation level read uncommitted).
That may be a 'living' content issue.
I've optimized it a little and now it is consuming 2 minutes.
One of the critical things were correlated subqueries.
Altering them into a join solved this behaviour.
Thank you once again ..... 😀
SELECT DISTINCT
'OFU22 - Shipped' as Rules,
getdate() as 'Check Date',
'Yes' as 'Blocking',
CM.ref_commande_fournisseur as 'Supplier Ref.',
LC.num_poste_fournisseur as 'Supplier Item',
LC.statut_ligne_cde as 'Status',
CL.raison_sociale as 'Sold To Name',
CL.id_business as 'Sold To',
LC.code_fournisseur as 'Managing Plant',
LC.code_site as 'Finishing Line',
'' as 'Batch',
LC.date_reception as 'Last update',
LC.ref_commande_fournisseur_2 + '/' + LC.num_poste_fournisseur_2 as 'Mill Order Reference'
FROM fcs_ligne_commande LC
inner join fcs_commande CM
on LC.id_commande = CM.id_commande
inner join boc_client CL
on CL.id_boc_client = CM.id_boc_client_donneur_ordre
WHERE LC.qte_exp is not null
AND ( select sum( CO.poids_brut)
from fcs_colis CO
where CO.ref_commande_fournisseur = CM.ref_commande_fournisseur
and CO.num_poste_fournisseur = LC.num_poste_fournisseur
and CO.statut_colis in ( '08' )
) <> LC.qte_exp
AND LC.statut_ligne_cde in ( '01', '05' )
Altered to
SELECT distinct
'OFU22 - Shipped' as Rules,
getdate() as 'Check Date',
'Yes' as 'Blocking',
CM.ref_commande_fournisseur as 'Supplier Ref.',
LC.num_poste_fournisseur as 'Supplier Item',
LC.statut_ligne_cde as 'Status',
CL.raison_sociale as 'Sold To Name',
CL.id_business as 'Sold To',
LC.code_fournisseur as 'Managing Plant',
LC.code_site as 'Finishing Line',
'' as 'Batch',
LC.date_reception as 'Last update',
LC.ref_commande_fournisseur_2 + '/' + LC.num_poste_fournisseur_2 as 'Mill Order Reference'
FROM fcs_ligne_commande LC
inner join fcs_commande CM
on LC.id_commande = CM.id_commande
inner join boc_client CL
on CL.id_boc_client = CM.id_boc_client_donneur_ordre
inner join ( /* 324732 rows in total */
select ref_commande_fournisseur, num_poste_fournisseur, sum( poids_brut) as sum_poids_brut
from fcs_colis
where statut_colis = '08'
group by ref_commande_fournisseur, num_poste_fournisseur
) CO
on CO.ref_commande_fournisseur = CM.ref_commande_fournisseur
and CO.num_poste_fournisseur = LC.num_poste_fournisseur
and CO.sum_poids_brut <> LC.qte_exp
WHERE LC.qte_exp is not null
AND LC.statut_ligne_cde in ( '01', '05' )
Altering the distinct into a group by didn't change the behaviour, so for the moment that will just be an extra comment.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 28, 2009 at 11:56 am
Why do the Britts always say 'Ten minutes Time'? Are there any other kind of Minutes?"
A minute amount of time might be defined as ten minutes according to the minutes of the meeting. And a small lizard might be my newt....sorry, I'll get my coat.
January 28, 2009 at 12:08 pm
David.Poole (1/28/2009)
Why do the Britts always say 'Ten minutes Time'? Are there any other kind of Minutes?"
A minute amount of time might be defined as ten minutes according to the minutes of the meeting. And a small lizard might be my newt....sorry, I'll get my coat.
Heh... here... let me get it for you... door, too! 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
February 3, 2009 at 9:59 am
Not that I want to complain, but does anyone see a problem with a company that would retain a person that consistenly produces code like this:
DECLARE @Count INT
SELECT @Count=MIN(EID) FROM Es(NOLOCK) WHERE ProjectID=@ProjectID AND Scanned=0
WHILE @Count IS NOT NULL
BEGIN
EXEC up_CalculateEPoints_Insert @Count
SELECT @Count=MIN(EID) FROM Es(NOLOCK) WHERE ProjectID=@ProjectID AND Scanned=0 AND EID > @Count
END
Please note that is would be on a table that in most cases contains more than 4 million rows, when confronted the responce was that "well at least I did not use cursors!" My thought is that cursors should have far better performance, but I have not the time and desire to confirm that point.
February 3, 2009 at 10:07 am
LeeBear35 (2/3/2009)
Not that I want to complain, but does anyone see a problem with a company that would retain a person that consistenly produces code like this:
DECLARE @Count INT
SELECT @Count=MIN(EID) FROM Es(NOLOCK) WHERE ProjectID=@ProjectID AND Scanned=0
WHILE @Count IS NOT NULL
BEGIN
EXEC up_CalculateEPoints_Insert @Count
SELECT @Count=MIN(EID) FROM Es(NOLOCK) WHERE ProjectID=@ProjectID AND Scanned=0 AND EID > @Count
END
Please note that is would be on a table that in most cases contains more than 4 million rows, when confronted the responce was that "well at least I did not use cursors!" My thought is that cursors should have far better performance, but I have not the time and desire to confirm that point.
Looks like an opportunity to train someone in a better way to do something, not a reason to "get rid of" someone. Now, if such training and mentoring has been provided and they just don't get "it", that would be different.
Counterpoint -- who wants to work for a company that looks for reasons to get rid of people they feel can't do the job because no one has taken the time to show someone a better way to do something?
Viewing 15 posts - 226 through 240 (of 255 total)
You must be logged in to reply to this topic. Login to reply