Join two views cause slow query.

Question

Join two views cause slow query.

pphimchaidee

SSC Eights!

Points: 859
More actions
August 30, 2011 at 10:31 pm

#247355

I have two views joining on accountID.
--------------------------
Main query of the report
--------------------------
select a.*, b.xx
from view_a as a
join view b as b
on a.accountid = b.accountid
When querying each view individually, it works fine. View_a takes about a minute to get the result and view_b takes about 1.5-2 minutes.
But when two views join together, the query takes so long time about 30-40 minutes.
I don't really have no idea why it is so slow when joining views together. If anyone knows how to make it quicker, please let me know.
Thank you very much.

Viewing 6 posts - 1 through 5 (of 5 total)

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

R.P.Rozema SSChampion Points: 12317 More actions · Answer 1

Without the internals of those views, their underlying tables and some test data we can not do much for you. Please provide some more details and people here will be happy to help.

edit: I've seen "wonders" done to the performance of queries after posting a query plan. Posting one for your query will help people here help you too.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Posting the actual execution plan would be extremely helpful in this case. As has been said, if you can get us more info - more people can help you faster and better.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

pphimchaidee SSC Eights! Points: 859 More actions · Answer 3

I'm new to SQL server. I have no idea how to paste the whole execution plan to this discussion board as both the views are huge sql scripts. Any recommendation, pls let me know.

Thank you.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Read the first article in my sig. Gail walks you through how to post that info.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

pphimchaidee SSC Eights! Points: 859 More actions · Answer 5

I could find the work around by creating temp tables of two views and then join temp tables together.

The performance improved 100%. I'm not quite understand the reason behind that but it works anyway. 🙂