SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Slow joins in 2008 that were ok in 2000
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cornall
Posting Yak Master

148 Posts

Posted - 07/20/2010 :  09:19:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 07/20/2010 :  18:49:17  Show Profile  Visit dinakar's Homepage  Reply with Quote
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 - 07/22/2010 :  04:31:34  Show Profile  Reply with Quote
I did reindex and update stats and usage. The database is still in 80 compatability mode. Is this likely to be the issue?

Edited by - cornall on 07/22/2010 04:31:44
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/22/2010 :  04:41:11  Show Profile  Reply with Quote
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 - 07/22/2010 :  04:52:27  Show Profile  Reply with Quote
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


Edited by - cornall on 07/22/2010 05:13:37
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/22/2010 :  07:47:03  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 07/22/2010 :  07:50:08  Show Profile  Reply with Quote
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 - 07/22/2010 :  10:08:48  Show Profile  Reply with Quote
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 - 07/22/2010 :  10:13:04  Show Profile  Reply with Quote
and example of content...
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/22/2010 :  11:22:59  Show Profile  Reply with Quote
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 - 07/22/2010 :  11:35:46  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 07/22/2010 :  11:48:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 07/22/2010 :  15:09:29  Show Profile  Visit dinakar's Homepage  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 07/23/2010 :  02:55:48  Show Profile  Reply with Quote
Thanks Dinakar, makes sense
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000