SQL Server Performance

"FOREACH" loop over a "list" -- how to implement in T-SQL?

Discussion in 'SQL Server 2005 General Developer Questions' started by aeblank, Nov 28, 2008.

  1. aeblank New Member

    Hi,
    THE PLAIN VANILLA PROBLEM
    I want to know the absolute fastest way to implement the following:
    1) SELECT some_data INTO my_temporary_list
    2) FOREACH item in my_temporary_list, DO SOME STUFF WITH item

    That's all I need to know how to do. I know that I can implement this in the CLR, but I heard that the CLR is slower when working with enormous datasets, (I'm looking at returning millions of rows of data with this command when it's in production, it needs to be fast!). I'll probably implement it in both CLR and in T-SQL then benchmark them, but I don't know how to implement this in T-SQL.
    Any suggestions or solutions would be much appreciated. Thanks!
    MORE BACKGROUND
    I'm coding a breadth-first tree generation algorithm. A few things: 1) itcan't be a recursive algorithm 2) the data in the database is NOTstored as a tree, but as a graph--I'm generating a tree from a graph, and returning the results breadth first.
    I have an algorithm that shoulddo just fine, but I don't know how to implement a FOREACH loop over atemporary LIST of results in T-SQL. Can someone let me know how I canarbitrarily add data to a list from a SQL SELECT command, and theniterate through those results and perform more operations?
    If you want more detail, here is the pseudo-code for my algorithm:
    INPUT start node
    OUTPUT result_set

    WHILE not enough results
    BEGIN
    /* fill current_depth_nodes_list */
    BEGIN
    IF current_depth_nodes_list is empty
    THEN add start_node to current_depth_nodes_list
    ELSE
    /* iterate through current_depth_nodes_list and fill it with the next depth
    of nodes. Do this by adding each nodes' adjacents to to a new list,
    then save that list back as current_depth_nodes
    */
    BEGIN
    DECLARE new_list
    FOREACH node IN current_depth_nodes
    BEGIN
    INSERT get_adjacent_nodes(node) INTO new_list
    END
    CLEAR current_depth_nodes_list
    current_depth_nodes_list = new_list
    END
    END

    /* iterate through current_depth_nodes and add adjacents of each node to result_set
    while marking the boundaries between depths and nodes.
    */
    add depth_start_marker to result_set
    BEGIN
    add node_start_marker to result_set

    FOREACH node IN current_depth_nodes_list
    BEGIN
    INSERT get_adjacent_nodes(node) INTO result_set
    END
    add node_end_marker to result_set
    END
    add depth_end_marker to result_set
    END

    RETURN result_set
  2. Adriaan New Member

    Syntax you could use would be the ye olde CURSOR, which is perfect for iterations.
    But depending on what you need to "do for each line", you may be surprised what an in-line SELECT statement can do with much better response times.
    Now, since you appear to be using SQL 2005, the CTE (common table expression) might come in handy.
    So plenty of syntax to discover and learn ...
  3. aeblank New Member

    Hi Adriaan,
    Thanks for the suggestion. I actually worked on this a bit more, and came up with a two-column output that conveys the same information. It's a recursive algorithm that uses a self-join. Unfortunately, it's a bit slow.
    I'll keep toying with that unless I come up with something better.
    -A

Share This Page