February 4, 2008 at 3:35 am
HI All,
I am reviewing code and I have seen a staff member use a very interesting method for calling a Stored Proc to return look-up data.
Here is the Code that they have writen so that they only use one procedure to select from all or one of the look-up tables in the DB.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[pGetLookupData](
@VvcTableNameVARCHAR(50) = NULL,
@ViLookupIDINT = NULL,
@VbAllBIT = 0
)
AS
SET NOCOUNT ON
IF @VvcTableName = 'VisaType' SELECT VisaTypeID as ID, type AS [Name] FROM VisaType order by type
ELSE IF @VvcTableName = 'Country' SELECT C.ipkCountryID as ID, C.sDescription AS [Name] FROM GroupData.dbo.Country C ORDER BY C.iSort, C.sDescription
ELSE IF @VvcTableName = 'CountryOfOrigin' SELECT CO.CountryOfOriginID as ID, C.sDescription AS [Name] FROM CountryOfOrigin CO LEFT JOIN GroupData.dbo.Country C ON CO.CountryID = C.ipkCountryID ORDER BY C.iSort, C.sDescription
ELSE IF @VvcTableName = 'Currency' SELECT CurrencyID as ID, Currency AS [Name] FROM Currency order by Currency
ELSE IF @VvcTableName = 'DestinationCountry' SELECT DC.DestinationCountryID as ID, C.sDescription AS [Name] FROM DestinationCountry DC LEFT JOIN GroupData.dbo.Country C ON DC.CountryID = C.ipkCountryID order by ID
ELSE IF @VvcTableName = 'EducationLevel' SELECT EducationLevelID as ID, EducationLevel AS [Name] FROM EducationLevel order by EducationLevelID
ELSE IF @VvcTableName = 'EmploymentType' SELECT EmploymentTypeID as ID, EmploymentType AS [Name] FROM EmploymentType order by EmploymentType
ELSE IF @VvcTableName = 'EmploymentLevel' SELECT EmploymentLevelID as ID, EmploymentLevel AS [Name] FROM EmploymentLevel order by EmploymentLevel
ELSE IF @VvcTableName = 'Experience' SELECT ExperienceID as ID, Experience AS [Name] FROM Experience order by Experience
ELSE IF @VvcTableName = 'InstitutionType' SELECT InstitutionTypeID as ID, InstitutionType AS [Name] FROM InstitutionType order by SortID, InstitutionType
ELSE IF @VvcTableName = 'Language' SELECT LanguageID as ID, Language AS [Name] FROM Language order by Language
ELSE IF @VvcTableName = 'PassportCountry' SELECT PC.PassportCountryID as ID, C.sDescription AS [Name] FROM PassportCountry PC LEFT JOIN GroupData.dbo.Country C ON PC.CountryID = C.ipkCountryID ORDER BY C.iSort, C.sDescription
ELSE IF @VvcTableName = 'Proficiency' SELECT ProficiencyID as ID, Proficiency AS [Name] FROM Proficiency order by Proficiency
ELSE IF @VvcTableName = 'PreGradEmploymentType' SELECT PreGradEmploymentTypeID as ID, EmploymentType AS [Name] FROM PreGradEmploymentType order by EmploymentType
ELSE IF @VvcTableName = 'RemunerationType' SELECT RemunerationTypeID as ID, RemunerationType AS [Name] FROM RemunerationType order by RemunerationType
ELSE IF @VvcTableName = 'RemunerationFrequency' SELECT RemunerationFrequencyID as ID, RemunerationFrequency AS [Name] FROM RemunerationFrequency order by RemunerationFrequency
ELSE IF @VvcTableName = 'SkillsCompetency' SELECT SkillsCompetencyID as ID, SkillsCompetency AS [Name] FROM SkillsCompetency order by SkillsCompetency
ELSE IF @VvcTableName = 'Sector'
BEGIN
IF @VbAll = 0
SELECT S.SectorID as ID, S.SectorName AS [Name]
FROM Sector S
JOIN SectorSubSector SSS ON S.SectorID = SSS.SectorID
JOIN vCurrentAgencySectorSubSector CA ON SSS.SectorSubSectorID = CA.ifkSectorSubSectorId
GROUP BY S.SectorID, S.SectorName
order by SectorName
ELSE
SELECT SectorID as ID, SectorName AS [Name] FROM Sector order by SectorName
END
ELSE IF @VvcTableName = 'SubSector'
BEGIN
IF @VbAll = 0
SELECT (CONVERT(VARCHAR(10), S.SubSectorID) + '00000' + CONVERT(VARCHAR(10), SSS.SectorSubSectorID)) as ID, S.SubSectorName AS [Name]
FROM SubSector S
JOIN SectorSubSector SSS ON S.SubSectorID = SSS.SubSectorID
JOIN vCurrentAgencySectorSubSector CA ON SSS.SectorSubSectorID = CA.ifkSectorSubSectorId
--WHERE SSS.SectorID = @ViLookupID
GROUP BY (CONVERT(VARCHAR(10), S.SubSectorID) + '00000' + CONVERT(VARCHAR(10), SSS.SectorSubSectorID)), S.SubSectorName
ORDER BY SubSectorName
ELSE
SELECT (CONVERT(VARCHAR(10), S.SubSectorID) + '00000' + CONVERT(VARCHAR(10), SSS.SectorSubSectorID)) as ID, S.SubSectorName AS [Name]
FROM SubSector S JOIN SectorSubSector SSS ON S.SubSectorID = SSS.SubSectorID
WHERE SSS.SectorID = @ViLookupID
ORDER BY SubSectorName
END
If I test this with the following called it takes 24ms:
EXEC [dbo].[pGetLookupData]
SkillsCompetency
,null
,0
However if I create a proc to select from the same lookup table as follows:
CREATE PROC dbo.MyTest
AS
SET NOCOUNT ON
SELECT SkillsCompetencyID as ID, SkillsCompetency AS [Name]
FROM SkillsCompetency
order by SkillsCompetency
And then execute it as follows it only takes 5ms:
EXEC dbo.MyTest.
My question is this:
Even though the ms amounts are different the Physical Reads will be the same becasue in the end the table will be read the same, does this mean that both ways are the same and either approach can be used?
Thanks in advance.
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 4, 2008 at 4:21 am
As you've already seen, no they're not both the same. Yes, the number of physical reads is the same, but you'll find that this procedure recompiles, a lot, probably every time it runs. That's an increase in processing time. In this case, is 24ms to 5ms worth worrying about? Maybe not, but have you tested the same procedure under load with multiple users? I suspect you'll see those times go up dramatically as each user has to wait for the previous recompile to complete. How many lookup tables are going to be in your database? If we're talking five or six, this may be no big deal and you could leave it in place (although leaving any poor practice in place almost always leads to trouble down the line). I've seen small systems with 50-60 lookup tables. You're going to maintain that much in a stack of spaghetti code like this? What about if you've 500 tables?
In short, while the physical I/O will likely be very similar, this will place a different load on your system and your long-term maintenance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 4, 2008 at 4:49 am
HI Grant,
Thanks for your reply, and that is pretty much what I was expecting someone to come back and say.
I also believe that you shouldn't put bad practise in place simply because it works in this instance.
Thanks very much again
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 4, 2008 at 5:35 am
And one thing to note about the procedure cache is that only one session may put a specific proc in the cache at a time.
If you have multiple users recompiling a proc at once, one of them will succeed in putting the plan into the cache and then can run the query. The rest have to wait, and may even have to recompile the query again.
Back on SQL 2000 I had a proc with exactly this problem, and I often saw queries waiting 800+ ms to do a cache insert. Once I saw one connection recompile a procedure 5 times before it managed to do the cache insert and run the procedure.
The above doesn't apply if the proc is marked WITH RECOMIPLE, as it's never cached
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply