Classics Forum

RE: SERIAL to INTEGER key joins vs. CHAR to CHAR.

Posted By: Ian Michael Gumby
Date: Tuesday, 9 March 2010, at 12:31 p.m.

In Response To: Re: SERIAL to INTEGER key joins vs. CHAR to CHAR. (Art Kagel)

Uhm...

Art, you're trying to fix a brain dead solution.
Hint: The patient is dead from the get go.

Frank,

First, what's the number of customers in the database?
10,100,1000, 10K,100K?
What's the number of loans per customer?

(For all we know this could be a paychex system where we're tracking paycheck loans...)

You still want to use the cust_id to loan_id join and indexing.

You want speed, you still have a couple of other tricks using 4GL/C and prepared cursors on looking up customers.
But that's besides the point.

Unloading and then reloading your master table of customers is completely brain daid. Yes, I said 'daid' instead of dead.

As Art pointed out there's a lot missing from your story and what's missing is important when you want to think about solutions.

Or you could run with a typical OTC response... "Have you tried UPDATE STATISTICS?" "Oh, you have? Well try it again... " ;-)

-G

> To: classics@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: SERIAL to INTEGER key joins vs. CHAR to CHAR. [3526]
> Date: Mon, 8 Mar 2010 19:25:31 -0500
>
> Wait,is this Standard Engine (SE)? You didn't mention you are using
> Standard Engine! I suppose if we all looked back at your previous post we
> might have guessed that (since you didn't mention it them except by
> inference), but I for one don't do that. I don't have time. You REALLY
> should post your engine and platform details when you first post a question.
>
> If so, then ignore what I wrote about UPDATE STATISTICS HIGH and MEDIUM,
> that only applies to Informix Dynamic Server. However, all of the comments
> everyone said about CLUSTER indexes is still true. Standard Engine (nor IDS
> for that matter) does NOT maintain your clustered indexed table in sorted
> order after the initial clustering. The ONLY reason that data is being
> returned in sorted order is that SE is using the index to find the records.
> It is using a key lookup to find the first matching row (if there is a
> filter) and then scanning across the index leaves which returns the keys and
> rowids in sorted order by the index or cluster key. Since the index was
> initially clustered, the majority of rows are right next to each other on
> disk so access is very fast. Newly added rows will force additional disk
> reads, but each read will pull multiple rows into memory so it will not
> affect performance until a significant percentage of the rows are no longer
> clustered. The exact percent depends on the number of rows that fit on page
> on disk. (Well, SE doesn't really organize data in pages on disk, but the
> IO buffer it uses to read a block of rows into memory works the same way.)
>
> By the way, unless you include an ORDER BY clause, the order of returned
> rows, EVEN WITH A CLUSTERED INDEX, is not guaranteed! Posting this
> application to IDS someday where the optimizer is smarter and is likely to
> ignore the clustered index if there is no ORDER BY clause (and sometimes
> even if there is one) is likely to break your application because the
> optimizer may be able to retrieve the data faster using a different index or
> sort it faster than it can be fetched using the index, even in leaf scan
> mode.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KS
> www.iiug.org/conf
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Mon, Mar 8, 2010 at 3:08 PM, FRANK ROTOLO <frank.pr@msn.com> wrote:
>
> > I am using INFORMIX-SQL (perform, ace).
> > My cluster indexes seem to maintain the rows physically ordered in the .dat
> > files because after adding new customers or loans, I query those tables and
> > the newly added customers appear to be in sorted order.
> >
> >
> >
> >
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --0015173fe862d9bf170481533738
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>

_________________________________________________________________
Hotmail: Powerful Free email with security by Microsoft.
http://clk.atdmt.com/GBL/go/201469230/direct/01/

Messages In This Thread

• SERIAL to INTEGER key joins vs. CHAR to CHAR.
  FRANK ROTOLO -- Monday, 8 March 2010, at 12:24 a.m.
  • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
    John Miller iii -- Monday, 8 March 2010, at 1:55 a.m.
  • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
    Art Kagel -- Monday, 8 March 2010, at 6:55 a.m.
    • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
      FRANK ROTOLO -- Monday, 8 March 2010, at 2:57 p.m.
      • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
        Art Kagel -- Monday, 8 March 2010, at 6:51 p.m.
        • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
          FRANK ROTOLO -- Monday, 8 March 2010, at 8:45 p.m.
    • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
      FRANK ROTOLO -- Monday, 8 March 2010, at 3:35 p.m.
      • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
        Jonathan Leffler -- Wednesday, 10 March 2010, at 2:04 a.m.
  • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
    Pankrich Khanthasa-ard -- Monday, 8 March 2010, at 11:01 a.m.
    • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
      Art Kagel -- Monday, 8 March 2010, at 11:46 a.m.
      • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
        FRANK ROTOLO -- Monday, 8 March 2010, at 3:08 p.m.
        • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
          Art Kagel -- Monday, 8 March 2010, at 7:25 p.m.
          • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
            FRANK ROTOLO -- Monday, 8 March 2010, at 9:20 p.m.
          • RE: SERIAL to INTEGER key joins vs. CHAR to CHAR.
            Ian Michael Gumby -- Tuesday, 9 March 2010, at 12:31 p.m.
            • Re: RE: SERIAL to INTEGER key joins vs. CHAR to CH
              FRANK ROTOLO -- Tuesday, 9 March 2010, at 6:18 p.m.
    • RE: SERIAL to INTEGER key joins vs. CHAR to CHAR.
      Zev Berezin -- Tuesday, 9 March 2010, at 3:18 p.m.

Classics Forum is maintained by Administrator with WebBBS 5.12.