Complicated Stored Procedures - SQL Server Forums

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

Transact-SQL (2000)

Complicated Stored Procedures

Author

Topic

J
Starting Member

5 Posts

Posted - 2004-09-24 : 17:08:24

I am new to T-SQL and want to start bringing my access queries to stored procedures to lower the overhead on my front end and increase performance -
My question is, is this the best way to do it? the first select and second Select use to be two individual queries joined in a third. Also how do I format ([Diagcnt]*.100)/[breedcnt])so that it shows as a percent

DROP TABLE #BreedCnt
DROP TABLE #BreedDiagCnt
SELECT tblDog.dog_Breed, Count(tblDog.dog_DogID) AS BreedCnt
INTO #BreedCnt
FROM tblDog
WHERE (((tblDog.dog_GDBTattoo) Is Not Null And (tblDog.dog_GDBTattoo)<>'0000' ) AND ((tblDog.dog_FYWhelped)>='1995' ))
GROUP BY tblDog.dog_Breed

SELECT thsVetDiagHist.dh_Breed, thsVetDiagHist.dh_DiagCode, Count(thsVetDiagHist.dh_DogID) AS DiagCnt
INTO #BreedDiagCnt
FROM tblDog INNER JOIN thsVetDiagHist ON tblDog.dog_DogID = thsVetDiagHist.dh_DogID
WHERE (((tblDog.dog_FYWhelped)>='1995'))
GROUP BY thsVetDiagHist.dh_Breed, thsVetDiagHist.dh_DiagCode

SELECT #BreedCnt.dog_Breed, #BreedDiagCnt.dh_DiagCode, ([Diagcnt]*.100)/[breedcnt] AS BreedIR
FROM #BreedCnt INNER JOIN #BreedDiagCnt ON #BreedCnt.dog_Breed = #BreedDiagCnt.dh_Breed
GROUP BY #BreedCnt.dog_Breed, #BreedDiagCnt.dh_DiagCode, ([Diagcnt]*.100)/[breedcnt];

Kristen
Test

22859 Posts

Posted - 2004-09-25 : 06:12:07

You could combine them into a single query (although the performance may suffer), or do the first one into a temporary table and then have the second one JOIN to that. (Sorry, I'd knock up an example for you, but I am against a dealine this morning so not very long to play SQLTeam :-(

The DROP TABLE statements will cause an SProc to fail, because the tables won't already exist. Put them at the end of the code so that the temporary tables are dropped after use (technically they will be dropped anyway, so there is no need, but it keeps you code tidy and ensures that they are dropped at the EARLIEST opportunity - releasing space in the TEMP database).

Alteratnively, you could use Table Variables (which will probably be faster)

Is speed is a consideration pre-CREATE your temporary tables - there is an overhead in using SELECT * INTO #MytempTable, and it prevents the query plan cache doing the best that it can. Make SURE that you create a primary key on your temporary tables - makes a big difference to performance, eeven if ther eare very few rows invovled (seems daft to me, as everything is in memory, but that's been my experience)

I suggest you put "dbo." in front of the table names in the FROM staement so that the owner is not ambiguous.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources