SQL Server Performance

Large number of rows

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by cj2610, Nov 24, 2006.

  1. cj2610 New Member

    We 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=':(' />]
  2. MohammedU New Member

    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.
  3. cj2610 New Member

    Mohamed.
    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.
  4. MohammedU New Member

    It is possible... but it depends on indexes, memory and disk...

    If necessary you may need to add extra indexes.



    Mohammed U.
  5. Adriaan New Member

    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.
  6. cj2610 New Member

    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

Share This Page