SQL Performance Issue

Question

SQL Performance Issue

jay-125866

SSCommitted

Points: 1629
More actions
July 21, 2015 at 6:14 pm

#300560

We're using a survey tool, Votations Nsurvey, to build a web form, and running into a SQL performance issue. Initially, when DB size is still relatively small, about several thousands of records. Now it has grown to hundreds of thousands records, and one of the Stored Procedure runs very slow, take about 30-50 seconds to complete.
The query is basically to select all questions and answers specific form, and then write it out to a web page. See attached file for SQL query.
I'm looking for any advises and suggestions on how to improve this query.
Thank you all very much.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

1) You have what are almost certainly views in that query, so G-d only knows what the decompiled query looks like. I have seen queries like that net out to HUNDREDS of lines of SELECT statement.

2) How many rows are returned?

3) Have you checked for blocking/waits during execution? sp_whoisactive for that

4) Have you done wait stats analysis during execution?

5) Have you done file IO stall analysis during execution?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

Some DDL for the tables and underlying objects involved would be helpful. Note the link in my subject line.

Also, could you include the query plan that this query generates?

From just a quick look at your query I can tell you that the ORDER BY is probably not doing you any favors.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Table definitions, index definitions and the actual execution plan please?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Emil B SSCertifiable Points: 5568 More actions · Answer 4

you can as well try sp_helpExpandView from Andy Yun to understand what is lying underneath your views

https://sqlbek.wordpress.com/2015/03/03/debuting-sp_helpexpandview/[/url]

jay-125866 SSCommitted Points: 1629 More actions · Answer 5

Thank you for your reply, Kevin.

Per your questions:

#1. Yes, it uses views, instead of tables;

#2. It only returns about 5-10 rows per question ID. It take 1-2 second run for one question. However, there are about 10 to 20 questions in a given survey form, and together, it takes about 30-50 seconds to complete entire form questionnaire.

#3. Not yet, but will try it today;

#4. Not yet, and need to learn how to do it first;

#5. Not yet, and need to learn how to do it first;

Again, thank you very much for your advises.