> and < searches running slowly

[Mr. Bigglesworth]

We have a couple of fields with regular indexes on them:

"create index index01 on table1(field01)"
"create index index02 on table1(field02)"

when I run a search like this with tsql I get a count returned quickly:

select count(MYID) on table1 where field01 > '77787676' and field02 < '81076437'

476 hits in total.

However, when I try to write out the results, it takes five minutes to do so:

select MYID on table1 where field01 > '77787676' and field02 < '81076437'


If I leave out the AND bit and run each term individually, IDs stream on the screen lighting-quick. Can you think of why the AND would slow things down so much here?

We're using Solaris version 4.00.1019027849. Thanks.

[Mr. Bigglesworth]

...naturally, I meant to type "select count from" up there.

[John]

The count and select should be similar, except that the count does not have to actually fetch the rows. If the table is not cached it might be slow, but still shouldn't take minutes.

We'll try here and see if we see something similar. An index on table1(field01, field02) would help as well.

[John]

When you to the select MYID with the AND do the results come back slowly, or does it take a long time, and they then all come back?

[Mr. Bigglesworth]

It takes 10-15 seconds, then a single MYID (which is a unique indexed field, for what it's worth) appears.

After that, we're looking at a minute or two before the other MYIDs show up, but when they do, it's quick. Then, another 30 second delay before the command prompt returns-- like it's still looking for more hits, but doesn't find any?

[John]

The performance of ANDs has been improved since the version you have, so you might try a newer version, otherwise contact tech support directly so they can walk through and try and locate the difference.