Can this be done using TSQL ?!

Hello
im writing an inventory application for a customer that needs to calculate
item cost by Moving Average method which requires calculating the cost after
each operation, i have a good experience with TSQL but so far i failed to
write the statement that can do this WITHOUT writing cursors
im trying to avoid calculating cost after each transaction to the inventory
. and by writing a stored procedure , get a list showing transactions and
item avg in a period
here is a description of moving avg method , also available here for those
who cant read html
http://www.fms.indiana.edu/auxiliary/inventory.asp
Moving Average--Perpetual
Continuous or moving average assigns a unit value to cost of goods available
for sale. In this scenario, the average cost determines cost of goods sold
at the time of each sale. This method requires a calculation of average unit
cost after each purchase as illustrated below.
# of Units Cost per Unit Total Cost Moving Avg. Cost
Beginning inventory, 7/1 200
$5,000
$25.00
Purchase, 8/10 100
$26.00
2,600
Inv. Balance 300
7,600
25.33
Sale, 9/15 (100)
25.33
(2,533)
Inv. Balance 200
5,067
Purchase, 12/7 600
27.00
16,200
Inv. Balance 800
21,267
26.58
Sale, 12/18 (300)
26.58
(7,975)
Inv. Balance 500
13,292
Sale, 2/22 (250)
26.58
(6,645
Inv. Balance 250
6,647
Purchase, 3/20 300
28.00
8,400
Inv. Balance 550
15,047
27.36
Sale, 5/15 (150)
27.36
(4,104)
Inv. Balance 400
27.36
10,943
Ending Inventory 400
10,943
Cost of Goods Sold 100
2,533
300
7,975
250
6,645
150
4,104
800
$21,257
Regards
Bassamcan you post DDL and some data..and also the example..pasted correctly..
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Bassam" <egbas@.yahoo.com> wrote in message
news:ORrEOZODFHA.3596@.TK2MSFTNGP12.phx.gbl...
> Hello
> im writing an inventory application for a customer that needs to calculate
> item cost by Moving Average method which requires calculating the cost
after
> each operation, i have a good experience with TSQL but so far i failed to
> write the statement that can do this WITHOUT writing cursors
> im trying to avoid calculating cost after each transaction to the
inventory
> . and by writing a stored procedure , get a list showing transactions and
> item avg in a period
> here is a description of moving avg method , also available here for those
> who cant read html
> http://www.fms.indiana.edu/auxiliary/inventory.asp
> Moving Average--Perpetual
> Continuous or moving average assigns a unit value to cost of goods
available
> for sale. In this scenario, the average cost determines cost of goods sold
> at the time of each sale. This method requires a calculation of average
unit
> cost after each purchase as illustrated below.
> # of Units Cost per Unit Total Cost Moving Avg. Cost
> Beginning inventory, 7/1 200
> $5,000
> $25.00
> Purchase, 8/10 100
> $26.00
> 2,600
>
> Inv. Balance 300
> 7,600
> 25.33
> Sale, 9/15 (100)
> 25.33
> (2,533)
>
> Inv. Balance 200
> 5,067
>
> Purchase, 12/7 600
> 27.00
> 16,200
>
> Inv. Balance 800
> 21,267
> 26.58
> Sale, 12/18 (300)
> 26.58
> (7,975)
>
> Inv. Balance 500
> 13,292
>
> Sale, 2/22 (250)
> 26.58
> (6,645
>
> Inv. Balance 250
> 6,647
>
> Purchase, 3/20 300
> 28.00
> 8,400
>
> Inv. Balance 550
> 15,047
> 27.36
> Sale, 5/15 (150)
> 27.36
> (4,104)
>
> Inv. Balance 400
> 27.36
> 10,943
>
> Ending Inventory 400
> 10,943
>
> Cost of Goods Sold 100
> 2,533
>
> 300
> 7,975
>
> 250
> 6,645
>
> 150
> 4,104
>
> 800
> $21,257
>
>
> Regards
> Bassam
>|||Please post DDL and some INSERT statements of your sample data:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||On Mon, 7 Feb 2005 09:27:19 +0200, Bassam wrote:

>im writing an inventory application for a customer that needs to calculate
>item cost by Moving Average method which requires calculating the cost afte
r
>each operation, i have a good experience with TSQL but so far i failed to
>write the statement that can do this WITHOUT writing cursors
Hi Bassam,
I think you can get the moving average by a simple self-join with group
by. Check the following example:
-- First, create a table to hold all transactions
-- Opening balance is considered a transaction in this simplified example
CREATE TABLE Operations
(OpDate smalldatetime not null primary key,
Amount int not null, -- >0 purchase <0 sale
UnitPrice money not null)
go
-- Insert all data (same as on web page you mentioned)
INSERT Operations (OpDate, Amount, UnitPrice)
SELECT '20040701', 200, 25
UNION ALL
SELECT '20040810', 100, 26
UNION ALL
SELECT '20040915', -100, 25.33
UNION ALL
SELECT '20041207', 600, 27
UNION ALL
SELECT '20041218', -300, 26.58
UNION ALL
SELECT '20050222', -250, 26.58
UNION ALL
SELECT '20050320', 300, 28
UNION ALL
SELECT '20050515', -150, 27.36
go
-- Here's the statement that will calculate amount, value and moving
-- average after each of the transaction.
SELECT a.OpDate AS InvDate,
SUM(b.Amount) AS Amount,
SUM(b.Amount * b.UnitPrice) AS Value,
SUM(b.Amount * b.UnitPrice) / SUM(b.Amount) AS MovingAvg
FROM Operations AS a
INNER JOIN Operations AS b
ON b.OpDate <= a.OpDate
GROUP BY a.OpDate
ORDER BY a.OpDate
go
-- Done. Now clean up the mess.
DROP TABLE Operations
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Thank you for your input but result of your statement will calculate
"Weighted Average" not "Moving Average"
difference is shown in examples in this link
http://www.fms.indiana.edu/auxiliary/inventory.asp
if you open this page and search for weighted average you fill find the
example which works with your statement but the just below example which is
for moving average won't work
i will post DDL and some data here to clear the case
Regards
Bassam
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hhme01diqfdiohvb2rj3t896k5deoc47e9@.
4ax.com...
> On Mon, 7 Feb 2005 09:27:19 +0200, Bassam wrote:
>
calculate
after
> Hi Bassam,
> I think you can get the moving average by a simple self-join with group
> by. Check the following example:
> -- First, create a table to hold all transactions
> -- Opening balance is considered a transaction in this simplified example
> CREATE TABLE Operations
> (OpDate smalldatetime not null primary key,
> Amount int not null, -- >0 purchase <0 sale
> UnitPrice money not null)
> go
> -- Insert all data (same as on web page you mentioned)
> INSERT Operations (OpDate, Amount, UnitPrice)
> SELECT '20040701', 200, 25
> UNION ALL
> SELECT '20040810', 100, 26
> UNION ALL
> SELECT '20040915', -100, 25.33
> UNION ALL
> SELECT '20041207', 600, 27
> UNION ALL
> SELECT '20041218', -300, 26.58
> UNION ALL
> SELECT '20050222', -250, 26.58
> UNION ALL
> SELECT '20050320', 300, 28
> UNION ALL
> SELECT '20050515', -150, 27.36
> go
> -- Here's the statement that will calculate amount, value and moving
> -- average after each of the transaction.
> SELECT a.OpDate AS InvDate,
> SUM(b.Amount) AS Amount,
> SUM(b.Amount * b.UnitPrice) AS Value,
> SUM(b.Amount * b.UnitPrice) / SUM(b.Amount) AS MovingAvg
> FROM Operations AS a
> INNER JOIN Operations AS b
> ON b.OpDate <= a.OpDate
> GROUP BY a.OpDate
> ORDER BY a.OpDate
> go
> -- Done. Now clean up the mess.
> DROP TABLE Operations
> go
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Mon, 7 Feb 2005 14:32:20 +0200, Bassam wrote:

>Hello Hugo,
>Thank you for your input but result of your statement will calculate
>"Weighted Average" not "Moving Average"
>difference is shown in examples in this link
>http://www.fms.indiana.edu/auxiliary/inventory.asp
>if you open this page and search for weighted average you fill find the
>example which works with your statement but the just below example which is
>for moving average won't work
Hi Bassam,
I did check that page, and the results of my query were equal to the
moving average quoted on that page (the table directly after the heading
"Moving Average--Perpetual").

>i will post DDL and some data here to clear the case
Excellent idea!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
I tested your statement, great , it works to a great detail !! , i found a
problem in the moving avg in date 2/22/05 , it should be exactly as the one
done on 12/18/04 (sales also) to be 26.5860 , but the one on 2/22/05 is
26.5920 (it is exactly 26.5860 in table) so i will make my tests what if i
put 20 sales transactions and see the result
but your statement gave max accurate to date to table.do you know a way to
overcome this small shift ?
thank you and welcome to any comments
Bassam|||On Mon, 7 Feb 2005 15:24:41 +0200, Bassam wrote:

>Hello Hugo,
>I tested your statement, great , it works to a great detail !! , i found a
>problem in the moving avg in date 2/22/05 , it should be exactly as the one
>done on 12/18/04 (sales also) to be 26.5860 , but the one on 2/22/05 is
>26.5920 (it is exactly 26.5860 in table) so i will make my tests what if i
>put 20 sales transactions and see the result
Hi Bassam,
I noted the difference as well. This is caused by rounding errors.
Consider the first few rows in the sample data. The beginning inventory
shows 200 units at a total cost of $ 5,000 - exactle $ 25.00 on average.
After the first purchase, there are 300 units on stock and the total cost
is equal to $ 7,600. The average price is $ 25.333333333333333 (etc), but
it is rounded down to $ 25.33. This would mean that if the following sale
would not be for 100 units (as listed in the example), but for 300 units,
the total sale price would be $ 7,599 and the remaining stock would be 0
units, for a total price of $ 1.
The example on the web page graciously avoids this anomaly by only
including a new price after each purchase. It doesn't list the moving avg
cost after a sale, so I could not verify if the values given by my query
are correct or not.
If you need the moving average cost to reflect the situation after the
last purchase instead of after the last sale, try this (slightly more
complicated) query:
SELECT a.OpDate AS InvDate,
SUM(b.Amount) AS Amount,
SUM(b.Amount * b.UnitPrice) AS Value,
(SELECT SUM(c.Amount * c.UnitPrice) / SUM(c.Amount)
FROM Operations AS c
WHERE c.OpDate <= (SELECT MAX(d.OpDate)
FROM Operations AS d
WHERE d.OpDate <= a.OpDate
AND d.Amount > 0)) AS MovingAvg
FROM Operations AS a
INNER JOIN Operations AS b
ON b.OpDate <= a.OpDate
GROUP BY a.OpDate
ORDER BY a.OpDate
(Note: if you only need the date and the moving average, not the amount
and value of the inventory, you can remove the group by and the join to
"Operations AS b" - IOW, you can simplify to:
SELECT a.OpDate AS InvDate,
(SELECT SUM(c.Amount * c.UnitPrice) / SUM(c.Amount)
FROM Operations AS c
WHERE c.OpDate <= (SELECT MAX(d.OpDate)
FROM Operations AS d
WHERE d.OpDate <= a.OpDate
AND d.Amount > 0)) AS MovingAvg
FROM Operations AS a
ORDER BY a.OpDate
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Bassam,
Thanks to Hugo for posting the DDL for the table, I will assume it is
correct. Here is a try, but do not compare to the table in the link, there i
s
an error there.
Error in the link:
Sale, 12/18 (300) 26.58 (7,975)
well, -300 * 26.58 should be 7,974.
select
a.OpDate,
sum(b.Amount) as number_of_units,
sum(b.Amount * b.UnitPrice) as total_cost,
(
select
sum(c.Amount * c.UnitPrice) / sum(c.Amount)
from
Operations as c
where
c.OpDate <= (
select
max(d.OpDate)
from
Operations as d
where
sign(d.Amount) >= 0 and d.OpDate <= a.OpDate
)
) as moving_avg_cost
from
Operations as a
inner join
Operations as b
on a.OpDate >= b.OpDate
group by
a.OpDate
order by
a.OpDate
go
AMB
"Bassam" wrote:

> Hello Hugo,
> Thank you for your input but result of your statement will calculate
> "Weighted Average" not "Moving Average"
> difference is shown in examples in this link
> http://www.fms.indiana.edu/auxiliary/inventory.asp
> if you open this page and search for weighted average you fill find the
> example which works with your statement but the just below example which i
s
> for moving average won't work
> i will post DDL and some data here to clear the case
> Regards
> Bassam
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:hhme01diqfdiohvb2rj3t896k5deoc47e9@.
4ax.com...
> calculate
> after
>
>|||Hello Hugo,
Thank you, Clear and efficient ! , only last problem , what if a user need
to delete a purchasing happened at beginning of month - or adjust its unit
price value, that means all next averages used in next sales is wrong and
need to be recalculated, is there a way to recalculate unit price for sales
transactions ' i need to adjust that before using your statement again or
result will be wrong
to make situation more complicated is that it might also be more purchasing
down there with sales, i mean suppose user deleted 1 of 5 purchasing done at
beginning of the month , on day 1 , while other purchasing happened on day 5
, 8 , 12 , 14 remains , and there are sales in between. , how then i can
recalculate unit price (which is the moving average) for sales
transactions.in between ?
Thank you
Bassam

Can someone help me with multiple "Left Outer Joins"?

I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
"Left Outer Joins" in order to return every transaction for a specific
set of criteria.

Using three "Left Outer Joins" slows the system down considerably.

I've tried creating a temp db, but I can't figure out how to execute
two select commands. (It throws the exception "The column prefix
'tempdb' does not match with a table name or alias name used in the
query.")

Looking for suggestions (and a lesson or two!) This is my first attempt
at SQL.

Current (working, albeit slowly) Query Below

TIA

SELECT

LEDGER_ENTRY.entry_amount,
LEDGER_TRANSACTION.credit_card_exp_date,
LEDGER_ENTRY.entry_datetime,
LEDGER_ENTRY.employee_id,
LEDGER_ENTRY.voucher_explanation,
LEDGER_ENTRY.card_reader_used_ind,
STAY.room_id,
GUEST.guest_lastname,
GUEST.guest_firstname,
STAY.arrival_time,
STAY.departure_time,
STAY.arrival_date,
STAY.original_departure_date,
STAY.no_show_status,
STAY.cancellation_date,
FOLIO.house_acct_id,
FOLIO.group_code,
LEDGER_TRANSACTION.original_receipt_id

FROM

mydb.dbo.LEDGER_ENTRY LEDGER_ENTRY,
mydb.dbo.LEDGER_TRANSACTION LEDGER_TRANSACTION,

mydb.dbo.FOLIO FOLIO
LEFT OUTER JOIN
mydb.dbo.STAY_FOLIO STAY_FOLIO
ON
FOLIO.folio_id = STAY_FOLIO.folio_id
LEFT OUTER JOIN
mydb.dbo.STAY STAY
ON
STAY_FOLIO.stay_id = STAY.stay_id
LEFT OUTER JOIN
mydb.dbo.GUEST GUEST
ON
FOLIO.guest_id = GUEST.guest_id

WHERE

LEDGER_ENTRY.trans_id = LEDGER_TRANSACTION.trans_id
AND FOLIO.folio_id = LEDGER_TRANSACTION.folio_id
AND LEDGER_ENTRY.payment_method='3737******6100'
AND LEDGER_ENTRY.property_id='abc123'

ORDER BY

LEDGER_ENTRY.entry_datetime DESCWhat is actually your question :-) ?

Jens Suessmeyer.|||My question is, Can this query be further optimized for speed?

I have tried creating temporary databases, to break-up the outer joins
into different select commands, but I couldn't get it to work properly.
I'm using VB6 and ADO, invoking the execute method of the adodb.command
object to return the recordset.|||To take access to different databases and tables you may
use for example syntax like this:
DatabaseName.TableName.ColumnName

I do not see why you would need to create a
temporal db and why this would help you
with performance.

I wonder if you meant a temporal table instead.

In general I experienced, that views (depending on what the do) may slow
down the whole query.
also ORDER BY.

I suggest you break your select statement in three peaces so you may
see with the profiler wich join would take the most of time.
maybe by applying an index to specific columns you get a bit more
performance.

if you watch the query from your VB-application, you wil have to
differ between the time thats used by your application and ADO
and the time the Database itself needs.
the bottleneck could also be at the application-side!

Hope this gave some hints.

Sonja

"Steve" <budgethelp@.yahoo.com> schrieb im Newsbeitrag
news:1126754368.398119.129660@.o13g2000cwo.googlegr oups.com...
>I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
> "Left Outer Joins" in order to return every transaction for a specific
> set of criteria.
> Using three "Left Outer Joins" slows the system down considerably.
> I've tried creating a temp db, but I can't figure out how to execute
> two select commands. (It throws the exception "The column prefix
> 'tempdb' does not match with a table name or alias name used in the
> query.")
> Looking for suggestions (and a lesson or two!) This is my first attempt
> at SQL.
> Current (working, albeit slowly) Query Below
> TIA
> SELECT
> LEDGER_ENTRY.entry_amount,
> LEDGER_TRANSACTION.credit_card_exp_date,
> LEDGER_ENTRY.entry_datetime,
> LEDGER_ENTRY.employee_id,
> LEDGER_ENTRY.voucher_explanation,
> LEDGER_ENTRY.card_reader_used_ind,
> STAY.room_id,
> GUEST.guest_lastname,
> GUEST.guest_firstname,
> STAY.arrival_time,
> STAY.departure_time,
> STAY.arrival_date,
> STAY.original_departure_date,
> STAY.no_show_status,
> STAY.cancellation_date,
> FOLIO.house_acct_id,
> FOLIO.group_code,
> LEDGER_TRANSACTION.original_receipt_id
> FROM
> mydb.dbo.LEDGER_ENTRY LEDGER_ENTRY,
> mydb.dbo.LEDGER_TRANSACTION LEDGER_TRANSACTION,
> mydb.dbo.FOLIO FOLIO
> LEFT OUTER JOIN
> mydb.dbo.STAY_FOLIO STAY_FOLIO
> ON
> FOLIO.folio_id = STAY_FOLIO.folio_id
> LEFT OUTER JOIN
> mydb.dbo.STAY STAY
> ON
> STAY_FOLIO.stay_id = STAY.stay_id
> LEFT OUTER JOIN
> mydb.dbo.GUEST GUEST
> ON
> FOLIO.guest_id = GUEST.guest_id
> WHERE
> LEDGER_ENTRY.trans_id = LEDGER_TRANSACTION.trans_id
> AND FOLIO.folio_id = LEDGER_TRANSACTION.folio_id
> AND LEDGER_ENTRY.payment_method='3737******6100'
> AND LEDGER_ENTRY.property_id='abc123'
> ORDER BY
> LEDGER_ENTRY.entry_datetime DESC|||Yes, I meant that I tried to create a temporary table, not db, sorry...

Regarding the 3 joins...would putting parenthesis around any of them
help?
How are they being processed exactly?
The first join has a single table reference immediately preceding the
join statement, but the others cannot (is that correct?)
What are the next two joins being joined to exacty (since there is no
table specified before the two join statements?

The tables that I'm joining look like this:

--FOLIO-----STAY FOLIO------STAY
|
|
|___________GUEST

All transactions have FOLIO records, but not all transactions have STAY
FOLIO, STAY, OR GUEST records.
I need to return all transactions that have a folio record.

This is the syntax I'm using to accomplish this:

mydb.dbo.FOLIO FOLIO
LEFT OUTER JOIN
mydb.dbo.STAY_FOLIO STAY_FOLIO
ON
FOLIO.folio_id = STAY_FOLIO.folio_id
LEFT OUTER JOIN
mydb.dbo.STAY STAY
ON
STAY_FOLIO.stay_id = STAY.stay_id
LEFT OUTER JOIN
mydb.dbo.GUEST GUEST
ON
FOLIO.guest_id = GUEST.guest_id

I don't understand how the order of the joins affects their processing.
Is there a better way to phrase the joins, given the table
relationships as outlined above?

Thanks!|||> Yes, I meant that I tried to create a temporary table, not db, sorry...

this would be accomplished with views. Like I mentioned before
but this may not be a Solution for your problem.

As I know a lot of Select Squences with a lot more Joins
than you need here, I do not believe that your performance-problem
results from the sql statement.
Depending on the server-machine your Database is installed on,
there may be different reasons, why this query takes a long time.

1)Maybe your tables are big. Lets asume each of them has 1 000 000 tuples.
Even then the query should not last (DEPENDING ON YOUR MACHINE)
a "long" time.
If this Machine is for example the whole time working on a 70% level
it slows down everything to death.
If the machine has enough breath to acomplish your query and you are testing
just solely we leave this section ...

2)the dbms tries to optimize sql -queries by itself, to make them faster, if
you want to
optimize more, use only the lines and columns you seek. It makes the whole
thing
a little faster if you simply snip columns and rows that you do not need.

3) it may help with performance to apply indexes to columns that will be
joined

4) Your application is getting all data over network one by one and
everything
slows down. Then its not a database or query -problem

5) use the SQL Profiler to see where the bottleneck is.

If you like, create for each join a view and then simply join the view with
folio
like this for example
------------
CREATE VIEW stay_test AS
Select Stay_folio.folio_id from
STAY_FOLIO left outer join STAY
ON
stay_folio.stay_id = stay.stay_id
-----------
SELECT * FROM
folio LEFT OUTER JOIN stay_test
ON
folio.folio_id = stay_test.folio_id
LEFT OUTER JOIN guest
ON
folio.guest_id = guest.guest_id
-----------

At the SQL profiler you can view each selection that is made and how long it
takes to get result

> Regarding the 3 joins...would putting parenthesis around any of them
> help?
> How are they being processed exactly?
> The first join has a single table reference immediately preceding the
> join statement, but the others cannot (is that correct?)
> What are the next two joins being joined to exacty (since there is no
> table specified before the two join statements?
> The tables that I'm joining look like this:
> --FOLIO-----STAY FOLIO------STAY
> |
> |
> |___________GUEST
> All transactions have FOLIO records, but not all transactions have STAY
> FOLIO, STAY, OR GUEST records.
> I need to return all transactions that have a folio record.
> This is the syntax I'm using to accomplish this:
> mydb.dbo.FOLIO FOLIO
> LEFT OUTER JOIN
> mydb.dbo.STAY_FOLIO STAY_FOLIO
> ON
> FOLIO.folio_id = STAY_FOLIO.folio_id
> LEFT OUTER JOIN
> mydb.dbo.STAY STAY
> ON
> STAY_FOLIO.stay_id = STAY.stay_id
> LEFT OUTER JOIN
> mydb.dbo.GUEST GUEST
> ON
> FOLIO.guest_id = GUEST.guest_id
> I don't understand how the order of the joins affects their processing.
> Is there a better way to phrase the joins, given the table
> relationships as outlined above?
> Thanks!|||On 14 Sep 2005 20:19:28 -0700, Steve wrote:

>I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
>"Left Outer Joins" in order to return every transaction for a specific
>set of criteria.
>Using three "Left Outer Joins" slows the system down considerably.

Hi Steve,

That need not be the case. I guess that adding the right indexes would
help a lot.

>I've tried creating a temp db, but I can't figure out how to execute
>two select commands. (It throws the exception "The column prefix
>'tempdb' does not match with a table name or alias name used in the
>query.")

I could help you with solving this problem, but I won't. Breaking a
query in smaller pieces with temp tables has a fair chance to hurt your
performance, and very limited chance to do any good.

The query optimizer can use all the tricks that you can use, and then
some. Better to trust that the optimizer will pick the right execution
plan from the flock of available options instead of forcing it to do the
way you think is best. There ARE cases where the optimizer does need
some guidance, but they are the exception rather than the rule.

>Current (working, albeit slowly) Query Below

Thanks for posting the query, but you'll have to provide a lot more
information to enable us to help you. We need to know the structure of
your tables (posted as CREATE TABLE statements, including all properties
and constraints, but excluding irrelevant columns), the indexes you have
defined for your tables, if any (posted as CREATE INDEX statements), a
few rows of sample data (posted as INSERT statements) and the expected
results from that sample data to give us an idea what you're trying to
achieve. Including a short description of your actual business problem
is a great idea too. See www.aspfaq.com/5006 for some useful pointers on
hjow to assemble the information we need, in the best format.

Oh, and we'd also like to know how many rows (approximately) you have in
each of your tables - and the execution plan that is currently used for
your query (you can get the execution plan if you run the query with SET
SHOWPLAN_ALL ON.

With that information, we can try to find out why your current query is
running slow, and how to remedy that.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Steve (budgethelp@.yahoo.com) writes:
> I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
> "Left Outer Joins" in order to return every transaction for a specific
> set of criteria.
> Using three "Left Outer Joins" slows the system down considerably.
> I've tried creating a temp db, but I can't figure out how to execute
> two select commands. (It throws the exception "The column prefix
> 'tempdb' does not match with a table name or alias name used in the
> query.")
> Looking for suggestions (and a lesson or two!) This is my first attempt
> at SQL.

As Hugo pointed out, it is impossible to give very precise advice from
from the information you have posted. Assuming that there is an index
on (payment_method, property_id) on LEDGER_ENTRY, and that all other
tables have indexes on the columns you join on, I would expect the query
to perform well. Then again, there can be several reasons to why it does
not.

I analysed your query, and I think that I found one flaw. Here is a
rewritten version:

SELECT LE.entry_amount, LT.credit_card_exp_date, LE.entry_datetime,
LE.employee_id, LE.voucher_explanation, LE.card_reader_used_ind,
S.room_id, G.guest_lastname, G.guest_firstname, S.arrival_time,
S.departure_time, S.arrival_date, S.original_departure_date,
S.no_show_status, S.cancellation_date, F.house_acct_id,
F.group_code, LT.original_receipt_id
FROM mydb.dbo.LEDGER_ENTRY LE
JOIN mydb.dbo.LEDGER_TRANSACTON LT ON LE.trans_id = LT.trans_id
JOIN mydb.dbo.FOLIO F ON F.folio_id = LT.folio_id
LEFT JOIN (mydb.dbo.STAY_FOLIO SF
JOIN mydb.dbo.STAY S ON SF.stay_id = S.stay_id)
ON F.folio_id = SF.folio_id
LEFT JOIN mydb.dbo.GUEST G ON F.guest_id = G.guest_id
WHERE LE.payment_method='3737******6100'
AND LE.property_id='abc123'
ORDER BY LE.entry_datetime DESC

This alters the semantics of the query slightly, and I guess to the
good. Whether it affects performance, I don't know.

One potential problem is if the joins from FOLIO to STAY_FOLIO and GUEST
could hit multiple rows in the latter tables. In such case you get too
many rows back, which also could cause poor performance.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Can Report service run a Stored Procedure?

Hi, there,

I have a report that requires to join a lot of tables.

May I ask if I can use a stored procedure to make all the joins and populate a temp table, then make the report sit on the temp table. so every time the user runs the report, it will kick off the stored procedure which will truncate the temp table and repopulate it, then show the report.

Thanks

I would use functions to break down the complexity. Call these functions from the stored procedure.|||

You could do that I suppose, although I'm not sure this is the best way. What about just using the stored procedure as the datasource for the report and not bother with a temp table at all?

If you do decide you need a temp table, then from what you're describing is sounds like you want a temp table that will continue to exist after the stored procedure has finished running. I will caution you that if you create a regular table in the database that you intend to use as a temp table (i.e. always truncating and repopulated the table when you run your stored proc) then you could run into problems if you have two users run your report at the same time (i.e. one run is truncating the table at the same time the other run is trying to retrieve data from the table). We've handled this in the past by creating an additional column in to hold the @.@.spid for the connection. That was years ago and I don't know if that's a good practice or not with MSSQL2005. I also don't know if that would work well with RS's usage of shared db connections.

|||

Thank you! eksplorer,

Your input is very helpful. if two users running the report at same time it will be a problem.

I am using report builder, may I ask how can you make stored procedure as a datasource when you are building a report model.

Thanks

|||

I haven't worked with report models. They way you do it in a reporting services report is to do something like this for the dataset:

= "exec pr_getmydata '" & User!UserID & "', '" & Parameters!prParm1.Value & "', " & Parameters!prParm2.Value & "'"

-bruce