Posted by yicong on 09/21/06 05:00

hi,All

could you tell me which case is more efficiency?(my tables have no index)
And does it has any else case more efficiency?

case1:

"select sum(Invoice_Production.Quantity) from Invoice_Production,(select
[dat_Item].ItemCode from [dat_Item],(select [dat_MachineType].MachineTypeID
from [dat_MachineType]"&subQuery&") as T3 where [dat_Item].MachineTypeID =
T3.machinetypeid) as T1,(select [Invoice].InvoiceNo from Invoice,(select
[users].user_id from [users] where [Users].User_ID = '"& rs2(0) &"') as T4
where T4.User_ID = invoice.dealerno and Invoice.Cyear >= "&startYear&" and
Invoice.Cyear <= "&endYear&" and Invoice.Cmonth >= "&startMonth&" and
Invoice.Cmonth <= "&endMonth&") as T2 where invoice_production.ItemCode =
T1.ItemCode and T2.invoiceno = invoice_production.invoiceno"

case2:

"select sum(Invoice_Production.Quantity) from
[Invoice_Production],[Invoice],[dat_MachineType],[dat_Item],[users] where
[users].user_id = [invoice].DealerNo and [dat_Item].ItemCode =
[Invoice_Production].ItemCode and [dat_Item].MachineTypeID =
[dat_MachineType].MachineTypeID and [Invoice_Production].InvoiceNo =
[Invoice].InvoiceNo and [Users].User_ID = '"& rs2(0) &"' and Invoice.Cyear
>= "&startYear&" and Invoice.Cyear <= "&endYear&" and Invoice.Cmonth >=
"&startMonth&" and Invoice.Cmonth <= "&endMonth&""

Thanks for any help

Yicong

• Next in forum: Re: Flattening Parent Child, an issue, please help
• Prev in forum: Re: cursor to compare/report on the same fields in 2 tables
• Thread view: about efficiency(rephrased)

[Reply to this message]