Need help with sql optimization...

Hi,

If possible, can someone please help me optimize the below sql code and/or at least reduce any possible repeating code?

SET NOCOUNT ON

-- Create a table variable to store user data
DECLARE @myTable TABLE
(
    RowID INT IDENTITY(1,1),
    UserID INT,
      BusinessType VARCHAR(1000) NULL,
      ProductsInterest VARCHAR(1000) NULL,
      ProductColors VARCHAR(1000) NULL,
      ProductCondition VARCHAR(1000) NULL,
      AnnualSpending VARCHAR(1000) NULL
)

-- Insert data into @myTable
INSERT INTO @myTable(UserID)
SELECT U.UserId
FROM Users U
INNER JOIN UserRegistration R ON U.UserId = R.UserId
WHERE R.StatusID = 1
ORDER BY U.UserId DESC

-- Declare required variables
DECLARE @RowCount INT, @I INT, @AnswerList_ProdInterest VARCHAR(1000), @AnswerList_ProdColors VARCHAR(1000), @AnswerList_ProdCondition VARCHAR(1000)
-- Get the number of rows in the looping table
SET @RowCount = (SELECT COUNT(RowID) FROM @myTable)
-- Initialize the iterator
SET @I = 1

-- Loop through the rows of @myTable
WHILE (@I <= @RowCount)
      BEGIN
            SELECT @AnswerList_ProdInterest = COALESCE(@AnswerList_ProdInterest + ', ', '') + A.Answer
            FROM UserSurvey_Answer A
            WHERE A.SurveyID  = (SELECT TOP 1 S.SurveyID
                                           FROM @myTable T
                                           LEFT JOIN UserSurvey S ON T.UserID = S.UserID
                                           WHERE T.RowID = @I
                                             AND S.Question = 'Please select the products that interest you.'
                                           ORDER BY S.SurveyID DESC)

            SELECT @AnswerList_ProdColors = COALESCE(@AnswerList_ProdColors + ', ', '') + A.Answer
            FROM UserSurvey_Answer A
            WHERE A.SurveyID  = (SELECT TOP 1 S.SurveyID
                                           FROM @myTable T
                                           LEFT JOIN UserSurvey S ON T.UserID = S.UserID
                                           WHERE T.RowID = @I
                                             AND S.Question = 'Please select the product colors of interest.'
                                           ORDER BY S.SurveyID DESC)

            SELECT @AnswerList_ProdCondition = COALESCE(@AnswerList_ProdCondition + ', ', '') + A.Answer
            FROM UserSurvey_Answer A
            WHERE A.SurveyID  = (SELECT TOP 1 S.SurveyID
                                           FROM @myTable T
                                           LEFT JOIN UserSurvey S ON T.UserID = S.UserID
                                           WHERE T.RowID = @I
                                             AND S.Question = 'Preferred product condition. (Please rank all options in order of interest. 5 - Most interested, 1 - Not interested.)'
                                           ORDER BY S.SurveyID DESC)

            UPDATE T
               SET BusinessType = (SELECT TOP 1 Answer FROM UserSurvey_Answer WHERE SurveyID = (SELECT TOP 1 SurveyID FROM UserSurvey WHERE UserID =

U.UserId AND Question = 'Please select your business type.' ORDER BY SurveyID DESC) ORDER BY DateEntered DESC),
                     ProductsInterest = @AnswerList_ProdInterest,
                     ProductColors = @AnswerList_ProdColors,
                     ProductCondition = @AnswerList_ProdCondition,
                     AnnualSpending = (SELECT TOP 1 Answer FROM UserSurvey_Answer WHERE SurveyID = (SELECT TOP 1 SurveyID FROM UserSurvey WHERE UserID

= U.UserId AND Question = 'What is your approximate annual spending?' ORDER BY SurveyID DESC) ORDER BY DateEntered DESC)
            FROM @myTable T
            INNER JOIN Users U ON T.UserID = U.UserId
            WHERE T.RowID = @I

            -- Reset all '@AnswerList_' related variables
            SET @AnswerList_ProdInterest = NULL
            SET @AnswerList_ProdColors = NULL
            SET @AnswerList_ProdCondition = NULL
        -- Increment the iterator
        SET @I = @I  + 1
      END

SET NOCOUNT OFF

-- Return the requested columns of data
SELECT U.FirstName, U.LastName, U.Email, U.City, U.State, U.Zip, U.UserID,
      'Please select your business type.'  = T.BusinessType, 'Please select the products that interest you.'  = T.ProductsInterest,
      'Please select the product colors of interest.'  = T.ProductColors, 'Preferred product condition. (Please rank all options in order of interest. 5 - Most interested, 1 - Not interested.)'  = T.ProductCondition,
      'What is your approximate annual spending?'  = T.AnnualSpending
FROM @myTable T
INNER JOIN Users U ON T.UserID = U.UserId    
ORDER BY U.DateCreated DESC

Many thanks in advance.
WebAppDeveloperAsked:
Who is Participating?
 
edemcsCommented:
Honestly, for doing this in the db, I think your solution is sound except for the UPDATE statement at the end of your while.  I would break out some of your nested selects and assign the variables the values before entering the UPDATE statement.
0
 
RGBDartCommented:
Can you privide me with scripts for tables
User, UserSurvey_Answer, UserSurvey ?

Your target willl look like this:
(I'll need to check syntax, when you'll give me scripts)
SELECT U.UserId, 
	ISNULL(A1.Answer, '') as ProductCondition, 
	ISNULL(A2.Answer, '') as ProductColors, 
	ISNULL(A3.Answer, '') as ProductsInterest, 		
	(SELECT TOP 1 Answer 
		FROM UserSurvey_Answer A4		
		WHERE A4.UserId = U.UserId
			SurveyID = 
			(SELECT TOP 1 SurveyID 
				FROM UserSurvey 
				WHERE UserID = U.UserId 
					AND Question = 'What is your approximate annual spending?' 
				ORDER BY SurveyID DESC) 
		ORDER BY DateEntered DESC) as AnnualSpending	
FROM Users U 
	INNER JOIN UserRegistration R ON U.UserId = R.UserId
	left join UserSurvey_Answer A1 on U.UserId = A1.UserID and A1.SurveyID = 
		(SELECT TOP 1 S.SurveyID 
		   from UserSurvey S
		   WHERE S.Question = 'Please select the products that interest you.'
				and S.UserID = U.UserId
		   ORDER BY S.SurveyID DESC)
	left join UserSurvey_Answer A2 on U.UserId = A2.UserID and A2.SurveyID = 
		(SELECT TOP 1 S.SurveyID 
		   from UserSurvey S
		   WHERE S.Question = 'Please select the product colors of interest.'
				and S.UserID = U.UserId
		   ORDER BY S.SurveyID DESC)
	left join UserSurvey_Answer A3 on U.UserId = A3.UserID and A3.SurveyID = 
		(SELECT TOP 1 S.SurveyID 
		   from UserSurvey S
		   WHERE S.Question = 'Preferred product condition. (Please rank all options in order of interest. 5 - Most interested, 1 - Not interested.)'
				and S.UserID = U.UserId
		   ORDER BY S.SurveyID DESC)		   		   		
WHERE R.StatusID = 1 
ORDER BY U.UserId DESC

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Without knowing nothing about your data model, the only thing I can help it's with SELECT TOP 1 ... ORDER BY. Those statements can and should be substituted by EXISTS. Check the code below.
SET NOCOUNT ON

-- Create a table variable to store user data
DECLARE @myTable TABLE
(
    RowID INT IDENTITY(1,1),
    UserID INT,
      BusinessType VARCHAR(1000) NULL,
      ProductsInterest VARCHAR(1000) NULL,
      ProductColors VARCHAR(1000) NULL,
      ProductCondition VARCHAR(1000) NULL,
      AnnualSpending VARCHAR(1000) NULL
)

-- Insert data into @myTable
INSERT INTO @myTable(UserID)
SELECT U.UserId 
FROM Users U 
INNER JOIN UserRegistration R ON U.UserId = R.UserId
WHERE R.StatusID = 1 
ORDER BY U.UserId DESC

-- Declare required variables
DECLARE @RowCount INT, @I INT, @AnswerList_ProdInterest VARCHAR(1000), @AnswerList_ProdColors VARCHAR(1000), @AnswerList_ProdCondition VARCHAR(1000)
-- Get the number of rows in the looping table
SET @RowCount = (SELECT COUNT(RowID) FROM @myTable)
-- Initialize the iterator
SET @I = 1

-- Loop through the rows of @myTable
WHILE (@I <= @RowCount)
      BEGIN
            SELECT @AnswerList_ProdInterest = COALESCE(@AnswerList_ProdInterest + ', ', '') + A.Answer
            FROM UserSurvey_Answer A
            WHERE EXISTS (SELECT 1
                          FROM @myTable T
                          LEFT JOIN UserSurvey S ON T.UserID = S.UserID
                          WHERE T.RowID = @I
                            AND S.Question = 'Please select the products that interest you.'
                            AND S.SurveyID  = A.SurveyID)


            SELECT @AnswerList_ProdColors = COALESCE(@AnswerList_ProdColors + ', ', '') + A.Answer
            FROM UserSurvey_Answer A
            WHERE EXISTS (SELECT 1 
							FROM @myTable T
                            LEFT JOIN UserSurvey S ON T.UserID = S.UserID
                            WHERE T.RowID = @I
								AND S.Question = 'Please select the product colors of interest.'
								AND S.SurveyID  = A.SurveyID)

            SELECT @AnswerList_ProdCondition = COALESCE(@AnswerList_ProdCondition + ', ', '') + A.Answer
            FROM UserSurvey_Answer A
            WHERE EXISTS (SELECT 1 
							FROM @myTable T
                            LEFT JOIN UserSurvey S ON T.UserID = S.UserID
                            WHERE T.RowID = @I
								AND S.Question = 'Preferred product condition. (Please rank all options in order of interest. 5 - Most interested, 1 - Not interested.)'
								AND S.SurveyID  = A.SurveyID)

            UPDATE T
               SET BusinessType = (SELECT TOP 1 Answer 
									FROM UserSurvey_Answer 
									WHERE EXISTS (SELECT 1
														FROM UserSurvey 
														WHERE UserID = U.UserId
															AND Question = 'Please select your business type.'
															AND UserSurvey.SurveyID  = UserSurvey_Answer.SurveyID)
									ORDER BY DateEntered DESC),
                     ProductsInterest = @AnswerList_ProdInterest,
                     ProductColors = @AnswerList_ProdColors,
                     ProductCondition = @AnswerList_ProdCondition,
                     AnnualSpending = (SELECT TOP 1 Answer 
										FROM UserSurvey_Answer 
										WHERE SurveyID = (SELECT TOP 1 SurveyID 
															FROM UserSurvey 
															WHERE UserID = U.UserId
																AND Question = 'What is your approximate annual spending?' 
																AND UserSurvey.SurveyID  = UserSurvey_Answer.SurveyID)
										ORDER BY DateEntered DESC)
            FROM @myTable T
            INNER JOIN Users U ON T.UserID = U.UserId
            WHERE T.RowID = @I

            -- Reset all '@AnswerList_' related variables
            SET @AnswerList_ProdInterest = NULL
            SET @AnswerList_ProdColors = NULL
            SET @AnswerList_ProdCondition = NULL
        -- Increment the iterator
        SET @I = @I  + 1
      END

SET NOCOUNT OFF

-- Return the requested columns of data
SELECT U.FirstName, U.LastName, U.Email, U.City, U.State, U.Zip, U.UserID,
      'Please select your business type.'  = T.BusinessType, 'Please select the products that interest you.'  = T.ProductsInterest,
      'Please select the product colors of interest.'  = T.ProductColors, 'Preferred product condition. (Please rank all options in order of interest. 5 - Most interested, 1 - Not interested.)'  = T.ProductCondition,
      'What is your approximate annual spending?'  = T.AnnualSpending 
FROM @myTable T 
INNER JOIN Users U ON T.UserID = U.UserId    
ORDER BY U.DateCreated DESC

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
No comments about the solutions?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.