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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.