Optimize the Query

Listed below is tsql statement that I set up execute every 60 minutes.
To check to see if all the values exist in the following list: '806478',
'806479','806480','806481' in the Call_Movements table.
I am only interest in the first six characters and I'm converting iNum data
type from money to varchar(20).
left(cast(iNum as varchar(20)),6)
Please help me optimize the t-sql statement listed below since this table is
large.
Is there away to change the following to binary format:
DECLARE @.CNT_MTN_REC_0 SMALLINT and change the following t-sql statement
listed below to be optimal?
Thank You,
T-SQL
DECLARE @.CNT_MTN_REC_0 SMALLINT
SET @.CNT_MTN_REC_0 =
(Select count(iNum)
from Call_Movements
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
AND left(cast(iNum as varchar(20)),6) = ('806478'))
--
-- Count the
--
DECLARE @.CNT_MTN_REC_1 SMALLINT
SET @.CNT_MTN_REC_1 =
(Select count(iNum)
from Call_Movements
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
AND left(cast(iNum as varchar(20)),6) = ('806479'))
--
--
--
DECLARE @.CNT_MTN_REC_2 SMALLINT
SET @.CNT_MTN_REC_2 =
(Select count(iNum)
from Call_Movements
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
AND left(cast(iNum as varchar(20)),6) = ('806480'))
--
--
--
DECLARE @.CNT_MTN_REC_3 SMALLINT
SET @.CNT_MTN_REC_3 =
(Select count(iNum)
from Call_Movements
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
AND left(cast(iNum as varchar(20)),6) = ('806481'))
--
--
--
if (@.CNT_MTN_REC_0 = 0) OR (@.CNT_MTN_REC_1 = 0) OR (@.CNT_MTN_REC_2 = 0) OR
(@.CNT_MTN_REC_3 = 0)
BEGIN
PRINT "Error has Occurred'
ENDJoe K. (Joe K.@.discussions.microsoft.com) writes:
> To check to see if all the values exist in the following list: '806478',
> '806479','806480','806481' in the Call_Movements table.
> I am only interest in the first six characters and I'm converting iNum
> data type from money to varchar(20).
> left(cast(iNum as varchar(20)),6)
> Please help me optimize the t-sql statement listed below since this
> table is large.
> Is there away to change the following to binary format:
> DECLARE @.CNT_MTN_REC_0 SMALLINT and change the following t-sql statement
> listed below to be optimal?
It would have helped if you had posted the CREATE TABLE and CREATE INDEX
statements for the tables.
But first, there is no need to run four SQL statemennts.
This could either be done as:
Select count(iNum), left(cast(iNum as varchar(20)),6)
from Call_Movements
where StartTime >= DATEADD(mi, -60, GETDATE())
AND left(cast(iNum as varchar(20)),6) IN
('806478', '806479', '806480', '806481')
GROUP BY left(cast(iNum as varchar(20)),6)
This produces a result set of four rows. If you need to get the result
into variables, you can do:
Select @.CNT_MTN_REC_0 = SUM(CASE left(cast(iNum as varchar(20)),6)
WHEN '806478' THEN 1
ELSE 0
END),
..
from Call_Movements
where StartTime >= DATEADD(mi, -60, GETDATE())
AND left(cast(iNum as varchar(20)),6) IN
('806478', '806479', '806480', '806481')
As you can see, I have also changed the condition on Start_Time, in case
this column is indexed. When an indexed column appears in an expression
like in your query, the index is of on use. The query is still problematic
due to the >=. If the index on StartTime is clustered it is not much of
an issue, but if there is only a onn-clustered index, the optimizer is not
likely to pick it in this case.
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|||Joe,
Will you have iNum values that start with 806478 (or any of the other 3
values) followed by other numbers before the decimal point? For example
8064781.01?
If not, then make sure you add a nonclustered index on
Call_Movement(iNum, StartTime) and add the following predicates to the
WHERE clause:
AND iNum >= 806478
AND iNum < 806482
You could try the following query:
IF ( SELECT COUNT(DISTINCT CAST(iNum as char(6)) )
FROM Call_Movements
WHERE StartTime >= DATEADD(minute, -60, CURRENT_TIMESTAMP)
AND iNum >= CAST(806478 AS money)
AND iNum < CAST(806482 AS money)
AND CAST(iNum as char(6)) IN ('806478', '806479', '806480',
'806481')
) < 4
BEGIN
PRINT "Error has Occurred'
END
HTH,
Gert-Jan
Joe K. wrote:
> Listed below is tsql statement that I set up execute every 60 minutes.
> To check to see if all the values exist in the following list: '806478',
> '806479','806480','806481' in the Call_Movements table.
> I am only interest in the first six characters and I'm converting iNum dat
a
> type from money to varchar(20).
> left(cast(iNum as varchar(20)),6)
> Please help me optimize the t-sql statement listed below since this table
is
> large.
> Is there away to change the following to binary format:
> DECLARE @.CNT_MTN_REC_0 SMALLINT and change the following t-sql statement
> listed below to be optimal?
> Thank You,
>
> T-SQL
> DECLARE @.CNT_MTN_REC_0 SMALLINT
> SET @.CNT_MTN_REC_0 =
> (Select count(iNum)
> from Call_Movements
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> AND left(cast(iNum as varchar(20)),6) = ('806478'))
> --
> -- Count the
> --
> DECLARE @.CNT_MTN_REC_1 SMALLINT
> SET @.CNT_MTN_REC_1 =
> (Select count(iNum)
> from Call_Movements
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> AND left(cast(iNum as varchar(20)),6) = ('806479'))
> --
> --
> --
> DECLARE @.CNT_MTN_REC_2 SMALLINT
> SET @.CNT_MTN_REC_2 =
> (Select count(iNum)
> from Call_Movements
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> AND left(cast(iNum as varchar(20)),6) = ('806480'))
> --
> --
> --
> DECLARE @.CNT_MTN_REC_3 SMALLINT
> SET @.CNT_MTN_REC_3 =
> (Select count(iNum)
> from Call_Movements
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> AND left(cast(iNum as varchar(20)),6) = ('806481'))
> --
> --
> --
> if (@.CNT_MTN_REC_0 = 0) OR (@.CNT_MTN_REC_1 = 0) OR (@.CNT_MTN_REC_2 = 0) OR
> (@.CNT_MTN_REC_3 = 0)
> BEGIN
> PRINT "Error has Occurred'
> END|||And encapsulate LEFT operation to sub-query may be get more good
performance.
Gert-Jan Strik =E5=86=99=E9=81=93=EF=BC=9A
> Joe,
> Will you have iNum values that start with 806478 (or any of the other 3
> values) followed by other numbers before the decimal point? For example
> 8064781.01?
> If not, then make sure you add a nonclustered index on
> Call_Movement(iNum, StartTime) and add the following predicates to the
> WHERE clause:
> AND iNum >=3D 806478
> AND iNum < 806482
> You could try the following query:
> IF ( SELECT COUNT(DISTINCT CAST(iNum as char(6)) )
> FROM Call_Movements
> WHERE StartTime >=3D DATEADD(minute, -60, CURRENT_TIMESTAMP)
> AND iNum >=3D CAST(806478 AS money)
> AND iNum < CAST(806482 AS money)
> AND CAST(iNum as char(6)) IN ('806478', '806479', '806480',
> '806481')
> ) < 4
> BEGIN
> PRINT "Error has Occurred'
> END
>
> HTH,
> Gert-Jan
>
> Joe K. wrote:
data
le is
=3D 0) OR|||"navyzhu@.gmail.com" wrote:
> And encapsulate LEFT operation to sub-query may be get more good
> performance.
I have not done performance tests to disprove it, but I highly doubt it!
I don't think LEFT will outperform CAST.
Besides, why use the proprietary LEFT when the standard CAST will do
just fine...
Gert-Jan
> Gert-Jan Strik 写道：
>