Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2004
    Posts
    38

    Unanswered: Newbie - Is this a job for a sproc?

    I'm optimizing an Access mdb to run in front of a SS 2005 database. My approach is to move as much of the processing as possible out of Access and in to SQL Server.

    I have a report uses an Access query as it's source. One field in that report is generated via a series of 4 or 5 sub-queries that are finally joined in to the report's source query.

    I have enough knowhow to turn each individual Access query into a veiw inside SQL server, but I'm wondering if this wouldn't be better accomplished using a stored procedure?

    Essentially, I'd need the sproc to open up a set of 50-60 records, loop through them until it finds the first record with certain criteria, then return a certain value as it's result. Finally, I need that vallue to be joined to a view that I will point to as the source for my report in Access.

    Is it possible to do this with a sproc? Is this the right way to use a sproc?

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by EndBoards

    Essentially, I'd need the sproc to open up a set of 50-60 records, loop through them until it finds the first record with certain criteria,
    Where is your data?
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Oct 2004
    Posts
    38
    Quote Originally Posted by rudra
    Where is your data?
    Thanks for the quick response..

    Not sure what you're asking.. The set of data to be looped through is in a view. The set of data for the results to be attached to will also be a view.

    Both views are based on tables residing in my database.

    Do you need me to post a sample of my data?

  4. #4
    Join Date
    Oct 2004
    Posts
    38
    here's more detail on my data

    The data to loop through is in a view called ActionItems.

    Relevant fields are:
    project_id, actionitem_id, actionitem_order, status_id, milestone_id

    Projects have about 50-60 action items, each with their unique id. They're in order by actionitem_order. Action items are grouped into milestones - figure 6 items per milestone.

    To get the return value, I want to loop through each action item until the first one with a status_id of 3 is reached. When that one is reached, the loop can be stopped and the prior milestone_id is passed on as the result of the function.

    I want to attach the result of that function to another view joined on Project_id.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Create this as a stored procedure that not only encorporates your views as nested subqueries, but which also find the first one with a value of 3 and returns it as a resultset.
    Basically, if you are looping through records, you are doing something wrong. Its time to join the big boys and learn set-based processing.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Oct 2004
    Posts
    38
    Quote Originally Posted by blindman
    Create this as a stored procedure that not only encorporates your views as nested subqueries, but which also find the first one with a value of 3 and returns it as a resultset.
    Basically, if you are looping through records, you are doing something wrong. Its time to join the big boys and learn set-based processing.
    Ok, cool. I'll dump the "loop-through" concept.. I have it as a series of SELECT queries in Access anyway, so moving that into a sproc is more of a direct migration anyway..

    Ok - you mention nested subqueries.. Do you have an example of a sproc that does this? One approach I've found in trying to figure this out myself is that of using temporary tables after each SELECT statement.. For some reason, this sounds like it would require more overhead than just chaining the SELECT statements together. Problem is, I don't know how to nest the statements together.. I'll do more research, but if you've got an example, I'd appreciate it..

    Thanks!

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    select * from (select * from (select * from SomeTable) Nest2) Nest1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Oct 2004
    Posts
    38
    Quote Originally Posted by blindman
    select * from (select * from (select * from SomeTable) Nest2) Nest1
    That's what I was thinking, but I couldn't find any examples to confirm..

    I'll work with it and post the followup questions that are sure to come..

    Thanks!

  9. #9
    Join Date
    Oct 2004
    Posts
    38
    Ok, really quick on the followup, eh?

    This is probably more of a SQL question, and maybe it should probably be obvious to me, but anyway..

    In Access, my top-level query would look something like...

    SELECT field1, field2, field3 FROM table INNER JOIN table ON query1.field1 = table.field1 GROUP BY query1.field2

    So in the sproc I go:

    SELECT field1, field2, field3 FROM table INNER JOIN table ON (Long SELECT Statement that makes up query1).field1 = table.field1 GROUP BY (Long SELECT Statement that makes up query1).field2

    Obviously the issue is the fact that I have two very long SELECT statements to deal with (on after the JOIN, one after the GROUP BY). Not a big deal with a simple 2 level combination of queries, but my actual progression is 5 levels progression with 7 different queries, some of which have criteria and multiple joins.

    That would make this a pretty large sproc with lots of syntax to verify and (in my case) probably get wrong.

    If that's what it takes, no problem.. I just want to confirm that before I go on..

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No. You are heading down the wrong path here. You join subqueries in your statement as virtual tables with their own aliases. You should not be using them as criteria.
    Plus, I highly doubt that your task requires five levels of subqueries.
    Read the sections on subqueries in Books Online. Then review your task again. Don't try to build the entire sql statement at once, but instead write code that performs the lowest level subqueries efficiently and then build up from there.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •