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

about Efficiency

I want to select one field from a table,but it should on some conditions
which refer to 5 table ,such as A.FILED1=B.FIELD1 AND B.FIELD2=C.FIELD3 AND
...
Should I use case "select sum(a.amount) from a,b,c,... where
a.field1=b.field1 and b.field2=c.field2 and ..." or "select sum(a.amount)
from select b.field1 from select c.field2 from..."?And which case is more
efficiency?
thanks!

?е??ε?????? в??????select ..from ...where ..and ..and..and ..and ..select ..from
select ..from select ..from .....?Ч??
ллOn 20.09.2006 10:51, yicong wrote:

Quote:

Originally Posted by

I want to select one field from a table,but it should on some conditions
which refer to 5 table ,such as A.FILED1=B.FIELD1 AND B.FIELD2=C.FIELD3 AND
...
Should I use case "select sum(a.amount) from a,b,c,... where
a.field1=b.field1 and b.field2=c.field2 and ..." or "select sum(a.amount)
from select b.field1 from select c.field2 from..."?And which case is more
efficiency?

I cannot make much sense of this. Please rephrase your question and
especially provide more detail (DDL, DML).

A general remark: if you have two equivalent DML statements you can
easily check with MS tools (Profiler, Query Analyzer) which of the two
is more efficient.

Regards

robert|||yicong wrote:

Quote:

Originally Posted by

I want to select one field from a table,but it should on some conditions
which refer to 5 table ,such as A.FILED1=B.FIELD1 AND B.FIELD2=C.FIELD3 AND
...
Should I use case "select sum(a.amount) from a,b,c,... where
a.field1=b.field1 and b.field2=c.field2 and ..." or "select sum(a.amount)
from select b.field1 from select c.field2 from..."?And which case is more
efficiency?

select sum(a.amount)
from a
join b on a.field1 = b.field1
join c on b.field2 = c.field2
...