Optimization of an sql proc stock [Beginner]

Question

Post reply

Optimization of an sql proc stock [Beginner]

novitito

SSC Rookie

Points: 40
More actions
September 10, 2010 at 7:56 am

#387763

Hye everybody,
I am not a specialist in Sql server (like you ;)) and i have got a problem with a proc stock whiche takes 41s to execute itself and it's not an prock stock of 3000 lines, and my table have less than 100000 rows so i don't understand.
Can you look at the proc stock and tell me if i have make some very bad performance choice.
The goal of this SQL script is to compute the number of consecutives day of continuous activity(Training or competition) for a student (Excuse-me for my english)
I used 5 tables:
Eleves (Student) : information about the student (254 rows)
Profils (Profils) : information about the profil of a student: his sport, his weight,etc... (416 rows)
Journees (Days) : a day of a student ( 70 444 rows)
Entrainement (Training) : a training of a student (52000 rows)
Competition (Competition) : a competiton of a student (6600 rows)
Thanks a lot
here it's the code
ALTER PROCEDURE [dbo].[GetElevesPasDeJourDeRepos]
@DatDeb DateTime,
@DatFin DateTime,
@AnnScoValue int,
@SpoValue int,
@ClaValue int
as
BEGIN
SET NOCOUNT ON;
create Table #JourActif(
JourneeID int,
EleveID int,
ProfilID int,
Date DateTime)
INSERT INTO #JourActif (JourneeID, EleveID,ProfilID,Date)
select DISTINCT j.journeeID, j.EleveID ,j.ProfilID,j.Date
from journees j
LEFT JOIN Entrainement ent ON ent.JourneeID = j.JourneeID
LEFT JOIN Competition com on com.JourneeID = j.JourneeID
LEFT JOIN Profils pro on pro.profilID = j.profilID
WHERE (com.CompetitionID IS NOT NULL OR ent.EntrainementID IS NOT NULL)
AND j.Date BETWEEN @DatDeb AND @DatFin
AND (pro.ClasseID = @ClaValue OR @ClaValue = 0)
AND (pro.SportID = @SpoValue OR @SpoValue = 0)
AND (pro.AnneeScolaireID = @AnnScoValue OR @AnnScoValue = 0)
--------------------------------------------------
----------------------------------------------
Create Table #JourSansReposByEleve(
EleveID int,
DateDeb Datetime,
DateFin DateTime,
Periode int,
JourneeID int
)
INSERT INTO #JourSansReposByEleve (EleveID, DateDeb,DateFin,Periode,journeeID)
select l.EleveID,l.Date as DateDeb,
(select min(a.Date ) as Date
from
#JourActif a
left outer join #JourActif b
on
a.Date = dateadd(day, -1, b.Date ) and
a.EleveID = b.EleveID
where b.Date is null and
a.Date >= l.Date and
a.EleveID = l.EleveID
) as DateFin,
DateDiff(day,l.Date,
-- Get first date in contiguous range
( select min(a.Date ) as Date
from #JourActif a
left outer join #JourActif b
on
a.Date = dateadd(day, -1, b.Date ) and
a.EleveID = b.EleveID
where b.Date is null and
a.Date >= l.Date and
a.EleveID = l.EleveID
))+1 as Periode,
-- Get last date in contiguous rangefrom "DBA"."visits" l
l.journeeID
from #JourActif l
left outer join #JourActif r
on r.Date = dateadd(day, -1, l.Date ) and
r.EleveID = l.EleveID
where r.Date is null
Order by Periode Desc
/******************************************************************************/
select LastName,FirstName,j1.eleveId, j1.periode as periode,j1.DateDeb ,j1.DateFin,lCla.Text as Classe, lSpo.Text as Sport,lAnnSco.Text as AnneeScolaire
From #JourSansReposByEleve j1
join Eleves ele on ele.EleveId = j1.EleveId
join Users u on u.UserID = ele.UserID
join Journees jou on jou.journeeID = j1.journeeID
join Profils pro on jou.ProfilID = pro.ProfilID
join Lists lCla on lCla.ListName ='Classe' AND pro.ClasseID = lCla.Value
join Lists lSpo on lSpo.ListName ='Sport' AND pro.SportID = lSpo.Value
join Lists lAnnSco on lAnnSco.ListName ='AnneeScolaire' AND pro.AnneeScolaireID = lAnnSco.Value,
(select eleveId, Max(Periode) as MaxPeriode
From #JourSansReposByEleve
Group by EleveId) maxPeriode
where j1.eleveID = maxPeriode.eleveID and j1.periode >=10
order by periode Desc
END

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I suggest that you:

1) Post your table scripts, as described in the article linked in my signature line

2) Post your sample data in a more consumable format (INSERT INTO script)

3) Post your required output with examples referred to your sample data

4) Read Jeff Moden's article on Tally Tables and their use. One of the possible uses of a tally table is finding gaps in a sequence, that appears to be what you're after.

Hope this helps

Gianluca

-- Gianluca Sartori

WayneS SSC Guru Points: 95461 More actions · Answer 2

It looks like you could benefit from checking out this excellent blog: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

novitito SSC Rookie Points: 40 More actions · Answer 3

Ok, I give you the .bak of the database where i have delete all unnecessary tables and procedure,

You can test the procStock

USE [TestDatabase]

GO

DECLARE@return_value int

EXEC@return_value = [dbo].[GetElevesPasDeJourDeRepos]

@DatDeb = N'01/01/2009',

@DatFin = N'01/09/2010',

@AnnScoValue = 0,

@SpoValue = 0,

@ClaValue = 0

GO

I will look a Tally Table but i am not sure it will improve the time

Thanks

David

Optimization of an sql proc stock [Beginner]

Cookies on SQLServerCentral