Friday, July 31, 2009

SQL: Temp Tables vs View


I have created a DTSX package for a requirement which fetched data from MS access and store it in SQL Server.For business calculations there we some Views required. The final stored procedure will insert data into a table which will be utilized by a ERP system. The insert query was a bit complex like it called fields in a View which was created from a view (can call it a nested view?) & so on... The execution time was more than 30 mins :( So we need to optimize things for better performance....

now my next attempt will be to try it out with temp tables. [drafting post...]

Yes... In place of the views I used two temporary tables and one view i kept it as it is because, It was only displaying distinct values from a table.

This time to complete execution the Stored procedure took only 34.031 seconds...

Wow... that's a huge difference.... I believe using Temp tables optimized the stored procedure than views. The point to be noted here is that the decision to go for View & Temporary tables is based on the scenario... In my case Temp tables did the trick :)

Best Regards

No comments: