about efficiency(rephrased)

[yicong]

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_Pro duction.Quantit y) from Invoice_Product ion,(select
[dat_Item].ItemCode from [dat_Item],(select [dat_MachineType].MachineTypeID
from [dat_MachineType]"&subQuery& ") as T3 where [dat_Item].MachineTypeID =
T3.machinetypei d) 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.dealern o and Invoice.Cyear >= "&startYear &" and
Invoice.Cyear <= "&endYear&" and Invoice.Cmonth >= "&startMont h&" and
Invoice.Cmonth <= "&endMonth& ") as T2 where invoice_product ion.ItemCode =
T1.ItemCode and T2.invoiceno = invoice_product ion.invoiceno"

case2:

"select sum(Invoice_Pro duction.Quantit y) from
[Invoice_Product ion],[Invoice],[dat_MachineType],[dat_Item],[users] where
[users].user_id = [invoice].DealerNo and [dat_Item].ItemCode =
[Invoice_Product ion].ItemCode and [dat_Item].MachineTypeID =
[dat_MachineType].MachineTypeID and [Invoice_Product ion].InvoiceNo =
[Invoice].InvoiceNo and [Users].User_ID = '"& rs2(0) &"' and Invoice.Cyear

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

"&startMont h&" and Invoice.Cmonth <= "&endMonth& ""

Thanks for any help

Yicong

[Ed Murphy]

Re: about efficiency(reph rased)

yicong wrote:

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

Add some indexes!

And does it has any else case more efficiency?

I would write it as follows, for clarity:

select sum(Invoice_Pro duction.Quantit y)
from [Invoice_Product ion]
join [Invoice] on [Invoice].InvoiceNo = [Invoice_Product ion].InvoiceNo
join [users] on [users].user_id = [invoice].DealerNo
join [dat_Item] on [dat_Item].ItemCode = [Invoice_Product ion].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 "&startMont h&" 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).

[Erland Sommarskog]

Re: about efficiency(reph rased)

yicong (cici-lool@163.com) writes:

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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx