about efficiency(rephrased)

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

Quote:

Originally Posted by

>= "&startYear&" and Invoice.Cyear <= "&endYear&" and Invoice.Cmonth >=

"&startMonth&" and Invoice.Cmonth <= "&endMonth&""

Thanks for any help

Yicongyicong wrote:

Quote:

Originally Posted by

could you tell me which case is more efficiency?(my tables have no index)

Add some indexes!

Quote:

Originally Posted by

And does it has any else case more efficiency?

I would write it as follows, for clarity:

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

Also, you can remove dat_Item and dat_MachineType entirely (unless you
might have null values with non-zero quantities, and want to exclude
those from the total).|||yicong (cici-lool@.163.com) writes:

Quote:

Originally Posted by

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

Why not benchmark instead? Without access to the data, it's difficult
to say what is going to happen.

Anyway, without indexes nothing will be effecient.

Furthermore you should not interpolate parameter values into the string
but use parameterised commands instead, not the least if you are interested
in performance.

I'm sorry, but I gave trying to understand your first query when I came
to this "subquery" that I don't know what it is.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx