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.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|||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|||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.


:D

Why not POST the query...|||Originally posted by Brett Kaiser
:D

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.|||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

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_ord er_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 = ?

Best way to this?

Hi All!
I'm setting up a game that involves pari-mutuel betting.
For example - Total Pool is 1, 213. Take total amount and divide into
bet.
Bet Odds would be:
Calculation
First- 314 4 - 1 (3.86 - 1)
(1213 / 314)
Second - 251 5 - 1 (4.83 - 1)
(1213 / 251)
Third - 414 3 - 1 (2.92 - 1) (1213 / 41
4)
Fourth - 234 5 - 1 (5.18 - 1) (1213 /
234)
Total - 1,213
I thought of setting three tables, one for totals of each place, one table
to hold the grand total, and one table for the results of the odds to go to.
And then at the end of the race, clear out all tables and do it again.
Any thoughts or better suggestions. I'm all ears!
Thanks!what do you mean one table to hold the grand total?
Is the total pool going to change?
If you are going to keep flushing the data then why do you need permanent
tables in the first place. I am sorry I don't know about this game..
Can you give an insight to it?
"Rudy" wrote:

> Hi All!
> I'm setting up a game that involves pari-mutuel betting.
> For example - Total Pool is 1, 213. Take total amount and divide into
> bet.
> Bet Odds would be:
> Calculation
> First- 314 4 - 1 (3.86 - 1)
> (1213 / 314)
> Second - 251 5 - 1 (4.83 - 1)
> (1213 / 251)
> Third - 414 3 - 1 (2.92 - 1) (1213 /
414)
> Fourth - 234 5 - 1 (5.18 - 1) (1213
/
> 234)
> Total - 1,213
> I thought of setting three tables, one for totals of each place, one table
> to hold the grand total, and one table for the results of the odds to go t
o.
> And then at the end of the race, clear out all tables and do it again.
> Any thoughts or better suggestions. I'm all ears!
> Thanks!
>|||I'm a little . Surely you don't need to store the computed data
somewhere.
You have your data (simplified, please don't criticise for lack of primary
keys, etc):
create table bets (position varchar(20), numbets int);
And you have data:
insert into bets values ('First', 314);
insert into bets values ('Second', 251);
insert into bets values ('Third', 414);
insert into bets values ('Fourth', 234);
And then you have views which show the info you want:
select *, (select sum(numbets) from bets) / convert(float,numbets) payout
from bets
But I wouldn't show the "5-1" version, because you might want to use 7-2 if
it's near 3.5 for example. Much easier just to say "Paying $1.52" or whateve
r.
Does this help?
Rob
"Rudy" wrote:

> Hi All!
> I'm setting up a game that involves pari-mutuel betting.
> For example - Total Pool is 1, 213. Take total amount and divide into
> bet.
> Bet Odds would be:
> Calculation
> First- 314 4 - 1 (3.86 - 1)
> (1213 / 314)
> Second - 251 5 - 1 (4.83 - 1)
> (1213 / 251)
> Third - 414 3 - 1 (2.92 - 1) (1213 /
414)
> Fourth - 234 5 - 1 (5.18 - 1) (1213
/
> 234)
> Total - 1,213
> I thought of setting three tables, one for totals of each place, one table
> to hold the grand total, and one table for the results of the odds to go t
o.
> And then at the end of the race, clear out all tables and do it again.
> Any thoughts or better suggestions. I'm all ears!
> Thanks!
>|||Hi Guys!
This helps Rob. I may want to keep some of the records, not sure. My main
objetive here is just to have the calculation done quickly. Omni, is just a
simple game to bet on horses.
Thanks again!!!
Rudy
"Rob Farley" wrote:
> I'm a little . Surely you don't need to store the computed data
> somewhere.
> You have your data (simplified, please don't criticise for lack of primary
> keys, etc):
> create table bets (position varchar(20), numbets int);
> And you have data:
> insert into bets values ('First', 314);
> insert into bets values ('Second', 251);
> insert into bets values ('Third', 414);
> insert into bets values ('Fourth', 234);
> And then you have views which show the info you want:
> select *, (select sum(numbets) from bets) / convert(float,numbets) payout
> from bets
> But I wouldn't show the "5-1" version, because you might want to use 7-2 i
f
> it's near 3.5 for example. Much easier just to say "Paying $1.52" or whate
ver.
> Does this help?
> Rob
>
> "Rudy" wrote:
>|||Well you can always save the contents of the view to an archive table after
betting stops. That's not going to be hard to do. But while the numbers are
changing, just let your view change with it.
"Rudy" wrote:
> Hi Guys!
> This helps Rob. I may want to keep some of the records, not sure. My main
> objetive here is just to have the calculation done quickly. Omni, is just
a
> simple game to bet on horses.
> Thanks again!!!
> Rudy
> "Rob Farley" wrote:
>