subquery vs declare @mytable table

Question

subquery vs declare @mytable table

Sarsoura

Ten Centuries

Points: 1220
More actions
February 5, 2012 at 8:24 am

#256189

Hi All,
I would like to do the following using sub queries
I want to merge studentid's of all students enrolled either in parent or child section and all assessment id's for all assessments of the primary section and scores to the assessmentpoint table.
If the student already has a score do not touch it if not add a row with null value.
here are my tables
section (sectionid, sectionname, parentsection)
enrollment (studentid,sectionid,enrollmentstatus)
assessment (sectionid,assessmentid,assessmentname,maxpoint)
assessmentpoint(assessmentid,studentid,score)
Note parentsection field is zero if the section is the parent
other wise it will be the sectionid of the parent section
I used before @declare @temp tables but this was taking memory
Is subqueries better and how to do that using a subquery.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sarsoura Ten Centuries Points: 1220 More actions · Answer 2

I am using declare @tempmy table to perform the following scanerio. I want to know if cte is better to do that because my sql server has high memory usage and if so how to do that using cte. Note my tables are huge

Thanks in advance

First part of the question

section

(sectionid,sectionname,parentsection)

example

(1,first,0)

(2,second,1)

(3,third,0)

this means first has a child second

and third has no children

enrollment

(sectionid,studentid,enrollmentstatus)

(1,1,1)

(1,2,1)

(2,3,1)

(2,4,1)

(3,5,1)

student

(studentid,firstname,lastname)

when i am in section=first

I want to see students enrolled in section=first and its child=second

I want to see the following students

1,2,3,4

second portion

only parent sections have assessments

assessment table (assessmentid,sectionid,maxpoints)

(1,1,10)

(2,2,10)

assessmentpoint table (will have assessmentid,studentid, score)

this will include assessments of the parent and students of both the parent and the children

each student enrolled in either parent or child should have a record in the assessmentpoint table for all assessments of the parent

if a new student is enrolled to either parent or child I need to add a record for him for each assessment of the parent

using merge

Thank you

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You didn't read the article. Please do so. The easier you can make it for someone to help you, the more likely it is that someone will.

CTE or subquery means that the work is done as one query. A table variable or temp table means that its split up, interim results stored in a table and then used later. Which is better depends on the specific scenario. For something simple, a subquery or CTE is likely to be better, but it's not really about memory usage, temp tables can and do spill to disk

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398730 More actions · Answer 4

No real way to give you solid advice based on generalities.

But, in general, test your queries. Concentrate on set-based solutions. Avoid row-by-row processing. Make sure the queries are avoiding all the common issues of sargeability of operations, correct data types, etc., etc., Other than that, I'm not sure what to say.

Table variables don't have statistics. Depending on what you're doing within the queries that might be an issue.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning