Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Slow joins in 2008 that were ok in 2000

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/
Go to Top of Page

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?
Go to Top of Page

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?
Go to Top of Page

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 rows

so

something like this


SELECT ID
,UDF1.Value
,UDF2.Value
,UDF3.Value
....
,UDF14.Value
,UDF15.Value
FROM 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 result

ID, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, UDF11, UDF12, UDF13, UDF14, UDF15

Go to Top of Page

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)
Go to Top of Page

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 ...)
Go to Top of Page

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.

Go to Top of Page

parody
Posting Yak Master

111 Posts

Posted - 2010-07-22 : 10:13:04
and example of content...
Go to Top of Page

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)
Go to Top of Page

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.
Go to Top of Page

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)
Go to Top of Page

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/
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-07-23 : 02:55:48
Thanks Dinakar, makes sense
Go to Top of Page
   

- Advertisement -