RE: a little help with query.

So about 84k records in the table match that profile_token. Not very unique. The query didn't run any faster when I removed the not null clause. So I guess this is a disk type performance issue. My onstat -g iov has the inverted pyramid. Don't know why else it would take so long. ----- Original Message ----- From: "Ian Michael Gumby" >;im_gumby@hotmail.com

On 6 July, 16:25, "Floyd Wellershaus" <fl...@fwellers.com> wrote: > So about 84k records in the table match that profile_token. Not very unique. > The query didn't run any faster when I removed the not null clause. So I guess this is a disk type performance issue. My onstat -g iov has the inverted pyramid.  Don't know why else it would take so long. > > ----- Original Message ----- > From: "Ian Michael Gumby" >;im_gu...@hotmail.com Is the index that it is using fragmented ?

> From: david@smooth1.co.uk > Subject: Re: a little help with query. > Date: Mon, 6 Jul 2009 12:06:19 -0700 > To: informix-list@iiug.org > > On 6 July, 16:25, "Floyd Wellershaus" <fl...@fwellers.com> wrote: > > So about 84k records in the table match that profile_token. Not very unique. > > The query didn't run any faster when I removed the not null clause. So I guess this is a disk type performance issue. My onstat -g iov has the inverted pyramid. Don't know why else it would take so long. > > > > ----- Original Message ----- > > From: "Ian Michael Gumby" >;im_gu...@hotmail.com > > Is the index that it is using fragmented ? He said it wasn't. So with respect to the fragmentation, each fragment will be hit. Is there a performance issue because the index doesn't match the fragmentation? 84K out of 94mil is roughly 0.1% of the records or roughly 1 in 1000 records would be part of this query. Ok, just tossing out some ideas... YMMV... 1) Create a compound index of profile_token and some other field that would make sense and make the record more unique. This might help, it may not. There is definitely going to be a trade off on disk space used and performance. 2) Can you increase the size of your pages? (You did say that you had fat records...) Is 50 seconds really unacceptable performance? Don't get me wrong, you want the query to be as fast as possible, however, you have limitations based on the size of the table (94 million rows), the width of each record, and your I/O bandwidth constraints. HTH -G _________________________________________________________________ Lauren found her dream laptop. Find the PC that’s right for you. http://www.microsoft.com/windows/choosepc/?ocid=ftp_val_wl_290