creating more efficient queries

Using SS2000. I see queries created using things like
where ziprequestid = cast(' + @.@.requestid + ' as int)
or
left([prop_mkey2],5) = BranchData.zipCode
or
where branch + left(tblFollowup.prop_mkey2, 5)
in(select distinct cast(branch_new as varchar) + cast(zip as varchar) from
tblZipReassign_New where zipziprequestid = ' + @.@.requestid + '))'
Are using things like "left" and "cast" always going to slow a query down?
Is it always going to be more efficient to say create a column for the
zipcode and populate it and index it instead of using "left([prop_mkey2],5)"
to parse it out of another column?
Thanks,
--
Dan D.This is a complex question, because the answer depends on so many other
factors. It is safe (although not certain) to say that it will never be
slower to replace such complex expressions with simpler, pre-calculated and
indexed values. But whether it will be actually be faster... Well, that
depends on the number of Page reads the query processor needs to do to
perform the query, and the answer to that is extremely complex. For example
in some cases, the optimizer maya decide NOT to use an index, even when one
is available, because, in it's judgement, reading teh table completely (a
table scan) will require fewer Page IOs than traversing the index once for
each of the records in the resultset. This is often the case for querys
which will return a "sizeable" percentage of the entire table. The
percentage threshold where this phenomenom occurs is surprisingly low.
(although it is dependant on the depth (number of levels) in the index, it
can be a s low as 5% of the records in the table.
Another phenomenom is that which occurs when the only fields the query
processor needs to read in order to process the query happen to be in some
index. This si then called a "Covering" index, and it means the processor
only needs to read the index, and not the table itself. This changes what
the optimizer will do as well.
ANyway, your best bet is get a bool on SQL Query Tuning and optimization.
"Dan D." wrote:

> Using SS2000. I see queries created using things like
> where ziprequestid = cast(' + @.@.requestid + ' as int)
> or
> left([prop_mkey2],5) = BranchData.zipCode
> or
> where branch + left(tblFollowup.prop_mkey2, 5)
> in(select distinct cast(branch_new as varchar) + cast(zip as varchar) from
> tblZipReassign_New where zipziprequestid = ' + @.@.requestid + '))'
> Are using things like "left" and "cast" always going to slow a query down?
> Is it always going to be more efficient to say create a column for the
> zipcode and populate it and index it instead of using "left([prop_mkey2],5
)"
> to parse it out of another column?
> Thanks,
> --
> Dan D.|||Thanks CBretana. That helps.
"CBretana" wrote:
> This is a complex question, because the answer depends on so many other
> factors. It is safe (although not certain) to say that it will never be
> slower to replace such complex expressions with simpler, pre-calculated an
d
> indexed values. But whether it will be actually be faster... Well, that
> depends on the number of Page reads the query processor needs to do to
> perform the query, and the answer to that is extremely complex. For examp
le
> in some cases, the optimizer maya decide NOT to use an index, even when on
e
> is available, because, in it's judgement, reading teh table completely (a
> table scan) will require fewer Page IOs than traversing the index once for
> each of the records in the resultset. This is often the case for querys
> which will return a "sizeable" percentage of the entire table. The
> percentage threshold where this phenomenom occurs is surprisingly low.
> (although it is dependant on the depth (number of levels) in the index, it
> can be a s low as 5% of the records in the table.
> Another phenomenom is that which occurs when the only fields the query
> processor needs to read in order to process the query happen to be in som
e
> index. This si then called a "Covering" index, and it means the processor
> only needs to read the index, and not the table itself. This changes wha
t
> the optimizer will do as well.
> ANyway, your best bet is get a bool on SQL Query Tuning and optimization.
> "Dan D." wrote:
>|||yr welcome! IN re-reading what I posted, I realized that I implied but did
not explicitly say, one important thing...
During the first phase of query optimization, (called ANalysis) the query
optimizer searches your query for anything that can be used to reduce the
number of page IOs it must perform. Any clause, or expression, in a where
clause or join condition, that can be used, is called a Search Argument
(SARG). A SARG limits the Page IOs that need to be made when it specifies
anb exact match, a range of values, or a conjunction of two SARGS with an
AND. An individual SARG MUST be a Column variable acting on a constant
expression (or variable that can be resolved to a constant) ANY expression
that has a column value being operated on by a function cannot be a SARG.
i.e,
HireDate > DateAdd(day, -30, getdate()) IS a SARG, but
DateAdd(day, 30 HireDate) > getdate() IS NOT a SARG
in the former, the expression
DateAdd(day, -30, getdate()) will be resolved to a constant, and compared
with the value of the HireDate column. If HireDate is in an index, the index
can be used. In the latter case a Table scan ahs tp be done, because the
query processor has to read every HireDate in the table to perform the
function DateAdd(day, 30 HireDate) on each value.
Hope this helps...
"Dan D." wrote:
> Thanks CBretana. That helps.
> "CBretana" wrote:
>|||That does help. As a general rule I try to get people to store something lik
e
DateAdd(day, -30, getdate()) in a variable and then use the variable in the
query. I didn't know that DateAdd(day, -30, getdate()) could still use the
index if it is in the right place in the query.
Thanks.
"CBretana" wrote:
> yr welcome! IN re-reading what I posted, I realized that I implied but di
d
> not explicitly say, one important thing...
> During the first phase of query optimization, (called ANalysis) the query
> optimizer searches your query for anything that can be used to reduce the
> number of page IOs it must perform. Any clause, or expression, in a where
> clause or join condition, that can be used, is called a Search Argument
> (SARG). A SARG limits the Page IOs that need to be made when it specifies
> anb exact match, a range of values, or a conjunction of two SARGS with an
> AND. An individual SARG MUST be a Column variable acting on a constant
> expression (or variable that can be resolved to a constant) ANY expressio
n
> that has a column value being operated on by a function cannot be a SARG.
> i.e,
> HireDate > DateAdd(day, -30, getdate()) IS a SARG, but
> DateAdd(day, 30 HireDate) > getdate() IS NOT a SARG
> in the former, the expression
> DateAdd(day, -30, getdate()) will be resolved to a constant, and compar
ed
> with the value of the HireDate column. If HireDate is in an index, the ind
ex
> can be used. In the latter case a Table scan ahs tp be done, because the
> query processor has to read every HireDate in the table to perform the
> function DateAdd(day, 30 HireDate) on each value.
> Hope this helps...
> "Dan D." wrote:
>