SQL Server Fun - 2

Question

Hi Lets have some another SQL query fun. This only for learning purpose anyone can reply with their inputs. Here we go.. I was working on my project and one of the requirement was to show product wise report. The product master is from an external SQL DB. So the process i followed. 1. Creating DBLink and fetching the records from master SQL DB through View . 2. Joining this View with my tables and showing the report. 3. Its takes around 8 - 10 secs to show the output for just 500 rows. So my question is what happening here why its takes this much time. SELECT * FROM mytable SELECT * FROM view Which will run faster from above sql statement. How will i optimize my query. Note: I already done this now the query run much faster but needs some inputs from all you people. :)

Suthish Nair · Answer

Instead of creating a view make Triggers on Insert/Update of the master table and create another temp table. Use this temp table.

Amit Choudhary · Answer

Suthish.. can you share how can we convert a view to a table.. m just curious..

Suthish Nair · Answer

Yes Joining a View and fetching records takes long time. But if we convert that View to Table then its works so fast.

Amit Choudhary · Answer

Hi Suthish I'm not doing much DB work but still i like to participate... If you are using views then it should reduce the time and if you want to create a view and it should be reasonable like.. if there is join takes place very frequently then you can create a view for it.. and in this case your View will run faster then your Join. SELECT * FROM mytable SELECT * FROM view In this case i think the first one will run faster. If we talk about the query optimization then you can evaluate your query by analyzing the data you are filtering joining with. The change in order of statements of filter and join will also increase the performance. :))

Suthish Nair · Answer

Nobody come on guys... If you dont like the post say it like that we can start the disucssion :)