Any SQL experts here?

Andy Dingley wrote:
> On 6 Feb, 19:23, Jerry Stuckle <jstuck....DeleteThis@attglobal.net> wrote:
>
>>> Whatever. Whoever the guilty party is, mysql_insert_id() (callable
>>> from PHP) isn't an exact facade for LAST_INSERT_ID() (not callable
>>> from PHP)
>> You should check your facts better, Andy. SELECT LAST_INSERT_ID() works
>> fine in PHP.
>
> It works fine "from" PHP, but not "in" PHP. It's a SQL function, not
> something that's callable directly as a PHP statement. You need to
> pass it to a driver and have some SQL engine execute it, not call it
> directly.
>

Running out of arguments so you have to pick minor points in words, Andy?

>> And they are exactly the same. Just one is a little more
>> efficient than the other (no need to parse the SQL).
>
> They're not the same (this is rather my point), read their
> documentation. There are two differences in their interface. Interface
> facades that are "similar" rather than "equivalent" are always a good
> source of future bugs.
>

No, they are exactly the same. They return exactly the same thing.
Both are well documented. It's just two different ways to get to the
same end.

>
>> The simple answer is - use good coding practices.
>
> Part of good coding practice is to use or design tools that assist,
> not set traps for you. PHP _can_ be coded well (most of MediaWiki's
> innards), but more usually it's an insecure bag of nails (most
> amateurs' sites, or the innards of PhpBB).
>

Yep. And that's why I use PHP. But the language is not responsible for
poor coding habits.

But tools do not encourage good coding practices. In fact, in some
cases they discourage them. No coding tool I know of, for instance,
will prevent SQL injection attacks (from any language and with any
database). But those tools can give you a false sense of security.
OTOH, a good design can show those possibilities. And peer review makes
it even more reliable.

> Using GOTO isn't the bad thing (it's usually all you've got),
> choosing languages that force you to use it is.
>

In the last 25+ years, the only language I've used that "forces" me to
use it is assembler (both IBM and Intel platforms). And I don't think
outside of those I've ever used a goto in that time.

>
>
>>>> In fact,
>>>> if you want to use ANY database with ANY language, you have to compile
>>>> that support in.
>>> I have to compile in some support for an abstract DB. I shouldn't have
>>> to compile in different support for each instance of a server
>>> platform.
>> And how do you access Oracle, MS SQL and DB2 from your "abstract DB"
>> without the appropriate libraries? ODBC? So you need to compile in the
>> ODBC library code.
>
> ODBC (or JDBC, or whatever) is the abstract driver interface and I've
> already included support for it. I don't have to recompile my core
> just to switch the DB back-end platform!
>

Actually, if you use shared libraries or dll's properly, you don't have
to recompile your code, either. Just load the appropriate library.

> As I'm mostly coding in either Java or Python, the idea of
> "recompiling cores" just to add features strikes me as ridiculously
> obsolete anyway. Haven't these people heard of dynamic loading?
> <http://xkcd.com/353/>
>

Sure. See above. But dynamic loading also has its disadvantages - i.e.
they require more memory and Os calls to access the DLL.

>
>> That's true. But you still have to compile in the ODBC code. And not
>> only is it slower than native access to any of the databases, you are
>> also limited to the least common denominator across all databases. You
>> can't take advantage of the database-specific features.
>
> You missed out that "Java is slow because it uses a JVM" too.
>

Yes, it suffers from the same problem as SmallTalk. But that isn't the
subject here.

> You've posted so many red herrings this week you could open a
> fishmongers.
>

Running out of arguments, are you?

>
>> And BTW - have you looked at the cost of the MS SQL ODBC driver for
>> Linux? $1,500 just to access the database.
>
> Free, you just need to look harder at where you get them from.
>

Ah, but you want the REAL MS drivers. That way you know they work!

> Mind you, I used to do Java servlet work under IIS / W2K with MS SQL
> as a back-end. I was spending a _fortune_ on the Merant drivers (JDBC
> to SQL Server), because there was a gap in the market that no-one felt
> like filling for free. This can be a real problem and cost, I just
> don't think it is any more (now I'm using a freebie JTDS driver).
>

I though you only use MS drivers.

>
>> So you send the SQL to another system, parse it to see what needs to be
>> done, perform the operation, then send the data back across the link.
>> Seems to be an awful lot of overhead.
>>
>> My programs call the database directly across the link. That's one less
>> thing which needs to be done, and one less thing which can go wrong.
>
> "one less thing which can go wrong", such as duplicating SQL language
> parsing in your client platform?
>

And who is doing that? When using remote databases, the parsing is done
on the server.

> For someone with "years of database experience", you still seem a bit
> flakey on the core concepts of a client-server DB platform.
>
>

Nope. I just don't agree with your "analysis". I showed this thread to
a couple of DBA friends of mine (they have some 500tb+ databases). They
just groaned because they've had programmers who thought like you and
had to be retrained.

>
>>>>> What's _wrong_ with using LAST_INSERT_ID() insert some neatly packaged
>>>>> SQL module, then throwing the value away afterwards because it really
>>>>> isn't necessary to know it afterwards? That's good design, according
>>>>> to "modular programming" concepts that have been regarded as a basic
>>>>> starting point for good design for 30-some years now.
>>>> Why even use it if you're just going to throw it away?
>>> Because the DB has already stored it. However the application code no
>>> longer needs the value. In most cases, and where primary keys used for
>>> foreign keys are anonymous sequences rather than visible SSNs etc,
>>> then this is just as it should be. Locate the records through their
>>> descriptive properties, leave the internal IDs inside the DB code as
>>> much as possible. If you absolutely must, retrieve a "reference"
>>> later and leave it alone as an opaque reference the app code doesn't
>>> fiddle with.
>> True - but you need to fetch the insert id to add rows to the related
>> tables.
>
> Assuming we only ever add rows to the foreign-keyed table once (for
> this key value), why should I ever need to "fetch" anything? In
> particular, why should I need to fetch it across a network linik
> between servers, back to the client app?
>

Since you only do it once, there's very little overhead to accessing the
key. But your primary key is compared millions of times (or, in the
case of a busy database, potentially billions of times every day).

> This key value stays inside the SQL, stays inside the SQL server and
> ought to stay inside a transaction too. If you can do it right (for a
> single append statement to both tables at once) then you don't even
> need to handle this value explicitly as a variable in your SQL source
> code.
>

You can. But what if you have to add 20 rows to 5 different tables?
Let's see you do all of that in one statement - and have that statement
understandable.

I would say that when I'm adding to relational tables, I'm updating at
least three tables over 1/2 the time. Often more.

And updating multiple tables in one SQL statement makes it more
difficult to analyze and correct errors should one of the updates fail.

>
>> OO is a completely new
>> concept. And MVC may have been talked about, but was seldom used 30
>> years ago.
>
> Maybe "OO is completely new" to you Jerry, but both OO and MVC have
> been around since the mid-70s. They weren't commonly seen, but they
> sure were talked about a lot (just read Byte from the early '80s).
> I've been using MVC myself in mainstream M$oft code since Windows 3.0,
> 18 years ago, not from any radical forward-looking, but just because
> that's how the standard tools worked.
>
>

You said 30 years ago, which was in the late 70's, and OO was new then.
There were no OO languages. But I'll bet you can't even name the
first OO language without looking it up on the internet (hint - it was
NOT java).

MVC wasn't even though about back then.

And if you think Byte was the latest in technology, then I understand
where you got many of your misunderstandings.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex.DeleteThis@attglobal.net
==================

Andy Dingley wrote:
> On 6 Feb, 18:25, Jerry Stuckle <jstuck....RemoveThis@attglobal.net> wrote:
>
>> There is no way
>> members of a bounded set can be *guaranteed* unique.
>
> Of course it can! As soon as you start applying bounds you make it
> very easy to guarantee uniqueness (NB this is a bound on the overall
> domain, not just the cardinality of its members). There's a limit in
> how many of them you can have, but that's big enough for us to live
> with.
>

OK, here's your bound set:

{1, 2, 3}

I need 4 unique values.

>
>> Eventually you will run out of numbers. Then what do you do?
>
> Invent something better. This, along with the Moon running to a
> standstill and the heat death of the universe, is a very real problem,
> it's just so far off that it's not a problem I have to worry about.
>

It can be a real problem with GUID's, also. All you need is one
collision to corrupt a database.

>
>>> Can't afford 128 bits? You must be paying your developers awfully
>>> cheaply if that's a remotely good trade-off.
>> Let's see.. 128 bits is 16 bytes. An integer is 4 bytes.
>
>> Every computer I've ever seen uses bytes.
>
> Get yourself some bigger integers. If you're juggling TB data, a
> processor word should 64 bit by now, not 8
>

This has nothing to do with words, Even 64 bit processors use bytes.
It's just that they can operate on 8 bytes at at time.

> Secondly, this is a linear scaling factor (and arguably not a big
> one). I don't sweat the linear stuff when scaling, I'm worrying about
> the exponentials first.
>

Rather, you should be looking at the performance bottlenecks. Linear
scaling may actually be worse than exponential, if it's used more often.

>
>> Let's look at a table with 10M rows. You will have 160M bytes just for
>> your key field. I will have 40M bytes. At 100M rows, that increases to
>> 1.6B bytes for you and 400M bytes for me. That 1.2B bytes can be
>> important, especially if you are restricted by the OS to a 4GB table.
>
> The odd gig here and there just isn't a problem today. I admit I've
> never tried to put 100M rows through MySQL, or M$oft Access for that
> matter.
>

Ah, but it is very important. And since you just admitted you've never
worked with big databases, you really don't know, do you?

Not only does it require more memory to handle the tables and indexes,
it requires more reads just to get to the data. That's a potential
performance problem.

> For that matter, big databases don't even count "rows". If you mean a
> _BIG_ database these days, you're talking about OLAP, hypercubes and
> datawarehousing, not old-tech RDBMS and SQL.
>

Yes, they do. Every dba on big systems looks at the number of rows in
the tables. It's one of the considerations that they use when designing
large systems.

>> In short, your method of using GUID's is not very scalable.
>
> First, it's not my method. It's a good method, so I'd like to claim
> credit for it, but I can't. I just learned it by reading Kimball.
>

It's very poor method.

> Secondly, tell _him_ about it. One of the major recognisable names
> around in DB theory and architecture, his word does tend to carry more
> weight than yours or mine.
>

Maybe recognizable to you. The two dba's I mentioned in the other
thread (who are very well known in their circles and attend major
international conferences every year) never heard of him.

>
>> No, I haven't benchmarked GUIDs themselves.
>
> So you're talking straight out of your backside.
>

Nope. But I have benchmarked non-integer vs. integer keys.

> I _have_ benchmarked GUID performance for foreign keys, I've done it
> for GB-sized DBs, I've done it in blue-chip research labs, I've
> published the results, I've been invited to speak internationally on
> those results, and I've been awarded patents on work derived from
> those results. Improvements to efficient architectures for RDF triple
> stores, if you care.
>

OK, try this one. Set up a table with 100M rows. Have a second table,
also with 100M rows. Use GUID's as keys.

Now create a many-to-many link table between the two with say 5 random
links between the two tables.


Now create a loop which pulls one value at random (not by the key) out
of the first table and all of the related rows in the second table (via
the link table). Repeat 100K times. How long did it take?

Repeat, only using integer keys.

In both cases, you are allowed to tune the performance of the database
to give best results.

>> I could care less about your patents, Andy.
>
>
>
>
>> I've worked on some very large databases over the years - even
>> mainframes with > 100M entries. The size of the key used to
>> cross-reference tables is very critical - as anyone with experience in
>> large databases will tell you.
>
> Of course it is. 128 bits is manageable on today's hardware. Stop
> thinking we're still loading things off cards, like you used to for
> your years of experience.
>

You must be really running out of arguments, Andy. More degrading
comments instead of facts.
>
>> A lot of people come to me for advice,
>
> Business is so good that Maryland wound you up for non-filing? That
> must be a business that's _so_ busy you don't even get time to run it.
>

ROFLMAO! You think you know what you're talking about. But you're just
trolling again.

> Besides which, you lost a contract from a FTSE100 for DB2 developer
> training just recently because of your attitude. I know, I was the one
> resourcing it.
>

Nope. I wouldn't have accepted you anyway. Do you have at least 500
developers needing to be trained on DB2? Those are the customers I work
with.

But then, had you called me, I would have turned you down anyway.

>
>> And ANSI disagrees with you. As do IBM, Microsoft, and other major
>> RDBMS players. Otherwise they could have implemented an automatic GUID
>> creation in their database. But they didn't.
>
> Check again, they have. What's (MS') NEWID()? Chopped liver?
>

OK, I wasn't aware NEWID() was the GUID. But then MS never was very
good at performance.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex.RemoveThis@attglobal.net
==================