Can we do a double Cursor?

Can we do a double Cursor where we select the data form one table using a
cursor and then use the key data such as ORDER number to select data from
another table using a cursor?
regards,
RonYou can. You can also try to take your bike on the freeway at rush hour and
challenge a street racer...
Any reason you can't do this with a simple join? What exactly are you
trying to accomplish?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
<Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
> Can we do a double Cursor where we select the data form one table using a
> cursor and then use the key data such as ORDER number to select data from
> another table using a cursor?
> --
> regards,
> Ron|||I’m trying to simulate SQR program. Do you have an example where you use
double cursor.
"Aaron [SQL Server MVP]" wrote:

> You can. You can also try to take your bike on the freeway at rush hour a
nd
> challenge a street racer...
> Any reason you can't do this with a simple join? What exactly are you
> trying to accomplish?
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> <Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
> news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
>
>|||I’m trying to simulate SQR program. Do you have an example where you use
double cursor.
"Aaron [SQL Server MVP]" wrote:

> You can. You can also try to take your bike on the freeway at rush hour a
nd
> challenge a street racer...
> Any reason you can't do this with a simple join? What exactly are you
> trying to accomplish?
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> <Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
> news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
>
>|||I have absolutely no idea what an "SQR program" is. Do you mean square
root?
Anyway, a double cursor would look like this:
CREATE TABLE #pk
(
id INT PRIMARY KEY
)
CREATE TABLE #fk
(
id int FOREIGN KEY REFERENCES #pk(id),
foo VARCHAR(2)
)
SET NOCOUNT ON
INSERT #pk
SELECT 1
UNION SELECT 2
INSERT #fk
SELECT 1, 'a'
UNION SELECT 1, 'b'
UNION SELECT 2, 'c'
DECLARE @.pkID INT, @.foo VARCHAR(2)
DECLARE c1 CURSOR FOR
SELECT id FROM #pk
OPEN c1
FETCH NEXT FROM c1 INTO @.pkID
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE c2 CURSOR FOR
SELECT foo FROM #fk WHERE id = @.pkID
OPEN c2
FETCH NEXT FROM c2 INTO @.foo
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Outer loop: '+RTRIM(@.pkID)
PRINT 'Inner loop: '+@.foo
FETCH NEXT FROM c2 INTO @.foo
END
CLOSE c2
DEALLOCATE c2
FETCH NEXT FROM c1 INTO @.pkID
END
CLOSE c1
DEALLOCATE c1
DROP TABLE #fk, #pk
Please don't do this on a production system. If you do, don't tell them I
told you how to do it. I will deny it and tell them it was Celko, spoofing
my IP and all.
On 3/22/05 8:03 PM, in article
9E814E08-0578-4953-8BD4-E0CEB718ADD2@.microsoft.com, "Ron,hayim"
<Ronhayim@.discussions.microsoft.com> wrote:

> Im trying to simulate SQR program. Do you have an example where you use
> double cursor.|||On Tue, 22 Mar 2005 15:41:02 -0800, Ron wrote:

>Can we do a double Cursor where we select the data form one table using a
>cursor and then use the key data such as ORDER number to select data from
>another table using a cursor?
Hi Ron,
Even if you really do need a cursor (which I doubt - and the statistics
are on my side), there is really no need to use two of the beasts.
Why not create one query that joins the two tables the way you want them
to be joined, filters rows you don't need and returns only the columns
you need? Then, if you really must, you can always use that query for
your cursor...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The script was a lot of help. SQR is a program that used by Peoplesoft.
This was just a special need. All of you right, just joining the tables
should do the it.
"Aaron [SQL Server MVP]" wrote:

> I have absolutely no idea what an "SQR program" is. Do you mean square
> root?
> Anyway, a double cursor would look like this:
>
> CREATE TABLE #pk
> (
> id INT PRIMARY KEY
> )
> CREATE TABLE #fk
> (
> id int FOREIGN KEY REFERENCES #pk(id),
> foo VARCHAR(2)
> )
> SET NOCOUNT ON
> INSERT #pk
> SELECT 1
> UNION SELECT 2
> INSERT #fk
> SELECT 1, 'a'
> UNION SELECT 1, 'b'
> UNION SELECT 2, 'c'
> DECLARE @.pkID INT, @.foo VARCHAR(2)
> DECLARE c1 CURSOR FOR
> SELECT id FROM #pk
> OPEN c1
> FETCH NEXT FROM c1 INTO @.pkID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DECLARE c2 CURSOR FOR
> SELECT foo FROM #fk WHERE id = @.pkID
> OPEN c2
> FETCH NEXT FROM c2 INTO @.foo
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Outer loop: '+RTRIM(@.pkID)
> PRINT 'Inner loop: '+@.foo
> FETCH NEXT FROM c2 INTO @.foo
> END
> CLOSE c2
> DEALLOCATE c2
> FETCH NEXT FROM c1 INTO @.pkID
> END
> CLOSE c1
> DEALLOCATE c1
> DROP TABLE #fk, #pk
>
> Please don't do this on a production system. If you do, don't tell them I
> told you how to do it. I will deny it and tell them it was Celko, spoofin
g
> my IP and all.
>
> On 3/22/05 8:03 PM, in article
> 9E814E08-0578-4953-8BD4-E0CEB718ADD2@.microsoft.com, "Ron,hayim"
> <Ronhayim@.discussions.microsoft.com> wrote:
>
>

Can we create composite logical key in data source view?

Hi, all,

Just found that I need to create a composite logical key consisting of a few columns in data source view in order to uniquely identify each row of record for the table in data source view, but then found that I am not able to create this kind of logical key? Is there any ways for us to create such a compoiste logical key in the data source view?

Thanks in advance and I am very much looking forward to hearing from you for your kind and helpful advices.

With best regards,

Yours sincerely,

You can create composite logical keys in the DSV, just multi-select columns, right click, and mark as logical key. However, if an existing physical key (i.e. a key reported by the relational source), then the DSV will not allow you to create any logical key.|||

Hi,

Thanks a lot.

WIth best regards,

Yours sincerely,

CAN USER EDIT DATA in the report?

Hi,
I have a report which shows all the orders in the systam . I want to be able
to view the order details for an order when an order number is clicked and be
able to edit some information so thagt it goes back to the databse. Is it
possible to edit data with REpotrting svcs?
If not, then is there a way, i can show all the orders in the databse, and
have a link in the report which takes the user to a web page where the user
can edit the order and then after editing the order, he again can view the
orginal (all orders ) in the system from the report and then click a link
which takes the user to the order details of that order so that the order can
be edited?
--
pmudNo, it is not possible to edit data in report in place.
Yes, it is possible to make a URL link to the edit page for every order.
You just need to setup an action for the textbox with order id that will be
an http link with order id parameter. Action is visible in properties for
the textbox.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:7CC6E584-B705-45A9-B83A-9821109B592A@.microsoft.com...
> Hi,
> I have a report which shows all the orders in the systam . I want to be
able
> to view the order details for an order when an order number is clicked and
be
> able to edit some information so thagt it goes back to the databse. Is it
> possible to edit data with REpotrting svcs?
> If not, then is there a way, i can show all the orders in the databse,
and
> have a link in the report which takes the user to a web page where the
user
> can edit the order and then after editing the order, he again can view the
> orginal (all orders ) in the system from the report and then click a link
> which takes the user to the order details of that order so that the order
can
> be edited?
> --
> pmudsql

Can SQL Server Express coexist with SQL 7/2000?

Can SQL Server Express coexist with SQL 7/2000?

What is the order in which they are to be installed/uninstalled?

Yes you can but you will need the SQL Server 2005 developer edition which comes with management studio and cost less than $40 to manage all. If you really don't want to spend the money then you may need to upgrade the SQL Server 7.0 to 2000 so you will only access the SQL Server Express through command line. I am assuming you know there is no DRI (declarative referential integrity) in SQL Server 7.0 and SQL Server 7.0 uses TDS(tabular data stream) 70, while SQL Server 2000 uses TDS 80 and SQL Server 2005 Express uses TDS 90. If you are interested check out Provantage for the developer edition. Hope this helps.

http://www.provantage.com/microsoft-e32-00575~7MCSB0EX.htm

|||

So I assume that what you meant to say was that sans the graphical management tool (management studio), SQL Server 7.0, 2000 and 2005 Express can all coexist? Correct me if I understood wrong.

Regarding Management Studio for graphical management of SQL 2005 Express, yes I have downloaded and installed that for free.

What about the 2nd part of my original question - say I already have SQL Server 7.0 or 2000 in my machine. Will installing SQL Server 2005 Express have any repercussions? What about the other way round- SQL 2005 Express first, then SQL Server 2000?

|||

You said you have installed SQL Server 2005 Express management tool, I am not sure you can use it to manage SQL Server 7/2000, if you can then you are good to go.

I must tell you I am not comfortable advising you to run SQL Server 7.0 without full management tool. If you are interested you can install SQL Server 7.0, 2000 and then the Express but also install the eval yes you can install all because I think the limit is seven in one box. The reason is I think it is ok to use express to deploy intranet application but I don't see it as a development tool. Hope this helps.

http://www.microsoft.com/sql/downloads/trial-software.mspx

Can SQL optimize an Order By clause?

In doing some analysis of our queries, we let users dynamically sort
data in a resulting grid, and, consistently, sorted queries are much
slower than non-sorted queries. Can SQL use indexes to get speed up a
query? For example, say we have a Orders table with 100 million
orders, clustered index on orderID, and we want to get the top 10
orders of all time, can sql server use an index on orderTotal to speed
up the query time of:
select top 10 orderID, orderTotal from orders order by orderTotal desc
Obviously, the real business case is more complex than this, but I want
to know if SQL server can use an index to just get the top 10 without
having to do a full table scan. From my tests, that doesn't appear to
be the case.Yes, for such a high-selectivity query, an index can be used. SQL Server wou
ld use the index on
OrderTotal to find the highest value. For the 10 first rows, it will use the
pointer in that index
to go fetch the data row, in descending order. The lower selectivity you hav
e, the less of a perf
gain you see by using the index, as a data page has to be visited *for each
row* to be returned. Too
low selectivity, it is cheaper to scan the table (or use some other index) a
nd then do the sort. I
recommend you check the execution plan, try some index hint, perhaps even th
e FIRST hint and also
make sure that statistics are up to date.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Xavryn@.gmail.com> wrote in message news:1134664775.545525.108690@.g43g2000cwa.googlegroups.c
om...
> In doing some analysis of our queries, we let users dynamically sort
> data in a resulting grid, and, consistently, sorted queries are much
> slower than non-sorted queries. Can SQL use indexes to get speed up a
> query? For example, say we have a Orders table with 100 million
> orders, clustered index on orderID, and we want to get the top 10
> orders of all time, can sql server use an index on orderTotal to speed
> up the query time of:
> select top 10 orderID, orderTotal from orders order by orderTotal desc
> Obviously, the real business case is more complex than this, but I want
> to know if SQL server can use an index to just get the top 10 without
> having to do a full table scan. From my tests, that doesn't appear to
> be the case.
>

Can SQL optimize an Order By clause?

In doing some analysis of our queries, we let users dynamically sort
data in a resulting grid, and, consistently, sorted queries are much
slower than non-sorted queries. Can SQL use indexes to get speed up a
query? For example, say we have a Orders table with 100 million
orders, clustered index on orderID, and we want to get the top 10
orders of all time, can sql server use an index on orderTotal to speed
up the query time of:
select top 10 orderID, orderTotal from orders order by orderTotal desc
Obviously, the real business case is more complex than this, but I want
to know if SQL server can use an index to just get the top 10 without
having to do a full table scan. From my tests, that doesn't appear to
be the case.
Yes, for such a high-selectivity query, an index can be used. SQL Server would use the index on
OrderTotal to find the highest value. For the 10 first rows, it will use the pointer in that index
to go fetch the data row, in descending order. The lower selectivity you have, the less of a perf
gain you see by using the index, as a data page has to be visited *for each row* to be returned. Too
low selectivity, it is cheaper to scan the table (or use some other index) and then do the sort. I
recommend you check the execution plan, try some index hint, perhaps even the FIRST hint and also
make sure that statistics are up to date.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Xavryn@.gmail.com> wrote in message news:1134664775.545525.108690@.g43g2000cwa.googlegr oups.com...
> In doing some analysis of our queries, we let users dynamically sort
> data in a resulting grid, and, consistently, sorted queries are much
> slower than non-sorted queries. Can SQL use indexes to get speed up a
> query? For example, say we have a Orders table with 100 million
> orders, clustered index on orderID, and we want to get the top 10
> orders of all time, can sql server use an index on orderTotal to speed
> up the query time of:
> select top 10 orderID, orderTotal from orders order by orderTotal desc
> Obviously, the real business case is more complex than this, but I want
> to know if SQL server can use an index to just get the top 10 without
> having to do a full table scan. From my tests, that doesn't appear to
> be the case.
>

Can SQL optimize an Order By clause?

In doing some analysis of our queries, we let users dynamically sort
data in a resulting grid, and, consistently, sorted queries are much
slower than non-sorted queries. Can SQL use indexes to get speed up a
query? For example, say we have a Orders table with 100 million
orders, clustered index on orderID, and we want to get the top 10
orders of all time, can sql server use an index on orderTotal to speed
up the query time of:
select top 10 orderID, orderTotal from orders order by orderTotal desc
Obviously, the real business case is more complex than this, but I want
to know if SQL server can use an index to just get the top 10 without
having to do a full table scan. From my tests, that doesn't appear to
be the case.Yes, for such a high-selectivity query, an index can be used. SQL Server would use the index on
OrderTotal to find the highest value. For the 10 first rows, it will use the pointer in that index
to go fetch the data row, in descending order. The lower selectivity you have, the less of a perf
gain you see by using the index, as a data page has to be visited *for each row* to be returned. Too
low selectivity, it is cheaper to scan the table (or use some other index) and then do the sort. I
recommend you check the execution plan, try some index hint, perhaps even the FIRST hint and also
make sure that statistics are up to date.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Xavryn@.gmail.com> wrote in message news:1134664775.545525.108690@.g43g2000cwa.googlegroups.com...
> In doing some analysis of our queries, we let users dynamically sort
> data in a resulting grid, and, consistently, sorted queries are much
> slower than non-sorted queries. Can SQL use indexes to get speed up a
> query? For example, say we have a Orders table with 100 million
> orders, clustered index on orderID, and we want to get the top 10
> orders of all time, can sql server use an index on orderTotal to speed
> up the query time of:
> select top 10 orderID, orderTotal from orders order by orderTotal desc
> Obviously, the real business case is more complex than this, but I want
> to know if SQL server can use an index to just get the top 10 without
> having to do a full table scan. From my tests, that doesn't appear to
> be the case.
>

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 Reporting Serivces do THIS?

Hi, we have a business need that would
1. allow a user to select and order the columns from a table in the UI
2. The user would then provide filtering on those columns (CLIENT SIDE)
3. User will then execute this report to return data based on criteria
** The initial UI screen will have data driven from a Stored Procedure
The filtering should be done on the client side, showing only the columns
that have been selected in the order that they were selected
4. User can then have this ad hoc query saved
Is there a process of method that Reporting Services provides out of the box
to have this functionality?
--
MCSD.NETI suggest reading up on Report Builder in RS 2005 (released in November). It
is a new addition to the product that is web based end user reporting. It
sounds close to what you want.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ramzi" <Ramzi@.discussions.microsoft.com> wrote in message
news:A419C5CB-370A-4678-9D58-BAA69E9006CA@.microsoft.com...
> Hi, we have a business need that would
> 1. allow a user to select and order the columns from a table in the UI
> 2. The user would then provide filtering on those columns (CLIENT SIDE)
> 3. User will then execute this report to return data based on criteria
> ** The initial UI screen will have data driven from a Stored Procedure
> The filtering should be done on the client side, showing only the columns
> that have been selected in the order that they were selected
> 4. User can then have this ad hoc query saved
> Is there a process of method that Reporting Services provides out of the
> box
> to have this functionality?
> --
> MCSD.NET