Query running slower on new server

[DaPi]

Tamil, that depends how (block)-selective "WHERE typeid <> 0" is. My [guess] above is that it isn't selective.

I did wonder about creating an index with EVERY column referenced in the query - do it with an IFFS . . . . !

[tamilselvan]

Your guess may be right or wrong.
We do not know selectivity of the column (or even block selectivity). I do not agree with you for creating an index on all columns used in group by clause + columns in select.
If you do it, then the index will become big, that leads to space wastage.

Tamil

[DaPi]

Hi Tamil,

Originally posted by tamilselvan
Your guess may be right or wrong.
We do not know selectivity of the column (or even block selectivity).

So might yours! I'd do a quick "count(*) where type_ip <> 0" to get a feel of it before creating and analyzing indexes.
(teriyaki probably knows the answer).

Originally posted by tamilselvan
I do not agree with you for creating an index on all columns used in group by clause + columns in select.

If you do it, then the index will become big, that leads to space wastage.

Sorry, British humour - this was not really a serious suggestion.