Re: sql performance question

Brad,

On Tue, Mar 18, 2008 at 3:57 PM, Bradley V. Stone <bvstone@xxxxxxxxxxxx> wrote:

did you try changing the order? Possibly the second time it's reusing a DP.

It didn't matter what order I ran them in. I could run both, one
right after the other, several times in a row, the select * always ran
faster than the other - at least - and this may mean something - it
displayed the first page worth of records faster.

I did not test paging through the file to see which one paused longer
between pages, so that may be the issue - it waited till after
gathering more records on the select fld,fld,fld than the * ?

...testing one last time, I ran the first one and paged down 10 times
without regard to response, and did the same thing with the second
one.

It was relatively the same amount of time for both to finally display
the 10th page of data, so that must be the deal - the first one just
displayed the first page quicker than the second one, while the second
one waited till several pages had loaded before displaying the first
page -

just guessing off the top of my head, maybe they were both probably
filling a fixed buffer size of data before displaying - the select *
filled that buffer more quickly than the select fields?

> > > > -----Original Message-----
> > > > Subject: sql performance question
> > > >
> > > > Why would the last of the following two SQL statements
> run ten times
> > > > slower than the other?
> > > >
> > > >
> > > > SELECT *
> > > > FROM BLORDLP A
> > > > JOIN BLORDDP B
> > > > ON ODORD# = OLORD#
> > > > AND ODLINE = OLLINE
> > > > WHERE OLSTTS <> ODSTTS
> > > >
> > > >
> > > > SELECT ODORD#, ODLINE, ODSTTS, OLSTTS
> > > > FROM BLORDLP A
> > > > JOIN BLORDDP B
> > > > ON ODORD# = OLORD#
> > > > AND ODLINE = OLLINE
> > > > WHERE OLSTTS <> ODSTTS
> > > >
> > > > The only difference being the first selects all fields and runs
> > > > immediately, the second only selects 4 fields and takes forever to
> > > > run.