Query with embedded SELECT

This is a request for help with a SELECT within SELECT. The following return
s
open occurances (no END_DATE) and selects the associated appointment with th
e
highest APPT_ID. I want to imbed another SELECT that will 1) read back to th
e
Appointment table (joining back on the APPT_ID) and return the APPT_DATE for
this appointment. 2) I want to JOIN to a table called Resource AS r on a
column in the Appointment table called a.RESOURCE_ID and get r. RESOURCE_NAM
E
from the resource table. Here is the original SELECT:
SELECT o.OCCURANCE_DESC AS CaseID, p.home_phone AS phone, RTRIM(p.first_name
+ ' ' + p.middle_name) + ' ' + p.last_name AS Patient, o.NOTE AS Comment,
MAX(a.APPT_ID) AS LastAppt FROM Occurance o INNER JOIN Patient p ON
o.CLIENT_ID = p.PatUniqueID JOIN Appointment a ON o.OCCURANCE_ID =
a.OCCURANCE_ID WHERE ISDATE(END_DATE) = 0 AND a.Status < 200 GROUP BY
o.OCCURANCE_DESC, p.home_phone, RTRIM(p.first_name + ' ' + p.middle_name) +
' ' + p.last_name, o.NOTE, o.OCCURANCE_ID ;
Thank you very muchrichardb
Please post DDL+ sample data + expected result. It's hard to suggest
something.
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:4D7B74D9-AD1C-40BB-94FF-EC18FF764920@.microsoft.com...
> This is a request for help with a SELECT within SELECT. The following
returns
> open occurances (no END_DATE) and selects the associated appointment with
the
> highest APPT_ID. I want to imbed another SELECT that will 1) read back to
the
> Appointment table (joining back on the APPT_ID) and return the APPT_DATE
for
> this appointment. 2) I want to JOIN to a table called Resource AS r on a
> column in the Appointment table called a.RESOURCE_ID and get r.
RESOURCE_NAME
> from the resource table. Here is the original SELECT:
> SELECT o.OCCURANCE_DESC AS CaseID, p.home_phone AS phone,
RTRIM(p.first_name
> + ' ' + p.middle_name) + ' ' + p.last_name AS Patient, o.NOTE AS Comment,
> MAX(a.APPT_ID) AS LastAppt FROM Occurance o INNER JOIN Patient p ON
> o.CLIENT_ID = p.PatUniqueID JOIN Appointment a ON o.OCCURANCE_ID =
> a.OCCURANCE_ID WHERE ISDATE(END_DATE) = 0 AND a.Status < 200 GROUP BY
> o.OCCURANCE_DESC, p.home_phone, RTRIM(p.first_name + ' ' + p.middle_name)
+
> ' ' + p.last_name, o.NOTE, o.OCCURANCE_ID ;
> Thank you very much|||I don't know what "post DDL+ sample data" means? Let me try to simplify the
request. Let's say I am selecting from a table of appointments the latest
appointment of each type for each patient as follows
SELECT p.PATIENT_NAME, a.APPT_TYPE, a.MAX(APPT_ID) AS LastestAppt FROM
Appointments JOIN Patients p ON a.PATIENT_ID = p.PATIENT_ID WHERE STATUS <
200 GROUP BY PATIENT_NAME, APPT_TYPE.
This would give:
PATIENT_NAME APPT_TYPE LastestAppt
-- -- --
JONES 90800 43
SMITH 81000 35
etc.
Each appointment row includes a code for the doctor. However, I did not want
to group on the doctor initially, because I only want one most recent
appointment for each patient. Now I want to know who is the doctor for that
appointment, so I think I need a second imbeded SELECT to go back into the
Appointment file and pick off the doctor's ID, resulting in:
PATIENT_NAME APPT_TYPE LastestAppt Doctor
-- -- -- --
JONES 90800 43 RMB
SMITH 81000 35 JDB
etc.
I cannot figure out how to do that and would appreciate help with an example
.
"Uri Dimant" wrote:

> richardb
> Please post DDL+ sample data + expected result. It's hard to suggest
> something.
>
>
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:4D7B74D9-AD1C-40BB-94FF-EC18FF764920@.microsoft.com...
> returns
> the
> the
> for
> RESOURCE_NAME
> RTRIM(p.first_name
> +
>
>|||Richardb
DDL means to post actual a table structure with some data.
CREATE TABLE xxx
(
col1 INT,
col2 INT,
blala...
)
INSERT INTO xxx VALUES ('data',data'.....)
Based on your narrotive I guess you need the following
SELECT p.PATIENT_NAME, a.APPT_TYPE,
a.MAX(APPT_ID) AS LastestAppt,D.Doctorid
FROM Appointments JOIN Patients p ON a.PATIENT_ID = p.PATIENT_ID JOIN
(SELECT max(doctoreID) Doctorid FROM
FROM 'SomeTable' GROUP BY Somthing) D ON
D.doctoreID=AnotherTable.Doctorid
WHERE STATUS <
200 GROUP BY PATIENT_NAME, APPT_TYPE.
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:8A2292C7-A997-47BA-A0A0-5173DDA61C26@.microsoft.com...
> I don't know what "post DDL+ sample data" means? Let me try to simplify
the
> request. Let's say I am selecting from a table of appointments the latest
> appointment of each type for each patient as follows
> SELECT p.PATIENT_NAME, a.APPT_TYPE, a.MAX(APPT_ID) AS LastestAppt FROM
> Appointments JOIN Patients p ON a.PATIENT_ID = p.PATIENT_ID WHERE STATUS <
> 200 GROUP BY PATIENT_NAME, APPT_TYPE.
> This would give:
> PATIENT_NAME APPT_TYPE LastestAppt
> -- -- --
> JONES 90800 43
> SMITH 81000 35
> etc.
> Each appointment row includes a code for the doctor. However, I did not
want
> to group on the doctor initially, because I only want one most recent
> appointment for each patient. Now I want to know who is the doctor for
that
> appointment, so I think I need a second imbeded SELECT to go back into the
> Appointment file and pick off the doctor's ID, resulting in:
> PATIENT_NAME APPT_TYPE LastestAppt Doctor
> -- -- -- --
> JONES 90800 43 RMB
> SMITH 81000 35 JDB
> etc.
> I cannot figure out how to do that and would appreciate help with an
example.
>
> "Uri Dimant" wrote:
>
with
to
APPT_DATE
a
Comment,
p.middle_name)sql

Query Tuning

Hi,
I have a query that selects 4 fields. One is of type Varchar(500). When I
execute the query, the response is about 9 seconds (very slow). When I
comment out the varchar field, it returns in less than 1 second.
It took me a while to figure out that it's not a missing index, i can't
figure this one out.
please advise.
rafaelHow many records are being returned?
"Rafael Chemtob" wrote:

> Hi,
> I have a query that selects 4 fields. One is of type Varchar(500). When
I
> execute the query, the response is about 9 seconds (very slow). When I
> comment out the varchar field, it returns in less than 1 second.
> It took me a while to figure out that it's not a missing index, i can't
> figure this one out.
> please advise.
> rafael
>
>|||10
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:6F062F1B-91A3-45B6-AB3E-F08DF4A37831@.microsoft.com...
> How many records are being returned?
> "Rafael Chemtob" wrote:
>
When I|||> How many records are being returned?
..And what is the average length of the data in those rows?
Thomas
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:6F062F1B-91A3-45B6-AB3E-F08DF4A37831@.microsoft.com...
> How many records are being returned?
> "Rafael Chemtob" wrote:
>|||ok, sorry for not being very detailed.
4 fields
id_rating INT
summary VARCHAR(500)
dt_rating smalldatetime
id_user INT
these are the 4 fields. The record count that's returned is 11 rows.
Hope that helps
thanks
"Thomas" <replyingroup@.anywhere.com> wrote in message
news:#45wbrTRFHA.2384@.tk2msftngp13.phx.gbl...
> ..And what is the average length of the data in those rows?
>
> Thomas
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:6F062F1B-91A3-45B6-AB3E-F08DF4A37831@.microsoft.com...
When I
>|||Is the performance discrepancy consistent? i.e., have you tested this a
number of times? How many records are in the table?,
and finally, is there an index on the table that contains all the other
three columns from the table, but not the varchar(500) column?
Also, please post the DDL for the tables, and the actual Query.
Charly
"Rafael Chemtob" wrote:

> ok, sorry for not being very detailed.
> 4 fields
> id_rating INT
> summary VARCHAR(500)
> dt_rating smalldatetime
> id_user INT
> these are the 4 fields. The record count that's returned is 11 rows.
> Hope that helps
> thanks
>
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:#45wbrTRFHA.2384@.tk2msftngp13.phx.gbl...
> When I
>
>|||I mean, is there an index on the table which includes columns
(id_rating, dt_rating, id_user), but not Column summary ?
"Rafael Chemtob" wrote:

> ok, sorry for not being very detailed.
> 4 fields
> id_rating INT
> summary VARCHAR(500)
> dt_rating smalldatetime
> id_user INT
> these are the 4 fields. The record count that's returned is 11 rows.
> Hope that helps
> thanks
>
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:#45wbrTRFHA.2384@.tk2msftngp13.phx.gbl...
> When I
>
>|||I query the table using id_rating (which is the PK).
and this is consistent. I comment out the varchar field and i get the
results MUCH quicker.
rafael
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:456CEB9F-6A05-464C-9EE5-93F4FF0B7DB3@.microsoft.com...
> I mean, is there an index on the table which includes columns
> (id_rating, dt_rating, id_user), but not Column summary ?
>
> "Rafael Chemtob" wrote:
>
When I
can't|||As I asked above, one possible reason for this is if there's an index that
includes the columns (id_rating, dt_rating, id_user), but NOT the summary
column. If that were the case, the query processor could use the index alon
e
for the query without Summary, but would be forced to do a table scan when
you include summary.. Is there such an index?
"Rafael Chemtob" wrote:

> I query the table using id_rating (which is the PK).
> and this is consistent. I comment out the varchar field and i get the
> results MUCH quicker.
> rafael
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:456CEB9F-6A05-464C-9EE5-93F4FF0B7DB3@.microsoft.com...
> When I
> can't
>
>|||Hi Rafael,
For performance questions like these, it is very important to post all
relevant DDL (so including indexes, constraints, etc.) and the exact
query.
So just a wild guess for now: make sure you have a clustered index on
the table. If the table does not have a clustered index, and you delete
many rows, then querying the table can become very slow.
HTH,
Gert-Jan
Rafael Chemtob wrote:
> Hi,
> I have a query that selects 4 fields. One is of type Varchar(500). When
I
> execute the query, the response is about 9 seconds (very slow). When I
> comment out the varchar field, it returns in less than 1 second.
> It took me a while to figure out that it's not a missing index, i can't
> figure this one out.
> please advise.
> rafael