Author |
Topic |
cornall
Posting Yak Master
148 Posts |
Posted - 2010-07-20 : 09:19:14
|
Hi,I have migrated a database from a 2000 server to a 2008 server. I join on the same table several times with different criteria and am seeing some strange behaviour.If I join on the table 5 times it runs in a few seconds as soon as I add a 6th join it takes around 2 mins!!It isn't the 6th join that is the problem either as I can remove say the 4th join so it is back down to a total of 5 and it runs in a few seconds!!This all worked fine in SQL 2000 the database is still in 2000 mode anyone got any suggestions?Also if I add a top clause say top 10000 even though there are only 7000 rows returned this makes it much quicker than running it with no top clause!Any ideas?Cheers |
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-07-20 : 18:49:17
|
some statistics problem? did you reindex/update stats after you migrated the database? and also made sure your Db is in 100 compatibility mode.. in other words have you followed all the best practices during/post upgrade? There is a SQL 2008 Upgrade Technical Reference guide if you want to search for it.. which has detailed upgrade process as well as post upgrade best practices..Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2010-07-22 : 04:31:34
|
I did reindex and update stats and usage. The database is still in 80 compatability mode. Is this likely to be the issue? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-22 : 04:41:11
|
Are there any implicit CASTs in the JOIN criteria?JOIN MyTable ON MyDateTimeColumn = MyVarcharDateColumn OR MyIntColumn = MyVarcharColumn I *think* SQL2000 SP4 introduced a "problem" that those are not optimised for Index use by Query Planner.Also look at the Query Plan and see what choices it is making. Maybe you've just hit a bizarre scenario.Any reason you are running in Compatibility=80 rather than native Compatibility=100? |
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2010-07-22 : 04:52:27
|
I just hadn't changed the compatability level. I have done so now and it makes no difference to the performance of my query.There doesn't seem to be any casts that I can see but I will have a closer look.Perhaps my SQL isn't the best.I did not design the database but it has a table of user defined fields with 1219498 rows. To get all the different user defined fields I have to join on it 15 times the table I am joining with has about 7000 rowsso something like thisSELECT ID ,UDF1.Value ,UDF2.Value ,UDF3.Value .... ,UDF14.Value ,UDF15.ValueFROM TableA TBLA LEFT JOIN TableB UDF1 ON TBLA.ID = UDF1.ID AND UDF1.TypeID = 1 LEFT JOIN TableB UDF2 ON TBLA.ID = UDF2.ID AND UDF2.TypeID = 2 LEFT JOIN TableB UDF3 ON TBLA.ID = UDF3.ID AND UDF3.TypeID = 3 .... LEFT JOIN TableB UDF14 ON TBLA.ID = UDF14.ID AND UDF14.TypeID = 14 LEFT JOIN TableB UDF15 ON TBLA.ID = UDF15.ID AND UDF15.TypeID = 15 This is a 3rd party product so I can't change the table design. Is there a better way to query it to get the resultID, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, UDF11, UDF12, UDF13, UDF14, UDF15 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-22 : 07:47:03
|
I don't think that is unreasonable.I'd want an index on TableB (ID, TypeID) but that's about it.other than that need to see what the query plan is doing when you add the extra one that slows it down(and maybe double check that statistics are up to date) |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-22 : 07:50:08
|
Hmmm ... I wonder if it is important to reindex / update statistics when you change Compatibility level? (I don't know the answer to that, but it won't hurt ...) |
|
|
parody
Posting Yak Master
111 Posts |
Posted - 2010-07-22 : 10:08:48
|
We had exactly the same issue afte 2008 upgrade, caused by some obscure code written by analysts. If you look at your wait stats everything gets caught on SOS_SCHEDULER_YIELD. Afetr lots of googling there was apparently an internal db engine issue which was fixed by a cumulative update (and SP2) but that didnt fix it for us. In the end I reviewed all their code and rewrote it mor efficiently or reviewed the indexing. There was also a trace flag to set on startup, cant remember what it was off top of my head but cant find out if you want to try it.what is the structure of table A? I think this can be written more efficiently. |
|
|
parody
Posting Yak Master
111 Posts |
Posted - 2010-07-22 : 10:13:04
|
and example of content... |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-22 : 11:22:59
|
Although you can't change the table design can you add a table of your own?You could "warehouse" the TableA-TableB JOINs so you have a single Warehouse-table with TableA's PK column and 15 "userdefined" columns so you only have to JOIN one table.You'd need something that freshens up your warehouse-table when TableB changes though - so may be OK if freshen-up once-a-day is OK, or that table rarely changes, or you are OK to add a Trigger on TableB (to updates your Warehouse table to match) |
|
|
parody
Posting Yak Master
111 Posts |
Posted - 2010-07-22 : 11:35:46
|
I was think of a PIVOT (or CASE) statement myself, would only require one scan of the table. Thats if I understand the data source properly. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-22 : 11:48:35
|
Yup, I agree that PIVOT (in place of my Warehouse-table) would probably do the job. If performance is of the essence though a static table may be better (but it could be made using PIVOT whenever the underlying data changed) |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-07-22 : 15:09:29
|
yes its recommended to reindex and update stats AFTER you change the compatibility mode.. not exactly sure of the real internal workings but I think index structures/alogrithms may have been improved in the uplevel version ...Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-23 : 02:55:48
|
Thanks Dinakar, makes sense |
|
|
|