Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Queries running slower in SQL 2008 than in SQL 2000 Expand / Collapse
Author
Message
Posted Saturday, May 16, 2009 4:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 16, 2009 6:35 AM
Points: 4, Visits: 21
hi guys,

Actually, i solved my problem by changig the query to this one :

SELECT DISTINCT Cast(Cast(tablre1.Col1 as nVarchar(2))+Cast(tabble1.col2 as nVarchar(3))) as nvarchar(5)) , Cast(Cast(tablre2.Col1 as nVarchar(2))+Cast(tabble2.col2 as nVarchar(3))) as nvarchar(5))
FROM Table1 left outer join table2
on Cast(Cast(tablre1.Col1 as nVarchar(2))+Cast(tabble1.col2 as nVarchar(3))) as nvarchar(5))
= Cast(Cast(tablre2.Col1 as nVarchar(2))+Cast(tabble2.col2 as nVarchar(3))) as nvarchar(5))
AND Cast(Cast(tablre2.Col1 as nVarchar(2))+Cast(tabble2.col2 as nVarchar(3))) as nvarchar(5)) IS NULL

It works definitly better this way...

But i'm still a bit perplex about SQL Server 2008, because it seems that NOT IN and NOT EXISTS are not the only statements that are running slower on SQL Server 2008 than SQL Server 2000 on big tables...
Post #718483
Posted Sunday, June 15, 2014 7:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 11:44 AM
Points: 20, Visits: 34
Our 2000 server was upgraded to 2008. There is a certain step in a DTS now SSIS package that updates a table, setting parents for child records, that used to run in 50 seconds in 2000. It never finished overnight in 2008 so I had to replace it with a WHILE loop which runs in 30-50 minutes now. It does have a subquery, but it's very simple. The flat file does not have any parent - child link (foreign key) so the reason for this step is to set up that relationship. Here are the details:
- table gets truncated and re-imported from daily flat file;
- 2.5 million+ rows;
- 3 types of records in it, parent and two types of child records;
- identity column TheIdNr , with index;
- recreating index manually after import doesn't improve the performance;
- UPDATE C SET TheParent = (SELECT MAX(TheIdNr) FROM myStagingTable P WHERE P.TheIdNr < C.TheIdNr AND P.RecordType ='Parent' )
FROM myStagingTable C WHERE C.RecordType in ('Child1', 'Child2)

Is this "normal" in 2008 SQL Server?
Post #1580936
Posted Sunday, June 15, 2014 9:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:03 AM
Points: 36,975, Visits: 31,492
RazvanS (6/15/2014)
Our 2000 server was upgraded to 2008. There is a certain step in a DTS now SSIS package that updates a table, setting parents for child records, that used to run in 50 seconds in 2000. It never finished overnight in 2008 so I had to replace it with a WHILE loop which runs in 30-50 minutes now. It does have a subquery, but it's very simple. The flat file does not have any parent - child link (foreign key) so the reason for this step is to set up that relationship. Here are the details:
- table gets truncated and re-imported from daily flat file;
- 2.5 million+ rows;
- 3 types of records in it, parent and two types of child records;
- identity column TheIdNr , with index;
- recreating index manually after import doesn't improve the performance;
- UPDATE C SET TheParent = (SELECT MAX(TheIdNr) FROM myStagingTable P WHERE P.TheIdNr < C.TheIdNr AND P.RecordType ='Parent' )
FROM myStagingTable C WHERE C.RecordType in ('Child1', 'Child2)

Is this "normal" in 2008 SQL Server?


What you have there is what is known as a "Triangular Join" and, if you got it to run as fast as you say you did in SQL Server 2000, you got very, very lucky with the indexes being just right and several other things. Please see the following article on what "Triangular Joins" are and why they're so bad for performance.
http://www.sqlservercentral.com/articles/T-SQL/61539/

So, what's the alternative? "It Depends". In order to be able to help you out, we need more information about the table and some readily consumable data. Please don't assume that you know how to do that. Please use the methods outlined in the article at the first link under "Helpful Links" in my signature line below. Thanks.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1580938
Posted Wednesday, June 18, 2014 11:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 11:44 AM
Points: 20, Visits: 34
Thank you Jeff, that is a good article. A few more considerations:
- adding with (nolock) in the select max subquery halves the time in the while loop approach here from 40 to 20 minutes;
- creating another staging table with just the parents in it (about 180K rows), having an index on the integer id field, and using that one instead of the same table in the subquery, solves completely the issue, it runs now without loop in 20 seconds, updating the 2.4+ million child records:
...
Insert Into dbo.ParentStage
SELECT TheIdNr From dbo.myStagingTable where RecordType ='Parent' order by 1

UPDATE M
set M.TheParent =(Select max(TheIdNr ) From dbo.ParentStage where TheIdNr< M.TheIdNr )
FROM dbo.myStagingTable M
where M.RecordType !='Parent'
Post #1583256
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse