Tidying up of sproc

[Samuel Hon]

Hi

I've written this sproc. Can any one suggest any optimisations to the
main query and its sub-queries please? Truncated DDL after
CREATE PROCEDURE dbo.spGetUnbilledInfoByClient
(
@ClientID varchar(5),
@Sort int = 0,
@Dir int = 0
) AS

SET NOCOUNT ON

-- The Main Query
SELECT mmatter, mdesc1, tkfirst + ' ' + tklast AS Contact,
CONVERT(varchar(10), mopendt, 103) As [Date], CONVERT(DECIMAL(18,2),
tTime.Cost) AS COST, tTime.Hours, tDisbursements.Disbursements

FROM matter
INNER JOIN timekeep ON mbillaty = timekeep.tkinit
lEFT JOIN (

SELECT cmatter, SUM(Disbursements) AS Disbursements FROM (
SELECT dbo.cost.cmatter,
CASE WHEN dbo.cost.cauth = 'SO' OR dbo.cost.cauth = 'CF'
THEN dbo.cost.cbillamt * 1.175
ELSE
dbo.cost.cbillamt
END AS Disbursements
FROM dbo.cost
WHERE (dbo.cost.cstatus = 'b') AND (dbo.cost.cinvoice IS NULL) AND
(LEFT(dbo.cost.cmatter, 5) = @ClientID)
) AS costs
GROUP BY cmatter

) AS tDisbursements
ON dbo.matter.mmatter = tDisbursements.cmatter

INNER JOIN
(
SELECT dbo.timecard.tmatter, SUM(dbo.timecard.tbillhrs) AS Hours,
SUM(dbo.timecard.tbilldol) AS Cost
FROM dbo.timecard JOIN dbo.batch ON dbo.timecard.tbatch =
dbo.batch.bbatch
WHERE (dbo.timecard.tinvoice IS NULL)
AND (dbo.timecard.tstatus = 'b')
AND (LEFT(dbo.timecard.tmatter, 5) = @ClientID)
AND (dbo.timecard.tbilldt IS NULL)
AND ( NOT dbo.batch.bfindt IS NULL)
GROUP BY tmatter
) AS tTime
ON dbo.matter.mmatter = tTime.tmatter

--Sort (see http://www.sqlteam.com/forums/topic....42&whichpage=3)
ORDER BY
CASE -- numeric fields
WHEN @Sort = 4 AND @Dir = 0
THEN tTime.Cost
WHEN @Sort = 5 AND @Dir = 0
THEN tTime.Hours
WHEN @Sort = 6 AND @Dir = 0
THEN tDisbursements.Disbursements
END DESC,

CASE -- datetime
WHEN @Sort = 0 AND @Dir = 0
THEN mopendt
WHEN @Sort = 3 AND @Dir = 0
THEN mopendt
END DESC,

CASE -- character fields
WHEN @Sort = 1 AND @Dir = 0
THEN mdesc1
WHEN @Sort = 2 AND @Dir = 0
THEN tkfirst + ' ' + tklast

END DESC,

-- ASC
CASE -- numeric fields
WHEN @Sort = 4 AND @Dir = 1
THEN tTime.Cost
WHEN @Sort = 5 AND @Dir = 1
THEN tTime.Hours
WHEN @Sort = 6 AND @Dir = 1
THEN tDisbursements.Disbursements
END ASC,

CASE -- datetime
WHEN @Sort = 0 AND @Dir = 1
THEN mopendt
WHEN @Sort = 3 AND @Dir = 1
THEN mopendt
END ASC,

CASE -- character fields
WHEN @Sort = 1 AND @Dir = 1
THEN mdesc1
WHEN @Sort = 2 AND @Dir = 1
THEN tkfirst + ' ' + tklast

END ASC
GO
CREATE TABLE [matter] (
[mname] [varchar] (24) COLLATE Latin1_General_CI_AS NULL ,
[mmatter] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[mrtcode] [smallint] NULL ,
[mdept] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[mopendt] [datetime] NULL ,
[mbillaty] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[mdesc1] [varchar] (48) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO


CREATE TABLE [cost] (
[cindex] [int] IDENTITY (1, 1) NOT NULL ,
[cmatter] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[camount] [money] NULL ,
[cbilldt] [datetime] NULL ,
[cbillamt] [money] NULL ,
[cinvoice] [int] NULL ,
[ccode] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[cbatch] [int] NULL ,
[cauth] [varchar] (10) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [costcode] (
[cocode] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[codesc1] [varchar] (48) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [timecard] (
[tindex] [int] IDENTITY (1, 1) NOT NULL ,
[tmatter] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[ttk] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[tbilldt] [datetime] NULL ,
[tbillhrs] [decimal](16, 2) NULL ,
[tbillrt] [decimal](16, 2) NULL ,
[tbilldol] [money] NULL ,
[tinvoice] [int] NULL ,
[tcode] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[tbatch] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [batch] (
[bbatch] [int] IDENTITY (1, 1) NOT NULL ,
[btype] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[bfindt] [datetime] NULL ,
[bop] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[bper] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[bpflag] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[bdopen] [datetime] NULL ,
[btothrs] [decimal](12, 2) NULL ,
[btotdol] [money] NULL
) ON [PRIMARY]
GO

[Erland Sommarskog]

Samuel Hon (no*****@samuelhon.co.uk) writes:

I've written this sproc. Can any one suggest any optimisations to the
main query and its sub-queries please? Truncated DDL after
It is difficult to optimize queries when you don't know what's in the
tables, and what the indexes are. But there is one thing here, which
is likely to be a serious issue:
(LEFT(dbo.cost.cmatter, 5) = @ClientID)
...
AND (LEFT(dbo.timecard.tmatter, 5) = @ClientID)

If you have indexes on these two columns, they are not very likely to be
used. Because when you but a table column in an expression, SQL Server
can no longer use that index to search for values, because the index
were built from the original values. At best, SQL Server can scan the
entire index, but this is rarely a winner in a query like this.

You could try rewriting the conditions as

dbo.cost.cmatter LIKE @ClientID + '%'

Another alternative is to add computed columns to the table:

clientid AS left(cmatter, 5)

and then index that column.

Note that for index on computed column to be used, you must all these
settings on: ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, ANSI_PADDING,
CONCAT_NULLS_YIELDS_NULL and ARITHABORT. Of these two, ANSI_NULLS and
QUOTED_IDENTIFIER are saved with the stored procedure - it is not the
run-time settings that apply. All these are on by default at run-time,
but some tools, Enterprise Manager and OSQL, do not have QUOTED_IDENT
on by default. And most libraries does not set ARITHABORT ON by default.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Samuel Hon]

Thanks Erland, my post was sent before I'd finished typing by me being
careless when clicking

I'll investigate the 'LEFT' issue with your suggestions.

Do you think that there is any problem with the sub-selects I've used?
Is this approach normally recommended?

Thanks again

Sam

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...

Samuel Hon (no*****@samuelhon.co.uk) writes:

I've written this sproc. Can any one suggest any optimisations to the
main query and its sub-queries please? Truncated DDL after

It is difficult to optimize queries when you don't know what's in the
tables, and what the indexes are. But there is one thing here, which
is likely to be a serious issue:

(LEFT(dbo.cost.cmatter, 5) = @ClientID)
...
AND (LEFT(dbo.timecard.tmatter, 5) = @ClientID)

If you have indexes on these two columns, they are not very likely to be
used. Because when you but a table column in an expression, SQL Server
can no longer use that index to search for values, because the index
were built from the original values. At best, SQL Server can scan the
entire index, but this is rarely a winner in a query like this.

You could try rewriting the conditions as

dbo.cost.cmatter LIKE @ClientID + '%'

Another alternative is to add computed columns to the table:

clientid AS left(cmatter, 5)

and then index that column.

Note that for index on computed column to be used, you must all these
settings on: ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, ANSI_PADDING,
CONCAT_NULLS_YIELDS_NULL and ARITHABORT. Of these two, ANSI_NULLS and
QUOTED_IDENTIFIER are saved with the stored procedure - it is not the
run-time settings that apply. All these are on by default at run-time,
but some tools, Enterprise Manager and OSQL, do not have QUOTED_IDENT
on by default. And most libraries does not set ARITHABORT ON by default.

[Erland Sommarskog]

Samuel Hon (no*****@samuelhon.co.uk) writes:

Do you think that there is any problem with the sub-selects I've used?
Is this approach normally recommended?

To use the correct terminology, they are derived tables, since they
appear where you could have a table in the query.

Derived tables are a very powerful tool when writing SQL queries. SQL Server
is free to reorder the evaluation order, as long as it does not affect the
result, and SQL Server often does a good job. At worst they are just like
temp tables, except that they are never materialized. So I think that
your derived tables are fine.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Samuel Hon]

>

You could try rewriting the conditions as

dbo.cost.cmatter LIKE @ClientID + '%'

WOW

It seems to be significantly faster. Currently running on a test
server with limited data so will see what happens on the production
server

Thanks Erland!

Dont suppose you could explain why?

[Erland Sommarskog]

Samuel Hon (no*****@samuelhon.co.uk) writes:

You could try rewriting the conditions as

dbo.cost.cmatter LIKE @ClientID + '%'

WOW

It seems to be significantly faster. Currently running on a test
server with limited data so will see what happens on the production
server

Thanks Erland!

Dont suppose you could explain why?

Actually, I think it in was my previous post, but let's take it again.

When you have an index on a column, SQL Server can use this index in
three ways:

o Seek it - that is look up a value following the index structure.
o Scan it - read all values.
o Don't use it all.

For seeks to be possible, there must be an expression in the query that
is aligned with the index. If you have a table with an index on
the integer columns (col1, col2), and you say:

SELECT COUNT(*) FROM tbl WHERE col1 = 12
SELECT COUNT(*) FROM tbl WHERE col2 = 12

For the first query, SQL Server can seek the index, because all the
index nodes with col1 = 12 is one place. For the second query, SQL
Server can not seek the index, but it will scan the index, because
that is cheaper than reading all the data pages. (The index nodes are
smaller, and thus more fit one page, and thus there are fewer pages
to scan.)

But if you say:

SELECT COUNT(*) FROM tbl WHERE col1 + 1 = 13
SELECT COUNT(*) FROM tbl WHERE col2 + 1 = 13

SQL Server will not seek in the index in either case, this is because
there is no index for (col + 1). In this particular case, SQL Server
could rewrite the query, but assume for instance the query:

SELECT COUNT(*) FROM tbl WHERE col1 % 100 = 12
SELECT COUNT(*) FROM tbl WHERE col2 % 100 = 12

Here it's getting tricky to find out which value in the index to seek.
So SQL Server is taking the easy way out:

If a column is involved in an expression on side of the comparison
operator, NO seeks can be performed on that column, only scans.

In the case:

dbo.cost.cmatter LIKE @ClientID + '%'

The column is on its own on the left side of the LIKE operator. There
is an expresion on the right, but the result of the expression is
constant for the query. Note, though, that:

dbo.cost.cmatter LIKE '%' + @ClientID + '%'

is another case where the index cannot be seeked. This is because you
can get hits on A0001B, AA0001B, Z00001UI etc.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ross Presser]

On Fri, 20 Aug 2004 21:23:31 +0000 (UTC), Erland Sommarskog wrote:

.... seek the index ...
.... index cannot be seeked ...

The grammar nazi in my head just woke up. :) Since in ordinary English, "to
seek x" means "to look all over the place until you find x", SQL Server
should have no trouble finding the index. :) Obviously you meant "seek in
the index for the value". Likewise, the past tense of "to seek" is
"sought", but to say "the index cannot be sought" is pretty strange ...

I'm not complaining about your language usage, Erland. "Seek" has
definitely taken on some new meanings since the data processing people (and
the librarians before them, I guess) got hold of it. It just struck me
funny for a moment, as a guy who loves words almost as much as databases.

[Erland Sommarskog]

Ross Presser (rp******@imtek.com) writes:

On Fri, 20 Aug 2004 21:23:31 +0000 (UTC), Erland Sommarskog wrote:

... seek the index ...
... index cannot be seeked ...

The grammar nazi in my head just woke up. :) Since in ordinary English,
"to seek x" means "to look all over the place until you find x", SQL
Server should have no trouble finding the index. :) Obviously you meant
"seek in the index for the value". Likewise, the past tense of "to
seek" is "sought", but to say "the index cannot be sought" is pretty
strange ...

I'm not complaining about your language usage, Erland. "Seek" has
definitely taken on some new meanings since the data processing people
(and the librarians before them, I guess) got hold of it. It just
struck me funny for a moment, as a guy who loves words almost as much as
databases.

If you make more grammar snubs like that I am going to default to answer
your questions!
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ross Presser]

I'm truly sorry. I'll keep grammar out of it from now on.

[Erland Sommarskog]

Ross Presser (rp******@NOSPAM.imtek.com.invalid) writes:

I'm truly sorry. I'll keep grammar out of it from now on.

Oh, nothing to be sorry for. There was a pun, in case you didn't notice!
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Samuel Hon]

:) Thanks Erland