0

i have 1 million row data in my sql how do i QUERY effeciently and fastly TO TAKE N ELEMENTS FROM SQL SERVER here is my code

  select  * from Persons where Id in 
    (select top 10 ID from Persons order by newid())

is it good? or you another option?

sticky bit
  • 36,626
  • 12
  • 31
  • 42
umar alfath
  • 13
  • 5
  • i don't know what newid() is, but 1 million rows is nothing. depends on the db engine though, but as long as you have an index on ID and whatever newid() touches, such a query should run in milliseconds. the golden rule is quite simple: all columns that are touched in the ``where`` clause shall be part of an index - in the best case even part of the _same_ index. If you can reduce the columns needed to select (``*`` in general is not a good idea) you can even put the select-columns in the index to the db engine can retrieve your result without touching the table at all. lightning fast. – Grisgram Mar 08 '20 at 05:43
  • It would help what you actually trying to achieve. Also the code you have seem to be more complicated than what is described in the question - some clarification why you chose that could be nice too. Please [edit] the question with those details. – Alexei Levenkov Mar 08 '20 at 05:46
  • Are you trying to take 10 random records from the `Persons` table? – Tim Biegeleisen Mar 08 '20 at 05:54
  • The only way that query runs as fast as it does is because of modern hardware. The query above requires a full table scan. Yes, sql server on a fair machine can scan. However, you are might want to make use of the TOP command or use a partion function to rank order the results. The way you have it, every record has to be serialized by the newid function and then only the top 10 returned. – Ross Bush Mar 08 '20 at 05:57

0 Answers0