Most Recent Date Function...Slow...why? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Most Recent Date Function...Slow...why?

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-03-30 : 12:08:57

Hello. Does anyone see why this is so slow?

SELECT CaseNumber, LastName, RefundAmt, dbo.GetMaxAppSent(tblcaprec.casenumber,tblcaprec.partnumber) as LastAppSent
FROM tblCapRec
WHERE (CaseNumber LIKE '37%' OR CaseNumber LIKE '06%') AND Status = 4

Here is the function:

CREATE FUNCTION GetMaxAppSent(@CaseNumber NVARCHAR(11), @PartNumber INT)
RETURNS datetime
AS
BEGIN

DECLARE @High_Date DATETIME

SELECT @High_Date = MAX_date
FROM (SELECT CaseNumber, PartNumber, MAX(dt) AS MAX_date FROM (
SELECT CaseNumber, PartNumber, MAX(appsent) AS dt FROM tblCaprec WHERE CaseNumber = @casenumber AND PartNumber = @partnumber GROUP BY CaseNumber, PartNumber UNION ALL
SELECT CaseNumber, PartNumber, MAX(appsent2) AS dt FROM tblCaprec WHERE CaseNumber = @casenumber AND PartNumber = @partnumber GROUP BY CaseNumber, PartNumber UNION ALL
SELECT CaseNumber, PartNumber, MAX(appsent3) AS dt FROM tblCaprec WHERE CaseNumber = @casenumber AND PartNumber = @partnumber GROUP BY CaseNumber, PartNumber UNION ALL
SELECT CaseNumber, PartNumber, MAX(appsent4) AS dt FROM tblCaprec WHERE CaseNumber = @casenumber AND PartNumber = @partnumber GROUP BY CaseNumber, PartNumber UNION ALL
SELECT CaseNumber, PartNumber, MAX(appsent5) AS dt FROM tblCaprec WHERE CaseNumber = @casenumber AND PartNumber = @partnumber GROUP BY CaseNumber, PartNumber
) xxx GROUP BY CaseNumber, PartNumber
) a
JOIN tblCaprec b
ON a.CaseNumber = b.CaseNumber AND a.PartNumber = b.PartNumber
WHERE b.CaseNumber = @casenumber AND b.PartNumber = @partnumber

RETURN @High_Date
END

Any ideas?

Brenda

If it weren't for you guys, where would I be?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-30 : 12:19:24

Why do you have all the group by's in the function?
Think you can get rid of them and the join at the end.
I take it CaseNumber, PartNumber are indexed?

SELECT @High_Date = MAX_date
FROM
(SELECT MAX(dt) AS MAX_date FROM
(
SELECT MAX(appsent) AS dt FROM tblCaprec WHERE CaseNumber = @casenumber AND PartNumber = @partnumber UNION ALL
SELECT MAX(appsent2) AS dt FROM tblCaprec WHERE CaseNumber = @casenumber AND PartNumber = @partnumber UNION ALL
SELECT MAX(appsent3) AS dt FROM tblCaprec WHERE CaseNumber = @casenumber AND PartNumber = @partnumber UNION ALL
SELECT MAX(appsent4) AS dt FROM tblCaprec WHERE CaseNumber = @casenumber AND PartNumber = @partnumber UNION ALL
SELECT MAX(appsent5) AS dt FROM tblCaprec WHERE CaseNumber = @casenumber AND PartNumber = @partnumber
) xxx
) a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-03-30 : 13:38:43

I've tired this:

DECLARE @High_Date DATETIME

SELECT @High_Date = MAX_date
FROM
(SELECT MAX(dt) AS MAX_date FROM
(
SELECT appsent AS dt FROM tblCaprec WHERE CaseNumber = '451-047049' AND PartNumber = 1 UNION ALL
SELECT appsent2 AS dt FROM tblCaprec WHERE CaseNumber = '451-047049' AND PartNumber = 1 UNION ALL
SELECT appsent3 AS dt FROM tblCaprec WHERE CaseNumber = '451-047049' AND PartNumber = 1 UNION ALL
SELECT appsent4 AS dt FROM tblCaprec WHERE CaseNumber = '451-047049' AND PartNumber = 1 UNION ALL
SELECT appsent5 AS dt FROM tblCaprec WHERE CaseNumber = '451-047049' AND PartNumber = 1) xxx
) a
JOIN tblCaprec b
ON a.CaseNumber = b.CaseNumber AND a.PartNumber = b.PartNumber
WHERE b.CaseNumber = '451-047049' AND b.PartNumber = 1

But it gives me this error:

Server: Msg 207, Level 16, State 3, Line 8
Invalid column name 'CaseNumber'.
Server: Msg 207, Level 16, State 1, Line 8
Invalid column name 'PartNumber'.

Any ideas why?

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-30 : 15:28:25

Show us the DDL for the CapRec table including PK and indexes.

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-30 : 15:31:19

this would be much faster and shorter, of course, if your data was normalized. (I know we went through this before, right?)

- Jeff

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-03-30 : 15:35:28

Yes, we went through that before. I am running a one man show, and don't have time nor a lot of knowledge. Hopefully sometime this year I can get around to that.

Here is what I have:

CREATE TABLE [tblCapRec] (
[CaseNumber] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PartNumber] [smallint] NOT NULL ,
[Status] [smallint] NULL ,
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AppSent] [datetime] NULL ,
[AppSent2] [datetime] NULL ,
[AppSent3] [datetime] NULL ,
[AppSent4] [datetime] NULL ,
[AppSent5] [datetime] NULL ,
CONSTRAINT [PK_tblCapRec] PRIMARY KEY CLUSTERED
(
[CaseNumber],
[PartNumber]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Thanks for your time!

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-30 : 15:38:15

So you don't have a primary key or any indexes?

It'll be slow without these.

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-03-30 : 15:40:38

The CaseNumber and PartNumber are the PK. Indexes...I don't think so.

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-30 : 15:47:16

Oops missed the PK part in the DDL. Looks like you'd benefit from a nonclustered index on CaseNumber, Status.

Normalizing is really the only way to fix this though.

What's the execution plan look like? Could you save it as an image and put it on a web server somewhere for us to see?

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-03-30 : 15:53:30

I really wish I had time to normalize this right now, but I have other things I need to do as well. My boss really needs to hire more people, but she can't afford it.

What do you mean by the execution plan?

How would I use a nonclustered index?

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-30 : 15:57:09

You create a nonclustered index and SQL Server will use it.

For the execution plan, open Query Analyzer, connect to the db server, hit CTRL+K, run your query. At the bottom, click on the tab for execution plan. Show us the image.

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-03-30 : 16:03:08

I don't have a web server to post my execution plan. I'll email it to you.

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-30 : 16:11:04

The execution plan looks good. The problem is your denormalized database. See if the extra nonclustered index that I sent you helps out any. If it doesn't, you're stuck with this performance problem until you normalize the database.

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-03-30 : 16:17:59

Tara

Did you send me an extra nonclustered index?

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-30 : 16:20:39

No. Do you not know how to create one? CREATE INDEX...

The previous message shouldn't have said that I sent you...should have said that I mentioned above.

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-03-30 : 16:23:06

While will an index be so much faster than a PK?

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-30 : 16:25:12

This extra index would cover your WHERE clause in the main SELECT whereas the PK doesn't. The PK is useful inside the function. SQL Server can use more than one index in a query.

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-03-30 : 16:28:46

So if I do this:

CREATE UNIQUE INDEX index_name
ON tblCapRec (CaseNumber)

Where will the index be?

I've read this:

http://www.w3schools.com/sql/sql_create.asp

How do you query on an index?

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-30 : 16:31:22

You don't query on it. SQL Server will do it for you.

Also, you don't want a unique index, just a regular nonclustered one:
CREATE INDEX idx_CaseNumber_Status ON tblCapRec (CaseNumber, Status)

If the performance doesn't improve with this index, you probably should delete it.

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-03-30 : 16:34:53

So I keep my query the same with the index on it? Will effect anything else? Like updating?

Brenda

If it weren't for you guys, where would I be?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-30 : 16:38:54

also -- consider creating a view that returns the maximum date per casenumber/partnumber. Create it once and save it (like your UDF). Then you should join to that view on your two key columns. That will be much faster than using a UDF if you SELECT statement is returning many rows. Overall, it is not a good idea to use a UDF when a join will suffice, since joins are much more efficient.

It also will allow for your execution plan to be accurate, since it doesn't always do a good job of taking into account the work needed to be done for UDF's.

- Jeff

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources