Advise on this query

• deastr

  SSC Enthusiast

  Points: 111

  More actions

  June 24, 2008 at 6:07 am

  #208742

  I'm kind of in doubt about a query I'm thinking and I'd like to ask for your opinion. I'd appreciate any advice.

  There are basically three tables I'm using. Tests, Questions and TestQuestions. Here's the simplified design:

  [Questions]

  ID

  Data

  [Tests]

  ID

  CompanyID

  [TestQuestions]

  TestID

  QuestionID

  There're companies and there're teachers in each company creating tests with selecting questions. The query I'm designing is in question list page. There're two things needed to be considered:

  1) The questions which are used before in a test by other teachers of the same company need to be listed at the end of the questions list

  2) It needs to be paged

  I thought of this:

  [font="Courier New"]with QuestionIdsUsedBefore as

  (

  select IDs of the questions from TestQuestions using CompanyID

  )

  select ID, '0' [UsedBefore] from Questions whose IDs are not in QuestionIdsUsedBefore

  union

  select ID, '1' [UsedBefore] from Questions whose IDs are in QuestionIdsUsedBefore

  order by UsedBefore[/font]

  I have two concern with this one. First one is about performance. The CTE could have tens of thousands of IDs which makes me wonder if I'm doing a right thing. The tables have proper indexes but I still don't know, I never worked with a db of this size before.

  Second one is implementing paging. I'm thinking of using select row_number from (this whole query) but haven't fully thought about it yet.

  What do you think? Is this a good query? I'm willing to do any changes even to do tables if you have any better ideas. I appreciate any advice..

• Jeff Moden

  SSC Guru

  Points: 1004639

  More actions

  June 24, 2008 at 9:31 pm

  #833687

  Try it... if it's too slow, tweak it. If you run out of tweaks, try a differect tactic.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)