0

I have a database table with almost 15000 rows of data, now I want to display questions randomly. User will enter number of question he want from each chapter, and according to that question should display. I am stuck about performance and way of doing this. If question is locked it should not display. here is my table structure. I am using .net core and sql server with EF Core.

QuestionId SubjectId ChapterId Question OptionA OptionB OptionC OptionD HasLocked(boolen)

Edit:

select * from table_name where HasLocked = false and ChapterId = '1' and ChapterId = '2';

Note: ChapterId will be dynamic.

cooldev
  • 47
  • 7
  • 1
    Are you stuck on *how* to do this, or on the *performance* of your code? Because, if you already have code, please add it to the question. If you do not already have code, then this question is most probably a duplicate of [this](https://stackoverflow.com/q/7781893/9363973) – MindSwipe Oct 05 '20 at 06:11
  • @MindSwipe I am stuck on how to do, because I have couple of conditions based on that I want data, not just randomly. – cooldev Oct 05 '20 at 06:13
  • 1
    Can you add your current SQL Query request. – IndieGameDev Oct 05 '20 at 06:14
  • 1
    You can easily chain the LINQ in my linked question to do this by prepending your conditions (with `Where`) and then at the end use your preferred way of randomly selecting – MindSwipe Oct 05 '20 at 06:15
  • @MindSwipe it will give good performance? or keep fetching data for minutes? My database is on shared hosting. – cooldev Oct 05 '20 at 06:20
  • @MathewHD Added – cooldev Oct 05 '20 at 06:21
  • 1
    You could add `ORDER BY RAND() LIMIT 1` – IndieGameDev Oct 05 '20 at 06:22
  • To create a random order and take the first item. – IndieGameDev Oct 05 '20 at 06:22
  • Fundamentally: Do you want a LINQ answer, or do you want a SQL answer? – MindSwipe Oct 05 '20 at 06:24
  • @MindSwipe Anything will fine! :p – cooldev Oct 05 '20 at 06:24
  • With LINQ your query would look similar to this: `table_name.Where(x => !x.HasLocked && ...).OrderBy(x => Guid.NewGuid()).Take(amount)`. This is quite performant as your SQL server only has to generate as many GUIDs as items your are taking – MindSwipe Oct 05 '20 at 06:30
  • Have you read https://dba.stackexchange.com/questions/955/what-is-the-best-way-to-get-a-random-ordering ? – Caius Jard Oct 05 '20 at 06:36
  • And @mindswipe please explain how ordering a 10 million record table by guid and then taking 100 records will be performant? You can't take 100 records and sort them, you have to sort 10 million records then take the first 100. Exaggerated numbers used to make the point – Caius Jard Oct 05 '20 at 06:37
  • @CaiusJard we don't need to order 10 millions records, we need to order 15'000. And what I said about performance isn't true (I shouldn't try and post comments on SO before my first coffee). But IMO for 15'000 records it should be OK – MindSwipe Oct 05 '20 at 07:12
  • Thing is it's not just 15000, but it's still 15k, on a low powered db on shared hosting. Your way has to run the query for each chapter, so it's no longer 15k, but say you want 3 questions from each of 10 chapters it's 150k, then if there are ten concurrent users it's 1.5 million.. but my initial comment was to speak out against your assertion that sqlserver only has to generate N guide in order to get N random rows from a result set of size X: it's not true. It has to generate and sort X guds, and generating them is more trivial than sorting them – Caius Jard Oct 05 '20 at 07:51
  • @cooldev the problem with this question is you're asking is to design your program for you, and we aren't here to do that (it's off topic for SO) because it's opinionated. You have to do the design. If I were you I would look at having a carefully modelled dataset where each question within a chapter has a contiguous number, you know the max number in each chapter, you get together a list of all the chapters and in the client side randomly generate N numbers from each chapter and then ask the db for them. Index the chapterid/questionnumber pair (but that's my opinion hence not an answer) – Caius Jard Oct 05 '20 at 07:55
  • @CaiusJard Not at all, I have design & developed already. I am just asking the best or suitable way to randomly fetch question depends on input by user. Let say I have 100 question in chapter 1, and user only want 4 questions from that; so now I am looking for suitable way to get only those 4 questions nothing more. Same goes for other chapters. thank yoou – cooldev Oct 05 '20 at 10:48

1 Answers1

0

You can use a random number generator. You can check this link for more information: https://www.c-sharpcorner.com/article/generating-random-number-and-string-in-C-Sharp/

Some code to generate a single random integer less than 15000 would look like this: int num = random.Next(15000); Since you want a number of questions from each chapter you have to get the input form the user first as to how many questions from each chapter and then put them in an array of integer. for example chapter 1 - 10 questions, chapter 2 - 5 questions and so on. For example: int [10] FromEachChapter; // If there are 10 chapters Then fill each array element with the user's desired number of questions. Then you will need a for loop to generate x amount of question numbers for each chapter. You can use the Rand class to generate those question numbers. Instead of a one dimensional array Use a 2D double integer array where the first column shows the question numbers required from the chapter and every column after that stores the randomly generated question numbers so instead of int [10] FromEachChapter //for 10 chapters use: int [10] [] FromEachChapter; // In the decleration as you don't yet know how many questions they want to choose from each chapter the second one is left blank. Lets say the user chooses 5 questions for chapter 1 which is chapter [0] as it is zero start you can say FromEachChapter[0] = new int [5]; // So that the first row will have 5 columns of integer each column holding the random number generated by the random number generator. Then you need to run your SQL queries not a single query one query for each chapter. eg SELECT * FROM QUESTIONS WHERE (CHAPTERID = 1 AND SUBJECTID=Y AND HASLOCKED=FALSE AND QUESTIONNUMBER = (The Question number that was randomly generated) OR (The Other question number that was generated etc));

So this program is more complicated than you think. Try breaking up the problem and working little by little if you want to become a good programmer. Hopefully that helps.

Soliman Soliman
  • 159
  • 1
  • 4
  • 17