SQL Server Performance Forum – Threads Archive
Large number of rowsWe have complex views which will scan morethan one million rows in tables, and retrun matching rows. Currently it takes more than half and hour to process it. Pls give me your ideas, on what conditions, I can get the results in 4 seconds. Is it possible at all to get it in 4 seconds?<br />Thanks for your helps.<br />[<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />]
It all depends on select in your view…
Are you using single table or multiple table? and the table(s) involved in view has proper indexes.. and also depends how many rows it is returning…
Check the query execution plan of the view… Mohammed U.
The view has joins to about 7 tables, and some of the tables have more than one million rows. First of all I want to know, Is it possible to get a restult set in less than 5 seconds? If yes. then under what conditions i can get it?. Is it possible to get a restult set in less than 5 seconds? thanks for your reply Mohamed.
It is possible… but it depends on indexes, memory and disk… If necessary you may need to add extra indexes. Mohammed U.
Don’t forget, when requesting data from a client app running remotely, the data has to travel along the network. When your query is returning one million rows, then this takes time. Not sure that it would even finish in under 5 seconds if the client app is running locally on the same machine as SQL Server – in fact, it becomes less likely since the client app will need to claim loads of resources to process the data by itself, which might cut into SQL Server’s share of resources.
Thanks mohammed and adriaan.
From your replies, I get a lot of confindence to go further and take up the job. This is my interview question, and I did see the views and SQL stmts. They are too complex, and pls help if I have questions on my way. Thanks