Any SQL experts here?

SpaceGirl wrote:
> Jerry Stuckle wrote:
>> Andy Dingley wrote:
>>> On 6 Feb, 19:25, Jerry Stuckle <jstuck....DeleteThis@attglobal.net> wrote:
>>>> John Bokma wrote:
>>>
>>>>> A backup is not going to help you with data that gets mangled
>>>>> thanks to
>>>>> misunderstanding, it's just backed up. And repairing the data when you
>>>>> finally discover that IDs are out of order, is either hard or
>>>>> impossible
>>>>> to fix.
>>>> Sure it will. Just reload the backup from before the problem happened.
>>>> It happens daily - not necessarily due to mangled data, but disk
>>>> crashes and the link.
>>>
>>> You really don't have any big-system experience, do you?!
>>>
>>
>> To start with, 8 years of mainframes while working for IBM. Some
>> awful big databases out there.
>>
>
> Different scale. You don't need that kind of industrial engineering for
> most web apps.
>

Not necessarily. There are a number of huge databases on the web. Some
of the biggest I know of are the airlines (even big back in the 80's).

But terabyte databases are not at all unknown on the web nowadays. Not
on smaller sites - but many of the large resellers have them.

For instance, one that I use regularly is Petco. I don't know the
internals of their database - but it's gotta be huge. And it's online.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex.DeleteThis@attglobal.net
==================
 >> Stay informed about: Any SQL experts here? 

Jerry Stuckle wrote:

> Or come the end of daylight savings time when the entire system goes back one hour?

I was just referring to a basic rule for databases:
that the structure of tables is very important, and if there are
problems
check if the tables structure can be improved...
For example a column with an easy to set and control
id variable could be a solution to this problem of correlated tables.

The database should give some failure warning for
inserting rows with duplicate values in a primary key column,
it is a good idea to check this when inserting values in tables.

I should not have written time as a suggestion for
unique values, I should have clarified that I meant
as a suggestion something including time, like
time plus a random string and maybe other things as well.
 >> Stay informed about: Any SQL experts here? 

Jerry Stuckle <jstucklex DeleteThis @attglobal.net> wrote:

> Or, you can ask in the correct newsgroup (in this case
> comp.databases.mysql) and get good answers.

One might get similar replies to mine .

> I don't think asking is bad.

Something as fundamental as that? IMNSHO yes.

> But perhaps asking in a more appropriate
> group would be better.

Yup, you probably don't learn much Japanese by visiting a Sushi bar ran by
two Italian guys.

[..]
> Who said this was for a real project?

Nobody, hence how I wrote my replies so far.

[..]
> Hiring good people can be expensive, also. But I agree that hiring
> cheap people is *more* expensive.

Yup.


--
John Bokma http://johnbokma.com/
 >> Stay informed about: Any SQL experts here? 

On 6 Feb, 23:28, Jerry Stuckle <jstuck... RemoveThis @attglobal.net> wrote:
> Andy Dingley wrote:
> > On 6 Feb, 19:25, Jerry Stuckle <jstuck... RemoveThis @attglobal.net> wrote:
> >> John Bokma wrote:
>
> >>> A backup is not going to help you with data that gets mangled thanks to
> >>> misunderstanding, it's just backed up. And repairing the data when you
> >>> finally discover that IDs are out of order, is either hard or impossible
> >>> to fix.
> >> Sure it will. Just reload the backup from before the problem happened.
> >> It happens daily - not necessarily due to mangled data, but disk
> >> crashes and the link.
>
> > You really don't have any big-system experience, do you?!
>
> To start with, 8 years of mainframes while working for IBM. Some awful
> big databases out there.

Did they let you see any of them?

Someone who claims to have "experience" as a DBA, yet hasn't come
across a "creeping corruption" problem where the backups back to the
year dot have been gradually polluted by the problem before anyone
noticed, just isn't all that experienced.
 >> Stay informed about: Any SQL experts here? 

On 6 Feb, 21:00, SpaceGirl <nothespacegirls....DeleteThis@subhuman.net> wrote:

> Plus... DBA's are really expensive to hire!

Not as expensive as _not_ hiring a good DBA when you need one!
 >> Stay informed about: Any SQL experts here? 

On 6 Feb, 18:25, Jerry Stuckle <jstuck... DeleteThis @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.


> 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.


> > 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

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.


> 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.

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.

> 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.

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.


> No, I haven't benchmarked GUIDs themselves.

So you're talking straight out of your backside.

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.

> 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.


> 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.

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.


> 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?
 >> Stay informed about: Any SQL experts here? 

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.

> 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.


> 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).

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



> >> 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!

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/>


> 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.

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


> 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.

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).


> 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?

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



> >>> 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?

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.


> 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.
 >> Stay informed about: Any SQL experts here? 

Jerry Stuckle wrote:
> Or come the end of daylight savings time when the entire system goes back one hour?

The solution to that is UTC, which doesn't change with the seasons.

--
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk

The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.
 >> Stay informed about: Any SQL experts here? 

Dylan Parry wrote:
> Jerry Stuckle wrote:
>> Or come the end of daylight savings time when the entire system goes back one hour?
>
> The solution to that is UTC, which doesn't change with the seasons.
>

If your server is set to run UTC. Many, however, run local time for
various reasons.

And it still doesn't take into consideration time adjustments.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex RemoveThis @attglobal.net
==================
 >> Stay informed about: Any SQL experts here? 

mynameisnobodyodyssea.TakeThisOut@googlemail.com wrote:
> Jerry Stuckle wrote:
>
>> Or come the end of daylight savings time when the entire system goes back one hour?
>
> I was just referring to a basic rule for databases:
> that the structure of tables is very important, and if there are
> problems
> check if the tables structure can be improved...
> For example a column with an easy to set and control
> id variable could be a solution to this problem of correlated tables.
>
> The database should give some failure warning for
> inserting rows with duplicate values in a primary key column,
> it is a good idea to check this when inserting values in tables.
>
> I should not have written time as a suggestion for
> unique values, I should have clarified that I meant
> as a suggestion something including time, like
> time plus a random string and maybe other things as well.
>
>
>

Or, just an automatically incremented column. It's much easier.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex.TakeThisOut@attglobal.net
==================
 >> Stay informed about: Any SQL experts here? 

Andy Dingley wrote:
> On 6 Feb, 23:28, Jerry Stuckle <jstuck....TakeThisOut@attglobal.net> wrote:
>> Andy Dingley wrote:
>>> On 6 Feb, 19:25, Jerry Stuckle <jstuck....TakeThisOut@attglobal.net> wrote:
>>>> John Bokma wrote:
>>>>> A backup is not going to help you with data that gets mangled thanks to
>>>>> misunderstanding, it's just backed up. And repairing the data when you
>>>>> finally discover that IDs are out of order, is either hard or impossible
>>>>> to fix.
>>>> Sure it will. Just reload the backup from before the problem happened.
>>>> It happens daily - not necessarily due to mangled data, but disk
>>>> crashes and the link.
>>> You really don't have any big-system experience, do you?!
>> To start with, 8 years of mainframes while working for IBM. Some awful
>> big databases out there.
>
> Did they let you see any of them?
>

Just a few multi-terabyte ones - which was big in those days, even for
mainframes.

> Someone who claims to have "experience" as a DBA, yet hasn't come
> across a "creeping corruption" problem where the backups back to the
> year dot have been gradually polluted by the problem before anyone
> noticed, just isn't all that experienced.
>

Nope. We had controls and test procedures on the programs - including
those which accessed databases. Additionally, if something would have
happened, daily reports would have caught it quite quickly.

When you're running thousands of database updates per hour, even a
single day's worth of work lost is expensive.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex.TakeThisOut@attglobal.net
==================
 >> Stay informed about: Any SQL experts here? 

On Feb 7, 12:01 am, John Bokma <j... RemoveThis @castleamber.com> wrote:
> SpaceGirl <nothespacegirls... RemoveThis @subhuman.net> wrote:
> > Books are great. Practical experience is always better.
>
> If you have no clue what you're doing it's hard to get experienced in it.
>
> --
> John Bokma http://johnbokma.com/

Yes. But I'm not new to SQL. If I'd never used SQL in my life, then,
fair enough.
 >> Stay informed about: Any SQL experts here? 

On 7 Feb, 15:39, SpaceGirl <nothespacegirls... RemoveThis @subhuman.net> wrote:

> I'm more interested in experimentation, and I'm happy to learn from my
> mistakes if it blows up in my face.

Wrongly-designed databases don't usually "blow up", they smoulder
slowly. Then at some future point, they burn through your hardware
budget and bottom line. The mistakes often don't become obvious from
the outside until it has become _very_ expensive to fix them.

I don't think I've ever personally seen a software fault as expensive
as faults of database design. Their design becomes so embedded in a
business, so critical, yet so difficult to fix, that no other piece of
software has the capability to cause such expensive havoc.
 >> Stay informed about: Any SQL experts here?