August 3, 2004 at 3:03 pm
I have tuned this proc from 8 min down to 5 seconds...Yipeee !!
But now I have the bug in me to get it going faster. I have read too many articles today regarding tuning to the point where it all makes no sence. I am relatively new to T-SQL and would appreciate any advice you guys can give as to where I can improve preformance
Thanks for taking the time to check this out
I am calling other procs from the one below and have listed them below this main script
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ALTER PROCEDURE dbo.ReturnAverages_Filtered
(
@AnswerIDList VARCHAR(400)
)
AS
/*
CREATE A TEMPORY TABLE TO STORE THE CALCULATED DATA
ONCE THE STORED PROCEDURE IS COMPLETE WE TRUNCATE
THE LIVE TABLE AND INSERT THE DATA FROM THE TEMP TABLE
*/
CREATE TABLE #TTFilteredAverageData
(
ID INT CONSTRAINT TTNationalAverageData_PK IDENTITY PRIMARY KEY NONCLUSTERED,
QuestionID INT NOT NULL,
AnswerID INT NOT NULL,
SubAnswerID INT NULL,
Average FLOAT NOT NULL
)
DECLARE @ProvIDINT
DECLARE @QuestionID INT
DECLARE @PreviousAnswerID INT
DECLARE @QuestionTypeID INT
DECLARE @AnswerID INT -- VARIABLE TO STORE THE ANSWER ID'S
DECLARE @PageTypeID INT
DECLARE @PathwayID INT
DECLARE @AverageInjections INT
DECLARE @WhereDoctorID VARCHAR(800)
SET @ProvID = 0
SET @PathwayID = 1
set @PreviousAnswerID = 0
SET @AverageInjections = 0
CREATE TABLE #integers(int INT NOT NULL)
EXEC sp_list_to_table @AnswerIDList
CREATE TABLE #doctorIDTable(DoctorIDs INT NOT NULL)
INSERT INTO #doctorIDTable (doctorIDs) (SELECT DISTINCT(DoctorID) as DoctorID FROM demographics inner join #integers ON demographics.AnswerID = #integers.int)
-- SET @WhereDoctorID = 'SELECT DISTINCT(DoctorID) as DoctorID FROM demographics WHERE (AnswerID IN (' + #integers + '))'
-- SET @DoctorIDList = SELECT distinct(DoctorID) as DoctorID FROM demographics WHERE (AnswerID IN (6,11,17))
/* GET A RS FOR ALL THE QUESTIONS FOR PATHWAY X */
DECLARE curr_rsQuestions CURSOR
LOCAL FAST_FORWARD
FOR
SELECT Questions.ID AS questionID, Questions.QuestionTypeID AS questionTypeID, Answers.ID AS answerID, Pages.PageTypeID
FROM QuestionTypes INNER JOIN
Questions ON QuestionTypes.ID = Questions.QuestionTypeID INNER JOIN
QuestionFamilies ON Questions.QuestionFamilyID = QuestionFamilies.ID INNER JOIN
Pages ON QuestionFamilies.PageID = Pages.ID INNER JOIN
Pathways ON Pages.PathwayID = Pathways.ID INNER JOIN
Answers ON Questions.ID = Answers.QuestionID
WHERE (Pathways.ID = @PathwayID) AND (Pages.PageTypeID IN (5, 6, 7, 12)) AND (Questions.QuestionTypeID <= 4)
ORDER BY Pages.ID, Questions.QuestionOrder, Answers.AnswerOrder
OPEN curr_rsQuestions
FETCH NEXT FROM curr_rsQuestions INTO @QuestionID, @QuestionTypeID, @AnswerID, @PageTypeID
WHILE (@@FETCH_STATUS = 0)
BEGIN --START LOOPING THRU QUESTIONS
/* GET A RS FOR ALL THE ANSWERS FOR THIS QUESTION ID */
/* CHECK TO SEE WHAT QUESTION TYPE THIS IS AND DEPENDING ON THE QUESTION TYPE
CALCULATE THE AVERAGES DIFFERENTLY */
-- DECLARE VARIABLES
DECLARE @totalU4TQ INT --VARIABLE TO STORE THE TOTAL USERS WHO ANSWERED THIS QUESTION
DECLARE @totalU4TA INT --VARIABLE TO STORE THE TOTAL USERS WHO ANSWERED THIS ANSEWER
DECLARE @totalTimesAnswered INT --VARIABLE TO STORE THE TOTAL TIMES THIS ANSWER WAS CHOSEN
DECLARE @average FLOAT(2) --VARIABLE TO STORE THE AVERAGE TIMES THIS ANSWER WAS ANSWERED
DECLARE @averageDose FLOAT(2) --VARIABLE TO STORE THE AVERAGE TIMES THIS DOSAGE WAS PRESCRIBED
SET @average = 0
IF @QuestionTypeID = 1 --SINGLE CHOICE
BEGIN
EXEC @totalU4TQ = sp_U4TQ @QuestionID, @ProvID, @PageTypeID, 1 --RETURNS THE TOTAL USERS WHO ANSWERED THIS QUESTION
EXEC @totalU4TA = sp_U4TA @AnswerID, @ProvID, @PageTypeID, 1 --RETURNS THE TOTAL TIMES THIS ANSWER WAS CHOSEN
IF @totalU4TA <> 0 --MAKE SURE THAT WE DON'T DEVIDE BY ZERO
SET @average = (CAST(@totalU4TA AS FLOAT)/CAST(@totalU4TQ AS FLOAT)) * 100 --CALCULATE THE AVERAGE
EXEC Insert_FilteredAverages @QuestionID, @AnswerID, NULL, @Average, @ProvID --INSERT THE DATA INTO #TTFilteredAverageData
END
ELSE
IF @QuestionTypeID = 2 --MULTIPLE CHOICE
BEGIN
EXEC @totalU4TA = sp_U4TA @AnswerID, @ProvID, @PageTypeID, 1 --RETURNS THE TOTAL USERS WHO ANSWERED THIS ANSWER
EXEC @totalTimesAnswered = sp_TimesAnswered @QuestionID, @ProvID, 1 --RETURNS THE TOTAL TIMES THIS ANSWER WAS CHOSEN
if @AnswerID = 361
IF @totalTimesAnswered <> 0 --MAKE SURE WE DON'T DEVIDE BY ZERO
SET @average = (CAST(@totalU4TA AS FLOAT)/CAST(@totalTimesAnswered AS FLOAT)) * 100 --CALCULATE THE AVERAGE'
EXEC Insert_FilteredAverages @QuestionID, @AnswerID, NULL, @Average, @ProvID --INSERT THE DATA INTO #TTFilteredAverageData
END
ELSE
/*
-- THIS QUESTION IS NOT IN USE FOR THE APPLICATION
IF @QuestionTypeID = 3 --SUB QUESTION
BEGIN
END
ELSE
*/
IF @QuestionTypeID = 4 --ROOT
/*
--we already have a list of @AnswerID's 304-312 which represent the injection sites (the ROOT question)
--qType = 5 get a rs of the sum of injection sites (basically count answerID's where = 304-312)
--qType = 6 get a rs of the answerIDs associated with @QuestionType 6 and questionFamily of @QuestionID
--
*/
BEGIN
--BRANCH
IF @PreviousAnswerID <> @AnswerID --WE ONLY NEED TO DO THIS ONCE PER INJECTION SITE
BEGIN
EXEC @averageDose = ReturnAverageDose @QuestionID, @AnswerID, @ProvID, 1 --RETURNS THE AVERAGE DOSE FOR THE SITE
EXEC Insert_FilteredAverages 43, @AnswerID, NULL, @averageDose, @ProvID --INSERT THAT AVERAGE IN THE DATABASE
SET @PreviousAnswerID = @AnswerID --SET THE PREVIOUS ANSWERID TO CURRENT ANSWERID SO WE ONLY DO THIS ONCE PER INJECTION SITE
END
--LEAF
DECLARE @SubAnswerID INT
-- GET A RS OF ALL THE SUB ANSWERS ASSOCITATED WITH THIS QUESTION FAMILY
DECLARE curr_rsSubAnswers CURSOR
LOCAL FAST_FORWARD
FOR
SELECT Answers.ID as subAnswerID
FROM Answers INNER JOIN
Questions ON Answers.QuestionID = Questions.ID INNER JOIN
QuestionTypes ON Questions.QuestionTypeID = QuestionTypes.ID INNER JOIN
QuestionFamilies ON Questions.QuestionFamilyID = QuestionFamilies.ID INNER JOIN
Pages ON QuestionFamilies.PageID = Pages.ID INNER JOIN
Pathways ON Pages.PathwayID = Pathways.ID
WHERE (Questions.QuestionTypeID = 6) AND (Pathways.ID = @PathwayID) AND (Questions.QuestionFamilyID = @QuestionID)
ORDER BY Answers.AnswerOrder
--START LOOPING THRU SUBANSWERS
OPEN curr_rsSubAnswers
FETCH NEXT FROM curr_rsSubAnswers INTO @SubAnswerID
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @averageDose = 0
DECLARE @U4TSA INT --STORES THE USERS 4 THIS SUB ANSWER
DECLARE @TotalTimesSubAnswered INT --STORES THE TOTAL TIMES THIS SUB ANSWER WAS CHOOSEN
EXEC @U4TSA= sp_U4TA @AnswerID, @ProvID, @PageTypeID, 1 --RETURNS THE TOTAL TIMES THIS ANSWER WAS CHOSEN
EXEC @TotalTimesSubAnswered = sp_TotalTimesSubAnswered @AnswerID, @SubAnswerID, @ProvID, 1
IF @TotalTimesSubAnswered <> 0 --MAKE SURE WE DON'T DEVIDE BY ZERO
SET @averageDose = (CAST(@TotalTimesSubAnswered AS FLOAT)/CAST(@U4TSA AS FLOAT)) * 100 --CALCULATE THE AVERAGE
EXEC Insert_FilteredAverages @QuestionID, @AnswerID, @SubAnswerID, @averageDose, @ProvID --INSERT THE DATA INTO #TTFilteredAverageData
FETCH NEXT FROM curr_rsSubAnswers INTO @SubAnswerID
END--END LOOPING THRU SUBANSWERS
--CLEAN UP
CLOSE curr_rsSubAnswers
DEALLOCATE curr_rsSubAnswers
END
FETCH NEXT FROM curr_rsQuestions INTO @QuestionID, @QuestionTypeID, @AnswerID, @PageTypeID --GET THE NEXT RECORD FROM THE RS
END --END LOOPING THRU QUESTIONS
--CLEAN UP
CLOSE curr_rsQuestions
DEALLOCATE curr_rsQuestions
--CLEAN UP AGAIN
DROP TABLE #integers
DROP TABLE #doctorIDTable
SELECT #TTFilteredAverageData.QuestionID, Questions.QuestionTypeID, Questions.TextEN AS QuestionTextEn, Questions.TextFR AS QuestionTextFr,
#TTFilteredAverageData.AnswerID, Answers.TextEN AS AnswerTextEn, Answers.TextFR AS AnswerTextFr, #TTFilteredAverageData.SubAnswerID,
#TTFilteredAverageData.Average, Answers_1.TextEN AS SubAnswerTextEN, Answers_1.TextFR AS SubAnswerTextFR
FROM QuestionFamilies INNER JOIN
Questions ON QuestionFamilies.ID = Questions.QuestionFamilyID INNER JOIN
Pages ON QuestionFamilies.PageID = Pages.ID INNER JOIN
PageTypes ON Pages.PageTypeID = PageTypes.ID RIGHT OUTER JOIN
#TTFilteredAverageData INNER JOIN
Answers ON #TTFilteredAverageData.AnswerID = Answers.ID ON Questions.ID = #TTFilteredAverageData.QuestionID LEFT OUTER JOIN
Answers Answers_1 ON #TTFilteredAverageData.SubAnswerID = Answers_1.ID
WHERE pages.pagetypeID <> 2
ORDER BY #TTFilteredAverageData.ID
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROCEDURE dbo.sp_U4TA
(
@AnswerID INT,
@ProvID INT,
@PageTypeID INT,
@WhereDoctorID INT
)
AS
DECLARE @ReturnVal INT
DECLARE @TableName VARCHAR(15)
DECLARE @SQL NVARCHAR(400)
IF @PageTypeID = 2
SET @TableName = 'Demographics'
ELSE
SET @TableName = 'Assessments'
IF @WhereDoctorID = 0
BEGIN
IF @ProvID = 0 -- for national query
BEGIN
SET @SQL = N'SELECT @ReturnVal = COUNT(*) FROM ' + @TableName + N' WHERE (AnswerID = ' + CONVERT(NVARCHAR(4), @AnswerID) + N')'
EXEC sp_executesql @SQL, N'@ReturnVal varchar(100) output', @ReturnVal output
END
ELSE
BEGIN
SET @SQL = N'SELECT @ReturnVal = COUNT(*) FROM ' + @TableName + N' WHERE (AnswerID = ' + CONVERT(NVARCHAR(4), @AnswerID) + N') AND (ProvinceID = ' + CONVERT(VARCHAR(2), @ProvID) + N')'
EXEC sp_executesql @SQL, N'@ReturnVal varchar(100) output', @ReturnVal output
END
END
ELSE
BEGIN
SET @SQL = N'SELECT @ReturnVal = COUNT(*) FROM ' + @TableName + ' INNER JOIN #doctorIDTable ON ' + @TableName + '.DoctorID = #doctorIDTable.doctorIDs WHERE (AnswerID = ' + CONVERT(VARCHAR(4), @AnswerID) + ')'
EXEC sp_executesql @SQL, N'@ReturnVal varchar(100) output', @ReturnVal output
END
IF @ReturnVal = NULL SET @ReturnVal = 0
RETURN @ReturnVal
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROCEDURE dbo.sp_U4TQ
(
@QuestionID INT,
@ProvID INT,
@PageTypeID INT,
@WhereDoctorID INT
)
AS
DECLARE @ReturnVal as Int
DECLARE @TableName as NVARCHAR(15)
DECLARE @SQL AS NVARCHAR(1000)
IF @PageTypeID = 2
SET @TableName = 'Demographics'
ELSE
SET @TableName = 'Assessments'
IF @WhereDoctorID = 0
BEGIN
IF @ProvID = 0 -- for national query
BEGIN
IF @PageTypeID = 2
SELECT @ReturnVal = COUNT(*)
FROM (SELECT DISTINCT DoctorID
FROM Demographics
WHERE (QuestionID = @QuestionID)) DERIVEDTBL
ELSE
SELECT @ReturnVal = COUNT(*)
FROM (SELECT DISTINCT DoctorAssessmentNum, DoctorID
FROM Assessments
WHERE (QuestionID = @QuestionID)) DERIVEDTBL
END
ELSE
BEGIN
SET @SQL = N'SELECT COUNT(*) as ReturnVal FROM ' + @TableName + ' WHERE QuestionID = ' + CONVERT(VARCHAR(4), @QuestionID) + ' AND (ProvinceID = ' + CONVERT(VARCHAR(2), @ProvID) + ')'
EXEC sp_executesql @SQL, N'@ReturnVal varchar(100) output', @ReturnVal output
END
END
ELSE
BEGIN
SET @SQL = N'SELECT @ReturnVal = COUNT(*) FROM ' + @TableName + ' INNER JOIN #doctorIDTable ON ' + @TableName + '.DoctorID = #doctorIDTable.doctorIDs WHERE (QuestionID = ' + CONVERT(VARCHAR(4), @QuestionID) + ')'
--SET @SQL = N'SELECT COUNT(*) as ReturnVal FROM ' + @TableName + ' WHERE QuestionID = ' + CONVERT(VARCHAR(4), @QuestionID) + N' AND (DoctorID = (' + @WhereDoctorID + N'))'
EXEC sp_executesql @SQL, N'@ReturnVal varchar(100) output', @ReturnVal output
END
IF @ReturnVal = NULL SET @ReturnVal = 0
IF @ReturnVal < 0 SET @returnVal = 0
RETURN @ReturnVal
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROCEDURE dbo.sp_TimesAnswered
(
@QuestionID INT,
@ProvID INT,
@WhereDoctorID INT
)
AS
DECLARE @ReturnVal as INT
IF @WhereDoctorID = 0
BEGIN
IF @ProvID = 0 -- for national query
SELECT @ReturnVal = COUNT(*)
FROM (SELECT DISTINCT DoctorAssessmentNum, DoctorID
FROM Assessments
WHERE (QuestionID = @QuestionID)) DERIVEDTBL
ELSE-- for provincial query
SELECT @ReturnVal = COUNT(*)
FROM (SELECT DISTINCT DoctorAssessmentNum, DoctorID
FROM Assessments
WHERE (QuestionID = @QuestionID) AND (ProvinceID = @ProvID)) DERIVEDTBL
END
ELSE
SELECT @ReturnVal = COUNT(*)
FROM (SELECT DISTINCT DoctorAssessmentNum, DoctorID
FROM Assessments INNER JOIN #doctorIDTable ON Assessments.DoctorID = #doctorIDTable.doctorIDs
WHERE (QuestionID = @QuestionID)) DERIVEDTBL
IF @ReturnVal = NULL SET @ReturnVal = 0
RETURN @ReturnVal
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
August 4, 2004 at 2:34 am
Best Advice: STOP using CURSORS.....the 'relative' performance (with large datasets) compared to SET_BASED PROCESSING is cr*p.
Your best strategy is to segment what you are doing into discrete sets (1 type per major question) and process each set as a unit. ie....get all the info required to complete the insert statement in one select. then write a seperate select statement for the next question. there is enough difference in what you are doing/gathering to justify creating similar but different select statements.
example
declare cursorx for select a,b,c,d....from tableb where xxxx
while @@fetch_status <> 0
insert into tablea (values from cursorx)
read next from cursorx
loop
in 99.9% of cases will perform much slower than the set-based equivalent
insert into tablea
select a,b,c,d from tableb where xxxx
other pointers to look out for are:
make sure all/most columns references in join/where conditions are covered by indices.
look at the execution plans....minimise 'scans'
creating the table outside of this proc....will stop the procedure being re-compiled on every run.
you can use index_hints to force the execution plan to be better than the "system-best-guess"
August 4, 2004 at 7:48 am
I usually go for table variables rather than temp tables where possible. You can create a primary key but can't create indexes on table variables though so if you need more than one index, this option is out.
DECLARE @x TABLE (
ID INT IDENTITY(1,1),
Name VARCHAR(20) PRIMARY KEY CLUSTERED)
August 4, 2004 at 8:00 am
- Wach out for the code used in sub-SPs
IF @ReturnVal = NULL SET @ReturnVal = 0
use IF @ReturnVal IS NULL SET @ReturnVal = 0 or SET @ReturnVal = ISNULL(@ReturnVal, 0)
Also, use output parameter to return value from the SP. RETURN is supposed to be used for status code only. Even better - use function instead.
- Create all tem tables at the very beginning of the SP. Mixing DDL and DML WILL cause unwanted SP recompilation. Do the same for variable declarations.
- Use functions instead of dynamic SQL
- If you can - get rid of cursors. I successfully replaced many cursors used for loop calculations with update statement using variables. Trivial example would be to enumerate rows by increment of 2. E.g.
DECLARE @RowNum INT
SET @RowNum = 2
UPDATE #TempTable
SET RowNum = @RowNum,
@RowNum = @RowNum + 2
(I learned this trick from a great book: "The Guru's Guide to Transact-SQL" by Ken Henderson)
August 4, 2004 at 8:03 am
Thanks for the advice Andrew it is most appreciated.
I can see how cursors are a preformance hit but I don't think I have much choice in the matter. I need to calculate aggregate data based on questionID's and answerID's. So as I loop through the initial recordset I call sp_U4TQ and SP_U4TA to calculate an average based on question and answer ID's that I pass as arguements to the above procs.
I don't see how I can calculate aggregate data using a set_based method?
I will look into implimenting some of the other pointers you listed
While reading yesterday I looked at the execution plan for my script and I tryed to figure out where my procedure was slowing down.
I don't know how to minimise scans?
What are index_hints?
August 4, 2004 at 8:15 am
Thanks Ken and Arif
I don't need to create indexes on the #tempTable so I will impliment the use of a table instead of a temporary table
Definitely I will update the @returnVal = Null to IS NULL as well as creating the tables at the begining of the script to avoid mixing DDL and DML
I will try and get rid of the dynamic SQL. I used it to avoid having the same query repeated with only the table name being changed. I have never used functions in SQL how do they work?
Thanks for the book suggestion I will check it out
August 4, 2004 at 8:36 am
select columnx, count(*) from tablea
group by columnx
is how you do aggregates.
index_hints....spelling/punctuation might be wrong....but look in BOL for info...or search here for the keywords "with index, hint"
scans....prevent by ensuring columns referenced are 'indexed'....an indexz defined on (cola,colb) in that order is no good to prevent a scan if your query is "select colb from tablea"...whereas it'll be great for a query "select cola from tablea"
August 4, 2004 at 8:57 am
Sorry what I meant was average data
So what I am am doing in each itteration of the loop is passing the answerID to SP_U4TA
SET @SQL = N'SELECT @ReturnVal = COUNT(*) FROM ' + @TableName + N' WHERE (AnswerID = ' + CONVERT(NVARCHAR(4), @AnswerID) + N')'
And QuestionID to SP_U4TQ
SELECT @ReturnVal = COUNT(*)
FROM (SELECT DISTINCT DoctorAssessmentNum, DoctorID
FROM Assessments
WHERE (QuestionID = @QuestionID)) DERIVEDTBL
SET @average = (CAST(@totalU4TA AS FLOAT)/CAST(@totalU4TQ AS FLOAT)) * 100
So do you think it is possible to combine those two queries into one that returns average data for a unit of Question and Answer ID's ?? So one query with a unit of questions and answer IDs that calculates for every row the averages using the above queries. Does that make sence?
From your first post that is what I imagined happening not count (*) from
I will check out BOL for index_hints
Scans....I get it now. I have run the Index Tuning Wizzard on all of my queries so I think that the scans are minimized as much as possible but I will double check to see that they are
Thanks again everyone for all your tips thus far
You guys are a great bunch and I am glad to have joined. I only hope that my knowledge thus far will be able to help other out
ok gotta get back to tuning this puppy !
August 4, 2004 at 9:23 am
Averaging is one of the AGGREGATE -type functions.
look up BOL for details.
Select cola, count(colb) , avg(colb) from tablea group by cola
is along the lines of what you want.
note: count(*) NOT always same as count(colb)...NULL's work differently here....again BOL/searhh here for specific issues.
August 4, 2004 at 9:30 am
Mark
Here's a little example that might help with your cursor elimination - at least for the ones that you want to loop through data and process each record individually.
SET NOCOUNT ON
DECLARE @x TABLE (
ID INT IDENTITY(1,1),
Name VARCHAR(20) PRIMARY KEY CLUSTERED)
INSERT INTO @x SELECT 'Jake 1'
INSERT INTO @x SELECT 'Jake 2'
INSERT INTO @x SELECT 'Jake 3'
INSERT INTO @x SELECT 'Jake 4'
SELECT Name AS 'Before'
FROM @x
DECLARE @max-2 INT, @id INT
SELECT @id = 1,
@max-2 = MAX(ID)
FROM @x
WHILE @id <= @max-2
BEGIN
UPDATE @x
SET Name = 'Elwood ' + CONVERT(VARCHAR(2),ID)
WHERE ID = @id
SELECT @id = @id + 1
END
SET NOCOUNT OFF
SELECT Name AS 'After'
FROM @x
As far as eliminating dynamic SQL, try your best but if you're looping through table names and processing each table individually, you may have to resign yourself to using dynamic SQL and moving on. I'd recommend using sp_executesql if possible. Here's a nifty trick to pass 'processed' info back from sp_executesql:
SET NOCOUNT ON
DECLARE @Tables TABLE (
RowID INT IDENTITY(1,1),
TableName VARCHAR(50),
Row_Count INT NULL)
DECLARE @max-2 INT,
@row INT,
@exec_str NVARCHAR(2000),
@tablename VARCHAR(50),
@retcode INT,
@Row_Count INT
INSERT INTO @Tables (TableName)
SELECT Table_Name
FROM Information_Schema.Tables
SELECT @max-2 = MAX(RowID),
@row = 1
FROM @Tables
WHILE @row <= @max-2
BEGIN
SELECT @tablename = TableName
FROM @Tables
WHERE RowID = @row
SELECT @exec_str = "SELECT @Row_Count=COUNT(*) FROM " + @TableName
EXEC @retcode = sp_ExecuteSQL @exec_str, N'@row_count INT OUTPUT', @Row_Count OUTPUT
UPDATE @Tables
SET Row_Count = @Row_Count
WHERE RowID = @row
SELECT @row = @row + 1
END
SELECT * FROM @Tables
SET NOCOUNT OFF
Word wrap may screw up this code, but I think you'll get the point.
Cheers,
Ken
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply