Re:SQL performance problem

Hello, I've a performance problem. In my database there are about 800 000 records in the biggest table. I need count distinct records in this table joined with another. And I have a dramatic decrease of performance: without 'distinct' it is counted about 2 min, but with 'distonct' about 8 min. Does anybody have an idea what should I do? Thanks Anna

Interesting....A couple of questions: What version of IDS are you running? Do the queries return the same number of rows? If not, then is the join correct? Are the times reproducible? Maybe the distinct SQL is benefiting from the data getting cached. Have you recently run update statistics? select nrows from systables where tabname="the 800k row table name" select constructed from sysdistrib where tabid=(select tabid fromsystables where tabname in (the tables); If nrows comes back 0 or way less than 800k, then that's a BIG problem. If there are not rows or the constructed date is old, then we have another problem. What are the explain plans? Are they different? You could always use directives to force the non-distinct SQL to use the distinct sql's plan. Ray Maciej Zygmunt wrote: > > Hello, > I've a performance problem. > In my database there are about 800 000 records in > the biggest table. > I need count distinct records in this table joined with another. > And I have a dramatic decrease of performance: without 'distinct' > it is counted about 2 min, but with 'distonct' about 8 min. > > Does anybody have an idea what should I do? > > Thanks > Anna