You're right Set.I use 2 different queries now and works great. I would like
to unify in one query. The query will parse all records anyway to provide
needed records, if I'm not wrong.Performance is from index, I suppose, in
this case!

Application is developed in C# on FB2.0.

Thanks,

Paul



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvaer
Sent: Tuesday, April 24, 2007 10:24 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] What is best approach for performance ???



Bad news, Paul. Neither of them will use any index in any case (well, at
least on Firebird 1.5 which I'm still using).

A far better solution would be to use two different queries and then let
your application choose the query without a WHERE clause if the 'All'
record was chosen. It wouldn't make any difference for the 'All' query,
but it would be far better for those cases where you knew the ProductId.

HTH,
Set

m24paul wrote:

> Hi
> I create custom component for using in my application, instead of
> default combobox. This component have `All' property for all record
> in that combobox.
> I need to know which sql have better performance on large tables:
> a. select ProductId, ProductName from Products where ProductId
> like :ComboBoxSelectedValue;
> In this case in combobox value for `All' record is `%' and is passed
> to query.
> b. select ProductId, ProductName from Products where (-1=
> ProductId) or (ProductId=:ComboBoxSelectedValue=;
> In this case in combobox value for `All' record is `-1' and is passed
> to query.
>
> I don't see differences on my development database. I have ~12000
> products. I intend to use this components for other fields, like
> orders or payments, where I could have ~ 1-2 mil records in 1 year!
>
> Thanks,
> Paul

[Non-text portions of this message have been removed]