Optimize the Query

microsoft.public.sqlserver.programming

8 Jun 2006 9:40 PM

Joe K.

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'
END

8 Jun 2006 10:18 PM

Erland Sommarskog

Joe 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, esq***@sommarskog.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

8 Jun 2006 10:47 PM

Gert-Jan Strik

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:
Show quote

>
> 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'
> END

9 Jun 2006 3:19 AM

navyzhu@gmail.com

And encapsulate LEFT operation to sub-query may be get more good
performance.

Gert-Jan Strik å†™é“ï¼š

Show quote

> 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 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'
> > END

9 Jun 2006 8:23 PM

Gert-Jan Strik

"navy***@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

Show quote

> Gert-Jan Strik å†™é“ï¼š
>
> > 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 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'
> > > END