Solved

SQL Query timeout on 2K8 but not 2K

Posted on 2011-09-08
7
304 Views
Last Modified: 2012-07-25
I wrote a SQL query a few years back against a SQL2K box that emulated a Pivot table and returned the result set in 8 secs or less.  I moved the databases to a SQL2K8 box and, with the same table structure and indexes, that same query now either times out or takes over 100 seconds to process.

What's the difference??

I've attached the query (putting literals in where variables normally go) that demonstrates the behavior.

Jeff

select 
DISTINCT 
case 
 WHEN ASSIGNMENT.COURSECODE = 'E00030' THEN ASSIGNNAME
 ELSE ASSIGNNAME + ' ('+C.COURSE+')'
 END AS ASSIGNNAME, 
assignment.assignid, 
CONVERT(VARCHAR(10),assigndate, 101) as assigndate, 
assigndate,
assignmenttype,
case substring (cast(assignscale as varchar(1)),1,1)
 WHEN 1 THEN '1-4'
 WHEN 2 THEN '1-3'
 WHEN 4 THEN '1-100'
END AS ASSIGNMENTSCALE,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol1,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol2, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol3,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol4, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol5, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol6,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol7, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol8, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol9,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol10, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol11, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol12,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol13, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol14, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol15,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol16, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol17, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol18,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol19,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol20, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol21, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol22,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol23, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol24, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol25,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol26, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol27, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol28,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol29,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol30, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol31, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol32,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol33, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol34, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol35,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol36, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol37, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol38,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol39,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol40, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol41, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol42,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol43, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol44, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol45,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol46, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol47, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol48,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol49,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol50, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol51, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol52,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol53, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol54, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol55,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol56, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol57, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol58,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol59,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol60, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol61, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol62,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol63, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol64, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol65,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol66, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol67, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol68,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol69,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol70, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol71, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol72,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol73, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol74, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol75,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol76, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol77, 
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol78,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol79,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC1,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC2, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC3,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC4, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC5, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC6,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC7, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC8, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC9,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC10, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC11, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC12,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC13, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC14, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC15,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC16, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC17, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC18,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC19,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC20, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC21, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC22,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC23, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC24, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC25,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC26, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC27, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC28,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC29,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC30, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC31, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC32,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC33, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC34, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC35,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC36, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC37, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC38,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC39,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC40, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC41, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC42,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC43, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC44, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC45,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC46, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC47, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC48,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC49,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC50, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC51, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC52,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC53, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC54, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC55,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC56, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC57, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC58,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC59,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC60, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC61, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC62,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC63, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC64, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC65,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC66, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC67, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC68,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC69,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC70, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC71, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC72,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC73, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC74, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC75,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC76, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC77, 
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC78,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC79
from assignment
join assigncat ac on assignment.assignid = ac.assignid
join courses c on c.coursecode = assignment.coursecode
where sectcode = '106'
and assigngrade = 6
and teachcode = '120003'
and assignmenttype like 'S'
and
((ASSIGNDATE >= '08/20/2011') and (convert(varchar(12), ASSIGNDATE, 101) <= '12/31/2011'))
and
(
(ac.catcode in (select catcode from cour_cat where coursecode = 'E00030'))
and
(ac.[apply] = 1)
)
order by assignment.assigndate

Open in new window

0
Comment
Question by:jswanberg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36507531
jswanberg, hmm. I started to read the question and was going to say look at the "execution plan" to see what is going on. However, after reading further and seeing the query, (1) you SHOULD re-write this using PIVOT if that is what it is trying to emulate; however, (2) you seem to be grabbing the same exact value or using the same table with the same filter for a number of the columns, so a simple JOIN or CROSS APPLY to accomplish this. In a nutshell, I would recommend you rewrite this.
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 36507566
Couple other direct performance points to consider (execution plan review should provide factual support):
- (convert(varchar(12), ASSIGNDATE, 101) <= '12/31/2011') is better written as ASSIGNDATE < '01/01/2012' -- i.e., ASSIGNDATE < DATEADD(dd, 1, '12/31/2011') -- otherwise, your index on ASSIGNDATE is potentially not going to be used.
- (ac.catcode in (select catcode from cour_cat where coursecode = 'E00030')), this may be hurting you -- you can try (exists(select 1 from cour_cat cc where cc.catcode = ac.catcode and coursecode = 'E00030')) or simply add an inner join based on your data.
- Expanding on the "based on your data" above, I would look at the relationships as I see you are using DISTINCT. If you can return your query in a unique manner in the first place, you can eliminate that potential overhead.
- assignmenttype like 'S' should just be assignmenttype = 'S' -- not really a performance thing, juts more correct since you are not doing any patterns there.
- check for unnecessary implicit conversions. You know your data, but for example something like this looks interesting:

case substring (cast(assignscale as varchar(1)),1,1)
 WHEN 1 THEN '1-4'
 WHEN 2 THEN '1-3'
 WHEN 4 THEN '1-100'
END AS ASSIGNMENTSCALE

You are converting a variable to a VARCHAR(1) then substring() it from the 1st character for 1 character, then compare the result to integers in CASE/WHEN.

Hope that helps you get going. Post back once you have tried something and we can help you through the process.
0
 

Author Comment

by:jswanberg
ID: 36507672
Yeah I knew that it needed rewriting especially since PIVOT didn't exist in 2K but does in 2K8.  I was more interested in the meantime in why the exact same query responded in 2K one way and so much more of a drag in 2K8.

Is it something in the structure that I can change to make it perform like 2K while I'm simultaneously rewriting the query??

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36507767
Run the explain plan and we can see exactly what is going on. There is so many things there, it could be indexing or something like that. It may have worked in 2k, but with change in database engine and query optimizer since then the query is being treated in some different way ... could be something silly, so looking at the execution plan will help narrow it down.
0
 

Assisted Solution

by:jswanberg
jswanberg earned 0 total points
ID: 36507794
There is no individual execution cost; the issue is the 160 separate nested queries now make up 97% cost to the overall but each one is only showing a 1% hit.  There are no hits against suggested indexes.

Looks like I'm stuck until I can rewrite the query to use the PIVOT command.
0
 
LVL 3

Expert Comment

by:hspoulsen
ID: 36509301
Is this a new SQL Server 2008?

If so, did you update the stats?
or
Rebuild indexes, then update stats?

If you are not the only user on the database, were you blocked by others?
(use Sp_whoisactive shows this)

Best regards,
Henrik Staun Poulsen, Stovi Software
0
 

Author Closing Comment

by:jswanberg
ID: 38222036
Gave insight on other items in the query that could be affecting performance without having to rewrite as a PIVOT query
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question