Rotational Query?

We're facing a large dilemma. We've rebuilt our entire database in a much
more "normalized" fashion than we had previously (the design was
outrageously bad before). Ultimately, we have the following scenario and
we're getting a huge timeout problem:
Tables:
TblOrder (large, central table, contains all "files", approximately
130,000 records)
OrderFees (joined to TblOrder by FileNumber field, contains FeeType and
Fee)
DateTypes (joined to AppraisalDates by DateType field, contains the
"name" of the Date)
AppraisalDates (joined to TblOrder by FileNumber field, contains
DateType Field and Date)
Right now we're doing a query that returns approximately 5000 records from
TblOrder, then it queries AppraisalDates to get the relevant dates, then it
queries OrderFees, get the relevant fees and then it cycles to the next
record in TblOrder. Big timeout problems.
The only solution I could come up with for this is to create a view that
somehow rotates the relevant records from OrderFees and AppraisalDates and
merges them with TblOrder so that we only have to do one query instead of 3
separate queries. Is this an appropriate solution? I don't know that this
will buy us any "time".
Any suggestions would be appreciated.
Thanks!
JamesPlease post DDL, sample INSERTs and required results so that we don't have
to guess.
http://www.aspfaq.com/etiquette.asp?id=5006
I don't understand why you would "query TblOrder... then query
AppraisalDates...", etc. Don't you just join these tables? You seem to be
describing a cursor.
--
David Portas
SQL Server MVP
--|||Is there a reason why you can't pull the records from all 4 tables in a
single query with joins?
From your description it looks like:
SELECT columns FROM
tblOrder JOIN OrderFees ON FileNumber...
tblOrder JOIN AppraisalDates ON FileNumber..
AppraisalDates JOIN DateTypes on DateType
The DDL and some INSERT commands for the various tables as well as the
output you would like to see would be helpful. Someone here could then
craft a query for you.
Rick Sawtell
MCT, MCSD, MCDBA
"James" <cppjames@.aol.com> wrote in message
news:%23Srk%23butEHA.3448@.TK2MSFTNGP09.phx.gbl...
> We're facing a large dilemma. We've rebuilt our entire database in a much
> more "normalized" fashion than we had previously (the design was
> outrageously bad before). Ultimately, we have the following scenario and
> we're getting a huge timeout problem:
> Tables:
> TblOrder (large, central table, contains all "files", approximately
> 130,000 records)
> OrderFees (joined to TblOrder by FileNumber field, contains FeeType
and
> Fee)
> DateTypes (joined to AppraisalDates by DateType field, contains the
> "name" of the Date)
> AppraisalDates (joined to TblOrder by FileNumber field, contains
> DateType Field and Date)
> Right now we're doing a query that returns approximately 5000 records from
> TblOrder, then it queries AppraisalDates to get the relevant dates, then
it
> queries OrderFees, get the relevant fees and then it cycles to the next
> record in TblOrder. Big timeout problems.
> The only solution I could come up with for this is to create a view that
> somehow rotates the relevant records from OrderFees and AppraisalDates and
> merges them with TblOrder so that we only have to do one query instead of
3
> separate queries. Is this an appropriate solution? I don't know that
this
> will buy us any "time".
> Any suggestions would be appreciated.
> Thanks!
> James
>|||Did you find an answer for this one? If not, post your queries and DDL and
we can help out.
Thanks,
Michael C., MCDBA
"James" <cppjames@.aol.com> wrote in message
news:%23Srk%23butEHA.3448@.TK2MSFTNGP09.phx.gbl...
> We're facing a large dilemma. We've rebuilt our entire database in a much
> more "normalized" fashion than we had previously (the design was
> outrageously bad before). Ultimately, we have the following scenario and
> we're getting a huge timeout problem:
> Tables:
> TblOrder (large, central table, contains all "files", approximately
> 130,000 records)
> OrderFees (joined to TblOrder by FileNumber field, contains FeeType
and
> Fee)
> DateTypes (joined to AppraisalDates by DateType field, contains the
> "name" of the Date)
> AppraisalDates (joined to TblOrder by FileNumber field, contains
> DateType Field and Date)
> Right now we're doing a query that returns approximately 5000 records from
> TblOrder, then it queries AppraisalDates to get the relevant dates, then
it
> queries OrderFees, get the relevant fees and then it cycles to the next
> record in TblOrder. Big timeout problems.
> The only solution I could come up with for this is to create a view that
> somehow rotates the relevant records from OrderFees and AppraisalDates and
> merges them with TblOrder so that we only have to do one query instead of
3
> separate queries. Is this an appropriate solution? I don't know that
this
> will buy us any "time".
> Any suggestions would be appreciated.
> Thanks!
> James
>