Reduct execution time

[neoklis]

Hi All,

I have the below SQL Function

ALTER FUNCTION dbo.analosi
(@mmdoc varchar(10),@mmdocyear varchar (4),@UnitA varchar(3),@mmcat varchar(4))

RETURNS float
AS
BEGIN

declare @res int
set @res=0

if @unitA='KG'
select @res=SUM(QKG) FROM RPT.dbo.RPT_MMD
GROUP BY MMDoc, MMDocYear, StorLoc, MMCat
HAVING (MMDoc = @mmdoc) AND (MMDocYear = @mmdocyear) AND (MMCat = @mmcat)

if @unitA='ST'
select @res=SUM(QST) FROM RPT.dbo.RPT_MMD
GROUP BY MMDoc, MMDocYear, StorLoc, MMCat
HAVING (MMDoc = @mmdoc) AND (MMDocYear = @mmdocyear) AND (MMCat = @mmcat)

RETURN @res

END


I call the the dbo.analosi function from an Update SQL Statement.


UPDATE PERF_Performance
SET ANALOSI_KG = dbo.analosi(MMDoc, MMDocYear, 'KG', 'ANAL')


The table that the function reads has about 600000 records. The problem is that the total execution time of the Update SQL Statement for about 300 records is about 30 secs. The updated records may vary depending on user options. That means that may need to be updated for much more records and the total execution time will increase much more. Is there any tip to reduct the total execution time?

Thank you.

 

[SQL_Hell]

Hi Neoklis,

Ok first things first:

1. What is the indexing stategy for RPT.dbo.RPT_MMD,
does it have any indexes? are they re-indexed regulary?

2. In your function you populate @unita with the result of a sum and a group by, but something seems wrong in this logic, if you are returning a single value then why do you need group by, group by should be used when you have more than 1 group.

Would the following not do the same?

select @res=SUM(QKG) FROM RPT.dbo.RPT_MMD

where (MMDoc = @mmdoc) AND (MMDocYear = @mmdocyear) AND (MMCat = @mmcat)

 

[neoklis]

Thanks for your reply, I really enjoy studying with you.

1)I have a primary key and I’ve left it as is. There is no indexing strategy and I would appreciate if you could tell me a little bit more about it.

2)Yes you are right! When I create the statement I had a group by to check and view some stuff but I forget to remove it! Silly me.. Can you please tell me the difference between “Where” and “Having”; I removed ‘group by’ but there was not difference at the execution time.

Thanks for your help

 

[neoklis]

Creating an index managed to solve my problem. Now it is running really fast.

Thanks SQL_Hellp

 

[SQL_Hell]

Ok excellent

But which column did you create the index on? also what fill factor did you use?

I recommend using 70-80% fill factor because it helps keep indexes on one page rather than them spannning more than one page (or something like that lol).

Generally you should add indexes for columns that you use WHERE clauses on, but dont take that as a definitive rule because it is possible to over index a table which will lead to bad insert performance. The best way to is to have a look at all your queries as an overview and only create indexes for the queries that really need them.

Well done for taking your own initiative and trying things out based upon my first post, it's always nice when you only have to point the person in the right direction rather than do it all for them