To Fetch Records from a table quickly which is having 10 Lakhs record

Question

Post reply

To Fetch Records from a table quickly which is having 10 Lakhs record

mkarthi1982

SSC Veteran

Points: 258
More actions
October 26, 2010 at 10:18 pm

#230372

I having a view which returns nearly 80 thousand records in 1 min which is very slower. View has join with two big table which has master table -3 Lakhs records, detail table - 10 Lakhs records. Every table has composite primary key. View needs to be faster any suggestions for me pls?

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

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

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 1

You can create Indexed view - add Index on the view.

As per BOL:

"An indexed view is a view that has been materialized. This means it has been computed and stored. You index a view by creating a unique clustered index on it. Indexed views dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows."

Refer: http://msdn.microsoft.com/en-us/library/ms191432.aspx

Thanks

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 2

You are referring SQL 2005, so

please refer: http://msdn.microsoft.com/en-us/library/cc917715.aspx

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mkarthi1982 SSC Veteran Points: 258 More actions · Answer 4

Thanks for the Replies.

I have speed up the performance using Maintenance Plan with Rebuild and Reorganize.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

mkarthi1982 (10/28/2010)
Thanks for the Replies.
I have speed up the performance using Maintenance Plan with Rebuild and Reorganize.

If you are doing rebuild and reorg on the same tables, PLEASE have a consultant help you out! Hmm, actually have one help you anyway because you shouldn't be using Maintenance Plans for index maintenance!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service