| Author |
Topic |
|
linda9898
Starting Member
28 Posts |
 Posted - 2008-03-30 : 11:01:27
|
| Hi ,I am working on a DWH project, and we decided few days ago to move from 2000 to 2005.we installed the 2005 on the same server with two different instances for testing the 2005.I migrate everything with the indexes but the issue is that queries at the 2005 take much more time than the same query on the 2000.for example i have a nasty qry which join 14 tables. at the 2000 its take around 2.5 - 3 mins and at the 2005 its infinity , they both have the same indexes.PLS ANYONE HAVE ANY IDEA WHATS THE PROBLEM ? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-03-30 : 11:05:37
|
| have you rebuilt indexes in the 2k5 the db?are joining columns of the same datatype?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
linda9898
Starting Member
28 Posts |
Posted - 2008-03-30 : 11:11:00
|
| hi,i craeted the indexes to be the same that i had on the 2000, and about the join yes offcourse all of them the same datatypes |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-03-30 : 11:12:53
|
| take a look at the query plans... you might want to try to recompile them._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
linda9898
Starting Member
28 Posts |
Posted - 2008-03-30 : 11:16:15
|
| is there any difference for how qrys should be written between 2000 and 2005 ? because on the 2000 everything works perfect |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-03-30 : 11:18:18
|
| ss2k5 has some improved T-SQL functionality but without looking at the queries itself i can't say anything specific._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
linda9898
Starting Member
28 Posts |
Posted - 2008-03-30 : 11:23:05
|
| here is the qrySELECT t.ID_Time, mc.ID_Marketing_Channel, vs.[ID_Service], IsNull(fr.ID_From,(select id_from from dim_from where id_iata_from = '999')) As ID_From, --When there is no match find the id of the unknown origin and destination IsNull(dto.ID_To,(select id_to from dim_to where id_iata_to = '999')) As ID_To, cc.ID_Agency, sc.ID_Sales_Channel, ss.ID_Supplier, toc.ID_Concurrent, b.ID_Brand, smi.Income_Gross, smi.Commission, smi.Income_Net, sfi.Cnt_Cpn_Vch, sfi.Cnt_pax, ISNULL(v.Concurrent_Total_Seats, 0)AS Cnt_Total_Concurrents, sfi.Cnt_Capacity, sfi.Cnt_Chd, sfi.Cnt_Inf, sfi.Cnt_Legs, sfi.Cnt_Mileage, s.obj_index As [PNR_Num], s.serv_num As [SRV_Num], s.sc_flight As [Gilboa_Flight_Num], sfi.Flight_Num FROM Gil_Services s Inner Join Dim_Time t On s.start_dt = t.Time_Stamp Inner Join SA_PNR_Marketing_Channel mc On s.obj_index = mc.PNR_Num Inner Join VW_SA_SRV_Dec_Service vs On s.serv_num = vs.SRV_Num Inner Join SA_SRV_From_To ft On s.serv_num = ft.SRV_Num Left Join Dim_From fr On fr.ID_Iata_From = ft.ID_Iata_From Left Join Dim_To dto On dto.ID_Iata_To = ft.ID_Iata_To Inner Join SA_PNR_Agency cc On cc.PNR_Num = s.obj_index Inner Join SA_SRV_Sales_Channel sc On sc.SRV_Num = s.serv_num Inner Join SA_SRV_Suppliers ss ON ss.SRV_Num = s.serv_num Inner Join SA_PNR_Type_of_Customer toc On s.obj_index = toc.PNR_Num Inner Join SA_PNR_Brand b On s.obj_index = b.PNR_Num Inner Join SA_SRV_Fact_Money_Income smi On smi.SRV_Num = s.serv_num Inner Join SA_SRV_Fact_Income sfi On sfi.SRV_Num = s.serv_num Left Join VW_Conurrent_Service_Income v On v.serv_num = s.serv_num |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-03-30 : 11:30:49
|
| are the execution plans the same for 2000 an 2005 query?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-30 : 11:36:00
|
| how did you migrate from 2000 to 2005 ? Did you update stats and maybe reorganize/rebuild index for tables? |
|
|
|
linda9898
Starting Member
28 Posts |
Posted - 2008-03-30 : 11:39:54
|
| the execution plans for the 2000 is 38.25%and for the 2005 is 100% |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-30 : 11:44:09
|
| Check whether Index are fragmented with DBCC showcontig in SQL 2005 |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-03-30 : 11:44:17
|
| i mean do they look the same? do you know how to read them?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
linda9898
Starting Member
28 Posts |
Posted - 2008-03-30 : 11:54:40
|
| Posted - 03/30/2008 : 11:44:17-------------------------------------------------------------------------------- i mean do they look the same? do you know how to read them?they dont look the same and i dont realy know how to read it |
|
|
|
linda9898
Starting Member
28 Posts |
Posted - 2008-03-30 : 11:56:32
|
| Check whether Index are fragmented with DBCC showcontig in SQL 2005i rebuilt all the indexes and the statistics as u said , but it still doesnt work.about the DBCC showcontig , i am not so familiar with it so i'll better raed a bit about it |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-03-30 : 12:27:14
|
you'll have to do some exec plan analisys to figure out what's wrong and how to fix it.it's not as simple as "i upgraded and i want it to work the same"  did you set the db compatibility level to 90 or is it still on 80?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
|
HELLLP !!! Moving from sql 2000 to 2005