Performance Problem

Question

Performance Problem

Tara-1044200

SSCoach

Points: 15951
More actions
April 12, 2010 at 9:38 pm

#213786

PLease check the attached sql plans where i pulled from the 1 query exectign against 2 different databases of same structure but the slow qry is taking 2 hrs and fast qry is taking 1min. Can some one let me know what to imrpve in the sloq query ?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"]Initially add Index to the existing table and try to execute the SLOW query and if still that query is taking much time then post query with table structure.

--Mahesh[/font]

MH-09-AM-8694

Tara-1044200 SSCoach Points: 15951 More actions · Answer 2

I think you can check the query from the query plan. But the thing is why is it running fast on larger database when compared to slow on smaller database. what might be he reasons.

Stefan_G SSCertifiable Points: 6609 More actions · Answer 3

You are reading 32 million rows from HomeLook_USA with a remote query. The optimizer estimates this remote query to return only 570 rows.

This extreme difference in estimated rows and actual rows is the root of the problem. In the slow query the optimizer has selected a LOOP JOIN while in the fast query the optimizer has selected a MERGE JOIN.

You might be able to solve the problem by using a JOIN hint to tell the system to use a MERGE JOIN for this big table.

If this does not work you can first load all the 32 million rows into a local temporary table and then join against that temporary table. This will give the server much better statistics which will enable it to select a better execution plan.

The problem might also be that you are missing an index on HomeLook_USA in the State_FL database.

By the way, the fast_query plan you posted is corrupt. It looks like someone has replaced "ca" with "USA" directly in the XML file. I had to replace "USA" with "ca" to be able to view the plan.

/SG

Tara-1044200 SSCoach Points: 15951 More actions · Answer 4

Thanks stefan that gives more information for me. But i was wondering why would execution plan chose different route for the same query, LOOP join in the slower one and MERGE join in the faster one. on what basis the query will chose the execution plan.

how would i make the slow query use MERGE join like fast query. the thing is i have provided some of my developers to use this query for all of their similat jobs, so this looks like the query has to be changed on fly when ever needed for better performance but my developers are not good at sql and so i provide them queries to use them for similar kind of criteria.

Stefan_G SSCertifiable Points: 6609 More actions · Answer 5

Tara-1044200 (4/13/2010)
Thanks stefan that gives more information for me. But i was wondering why would execution plan chose different route for the same query, LOOP join in the slower one and MERGE join in the faster one. on what basis the query will chose the execution plan.

As I said, the reason is that there are no statistics available for the remote query. The server thinks the remote query will only return 500 rows so the execution plan is optimized for other things such as the size of the other involved tables.

how would i make the slow query use MERGE join like fast query. the thing is i have provided some of my developers to use this query for all of their similat jobs, so this looks like the query has to be changed on fly when ever needed for better performance but my developers are not good at sql and so i provide them queries to use them for similar kind of criteria.

As I said, try using a JOIN HINT - if that does not work download the remote data into a temporary table before joining.

What kind of server is the remote server ? SQL Server? DB/2 ? Oracle?

/SG

Tara-1044200 SSCoach Points: 15951 More actions · Answer 6

remote server is a also sql 2005 but why is the other fast query which also has remote query is doing faster with 100 times bigger data ? If you comapre both queries has remote query with same number of rows (32million)

Stefan_G SSCertifiable Points: 6609 More actions · Answer 7

Tara-1044200 (4/13/2010)
remote server is a also sql 2005 but why is the other fast query which also has remote query is doing faster with 100 times bigger data ? If you comapre both queries has remote query with same number of rows (32million)

The local server does not know that there are 32 million rows of remote data. It thinks there are only 500 rows.

The local server thinks the two execution plans have about the same cost. (Estimated subtree cost=4.8)

The reason is it selects different plans is probably that the size of the #ca and #fl tables are very different. One is 47 rows while the other is 1300 rows.

It is just a coincidence that the plan selected for the #ca table turns out to be fast while the plan selected for the #fl table turns out to be very slow.

I think you should stop worrying about exactly why the plans are different and focus on getting a more efficient plan for the slow query. (for example using a JOIN hint)

/SG

Stefan_G SSCertifiable Points: 6609 More actions · Answer 8

What I mean is try rewriting the from clause of the query like this:

from #FL as a inner HASH join Obbeaver.State_FL.dbo.HomeLook_USA as b on a.Clmn10 = b.Clmn10

left join Obbeaver.State_FL.dbo.VwOffLook_USA as c on b.StateeRevCode = c.SCode

and a.EmpID = c.EmpID

where EmpType IN ('P','G') and a.Clmn10 > '000000006' and a.EmpID = 'FL'

The only difference from your original query is the word HASH.

I am fairly sure this will fix the problem.