A Design Question using a fictious example

Question

A Design Question using a fictious example

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101097 More actions · Answer 1

You could easily have an order of magnitude more rows in SG table than in T, and if you have a phone app waiting for the response, I would still reduce the I/O for that query as much as possible.

The query might be able to specify a NOT EXISTs rather than a traditional JOIN, but there would still then be several seeks into the SG index rather than a single SEEK and scan on T.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101097 More actions · Answer 2

(page bump needed?)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

eichnerm SSC Veteran Points: 206 More actions · Answer 3

eichnerm

SSC Veteran

Points: 206

November 21, 2024 at 2:31 am

#4486809

I never thought of that.

Thanks

Ross McMicken SSCarpal Tunnel Points: 4436 More actions · Answer 4

For a phone app, all the work should be on the server, with no queries, etc being sent by the phone. Similar to how a good website delivers the page, but all the hard work is done server side.

Or, have a stored proc run the query and the phone just runs the the SP and gets the result set.

eichnerm SSC Veteran Points: 206 More actions · Answer 5

That's a given. I always do as much work as I can on the server. The question is whether to have the stored procedure do a join to obtain all the information that it needs or to put a flag in the main table (in this example) Tests or to obtain the necessary information. In other words, I could put a flag in the Tests table that tells me all the tests have been graded, thus avoiding the join, or do the join on the Grades table so that the query, rather than a flag tells me all the tests have been graded.

In the past I found the flag method to be problematic. I am leaning towards doing what I have always done in the past. That is to do the join and not use the flag.

The only reason that I asked this question is that I was told by a phone app developer, to avoid joins where ever possible. Based on the feedback that I am getting, I believe that he gave me bad advice.

Thanks for the input.