Query Performance

HI to All!
Is there anyway to improve the performance of Queries involving
Substring clause. i have indexes on the query columns but performance is
not good. e.g if i use a query without substring it gives me result in
less than 1 seconds over a more than 1.3 million rows and when i use
substring it takes more than 10 seconds.
Regards
Farid
*** Sent via Developersdex http://www.examnotes.net ***Sometimes there is and sometimes there is not. Either way - it starts with
you posting DDL and sample data.
Maybe you don't even need the substring function. How can we tell?
ML|||
Dear ML
i have to use Substring function because i have data like
600600-02-12345 here i 600600 means my center. i have to substring this
to identify records related to 600600 center.
i m issuing following query
select count(distinct(substring(output_ref,1,6)
)) as ns123
from tbl_Main_Con where day(load_date)= '7' and month(load_date)= '9'
and year(load_date)= '2005'
this query returns result in more than 10 seconds. how can i improve its
performance.
Regards,
*** Sent via Developersdex http://www.examnotes.net ***|||Change your WHERE clause to:
where
load_date >= '20050907'
and load_date < '20050908'
You may want to create an index on load_date or (load_date, output_ref).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
news:OVWKl75sFHA.1256@.TK2MSFTNGP09.phx.gbl...
Dear ML
i have to use Substring function because i have data like
600600-02-12345 here i 600600 means my center. i have to substring this
to identify records related to 600600 center.
i m issuing following query
select count(distinct(substring(output_ref,1,6)
)) as ns123
from tbl_Main_Con where day(load_date)= '7' and month(load_date)= '9'
and year(load_date)= '2005'
this query returns result in more than 10 seconds. how can i improve its
performance.
Regards,
*** Sent via Developersdex http://www.examnotes.net ***|||Try using the LEFT function, although I doubt it would make any difference.
Are frequently sought columns indexed?
If you can, consider redesigning the table to store this piece of
information separately. Maybe even in a computed column, e.g. using the
following expression: left(output_ref, 6).
Read Tom's reply for another suggestion.
ML|||Just check with this
Initialize the parameter to NULL
SET @.Auto = 0
Detail.Automated = ISNULL(@.Auto ,Detail.Automated)
NOTE : Constraint Detail.Automated is not null
Detail.Automated = ISNULL(@.Auto ,Detail.Automated) and Detail.Automated IS
NULL
NOTE : Constraint Detail.Automated can have NULL values.
and Also check whether any index on the search arguments in the where caluse
.
HTH
Rajesh Peddireddy
"Jim Abel" wrote:

> The folling query works but is it the most efficient way to write it from
a
> performance aspect?
> The parameter can be set with the values (0,1 and %) these can be changed
if
> needed for the final query version.
> The Detail.Automated database field is a datatype of bit
> Should I be writing this to avoid the LIKE keyword?
>
> -- only used for testing
> DECLARE @.Auto AS char (1)
> SET @.Auto = '%'
> -- end test
> SELECT DISTINCT Status.ID,
> Info.ID,
> Info.Text,
> Detail.Automated,
> Status.Status
> FROM Status INNER JOIN Info
> ON Status.ID = Info.ID
> INNER JOIN Detail
> ON Info.ID = Detail.ID
> WHERE (Status.ID = 4)
> AND (Detail.Automated LIKE @.Auto|||check also indexes on the join fields
HTH
Rajesh Peddireddy
"Rajesh" wrote:
> Just check with this
> Initialize the parameter to NULL
> SET @.Auto = 0
> Detail.Automated = ISNULL(@.Auto ,Detail.Automated)
> NOTE : Constraint Detail.Automated is not null
> Detail.Automated = ISNULL(@.Auto ,Detail.Automated) and Detail.Automated I
S
> NULL
> NOTE : Constraint Detail.Automated can have NULL values.
> and Also check whether any index on the search arguments in the where calu
se.
> HTH
> Rajesh Peddireddy
> "Jim Abel" wrote:
>