Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Unanswered: Big queries return empty result set

    Hello guys,

    MS SQL server 2000 behavies strange with big queries that involves relatively large number of tables:
    If I just enumerate columns I want to receive, the result set is empty. Adding * to the column list without making any change to where clause seems to solve the problem but I guess it's not the best practice.
    The most amazing is that this behavior is not stable, so I suppose there's something to deal with server itself, not the application.
    Has anybody suffered this problem and what solution was adopted?
    Thanks for any information you can provide.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Most of my queries are small (less than 200 columns and 500,000 rows), so maybe I'm not hitting the kind of volume that you are, but I've never heard of that behavior! I also rarely push beyond 30-50 tables in a single query with the exception of one purchased package that sometimes can create monsters.

    Can you give me a rough idea of how big your database is (in gigabytes) and how big your result set is (rows, columns, and megabytes)? I'll try to reproduce the behavior here with a known good server.

    -PatP

  3. #3
    Join Date
    Apr 2004
    Posts
    3
    Definitely my queries never get this volume. I have a join of about 15 tables with 10 columns in each. Empty recordset is an ocasional behavior, i mean today the query may work perfectly, tomorrow absolutely exact query with absolutely exact parameters without any modification made to the database fails.
    I was told that this could be caused by particular, localized version of SQL server that we use (sql server 2000 SP3 spanish, 8.00.760). Could you confirm that?

    Originally posted by Pat Phelan
    Most of my queries are small (less than 200 columns and 500,000 rows), so maybe I'm not hitting the kind of volume that you are, but I've never heard of that behavior! I also rarely push beyond 30-50 tables in a single query with the exception of one purchased package that sometimes can create monsters.

    Can you give me a rough idea of how big your database is (in gigabytes) and how big your result set is (rows, columns, and megabytes)? I'll try to reproduce the behavior here with a known good server.

    -PatP

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Pat Phelan
    Most of my queries are small (less than 200 columns and 500,000 rows), so maybe I'm not hitting the kind of volume that you are, but I've never heard of that behavior! I also rarely push beyond 30-50 tables in a single query with the exception of one purchased package that sometimes can create monsters.


    Why not POST the query....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2004
    Posts
    3
    Originally posted by Brett Kaiser


    Why not POST the query....
    As you wish:
    ---NON-EMPTY RESULTSET---

    select bb.i_object_id as bb_aa, bb.i_object_type_id as bb_ab,
    <skipped about 50 columns>
    bl.i_operation_id as bl_ak, bl.integra_operation_id as bl_al,
    * <----- this allows me receive data
    from i_sysobject bb, part_list_item bc,
    i_relation bd,
    i_sysobject be, part_list bf,
    i_relation bg, i_sysobject bh,
    production_order bi, i_relation bj,
    i_sysobject bk, operation bl
    where bc.i_part_list_item_id = bb.i_object_id and
    bf.state = ? and
    bf.i_part_list_id = be.i_object_id and
    bi.i_production_order_id = bh.i_object_id and
    bl.i_operation_id = bk.i_object_id and
    bc.i_part_list_item_id=bd.i_relation_child_object_ id and
    bd.i_relation_parent_object_id=bf.i_part_list_id and
    bf.i_part_list_id=bg.i_relation_child_object_id and
    bg.i_relation_parent_object_id=bi.i_production_ord er_id and
    bf.i_part_list_id=bj.i_relation_child_object_id and
    bj.i_relation_parent_object_id=bl.i_operation_id
    order by bf.i_part_list_id asc

    ---EMPTY RESULTSET---
    select bb.i_object_id as bb_aa, bb.i_object_type_id as bb_ab,
    <skipped about 50 columns>
    bl.i_operation_id as bl_ak, bl.integra_operation_id as bl_al
    from i_sysobject bb, part_list_item bc,
    i_relation bd,
    i_sysobject be, part_list bf,
    i_relation bg, i_sysobject bh,
    production_order bi, i_relation bj,
    i_sysobject bk, operation bl
    where bc.i_part_list_item_id = bb.i_object_id and
    bf.state = ? and
    bf.i_part_list_id = be.i_object_id and
    bi.i_production_order_id = bh.i_object_id and
    bl.i_operation_id = bk.i_object_id and
    bc.i_part_list_item_id=bd.i_relation_child_object_ id and
    bd.i_relation_parent_object_id=bf.i_part_list_id and
    bf.i_part_list_id=bg.i_relation_child_object_id and
    bg.i_relation_parent_object_id=bi.i_production_ord er_id and
    bf.i_part_list_id=bj.i_relation_child_object_id and
    bj.i_relation_parent_object_id=bl.i_operation_id
    order by bf.i_part_list_id asc


    bf.state = ? receives the SAME parameter in both cases.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Everything is identity, isn't it...

    Is one of these tables a driver? Like what you want to base your result set on?

    Lots of sele referencing...

    Maybe you can use derived tables..

    SELECT * FROM (SELECT * FROM ...join the table that relate) AS A
    LEFT JOIN (SELECT * FROM ..same thing) AS B
    ON A.key = B.Key

    Here's your code cleaned up some..Not sure if it's doing the same thing...

    I think it is...and easier to see what you're trying to do

    Code:
       SELECT *
         FROM i_sysobject 	   bb
    LEFT JOIN part_list_item   bc 	ON bc.i_part_list_item_id = bb.i_object_id  
    LEFT JOIN i_relation 	   bd 	ON bc.i_part_list_item_id=bd.i_relation_child_object_id 
    LEFT JOIN i_sysobject 	   be	ON bf.i_part_list_id = be.i_object_id and 
    LEFT JOIN part_list 	   bf 	ON bd.i_relation_parent_object_id=bf.i_part_list_id
    LEFT JOIN i_relation 	   bg 	ON bf.i_part_list_id=bg.i_relation_child_object_id
    LEFT JOIN i_sysobject 	   bh 	ON bi.i_production_order_id = bh.i_object_id
    LEFT JOIN production_order bi 	ON bg.i_relation_parent_object_id=bi.i_production_order_id
    LEFT JOIN i_relation 	   bj 	ON bf.i_part_list_id=bj.i_relation_child_object_id 
    LEFT JOIN i_sysobject 	   bk 	ON bl.i_operation_id = bk.i_object_id
    LEFT JOIN operation 	   bl	ON bj.i_relation_parent_object_id=bl.i_operation_id 
        WHERE bf.state = ?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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