slowdown of query using <>0 in sum field

[hedgracer]

I have a query that has a <>0 in a summed number field. The query runs extremely slow (30+ seconds) when this parameter is in this field. If I eliminate this parameter the query runs in milliseconds. This is a query on a linked odbc sql server 2005 table. Any thoughts on the theory of why this happens? Thanks.

Dave

 

[lespaul]

does it matter if it sums a zero? that shouldn't change the total....

1+2+3 = 6
1+2+0+3 = 6



Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[hedgracer]

This is actually a theory question. The query in question yields 294 rows with <>0 and 296 rows without <>0 so the omission is no big deal. I just wondered why the slowdown happened in the first place.

Dave

 

[lespaul]

any time you use a not equals function it will slow down the query....

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[cmmrfrds]

Are you running some kind of timer or are you just looking visually when the data starts to return to the screen?

If you are looking visually, then an non-aggregate query will start returning records when a threshold is reached, say 100 records. An aggregate query has to aggregate all the records before any can be returned to the client.

 

[CaptainD]

gmastro posted a response a while back that talked about making a query "sargable"

This site explained it pretty well and might help you in designing queries.

http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbugen9/00000463.htm