westsorkin wrote:

> Thanks for the comments!
>
> Of course, I will put FK and I will have a UserID parameter in my
> query. So, now the question is, how slower is the first query (if it
> is), compared to the second one:
>
> select
> Message.id, Message.Subject
> from
> Messages
> inner join UserMessages on (UserMessages.MessageID = Messages.ID)
> where
> (
> (:show_only_unread = 'YES' and UserMessages.IsRead = 'NO') or
> (:show_only_unread = 'NO')
> )
> and
> (
> UserMessages.UserId = :UserID
> )
>
>
> select
> Message.id, Message.Subject
> from
> Messages
> inner join UserMessages on (UserMessages.MessageID = Messages.ID)
> where
> UserMessages.UserId = :UserID and
> UserMessages.IsRead = 'NO'
>
> When parameter "show_only_unread" is "No", there should not be any
> performance differences, right? But what about when "show_only_unread"
> is "Yes"?
>
> WestSorkin
>

Here are a small test I did to show you the difference:

--------------------------------------------------------
Example 1
Executing 100x the query:
select P.Numero, P.Status from Cliente C join PedidoVenda P on
(P.ClienteID = C.EmpresaID) where (P.AnalistaID = 7) and ((:Status =
'0') or (P.Status = :Status))
Using Parameter :Status = 1
Records: 4
Time: 2328 ms
PLAN JOIN (P INDEX (FK_PEDIDOVENDA_ANALISTA), C INDEX (PK_CLIENTE))
--------------------------------------------------------

--------------------------------------------------------
Example 2
Executing 100x the query:
select P.Numero, P.Status from Cliente C join PedidoVenda P on
(P.ClienteID = C.EmpresaID) where (P.AnalistaID = 7) and P.Status = :Status
Using Parameter :Status = 1
Records: 4
Time: 125 ms
PLAN JOIN (P INDEX (FK_PEDIDOVENDA_ANALISTA, SK_PEDIDOSTATUS), C INDEX
(PK_CLIENTE))
--------------------------------------------------------

--------------------------------------------------------
Example 3
Executing 100x the query:
select P.Numero, P.Status from Cliente C join PedidoVenda P on
(P.ClienteID = C.EmpresaID) where (P.AnalistaID = 7) and ((:Status =
'0') or (P.Status = :Status))
Using Parameter :Status = 0
Records: 4529
Time: 11781 ms
PLAN JOIN (P INDEX (FK_PEDIDOVENDA_ANALISTA), C INDEX (PK_CLIENTE))
--------------------------------------------------------

--------------------------------------------------------
Example 4
Executing 100x the query:
select P.Numero, P.Status from Cliente C join PedidoVenda P on
(P.ClienteID = C.EmpresaID) where (P.AnalistaID = 7)
Using Parameter :Status = 0
Records: 4529
Time: 11516 ms
PLAN JOIN (P INDEX (FK_PEDIDOVENDA_ANALISTA), C INDEX (PK_CLIENTE))
--------------------------------------------------------

As you can see on Example 1 even If I supply a Special status (value 1)
no index was used and the plan was exaclty equal to Example 3 and Example 4

On Example 2 I specified the Status and an index could be used to help
filter out the records.

All examples did a fetch all.

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br