2

I attended an interview last week, and learned that their database is terabytes in size.

I was told that that the performance of query must be less than 3 seconds.

How can I achieve this level of performance for a database of this size using Microsoft SQL Server and ASP.NET? is it possible?

Tim M.
  • 53,671
  • 14
  • 120
  • 163
Thomas Koh
  • 213
  • 5
  • 17
  • Your question is too broad. It depends on the server and the way the data is structured and how far it is normalized. You could have asked them this question; this is what interviews are for. You could have started a discussion with them and asked intelligent, curious questions. – CodingYoshi Jun 10 '18 at 15:28
  • 1
    I would expect sub-second response time with appropriate indexes and a reasonable number of rows are returned. But if you are scanning billions of rows, 3 seconds will be difficult to achieve. Much depends on the particulars of the query too. I suspect the interview question was open-ended without a single right or wrong answer. – Dan Guzman Jun 10 '18 at 15:29

1 Answers1

1

The trick is "hidden" on the Database index.

enter image description here

If you understand how this works, you can make correct queries, build correct databases and archive the maximum possible speed.

How does database indexing work?
https://www.essentialsql.com/what-is-a-database-index/

Aristos
  • 66,005
  • 16
  • 114
  • 150
  • Actually i have experience in oracle database but not microsoft sql server database.From my understanding using normal indexes with a well optimized query with a terabytes size database,it is also very difficult to achieve this result unless using advanced techniques like compression,partitioning,bitmap indexes in oracle.My understanding of microsoft sql server is limited,so the question i am asking is are there any advanced techniques that can be used in this platform to achieve this performance rather than the general improving of sql queries using explain plain and normal indexes. – Thomas Koh Jun 12 '18 at 01:25
  • @ThomasKoh Actually never a good database is terabytes in size, unless you save inside images, or lot of texts from anything, or a lot of temporary staff, or flat tables. Therefore, if we actually save texts even then you can index that text and make quick queries. The SQL Server is not limited, the last version of is also saves the table and the index direct on memory for even faster results. The queries extreme fast, I have archive results than is less than 50ms to replay a full page (not only make queries) – Aristos Jun 12 '18 at 06:18
  • @ThomasKoh To optimize all that, asp.net and queries, is a multi-knowledge process, that can include, cache, good indexing, data on memory, avoid many opens on database, and small many thinks that have to do with internet as well. From MS SQL part, the sql server make a very good work inside and replay lighting fast. – Aristos Jun 12 '18 at 06:18