Query taking long time to run on 2008 didnt on 2005

[rkrause]

Below is my query. We did a recent upgraded from SQL 05 to 08 and before this query would run in about 7-8 seconds on sql 2005, now in 2008 it takes well over 3 minutes to complete. any ideas why or what i can do?




select receiptdate, lotnumber, receiptquantity, palletcode, pallettestoneresult
, Cast(pallettestoneresult as varchar(40))as protein
, convert(decimal(4,2), pallettestoneresult) as proteintest
, 24'targetlow', 24.5'targethigh'
,CASE WHEN convert(decimal(4,2), pallettestoneresult) > 24.5
THEN ( receiptquantity * ((convert(decimal(4,2), pallettestoneresult))/100) / ((24.5/100)) - receiptquantity )
ELSE 0 END AS dolamt
,CASE WHEN convert(decimal(4,2), pallettestoneresult) > 24.1
THEN ( receiptquantity * ((convert(decimal(4,2), pallettestoneresult))/100) / ((24.1/100)) - receiptquantity )
ELSE 0 END AS dolamt2
--avg(convert(decimal(4,2), pallettestoneresult)) as total
from bk_delacproteinchart
where receiptdate = '4-20-2012'
group by receiptdate, lotnumber, receiptquantity, palletcode, pallettestoneresult
order by palletcode

 

[mdlueck]

Have you checked the query's Access Plan in the Management Studio? Is it using indexes for all operations or falling back to table scan method?

 

[rkrause]

im not sure how to do that, can you tell me? what would change in sql versions that would slow this down in sql 2008?

 

[SQL_Hell]

do you have any indexes already?
Why the need for the group by? this looks suspiciously like a converted access query
Do you need the order by?

 

[rkrause]

I have no indexes and i didnt have any before either on my sql 2005 server. i can take out the group by and order by and it still takes 2:50 to execute.

 

[mdlueck]

im not sure how to do that, can you tell me?

Microsoft SQL Server Management Studio
Open the query located in a text file ending with .sql extension Make sure you have:

USE [TheDatabaseName]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

[COLOR=Blue]-- Define query here...[/COLOR]

GO

SET ANSI_PADDING OFF
GO

Query menu \ Display Estimated Execution Plan

View the results and pay attention to the Query Cost flowchart. You want to see it choosing to use Indexes for operations... "Index scan / Index seek".

what would change in sql versions that would slow this down in sql 2008?

Anything, That is a major version jump. Just the usual "upgrade pains".

I have no indexes

Then perhaps it is time for you to stregegically add some. The Execution Plan will show you were index(es) are needed.

 

[SQL_Hell]

The Execution Plan will show you were index(es) are needed.

As a side note, I wouldn't rely on that, it more often than not misses out good indexes to add. If you are talking about the index suggestions in the execution plan. However if you are tlaking about the execution plan in general then yes

Sorry if this is confusing, hard to explain

 

[SQL_Hell]

just out of interest, did you re-create indexes and statistics when you upgraded to sql server 2008? and did you update the database compatability level?

 

[rkrause]

i was able to figure this out, i dont have any explanation on why it woudlnt run faster on the new 2008 server, but i had another view that this view was pointing too, and it had a > 1 on a field in a where clasue, so i changed it in the select statement to a MAX

 

[SQL_Hell]

Seems a strange solution, I wonder if it had a bad execution plan. Try setting back to > 1 and see if the problem is still there