Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Finding combinations of values Expand / Collapse
Author
Message
Posted Friday, December 14, 2012 5:11 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 1,963, Visits: 2,898
CELKO (12/14/2012)
.. Total failure.. Besides which, I believe my earlier post was the same idea, except coded correctly .


Yeah, it was awful!

What I wanted to do was avoid the CASE expression solution and go for something using functions that compile to simple assembly language.


DELETE FROM Client_Shares
WHERE COALESCE (SIGN(ABS(lm_id)+1), 0)
+ COALESCE (SIGN(ABS(ml_id) +1), 0)
+ COALESCE (SIGN(ABS(is_id) +1), 0)
+ COALESCE (SIGN(ABS(t_id)+1), 0) >= 2;



Yiikes, that's incredibly convoluted. What a royal pita to try to understand, much less change or debug later!!


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1396840
Posted Friday, December 14, 2012 5:29 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
[ Yiikes, that's incredibly convoluted. What a royal pita to try to understand, much less change or debug later!!


But it is fast and portable

On a more serious note, that kind of function nesting used to be standard idioms in early SQL. If you can find some articles and books by David Rozenshtein, he did a lot of stuff with it to create the Characteristic function, etc.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1396841
Posted Friday, December 14, 2012 5:43 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 1,963, Visits: 2,898
CELKO (12/14/2012)
[ Yiikes, that's incredibly convoluted. What a royal pita to try to understand, much less change or debug later!!


But it is fast and portable

On a more serious note, that kind of function nesting used to be standard idioms in early SQL. If you can find some articles and books by David Rozenshtein, he did a lot of stuff with it to create the Characteristic function, etc.



Yeah, and we used to use punch cards and write our own bubble sorts too. For me to use something that indecipherable, there'd have to be a noticeable performance diff.

As to "portable", that really is a myth. There are so many custom approaches in each variant of SQL you can never port anything easily.


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1396843
Posted Monday, December 17, 2012 6:32 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
CELKO (12/14/2012)
.. Total failure.. Besides which, I believe my earlier post was the same idea, except coded correctly .


Yeah, it was awful!

What I wanted to do was avoid the CASE expression solution and go for something using functions that compile to simple assembly language.


DELETE FROM Client_Shares
WHERE COALESCE (SIGN(ABS(lm_id)+1), 0)
+ COALESCE (SIGN(ABS(ml_id) +1), 0)
+ COALESCE (SIGN(ABS(is_id) +1), 0)
+ COALESCE (SIGN(ABS(t_id)+1), 0) >= 2;


Why overcomplicate it? Why not the solution I posted, that simply uses Where <col> Is Null? Your solution doesn't improve anything on this, makes it impossible for SQL Server to use indexes on the columns, makes it unclear to anyone reading the code what it's supposed to do (you'll need to add a lot of documentation to your version), and seems to be complexity for the sake of "cleverness".

Edit: And, so far as I know, "Where <col> Is Null" is valid in every version of SQL I've ever seen, so your solution isn't even "more portable".


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1397230
Posted Monday, December 17, 2012 7:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
CELKO (12/14/2012)
[quote] .. Total failure.. Besides which, I believe my earlier post

Yeah, it was awful!

What I wanted to do was avoid the CASE expression solution and go for something using functions that compile to simple assembly language.


DELETE FROM Client_Shares
WHERE COALESCE (SIGN(ABS(lm_id)+1), 0)
+ COALESCE (SIGN(ABS(ml_id) +1), 0)
+ COALESCE (SIGN(ABS(is_id) +1), 0)
+ COALESCE (SIGN(ABS(t_id)+1), 0) >= 2;


Yep, and this one is total failure too (OP wasn't interested in code obfuscation)!
Can you understand the simple fact that around 99% of projects/clients/developers/etc. DO NOT NEED PORTABILITY! Especially, as portability of SQL does not exist for now.

If you really want to avoid CASE expression, it can be achieved in much more elegant way:

DELETE t
FROM trefClientShares t
CROSS APPLY (SELECT COUNT(v) nnv
FROM (VALUES (intLMid),(intMLid),(intISid),(intFRid)) v(v)) a
WHERE a.nnv < 2



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1397258
Posted Monday, December 17, 2012 1:29 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
As to "portable", that really is a myth. There are so many custom approaches in each variant of SQL you can never port anything easily.


Then how I have earned a living writing portable SQL for 25+ years? Why does the US Government have FIPS Flaggers? Why did NIST set up the FIPS-127 conformance test suite?

Portable code has to be designed from the start with this goal. Ever read Jerry Weinberg's classic: The Psychology of Computer Programming: Silver Anniversary Edition (ISBN-13: 978-0932633422). Yes, it is so good it gets a Silver anniversary.

Part of the book deals with setting goals and how that affects the code. If you start off like a cowboy coder with "Larry the Cable Guy" philosophy of "Git'er done!", you get code written fast. If you ask for smallest possible code or the fastest execution, you get other kinds of programs.

We know that 80-95% of the total lifetime cost of a system is in maintaining it. DoD, the largest user of software on Earth, found that code that can port from release to release of the same product or to other products saves you tens of billions of dollars.

Some of my consulting work is for VC's who want to know if the applicant's databases can scale up and port (usually to a mainframe, but more and more, we scale down to mobile devices). Sometimes a Venture Capitalist gets a winner! Winners have to grow. The way to grow is to port.

I have killed projects with a bad recommendation. In one case, two start-ups in the same market space, a POS based on a particular chip, needed a decision. One of them had a really neat, flashy interface and used a proprietary language tuned for that chip. We had a lot of these high level, structured assemblers in those days.

The competition was in ANSI Standard C and had a dull interface. They could have lapsed into assembler from the C and gotten a real boost on the particular chip they had, but they stuck to Standard C.

We went with the dull guys. A few months later, the proprietary language and the chip were discontinued. The flashy, non-portable, guys learned one of the laws of ecology. "The better a species is adapted to one environments, the worse it is adapted to all other environments." Or "there are no snakes at the North Pole and no polar bears in the Sahara Desert"



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1397406
Posted Tuesday, December 18, 2012 7:09 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
CELKO (12/17/2012)
As to "portable", that really is a myth. There are so many custom approaches in each variant of SQL you can never port anything easily.


Then how I have earned a living writing portable SQL for 25+ years? Why does the US Government have FIPS Flaggers? Why did NIST set up the FIPS-127 conformance test suite?

Portable code has to be designed from the start with this goal. Ever read Jerry Weinberg's classic: The Psychology of Computer Programming: Silver Anniversary Edition (ISBN-13: 978-0932633422). Yes, it is so good it gets a Silver anniversary.

Part of the book deals with setting goals and how that affects the code. If you start off like a cowboy coder with "Larry the Cable Guy" philosophy of "Git'er done!", you get code written fast. If you ask for smallest possible code or the fastest execution, you get other kinds of programs.

We know that 80-95% of the total lifetime cost of a system is in maintaining it. DoD, the largest user of software on Earth, found that code that can port from release to release of the same product or to other products saves you tens of billions of dollars.

Some of my consulting work is for VC's who want to know if the applicant's databases can scale up and port (usually to a mainframe, but more and more, we scale down to mobile devices). Sometimes a Venture Capitalist gets a winner! Winners have to grow. The way to grow is to port.

I have killed projects with a bad recommendation. In one case, two start-ups in the same market space, a POS based on a particular chip, needed a decision. One of them had a really neat, flashy interface and used a proprietary language tuned for that chip. We had a lot of these high level, structured assemblers in those days.

The competition was in ANSI Standard C and had a dull interface. They could have lapsed into assembler from the C and gotten a real boost on the particular chip they had, but they stuck to Standard C.

We went with the dull guys. A few months later, the proprietary language and the chip were discontinued. The flashy, non-portable, guys learned one of the laws of ecology. "The better a species is adapted to one environments, the worse it is adapted to all other environments." Or "there are no snakes at the North Pole and no polar bears in the Sahara Desert"


Yes, there's a point where portability is the primary factor. Large bureaucracies often need everything to follow published standards in order to get anything done at all. On the other hand, DoD and other large bureaucracies are more infamous for the projects they waste vast amounts of money and effort on and never finish (in some cases due to overemphasis on obsolete standards) than they are famous for getting things done efficiently or even well.

Another point is that, within its proper environment, a specialized organism is often vastly superior to a generalized one. Rats are highly generalized organisms, capable of surviving in a wide range of environments, while methanopyrus (http://en.wikipedia.org/wiki/Methanopyrus_kandleri) is an extremely specialized one capable of surviving only in a very specific environment. But if you put a rat in a pot of boiling sufuric acid, it won't survive as well as some mehanopyrus would in the same pot.

Keep in mind that some extremophile organisms are thought to be essentially unmodified over about 4 billion years. So, long-term survivability of these extremely specialized organisms isn't really a question.

So your point doesn't actually prove your point, if you get my point.

On the other hand, code portability has certainly worked out well for you. You obviously have survived well by becoming a specialized organism in a specialized environment that fits your particular survival adaptations and assertions.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1397781
Posted Tuesday, December 18, 2012 8:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
To SELKO:
GSquared (12/18/2012)

...
So your point doesn't actually prove your point, if you get my point.

On the other hand, code portability has certainly worked out well for you. You obviously have survived well by becoming a specialized organism in a specialized environment that fits your particular survival adaptations and assertions.


I do like your point and the way you pointed it out!

There is actually one interesting example in IT world too.
There was recently one successful start-up IT company in UK, specialising in the Mobile phone games. Their initial idea was to develop games in portable code, so it could be played on different devices without much changes. They were very successful in this idea and they found venture capitalist to finance them. But after initial success and getting finance, they stopped concentrating on code portability, as to develop the best possible product for particular device (eg. OS) is often impossible (or financially unsustainable) without utilising of the proprietary device (OS) features.
With available financial power and relevant expertise, this company found that it's more appropriate and profitable to develops games for each OS separately using the most appropriate proprietor features of the OS. Yeah, they are not government department, so they cannot allow themselves to waste time and money as they are not founded by people taxes...
And on another hand, does Mr. CELKO, by providing the example of some "vendor" software disappearance in "few months time", implies that SQL Server is going to disappear soon and all SQL Server developers should prepare themselves to write code so it can be ported to DB2?




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1397824
Posted Tuesday, December 18, 2012 8:21 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Eugene Elutin (12/18/2012)
To SELKO:
GSquared (12/18/2012)

...
So your point doesn't actually prove your point, if you get my point.

On the other hand, code portability has certainly worked out well for you. You obviously have survived well by becoming a specialized organism in a specialized environment that fits your particular survival adaptations and assertions.


I do like your point and the way you pointed it out!

There is actually one interesting example in IT world too.
There was recently one successful start-up IT company in UK, specialising in the Mobile phone games. Their initial idea was to develop games in portable code, so it could be played on different devices without much changes. They were very successful in this idea and they found venture capitalist to finance them. But after initial success and getting finance, they stopped concentrating on code portability, as to develop the best possible product for particular device (eg. OS) is often impossible (or financially unsustainable) without utilising of the proprietary device (OS) features.
With available financial power and relevant expertise, this company found that it's more appropriate and profitable to develops games for each OS separately using the most appropriate proprietor features of the OS. Yeah, they are not government department, so they cannot allow themselves to waste time and money as they are not founded by people taxes...
And on another hand, does Mr. CELKO, by providing the example of some "vendor" software disappearance in "few months time", implies that SQL Server is going to disappear soon and all SQL Server developers should prepare themselves to write code so it can be ported to DB2?


Not exactly, on the part I added emphasis to.

He's made the point before, more clearly than he did here, that writing "portable code" in SQL, also includes writing code that can easily be ported to the next version of SQL Server.

If you write ANSI/ISO standard SQL, instead of specialized T-SQL extensions, then you can expect that SQL Server 2015 will support your queries, even if you wrote them on SQL Server 2000 originally.

It's another case of being "partially correct". Part of this is that "standards compliant SQL" is actually a moving target, as with other standards, since the standards themselves evolve over time.

Then there's the silliness of some of the standards, compared to what's expected by those who actually use the technology. For example, Truncate wasn't accepted into the standard until 2008. Before that, someone like Joe could go off on you for violating standards if you used Truncate. Since then, it's provable that you actually were complying with the what the standard should have been, you were just ahead of your time.

Innovation depends on violation of accepted practices and standards. But innovation doesn't necessarily imply improvement. Sometimes it does improve things, and sometimes it's just the Edsel (http://en.wikipedia.org/wiki/Edsel).


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1397833
Posted Tuesday, December 18, 2012 8:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
... you were just ahead of your time.


Yeah, I'm always! But "they" don't understand me.
That is common among us, great painters...

█████████████
█████████████
█████████████
█████████████
█████████████
█████████████
█████████████

But, last time it was sold for $1,000,000


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1397850
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse