query question

I have shopping cart and the items are stored in a
StoreCart table. I'm trying to write a query that
returns the items in the cart in certain way but I don't
know how to do it. Say for example that I have the
following items:
Product Qty
-- --
Toy_1 3
Toy_2 1
Toy_3 2
I want to write a query that will return the item the
number of times that the Qty field has.
Product
--
Toy_1
Toy_1
Toy_1
Toy_2
Toy_3
Toy_3
How can I do this?
TIA,
Vic"Vic" <vduran@.specpro-inc.com> wrote in message
news:0e4201c53fcc$350cb270$a501280a@.phx.gbl...
>I have shopping cart and the items are stored in a
> StoreCart table. I'm trying to write a query that
> returns the items in the cart in certain way but I don't
> know how to do it. Say for example that I have the
> following items:
> Product Qty
> -- --
> Toy_1 3
> Toy_2 1
> Toy_3 2
> I want to write a query that will return the item the
> number of times that the Qty field has.
> Product
> --
> Toy_1
> Toy_1
> Toy_1
> Toy_2
> Toy_3
> Toy_3
>
create table N(i int primary key)
go
set nocount on
declare @.i int
set @.i = 0
while @.i < 10000
begin
insert into N(i) values (@.i)
set @.i = @.i + 1
end
go
create table cart(product varchar(50) not null, Qty int not null, primary
key (Product,qty))
insert into cart(product,qty)values('Toy_1',3)
insert into cart(product,qty)values('Toy_2',1)
insert into cart(product,qty)values('Toy_3',2)
go
select product
from
cart
join N on n.i < cart.qty
order by product
David

query question

Hi I have 3 tables and am wondering if there is a way to do this. I would
like a qerry that returns data for a data item but will return only the # of
records for the event and not the destination. For example if the query
returns data for Data ID 4
I would want to see as the results.
Data name event destination
report recieved office
report intransit frontdesk
Even though there are 3 destinations I would like to only list the first 2
and base the number of records returned on the number of events. Thanks.
table1 event
******************************************
*pri key Count * foriegn key Data ID * event *
* 1 * 3 * arrived *
* 2 * 4 * recieved *
* 3 * 4 * intransit *
******************************************
table2 data item
*****************************
* Pri key Data ID * Data name *
* 3 * email *
* 4 * report *
*****************************
table3- destination-note no prim key
**********************************
* Data ID * destination *
* 4 * office *
* 4 * frontdesk *
* 4 * mailroom *
**********************************
--
Paul G
Software engineer.Hi Paul
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful DDL
and example data in a usable format, also posting the expected output from
the data provided would be helpful e.g.
CREATE TABLE [event] ( [Count] int, [Data ID] int, [event] varchar(30) )
INSERT INTO [event] ( [Count], [Data ID], [event] )
SELECT 1, 3, 'arrived'
UNION ALL SELECT 2, 4, 'recieved'
UNION ALL SELECT 3, 4, 'intransit'
CREATE TABLE [data item] ( [Data ID] int, [Data name] varchar(30) )
INSERT INTO [data item] ( [Data ID], [Data name] )
SELECT 3, 'email'
UNION ALL SELECT 4, 'report'
CREATE TABLE destination ( [Data ID] int, destination varchar(30) )
INSERT INTO destination ( [Data ID], destination )
SELECT 4, 'office'
UNION ALL SELECT 4, 'frontdesk'
UNION ALL SELECT 4, 'mailroom'
There is no way to easily distinguish your destinations e.g
SELECT d.[Data name], e.[Event], f.destination
FROM [Data Item] d
JOIN [Event] e ON d.[Data Id] = e.[Data Id]
JOIN destination f ON f.[Data Id] = e.[Data Id]
ORDER BY e.[Count] DESC
Returns
Data name Event destination
-- -- --
report intransit office
report intransit frontdesk
report intransit mailroom
report recieved office
report recieved mailroom
report recieved frontdesk
(6 row(s) affected)
Limiting this to top 2
SELECT TOP 2 d.[Data name], e.[Event], f.destination
FROM [Data Item] d
JOIN [Event] e ON d.[Data Id] = e.[Data Id]
JOIN destination f ON f.[Data Id] = e.[Data Id]
ORDER BY e.[Count] DESC
Returns
Data name Event destination
-- -- --
report intransit office
report intransit frontdesk
(2 row(s) affected)
You could give destination alphabetical rank such as
SELECT f.[Data ID], f.destination,
( SELECT COUNT(*) FROM destination b WHERE f..destination <
b.destination )+1 AS RANK
FROM destination f
This may then allow you to pick different destinations by joining to the
event count (you may have to rank these to get similar number ranges.
SELECT TOP 2 d.[Data name], e.[Event], g.destination
FROM [Data Item] d
JOIN [Event] e ON d.[Data Id] = e.[Data Id]
JOIN ( SELECT f.[Data ID],
f.destination,
( SELECT COUNT(*) FROM destination b WHERE f.destination < b.destination )+1
AS RANK
FROM destination f
) g ON g.[Data Id] = e.[Data Id] AND e.[count] = g.rank
ORDER BY e.[Count] DESC
Data name Event destination
-- -- --
report intransit frontdesk
report recieved mailroom
(2 row(s) affected)
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:7FC06E5C-8E29-4F97-9268-F7E9A6878C03@.microsoft.com...
> Hi I have 3 tables and am wondering if there is a way to do this. I would
> like a qerry that returns data for a data item but will return only the #
> of
> records for the event and not the destination. For example if the query
> returns data for Data ID 4
> I would want to see as the results.
> Data name event destination
> report recieved office
> report intransit frontdesk
> Even though there are 3 destinations I would like to only list the first 2
> and base the number of records returned on the number of events. Thanks.
> table1 event
> ******************************************
> *pri key Count * foriegn key Data ID * event *
> * 1 * 3 * arrived
> *
> * 2 * 4 * recieved
> *
> * 3 * 4 * intransit
> *
> ******************************************
> table2 data item
> *****************************
> * Pri key Data ID * Data name *
> * 3 * email *
> * 4 * report *
> *****************************
> table3- destination-note no prim key
> **********************************
> * Data ID * destination *
> * 4 * office *
> * 4 * frontdesk *
> * 4 * mailroom *
> **********************************
>
> --
> Paul G
> Software engineer.|||hi thanks for the response. Does seem quite useful to use DDL, will do this
in the future. Unfortunately I will net be able to use a constant for the #
of records returned as the number of records returned for each data item will
not be the destinations but the number of events for each data item. Guess I
will probably use 2 queries the first one getting the # of events for each
data item and the second returning the data name, event and destination and
using the TOP or ROWCOUNT to limit the # of records returned. Thanks again,
Paul.
--
Paul G
Software engineer.
"John Bell" wrote:
> Hi Paul
> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful DDL
> and example data in a usable format, also posting the expected output from
> the data provided would be helpful e.g.
> CREATE TABLE [event] ( [Count] int, [Data ID] int, [event] varchar(30) )
> INSERT INTO [event] ( [Count], [Data ID], [event] )
> SELECT 1, 3, 'arrived'
> UNION ALL SELECT 2, 4, 'recieved'
> UNION ALL SELECT 3, 4, 'intransit'
> CREATE TABLE [data item] ( [Data ID] int, [Data name] varchar(30) )
> INSERT INTO [data item] ( [Data ID], [Data name] )
> SELECT 3, 'email'
> UNION ALL SELECT 4, 'report'
>
> CREATE TABLE destination ( [Data ID] int, destination varchar(30) )
> INSERT INTO destination ( [Data ID], destination )
> SELECT 4, 'office'
> UNION ALL SELECT 4, 'frontdesk'
> UNION ALL SELECT 4, 'mailroom'
>
> There is no way to easily distinguish your destinations e.g
> SELECT d.[Data name], e.[Event], f.destination
> FROM [Data Item] d
> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> JOIN destination f ON f.[Data Id] = e.[Data Id]
> ORDER BY e.[Count] DESC
> Returns
> Data name Event destination
> -- -- --
> report intransit office
> report intransit frontdesk
> report intransit mailroom
> report recieved office
> report recieved mailroom
> report recieved frontdesk
> (6 row(s) affected)
>
> Limiting this to top 2
> SELECT TOP 2 d.[Data name], e.[Event], f.destination
> FROM [Data Item] d
> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> JOIN destination f ON f.[Data Id] = e.[Data Id]
> ORDER BY e.[Count] DESC
> Returns
> Data name Event destination
> -- -- --
> report intransit office
> report intransit frontdesk
> (2 row(s) affected)
> You could give destination alphabetical rank such as
>
> SELECT f.[Data ID], f.destination,
> ( SELECT COUNT(*) FROM destination b WHERE f..destination <
> b.destination )+1 AS RANK
> FROM destination f
> This may then allow you to pick different destinations by joining to the
> event count (you may have to rank these to get similar number ranges.
> SELECT TOP 2 d.[Data name], e.[Event], g.destination
> FROM [Data Item] d
> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> JOIN ( SELECT f.[Data ID],
> f.destination,
> ( SELECT COUNT(*) FROM destination b WHERE f.destination < b.destination )+1
> AS RANK
> FROM destination f
> ) g ON g.[Data Id] = e.[Data Id] AND e.[count] = g.rank
> ORDER BY e.[Count] DESC
> Data name Event destination
> -- -- --
> report intransit frontdesk
> report recieved mailroom
> (2 row(s) affected)
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:7FC06E5C-8E29-4F97-9268-F7E9A6878C03@.microsoft.com...
> > Hi I have 3 tables and am wondering if there is a way to do this. I would
> > like a qerry that returns data for a data item but will return only the #
> > of
> > records for the event and not the destination. For example if the query
> > returns data for Data ID 4
> > I would want to see as the results.
> > Data name event destination
> > report recieved office
> > report intransit frontdesk
> >
> > Even though there are 3 destinations I would like to only list the first 2
> > and base the number of records returned on the number of events. Thanks.
> >
> > table1 event
> > ******************************************
> > *pri key Count * foriegn key Data ID * event *
> > * 1 * 3 * arrived
> > *
> >
> > * 2 * 4 * recieved
> > *
> >
> > * 3 * 4 * intransit
> > *
> >
> > ******************************************
> > table2 data item
> > *****************************
> > * Pri key Data ID * Data name *
> > * 3 * email *
> >
> > * 4 * report *
> >
> > *****************************
> > table3- destination-note no prim key
> > **********************************
> > * Data ID * destination *
> > * 4 * office *
> >
> > * 4 * frontdesk *
> > * 4 * mailroom *
> >
> > **********************************
> >
> >
> >
> > --
> > Paul G
> > Software engineer.
>
>|||Hi
SQL 2005 allows top to take a variable, but that may not be any use to
yourself. You may be able to use a having clause if you can formulate the
number of rows required and then use the ranking as the tested value.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:0881ACE2-979C-425A-B53B-3627D46A6A05@.microsoft.com...
> hi thanks for the response. Does seem quite useful to use DDL, will do
> this
> in the future. Unfortunately I will net be able to use a constant for the
> #
> of records returned as the number of records returned for each data item
> will
> not be the destinations but the number of events for each data item.
> Guess I
> will probably use 2 queries the first one getting the # of events for each
> data item and the second returning the data name, event and destination
> and
> using the TOP or ROWCOUNT to limit the # of records returned. Thanks
> again,
> Paul.
> --
> Paul G
> Software engineer.
>
> "John Bell" wrote:
>> Hi Paul
>> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful DDL
>> and example data in a usable format, also posting the expected output
>> from
>> the data provided would be helpful e.g.
>> CREATE TABLE [event] ( [Count] int, [Data ID] int, [event] varchar(30) )
>> INSERT INTO [event] ( [Count], [Data ID], [event] )
>> SELECT 1, 3, 'arrived'
>> UNION ALL SELECT 2, 4, 'recieved'
>> UNION ALL SELECT 3, 4, 'intransit'
>> CREATE TABLE [data item] ( [Data ID] int, [Data name] varchar(30) )
>> INSERT INTO [data item] ( [Data ID], [Data name] )
>> SELECT 3, 'email'
>> UNION ALL SELECT 4, 'report'
>>
>> CREATE TABLE destination ( [Data ID] int, destination varchar(30) )
>> INSERT INTO destination ( [Data ID], destination )
>> SELECT 4, 'office'
>> UNION ALL SELECT 4, 'frontdesk'
>> UNION ALL SELECT 4, 'mailroom'
>>
>> There is no way to easily distinguish your destinations e.g
>> SELECT d.[Data name], e.[Event], f.destination
>> FROM [Data Item] d
>> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
>> JOIN destination f ON f.[Data Id] = e.[Data Id]
>> ORDER BY e.[Count] DESC
>> Returns
>> Data name Event destination
>> -- -- --
>> report intransit office
>> report intransit frontdesk
>> report intransit mailroom
>> report recieved office
>> report recieved mailroom
>> report recieved frontdesk
>> (6 row(s) affected)
>>
>> Limiting this to top 2
>> SELECT TOP 2 d.[Data name], e.[Event], f.destination
>> FROM [Data Item] d
>> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
>> JOIN destination f ON f.[Data Id] = e.[Data Id]
>> ORDER BY e.[Count] DESC
>> Returns
>> Data name Event destination
>> -- -- --
>> report intransit office
>> report intransit frontdesk
>> (2 row(s) affected)
>> You could give destination alphabetical rank such as
>>
>> SELECT f.[Data ID], f.destination,
>> ( SELECT COUNT(*) FROM destination b WHERE f..destination <
>> b.destination )+1 AS RANK
>> FROM destination f
>> This may then allow you to pick different destinations by joining to the
>> event count (you may have to rank these to get similar number ranges.
>> SELECT TOP 2 d.[Data name], e.[Event], g.destination
>> FROM [Data Item] d
>> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
>> JOIN ( SELECT f.[Data ID],
>> f.destination,
>> ( SELECT COUNT(*) FROM destination b WHERE f.destination <
>> b.destination )+1
>> AS RANK
>> FROM destination f
>> ) g ON g.[Data Id] = e.[Data Id] AND e.[count] = g.rank
>> ORDER BY e.[Count] DESC
>> Data name Event destination
>> -- -- --
>> report intransit frontdesk
>> report recieved mailroom
>> (2 row(s) affected)
>> John
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:7FC06E5C-8E29-4F97-9268-F7E9A6878C03@.microsoft.com...
>> > Hi I have 3 tables and am wondering if there is a way to do this. I
>> > would
>> > like a qerry that returns data for a data item but will return only the
>> > #
>> > of
>> > records for the event and not the destination. For example if the
>> > query
>> > returns data for Data ID 4
>> > I would want to see as the results.
>> > Data name event destination
>> > report recieved office
>> > report intransit frontdesk
>> >
>> > Even though there are 3 destinations I would like to only list the
>> > first 2
>> > and base the number of records returned on the number of events.
>> > Thanks.
>> >
>> > table1 event
>> > ******************************************
>> > *pri key Count * foriegn key Data ID * event *
>> > * 1 * 3 * arrived
>> > *
>> >
>> > * 2 * 4 * recieved
>> > *
>> >
>> > * 3 * 4 * intransit
>> > *
>> >
>> > ******************************************
>> > table2 data item
>> > *****************************
>> > * Pri key Data ID * Data name *
>> > * 3 * email *
>> >
>> > * 4 * report *
>> >
>> > *****************************
>> > table3- destination-note no prim key
>> > **********************************
>> > * Data ID * destination *
>> > * 4 * office *
>> >
>> > * 4 * frontdesk *
>> > * 4 * mailroom *
>> >
>> > **********************************
>> >
>> >
>> >
>> > --
>> > Paul G
>> > Software engineer.
>>|||ok thanks for the additional information. I guess I could use the ROWCOUNT
which does take a variable. Hopefully we will upgrade to SQL 2005 but it
will be awhile.
--
Paul G
Software engineer.
"John Bell" wrote:
> Hi
> SQL 2005 allows top to take a variable, but that may not be any use to
> yourself. You may be able to use a having clause if you can formulate the
> number of rows required and then use the ranking as the tested value.
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:0881ACE2-979C-425A-B53B-3627D46A6A05@.microsoft.com...
> > hi thanks for the response. Does seem quite useful to use DDL, will do
> > this
> > in the future. Unfortunately I will net be able to use a constant for the
> > #
> > of records returned as the number of records returned for each data item
> > will
> > not be the destinations but the number of events for each data item.
> > Guess I
> > will probably use 2 queries the first one getting the # of events for each
> > data item and the second returning the data name, event and destination
> > and
> > using the TOP or ROWCOUNT to limit the # of records returned. Thanks
> > again,
> > Paul.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "John Bell" wrote:
> >
> >> Hi Paul
> >>
> >> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful DDL
> >> and example data in a usable format, also posting the expected output
> >> from
> >> the data provided would be helpful e.g.
> >> CREATE TABLE [event] ( [Count] int, [Data ID] int, [event] varchar(30) )
> >>
> >> INSERT INTO [event] ( [Count], [Data ID], [event] )
> >>
> >> SELECT 1, 3, 'arrived'
> >>
> >> UNION ALL SELECT 2, 4, 'recieved'
> >>
> >> UNION ALL SELECT 3, 4, 'intransit'
> >>
> >> CREATE TABLE [data item] ( [Data ID] int, [Data name] varchar(30) )
> >>
> >> INSERT INTO [data item] ( [Data ID], [Data name] )
> >>
> >> SELECT 3, 'email'
> >>
> >> UNION ALL SELECT 4, 'report'
> >>
> >>
> >> CREATE TABLE destination ( [Data ID] int, destination varchar(30) )
> >>
> >> INSERT INTO destination ( [Data ID], destination )
> >>
> >> SELECT 4, 'office'
> >>
> >> UNION ALL SELECT 4, 'frontdesk'
> >>
> >> UNION ALL SELECT 4, 'mailroom'
> >>
> >>
> >> There is no way to easily distinguish your destinations e.g
> >>
> >> SELECT d.[Data name], e.[Event], f.destination
> >>
> >> FROM [Data Item] d
> >>
> >> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> >>
> >> JOIN destination f ON f.[Data Id] = e.[Data Id]
> >>
> >> ORDER BY e.[Count] DESC
> >>
> >> Returns
> >>
> >> Data name Event destination
> >>
> >> -- -- --
> >>
> >> report intransit office
> >>
> >> report intransit frontdesk
> >>
> >> report intransit mailroom
> >>
> >> report recieved office
> >>
> >> report recieved mailroom
> >>
> >> report recieved frontdesk
> >>
> >> (6 row(s) affected)
> >>
> >>
> >>
> >> Limiting this to top 2
> >>
> >> SELECT TOP 2 d.[Data name], e.[Event], f.destination
> >>
> >> FROM [Data Item] d
> >>
> >> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> >>
> >> JOIN destination f ON f.[Data Id] = e.[Data Id]
> >>
> >> ORDER BY e.[Count] DESC
> >>
> >> Returns
> >>
> >> Data name Event destination
> >>
> >> -- -- --
> >>
> >> report intransit office
> >>
> >> report intransit frontdesk
> >>
> >> (2 row(s) affected)
> >>
> >> You could give destination alphabetical rank such as
> >>
> >>
> >>
> >> SELECT f.[Data ID], f.destination,
> >>
> >> ( SELECT COUNT(*) FROM destination b WHERE f..destination <
> >> b.destination )+1 AS RANK
> >>
> >> FROM destination f
> >>
> >> This may then allow you to pick different destinations by joining to the
> >> event count (you may have to rank these to get similar number ranges.
> >>
> >> SELECT TOP 2 d.[Data name], e.[Event], g.destination
> >>
> >> FROM [Data Item] d
> >>
> >> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> >>
> >> JOIN ( SELECT f.[Data ID],
> >>
> >> f.destination,
> >>
> >> ( SELECT COUNT(*) FROM destination b WHERE f.destination <
> >> b.destination )+1
> >> AS RANK
> >>
> >> FROM destination f
> >>
> >> ) g ON g.[Data Id] = e.[Data Id] AND e.[count] = g.rank
> >>
> >> ORDER BY e.[Count] DESC
> >>
> >> Data name Event destination
> >>
> >> -- -- --
> >>
> >> report intransit frontdesk
> >>
> >> report recieved mailroom
> >>
> >> (2 row(s) affected)
> >>
> >> John
> >>
> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> news:7FC06E5C-8E29-4F97-9268-F7E9A6878C03@.microsoft.com...
> >> > Hi I have 3 tables and am wondering if there is a way to do this. I
> >> > would
> >> > like a qerry that returns data for a data item but will return only the
> >> > #
> >> > of
> >> > records for the event and not the destination. For example if the
> >> > query
> >> > returns data for Data ID 4
> >> > I would want to see as the results.
> >> > Data name event destination
> >> > report recieved office
> >> > report intransit frontdesk
> >> >
> >> > Even though there are 3 destinations I would like to only list the
> >> > first 2
> >> > and base the number of records returned on the number of events.
> >> > Thanks.
> >> >
> >> > table1 event
> >> > ******************************************
> >> > *pri key Count * foriegn key Data ID * event *
> >> > * 1 * 3 * arrived
> >> > *
> >> >
> >> > * 2 * 4 * recieved
> >> > *
> >> >
> >> > * 3 * 4 * intransit
> >> > *
> >> >
> >> > ******************************************
> >> > table2 data item
> >> > *****************************
> >> > * Pri key Data ID * Data name *
> >> > * 3 * email *
> >> >
> >> > * 4 * report *
> >> >
> >> > *****************************
> >> > table3- destination-note no prim key
> >> > **********************************
> >> > * Data ID * destination *
> >> > * 4 * office *
> >> >
> >> > * 4 * frontdesk *
> >> > * 4 * mailroom *
> >> >
> >> > **********************************
> >> >
> >> >
> >> >
> >> > --
> >> > Paul G
> >> > Software engineer.
> >>
> >>
> >>
>
>

query question

Hi I have 3 tables and am wondering if there is a way to do this. I would
like a qerry that returns data for a data item but will return only the # of
records for the event and not the destination. For example if the query
returns data for Data ID 4
I would want to see as the results.
Data name event destination
report recieved office
report intransit frontdesk
Even though there are 3 destinations I would like to only list the first 2
and base the number of records returned on the number of events. Thanks.
table1 event
****************************************
**
*pri key Count * foriegn key Data ID * event *
* 1 * 3 * arrived *
* 2 * 4 * recieved *
* 3 * 4 * intransit *
****************************************
**
table2 data item
*****************************
* Pri key Data ID * Data name *
* 3 * email *
* 4 * report *
*****************************
table3- destination-note no prim key
**********************************
* Data ID * destination *
* 4 * office *
* 4 * frontdesk *
* 4 * mailroom *
**********************************
Paul G
Software engineer.Hi Paul
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful DDL
and example data in a usable format, also posting the expected output from
the data provided would be helpful e.g.
CREATE TABLE [event] ( [Count] int, [Data ID] int, [event] v
archar(30) )
INSERT INTO [event] ( [Count], [Data ID], [event] )
SELECT 1, 3, 'arrived'
UNION ALL SELECT 2, 4, 'recieved'
UNION ALL SELECT 3, 4, 'intransit'
CREATE TABLE [data item] ( [Data ID] int, [Data name] varchar(30
) )
INSERT INTO [data item] ( [Data ID], [Data name] )
SELECT 3, 'email'
UNION ALL SELECT 4, 'report'
CREATE TABLE destination ( [Data ID] int, destination varchar(30) )
INSERT INTO destination ( [Data ID], destination )
SELECT 4, 'office'
UNION ALL SELECT 4, 'frontdesk'
UNION ALL SELECT 4, 'mailroom'
There is no way to easily distinguish your destinations e.g
SELECT d.[Data name], e.[Event], f.destination
FROM [Data Item] d
JOIN [Event] e ON d.[Data Id] = e.[Data Id]
JOIN destination f ON f.[Data Id] = e.[Data Id]
ORDER BY e.[Count] DESC
Returns
Data name Event destination
-- -- --
--
report intransit office
report intransit frontdesk
report intransit mailroom
report recieved office
report recieved mailroom
report recieved frontdesk
(6 row(s) affected)
Limiting this to top 2
SELECT TOP 2 d.[Data name], e.[Event], f.destination
FROM [Data Item] d
JOIN [Event] e ON d.[Data Id] = e.[Data Id]
JOIN destination f ON f.[Data Id] = e.[Data Id]
ORDER BY e.[Count] DESC
Returns
Data name Event destination
-- -- --
--
report intransit office
report intransit frontdesk
(2 row(s) affected)
You could give destination alphabetical rank such as
SELECT f.[Data ID], f.destination,
( SELECT COUNT(*) FROM destination b WHERE f..destination <
b.destination )+1 AS RANK
FROM destination f
This may then allow you to pick different destinations by joining to the
event count (you may have to rank these to get similar number ranges.
SELECT TOP 2 d.[Data name], e.[Event], g.destination
FROM [Data Item] d
JOIN [Event] e ON d.[Data Id] = e.[Data Id]
JOIN ( SELECT f.[Data ID],
f.destination,
( SELECT COUNT(*) FROM destination b WHERE f.destination < b.destination )+1
AS RANK
FROM destination f
) g ON g.[Data Id] = e.[Data Id] AND e.[count] = g.rank
ORDER BY e.[Count] DESC
Data name Event destination
-- -- --
--
report intransit frontdesk
report recieved mailroom
(2 row(s) affected)
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:7FC06E5C-8E29-4F97-9268-F7E9A6878C03@.microsoft.com...
> Hi I have 3 tables and am wondering if there is a way to do this. I would
> like a qerry that returns data for a data item but will return only the #
> of
> records for the event and not the destination. For example if the query
> returns data for Data ID 4
> I would want to see as the results.
> Data name event destination
> report recieved office
> report intransit frontdesk
> Even though there are 3 destinations I would like to only list the first 2
> and base the number of records returned on the number of events. Thanks.
> table1 event
> ****************************************
**
> *pri key Count * foriegn key Data ID * event *
> * 1 * 3 * arrived
> *
> * 2 * 4 * recieved
> *
> * 3 * 4 * intransit
> *
> ****************************************
**
> table2 data item
> *****************************
> * Pri key Data ID * Data name *
> * 3 * email *
> * 4 * report *
> *****************************
> table3- destination-note no prim key
> **********************************
> * Data ID * destination *
> * 4 * office *
> * 4 * frontdesk *
> * 4 * mailroom *
> **********************************
>
> --
> Paul G
> Software engineer.|||hi thanks for the response. Does seem quite useful to use DDL, will do this
in the future. Unfortunately I will net be able to use a constant for the #
of records returned as the number of records returned for each data item wil
l
not be the destinations but the number of events for each data item. Guess
I
will probably use 2 queries the first one getting the # of events for each
data item and the second returning the data name, event and destination and
using the TOP or ROWCOUNT to limit the # of records returned. Thanks again,
Paul.
--
Paul G
Software engineer.
"John Bell" wrote:

> Hi Paul
> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful DDL
> and example data in a usable format, also posting the expected output from
> the data provided would be helpful e.g.
> CREATE TABLE [event] ( [Count] int, [Data ID] int, [event]
varchar(30) )
> INSERT INTO [event] ( [Count], [Data ID], [event] )
> SELECT 1, 3, 'arrived'
> UNION ALL SELECT 2, 4, 'recieved'
> UNION ALL SELECT 3, 4, 'intransit'
> CREATE TABLE [data item] ( [Data ID] int, [Data name] varchar(
30) )
> INSERT INTO [data item] ( [Data ID], [Data name] )
> SELECT 3, 'email'
> UNION ALL SELECT 4, 'report'
>
> CREATE TABLE destination ( [Data ID] int, destination varchar(30) )
> INSERT INTO destination ( [Data ID], destination )
> SELECT 4, 'office'
> UNION ALL SELECT 4, 'frontdesk'
> UNION ALL SELECT 4, 'mailroom'
>
> There is no way to easily distinguish your destinations e.g
> SELECT d.[Data name], e.[Event], f.destination
> FROM [Data Item] d
> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> JOIN destination f ON f.[Data Id] = e.[Data Id]
> ORDER BY e.[Count] DESC
> Returns
> Data name Event destination
> -- -- --
--
> report intransit office
> report intransit frontdesk
> report intransit mailroom
> report recieved office
> report recieved mailroom
> report recieved frontdesk
> (6 row(s) affected)
>
> Limiting this to top 2
> SELECT TOP 2 d.[Data name], e.[Event], f.destination
> FROM [Data Item] d
> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> JOIN destination f ON f.[Data Id] = e.[Data Id]
> ORDER BY e.[Count] DESC
> Returns
> Data name Event destination
> -- -- --
--
> report intransit office
> report intransit frontdesk
> (2 row(s) affected)
> You could give destination alphabetical rank such as
>
> SELECT f.[Data ID], f.destination,
> ( SELECT COUNT(*) FROM destination b WHERE f..destination <
> b.destination )+1 AS RANK
> FROM destination f
> This may then allow you to pick different destinations by joining to the
> event count (you may have to rank these to get similar number ranges.
> SELECT TOP 2 d.[Data name], e.[Event], g.destination
> FROM [Data Item] d
> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> JOIN ( SELECT f.[Data ID],
> f.destination,
> ( SELECT COUNT(*) FROM destination b WHERE f.destination < b.destination )
+1
> AS RANK
> FROM destination f
> ) g ON g.[Data Id] = e.[Data Id] AND e.[count] = g.rank
> ORDER BY e.[Count] DESC
> Data name Event destination
> -- -- --
--
> report intransit frontdesk
> report recieved mailroom
> (2 row(s) affected)
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:7FC06E5C-8E29-4F97-9268-F7E9A6878C03@.microsoft.com...
>
>|||Hi
SQL 2005 allows top to take a variable, but that may not be any use to
yourself. You may be able to use a having clause if you can formulate the
number of rows required and then use the ranking as the tested value.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:0881ACE2-979C-425A-B53B-3627D46A6A05@.microsoft.com...[vbcol=seagreen]
> hi thanks for the response. Does seem quite useful to use DDL, will do
> this
> in the future. Unfortunately I will net be able to use a constant for the
> #
> of records returned as the number of records returned for each data item
> will
> not be the destinations but the number of events for each data item.
> Guess I
> will probably use 2 queries the first one getting the # of events for each
> data item and the second returning the data name, event and destination
> and
> using the TOP or ROWCOUNT to limit the # of records returned. Thanks
> again,
> Paul.
> --
> Paul G
> Software engineer.
>
> "John Bell" wrote:
>|||ok thanks for the additional information. I guess I could use the ROWCOUNT
which does take a variable. Hopefully we will upgrade to SQL 2005 but it
will be awhile.
--
Paul G
Software engineer.
"John Bell" wrote:

> Hi
> SQL 2005 allows top to take a variable, but that may not be any use to
> yourself. You may be able to use a having clause if you can formulate the
> number of rows required and then use the ranking as the tested value.
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:0881ACE2-979C-425A-B53B-3627D46A6A05@.microsoft.com...
>
>

Query problem

I am using SQL SERVER 2005 FT-enable database,
repleat same query it returns the results are expect,but try five times later,
it returns record is no data,
Has anyone seen this issue ?
THANKS!!
It is difficult to understand your question.
It would help us better assist you if you could include table DDL, query
strategy used so far, sample data in the form of INSERT statements, and an
illustration of the desired results. (For help with that refer to:
http://www.aspfaq.com/5006 and to
http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )
The less 'set up' work we have to do, the more likely you are going to have
folks tackle your problem and help you. Without this effort from you, we are
just playing guessing games.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"akira-888" <akira888@.discussions.microsoft.com> wrote in message
news:E8DEDB72-A958-455A-8458-85FF94B0B7AD@.microsoft.com...
>I am using SQL SERVER 2005 FT-enable database,
> repleat same query it returns the results are expect,but try five times
> later,
> it returns record is no data,
> Has anyone seen this issue ?
> THANKS!!
>
|||Sorry my poor english
my qruestion is
some query like :
select * from test where contains(description ,'二次金改')
return 343 records--ok
try again same query
return 343 records--ok
but try 4 times later the same query,
return 0 record --stranger
and continue try is alway return 0 recode
Next day try the same query ,
return return 343 records--ok
but same situation appear again-try 4 times later the same query
return 0 record
"Arnie Rowland" wrote:

> It is difficult to understand your question.
> It would help us better assist you if you could include table DDL, query
> strategy used so far, sample data in the form of INSERT statements, and an
> illustration of the desired results. (For help with that refer to:
> http://www.aspfaq.com/5006 and to
> http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )
>
> The less 'set up' work we have to do, the more likely you are going to have
> folks tackle your problem and help you. Without this effort from you, we are
> just playing guessing games.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "akira-888" <akira888@.discussions.microsoft.com> wrote in message
> news:E8DEDB72-A958-455A-8458-85FF94B0B7AD@.microsoft.com...
>
>

query problem

The subselect in the query returns 897 rows, but when it
is included in the
where clause of an update statement, the whole table is
returned and
updated. Why? And how can I change this to only update
the 897 rows that
the subselect is returning?
UPDATE Item
SET sz_amount = 1
WHERE item_id IN (SELECT Item.item_id FROM dbo.Item INNER
JOIN dbo.ItemExtended ON dbo.Item.item_id =
dbo.ItemExtended.item_id INNER JOIN dbo.IRISubcategory ON
dbo.IRISubcategory.iri_subcategory_id =
dbo.ItemExtended.iri_subcategory_id INNER JOIN
dbo.IRICategory ON dbo.IRISubcategory.iri_category_id
= dbo.IRICategory.iri_category_id WHERE
(dbo.Item.sz_amount = 10) AND
(dbo.IRICategory.code = '1820'))
I'm not sure but try this an tell me if it works.
UPDATE Item
SET sz_amount = 1
FROM dbo.Item INNER
JOIN dbo.ItemExtended ON dbo.Item.item_id = dbo.ItemExtended.item_id
JOIN dbo.IRISubcategory ON dbo.IRISubcategory.iri_subcategory_id =
dbo.ItemExtended.iri_subcategory_id
JOIN dbo.IRICategory ON dbo.IRISubcategory.iri_category_id =
dbo.IRICategory.iri_category_id
WHERE dbo.Item.sz_amount = 10 AND
dbo.IRICategory.code = '1820'
--Buddy
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:253d01c427cf$38e3e730$a501280a@.phx.gbl...
> The subselect in the query returns 897 rows, but when it
> is included in the
> where clause of an update statement, the whole table is
> returned and
> updated. Why? And how can I change this to only update
> the 897 rows that
> the subselect is returning?
>
> UPDATE Item
> SET sz_amount = 1
> WHERE item_id IN (SELECT Item.item_id FROM dbo.Item INNER
> JOIN dbo.ItemExtended ON dbo.Item.item_id =
> dbo.ItemExtended.item_id INNER JOIN dbo.IRISubcategory ON
> dbo.IRISubcategory.iri_subcategory_id =
> dbo.ItemExtended.iri_subcategory_id INNER JOIN
> dbo.IRICategory ON dbo.IRISubcategory.iri_category_id
> = dbo.IRICategory.iri_category_id WHERE
> (dbo.Item.sz_amount = 10) AND
> (dbo.IRICategory.code = '1820'))
>
>
>
|||Hi Jamie,
From your descriptions, I know your subselect query will work fine and get
the correct result alone. However it goes wrong when you make it as
subselect.
Would you please have a try on Buddy Ackerman's query and tell me whether
it works. If it doesn't, would you please show me your DDL and I could
reproduce it on my machine
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

query problem

The subselect in the query returns 897 rows, but when it
is included in the
where clause of an update statement, the whole table is
returned and
updated. Why? And how can I change this to only update
the 897 rows that
the subselect is returning?
UPDATE Item
SET sz_amount = 1
WHERE item_id IN (SELECT Item.item_id FROM dbo.Item INNER
JOIN dbo.ItemExtended ON dbo.Item.item_id = dbo.ItemExtended.item_id INNER JOIN dbo.IRISubcategory ON
dbo.IRISubcategory.iri_subcategory_id = dbo.ItemExtended.iri_subcategory_id INNER JOIN
dbo.IRICategory ON dbo.IRISubcategory.iri_category_id
= dbo.IRICategory.iri_category_id WHERE
(dbo.Item.sz_amount = 10) AND
(dbo.IRICategory.code = '1820'))I'm not sure but try this an tell me if it works.
UPDATE Item
SET sz_amount = 1
FROM dbo.Item INNER
JOIN dbo.ItemExtended ON dbo.Item.item_id = dbo.ItemExtended.item_id
JOIN dbo.IRISubcategory ON dbo.IRISubcategory.iri_subcategory_id =dbo.ItemExtended.iri_subcategory_id
JOIN dbo.IRICategory ON dbo.IRISubcategory.iri_category_id =dbo.IRICategory.iri_category_id
WHERE dbo.Item.sz_amount = 10 AND
dbo.IRICategory.code = '1820'
--Buddy
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:253d01c427cf$38e3e730$a501280a@.phx.gbl...
> The subselect in the query returns 897 rows, but when it
> is included in the
> where clause of an update statement, the whole table is
> returned and
> updated. Why? And how can I change this to only update
> the 897 rows that
> the subselect is returning?
>
> UPDATE Item
> SET sz_amount = 1
> WHERE item_id IN (SELECT Item.item_id FROM dbo.Item INNER
> JOIN dbo.ItemExtended ON dbo.Item.item_id => dbo.ItemExtended.item_id INNER JOIN dbo.IRISubcategory ON
> dbo.IRISubcategory.iri_subcategory_id => dbo.ItemExtended.iri_subcategory_id INNER JOIN
> dbo.IRICategory ON dbo.IRISubcategory.iri_category_id
> = dbo.IRICategory.iri_category_id WHERE
> (dbo.Item.sz_amount = 10) AND
> (dbo.IRICategory.code = '1820'))
>
>
>|||Hi Jamie,
From your descriptions, I know your subselect query will work fine and get
the correct result alone. However it goes wrong when you make it as
subselect.
Would you please have a try on Buddy Ackerman's query and tell me whether
it works. If it doesn't, would you please show me your DDL and I could
reproduce it on my machine
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

Query problem

I am using SQL SERVER 2005 FT-enable database,
repleat same query it returns the results are expect,but try five times later,
it returns record is no data,
Has anyone seen this issue ?
THANKS!!It is difficult to understand your question.
It would help us better assist you if you could include table DDL, query
strategy used so far, sample data in the form of INSERT statements, and an
illustration of the desired results. (For help with that refer to:
http://www.aspfaq.com/5006 and to
http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )
The less 'set up' work we have to do, the more likely you are going to have
folks tackle your problem and help you. Without this effort from you, we are
just playing guessing games.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"akira-888" <akira888@.discussions.microsoft.com> wrote in message
news:E8DEDB72-A958-455A-8458-85FF94B0B7AD@.microsoft.com...
>I am using SQL SERVER 2005 FT-enable database,
> repleat same query it returns the results are expect,but try five times
> later,
> it returns record is no data,
> Has anyone seen this issue ?
> THANKS!!
>|||Sorry my poor english
my qruestion is
some query like :
select * from test where contains(description ,'äº?次é'æ?¹')
return 343 records--ok
try again same query
return 343 records--ok
but try 4 times later the same query,
return 0 record --stranger
and continue try is alway return 0 recode
Next day try the same query ,
return return 343 records--ok
but same situation appear again-try 4 times later the same query
return 0 record
"Arnie Rowland" wrote:
> It is difficult to understand your question.
> It would help us better assist you if you could include table DDL, query
> strategy used so far, sample data in the form of INSERT statements, and an
> illustration of the desired results. (For help with that refer to:
> http://www.aspfaq.com/5006 and to
> http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )
>
> The less 'set up' work we have to do, the more likely you are going to have
> folks tackle your problem and help you. Without this effort from you, we are
> just playing guessing games.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "akira-888" <akira888@.discussions.microsoft.com> wrote in message
> news:E8DEDB72-A958-455A-8458-85FF94B0B7AD@.microsoft.com...
> >I am using SQL SERVER 2005 FT-enable database,
> >
> > repleat same query it returns the results are expect,but try five times
> > later,
> >
> > it returns record is no data,
> >
> > Has anyone seen this issue ?
> >
> > THANKS!!
> >
>
>

Query problem

I am using SQL SERVER 2005 FT-enable database,
repleat same query it returns the results are expect,but try five times late
r,
it returns record is no data,
Has anyone seen this issue ?
THANKS!!It is difficult to understand your question.
It would help us better assist you if you could include table DDL, query
strategy used so far, sample data in the form of INSERT statements, and an
illustration of the desired results. (For help with that refer to:
http://www.aspfaq.com/5006 and to
http://classicasp.aspfaq.com/genera...br />
red.html )
The less 'set up' work we have to do, the more likely you are going to have
folks tackle your problem and help you. Without this effort from you, we are
just playing guessing games.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"akira-888" <akira888@.discussions.microsoft.com> wrote in message
news:E8DEDB72-A958-455A-8458-85FF94B0B7AD@.microsoft.com...
>I am using SQL SERVER 2005 FT-enable database,
> repleat same query it returns the results are expect,but try five times
> later,
> it returns record is no data,
> Has anyone seen this issue ?
> THANKS!!
>|||Sorry my poor english
my qruestion is
some query like :
select * from test where contains(description ,'二次金改')
return 343 records--ok
try again same query
return 343 records--ok
but try 4 times later the same query,
return 0 record --stranger
and continue try is alway return 0 recode
Next day try the same query ,
return return 343 records--ok
but same situation appear again-try 4 times later the same query
return 0 record
"Arnie Rowland" wrote:

> It is difficult to understand your question.
> It would help us better assist you if you could include table DDL, query
> strategy used so far, sample data in the form of INSERT statements, and an
> illustration of the desired results. (For help with that refer to:
> http://www.aspfaq.com/5006 and to
> http://classicasp.aspfaq.com/genera... />
wered.html )
>
> The less 'set up' work we have to do, the more likely you are going to hav
e
> folks tackle your problem and help you. Without this effort from you, we a
re
> just playing guessing games.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e
> top yourself.
> - H. Norman Schwarzkopf
>
> "akira-888" <akira888@.discussions.microsoft.com> wrote in message
> news:E8DEDB72-A958-455A-8458-85FF94B0B7AD@.microsoft.com...
>
>

query problem

The subselect in the query returns 897 rows, but when it
is included in the
where clause of an update statement, the whole table is
returned and
updated. Why? And how can I change this to only update
the 897 rows that
the subselect is returning?
UPDATE Item
SET sz_amount = 1
WHERE item_id IN (SELECT Item.item_id FROM dbo.Item INNER
JOIN dbo.ItemExtended ON dbo.Item.item_id =
dbo.ItemExtended.item_id INNER JOIN dbo.IRISubcategory ON
dbo.IRISubcategory.iri_subcategory_id =
dbo.ItemExtended.iri_subcategory_id INNER JOIN
dbo.IRICategory ON dbo.IRISubcategory.iri_category_id
= dbo.IRICategory.iri_category_id WHERE
(dbo.Item.sz_amount = 10) AND
(dbo.IRICategory.code = '1820'))I'm not sure but try this an tell me if it works.
UPDATE Item
SET sz_amount = 1
FROM dbo.Item INNER
JOIN dbo.ItemExtended ON dbo.Item.item_id = dbo.ItemExtended.item_id
JOIN dbo.IRISubcategory ON dbo.IRISubcategory.iri_subcategory_id =
dbo.ItemExtended.iri_subcategory_id
JOIN dbo.IRICategory ON dbo.IRISubcategory.iri_category_id =
dbo.IRICategory.iri_category_id
WHERE dbo.Item.sz_amount = 10 AND
dbo.IRICategory.code = '1820'
--Buddy
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:253d01c427cf$38e3e730$a501280a@.phx.gbl...
> The subselect in the query returns 897 rows, but when it
> is included in the
> where clause of an update statement, the whole table is
> returned and
> updated. Why? And how can I change this to only update
> the 897 rows that
> the subselect is returning?
>
> UPDATE Item
> SET sz_amount = 1
> WHERE item_id IN (SELECT Item.item_id FROM dbo.Item INNER
> JOIN dbo.ItemExtended ON dbo.Item.item_id =
> dbo.ItemExtended.item_id INNER JOIN dbo.IRISubcategory ON
> dbo.IRISubcategory.iri_subcategory_id =
> dbo.ItemExtended.iri_subcategory_id INNER JOIN
> dbo.IRICategory ON dbo.IRISubcategory.iri_category_id
> = dbo.IRICategory.iri_category_id WHERE
> (dbo.Item.sz_amount = 10) AND
> (dbo.IRICategory.code = '1820'))
>
>
>|||Hi Jamie,
From your descriptions, I know your subselect query will work fine and get
the correct result alone. However it goes wrong when you make it as
subselect.
Would you please have a try on Buddy Ackerman's query and tell me whether
it works. If it doesn't, would you please show me your DDL and I could
reproduce it on my machine
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.sql

query performance

2.7 Million rows in accounting_tran table
3.1 Million rows in charge table
Following query only returns 333 rows.
SELECT c.accounting_tran_id
FROM accounting_tran at
JOIN charge c on at.accounting_tran_id = c.accounting_tran_id
WHERE at.lctn_id = 'VA437'
and at.acct_tran_status_typ <> 'e'
and c.fiscal_period = 200504
Problem: query cost over 50. Can't get around table scan or index scan on
charge table. Accounting_Tran table using index s,
Charge Table Indexes:
ix_fp fiscal_period
ix_test1 accounting_tran_id, fiscal_period
ix_test2 charge_id, accounting_tran_id, fiscal_period
Any ideas on how to improve performance?
Thanks in advance,
ChrisCan you paste in here the query plan of the query to see where the
bottleneck is (if there is any)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Chris" <Chris@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F7F01F8C-5153-4A34-8195-BA3C8207CCCF@.microsoft.com...
> 2.7 Million rows in accounting_tran table
> 3.1 Million rows in charge table
> Following query only returns 333 rows.
> SELECT c.accounting_tran_id
> FROM accounting_tran at
> JOIN charge c on at.accounting_tran_id = c.accounting_tran_id
> WHERE at.lctn_id = 'VA437'
> and at.acct_tran_status_typ <> 'e'
> and c.fiscal_period = 200504
> Problem: query cost over 50. Can't get around table scan or index scan on
> charge table. Accounting_Tran table using index s,
> Charge Table Indexes:
> ix_fp fiscal_period
> ix_test1 accounting_tran_id, fiscal_period
> ix_test2 charge_id, accounting_tran_id, fiscal_period
> Any ideas on how to improve performance?
> Thanks in advance,
> Chris
>|||DDL, please, including which tables these indices are on...
"Chris" wrote:

> 2.7 Million rows in accounting_tran table
> 3.1 Million rows in charge table
> Following query only returns 333 rows.
> SELECT c.accounting_tran_id
> FROM accounting_tran at
> JOIN charge c on at.accounting_tran_id = c.accounting_tran_id
> WHERE at.lctn_id = 'VA437'
> and at.acct_tran_status_typ <> 'e'
> and c.fiscal_period = 200504
> Problem: query cost over 50. Can't get around table scan or index scan on
> charge table. Accounting_Tran table using index s,
> Charge Table Indexes:
> ix_fp fiscal_period
> ix_test1 accounting_tran_id, fiscal_period
> ix_test2 charge_id, accounting_tran_id, fiscal_period
> Any ideas on how to improve performance?
> Thanks in advance,
> Chris
>|||You can use SET SHOWPLAN option to get a text version of the query plan.
Rick Sawtell
MCT, MCSD, MCDBA
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:Ot$0dcLVFHA.584@.TK2MSFTNGP15.phx.gbl...
> Can you paste in here the query plan of the query to see where the
> bottleneck is (if there is any)
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Chris" <Chris@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:F7F01F8C-5153-4A34-8195-BA3C8207CCCF@.microsoft.com...
on
>|||StmtText StmtId NodeId Parent PhysicalO
p
LogicalOp Argument DefinedValues
EstimateRows EstimateIO EstimateCPU
AvgRowSize TotalSubtreeCost OutputList Warnings Type
Parallel EstimateExecutions
-- -- -- --
-- -- --
-- -- --
-- -- -- --
-- -- -- --
SET STATISTICS PROFILE ON 76 1 0 NULL
NULL 1 NULL
NULL NULL NULL
NULL NULL NULL NULL SETSTATON
0 NULL
(1 row(s) affected)
StmtText
StmtId
NodeId Parent PhysicalOp LogicalOp
Argument
DefinedValues
EstimateRows EstimateIO
EstimateCPU AvgRowSize TotalSubtreeCost OutputList
Warnings Type
Parallel EstimateExecutions
----
----
---
-- -- -- --
--
----
---
----
-- -- --
-- --
---- --
-- -- --
SELECT c.accounting_tran_id
FROM accounting_tran at
JOIN charge c on at.accounting_tran_id = c.accounting_tran_id
WHERE at.lctn_id = 'VA437'
and at.acct_tran_status_typ <> 'e'
and c.fiscal_period = 200504 77 1 0
NULL NULL 1
NULL
7533.1636 NULL NULL
NULL 51.250423 NULL
NULL SELECT 0 NULL
|--Parallelism(Gather Streams)
77
3 1 Parallelism Gather Streams
NULL
NULL
7533.1636 0.0
4.2613383E-2 15 51.249668
[c].[accounting_tran_id] NULL PLAN_ROW
-1 1.0
|--Hash Match(Inner Join,
HASH:([at].[accounting_tran_id])=([c].[accounting_tran_id]))
77 4 3 Hash
Match Inner Join
HASH:([at].[accounting_tran_id])=([c].[accounting_tran_id])
NULL
7533.1636 0.0 0.22781526
15 51.207058 [c].[accounting_tran_id]
NULL PLAN_ROW -1
1.0
|--Bitmap(HASH:([at].[accounting_tran_id]),
DEFINE:([Bitmap1002]))
77 5 4 Bitmap
Bitmap Create HASH:([at].[accounting_tran_id])
[Bitmap1002] 6729.1655
0.0 4.5124404E-2 121 41.125393
[at].[accounting_tran_id] NULL
PLAN_ROW -1 1.0
| |--Parallelism(Repartition Streams, PARTITION
COLUMNS:([at].[accounting_tran_id]))
77 6 5 Parallelism
Repartition Streams PARTITION COLUMNS:([at].[accounting_tran_id])
NULL
6729.1655 0.0
4.5124404E-2 121 41.125393
[at].[accounting_tran_id] NULL PLAN_ROW
-1 1.0
| |--Filter(WHERE:([at].[acct_tran_status_typ]<'e' OR
[at].[acct_tran_status_typ]>'e'))
77
7 6 Filter Filter
WHERE:([at].[acct_tran_status_typ]<'e' OR
[at].[acct_tran_status_typ]>'e') NULL
6729.1655
0.0 2.9812064E-3 121 41.080269
[at].[accounting_tran_id] NULL
PLAN_ROW -1 1.0
| |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([RetailNightly].[dbo].[Accounting_Tran] AS [at]))
77 8 7 Bookmark Lookup Bookmark
Lookup BOOKMARK:([Bmk1000]),
OBJECT:([RetailNightly].[dbo].[Accounting_Tran] AS [at])
[at].[accounting_tran_id], [at].[acct_tran_status_typ]
6775.4692 41.056252 3.726508E-3
121 41.077286 [at].[accounting_tran_id],
[at].[acct_tran_status_typ] NULL PLAN_ROW -1
1.0
| |--Index
S(OBJECT:([RetailNightly].[dbo].[Accounting_Tran].[Lctn_Id] AS [at]),
SEEK:([at].[LCTN_ID]='VA437') ORDERED FORWARD)
77 10 8 Index
S Index S
OBJECT:([RetailNightly].[dbo].[Accounting_Tran].[Lctn_Id] AS [at]),
SEEK:([at].[LCTN_ID]='VA437') ORDERED FORWARD [Bmk1000]
6775.4692 0.01353462
3.7759214E-3 46 1.7310541E-2 [Bmk1000]
NULL PLAN_ROW
-1 1.0
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([c].[accounting_tran_id]), WHERE:(PROBE([Bitmap1002])=TRUE))
77 18 4 Parallelism
Repartition Streams PARTITION COLUMNS:([c].[accounting_tran_id]),
WHERE:(PROBE([Bitmap1002])=TRUE) NULL
74968.383 0.0
0.21370938 23 9.8538456
[c].[accounting_tran_id] NULL PLAN_ROW
-1 1.0
|--Index
Scan(OBJECT:([RetailNightly].[dbo].[Charge].[IX_test2] AS [c]),
WHERE:([c].[fiscal_period]=200504))
77 19
18 Index Scan Index Scan
OBJECT:([RetailNightly].[dbo].[Charge].[IX_test2] AS [c]),
WHERE:([c].[fiscal_period]=200504) [c].[fiscal_period],
[c].[accounting_tran_id] 74968.383 7.1294303
1.7479717 23 8.8774023
[c].[fiscal_period], [c].[accounting_tran_id] NULL PLAN_ROW
-1 1.0
(10 row(s) affected)
StmtText StmtId NodeId Parent PhysicalOp
LogicalOp Argument DefinedValues
EstimateRows EstimateIO EstimateCPU
AvgRowSize TotalSubtreeCost OutputList Warnings Type
Parallel EstimateExecutions
-- -- -- --
-- -- --
-- -- --
-- -- -- --
-- -- -- --
SET STATISTICS PROFILE OFF 78 1 0 NULL
NULL 1 NULL
NULL NULL NULL
NULL NULL NULL NULL SETSTATON
0 NULL
(1 row(s) affected)
"Jens Sü?meyer" wrote:

> Can you paste in here the query plan of the query to see where the
> bottleneck is (if there is any)
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Chris" <Chris@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:F7F01F8C-5153-4A34-8195-BA3C8207CCCF@.microsoft.com...
>
>|||Chris,
That is a remarkable query plan you posted. Did you actually get that
one with the indexes you described below? By the way, which index is
clustered (if any)?
I am surprised, because scanning index ix_test1 looks more favorable
than ix_test2 (assuming both are nonclustered).
The query benefits from a nonclustered index on Charge(fiscal_period,
accounting_tran_id) or a clustered index on Charge(accounting_tran_id)
You could also test if rewriting the query as below makes any
difference:
SELECT accounting_tran_id
FROM accounting_tran at
WHERE at.lctn_id = 'VA437'
AND at.acct_tran_status_typ <> 'e'
AND EXISTS (
SELECT 1
FROM charge c
WHERE c.accounting_tran_id = at.accounting_tran_id
AND c.fiscal_period = 200504
)
Hope this helps,
Gert-Jan
Chris wrote:
> 2.7 Million rows in accounting_tran table
> 3.1 Million rows in charge table
> Following query only returns 333 rows.
> SELECT c.accounting_tran_id
> FROM accounting_tran at
> JOIN charge c on at.accounting_tran_id = c.accounting_tran_id
> WHERE at.lctn_id = 'VA437'
> and at.acct_tran_status_typ <> 'e'
> and c.fiscal_period = 200504
> Problem: query cost over 50. Can't get around table scan or index scan on
> charge table. Accounting_Tran table using index s,
> Charge Table Indexes:
> ix_fp fiscal_period
> ix_test1 accounting_tran_id, fiscal_period
> ix_test2 charge_id, accounting_tran_id, fiscal_period
> Any ideas on how to improve performance?
> Thanks in advance,
> Chris

Query Optimization

Hi,
I have a DB-based application, which has a UDF like this:
CREATE FUNCTION fn_concat(@.A varchar(255), @.B varchar(255))
RETURNS varchar(255)
AS BEGIN
RETURN coalesce(@.A,@.B)
END
Using SQL-Server 2000 I execute the following statement:
SELECT DISTINCT
A.a, dbo.sp_concat(A.b, A.c) as x
FROM
A LEFT OUTER JOIN B ON
A.id = B.id
Since the statement is only selecting columns from table "A", it is
being optimized so that the join with table "B" is not being executed.
Because table "B" is quite large, this saves quite some execution-time.
When this statement is being executed on a SQL-Server 2005 the join is
being executed, resulting in a much longer execution-time. This seems to
be because of the UDF, because if this is being left out, the optimizer
eliminates the processing of table "B".
Background: I have a view, which consists of a lot of joins of several
tables, and I dynamically build the select-clause of the statement in my
application. Because the optimizer only processes the tables that are
actually being used in the select-statement this is an easy way to not
deal with the joins in the application itself.
But why is the use of the function changing the behavior of the 2005
optimizer?
Henning Eiben
busitec GmbH
Consultant
e-mail: eiben@.busitec.de
+49 (251) 13335-0 Tel
+49 (251) 13335-35 Fax
Rudolf-Diesel-Strae 59
48157 Mnster
www.busitec.de
Sitz der Gesellschaft: Mnster
HR B 55 75 - Amtsgericht Mnster
USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
Geschftsfhrer: Simon Bwer, Henning Eiben, Stefan Khn, Martin Saalmann
... ERROR: CPU not found.
>> CREATE FUNCTION fn_concat(@.A varchar(255), @.B varchar(255))[vbcol=seagreen]
This seems like a typical case of UDF abuse. Is there a real need for an UDF
for such simple cases? Why not simply write your SQL statement as:
SELECT A.a, COALESCE( A.b, A.c )
FROM A LEFT JOIN B
ON A.id = B.id ;
[vbcol=seagreen]
The optimizer in SQL 2000 and SQL 2005 are different and this could be one
of the processing differences due to an internal optimization routine called
constant folding, which is pretty common in most modern DBMSs.
Anith
|||Anith Sen wrote:

> This seems like a typical case of UDF abuse. Is there a real need for an UDF
> for such simple cases? Why not simply write your SQL statement as:
Well, I simplified the UDF ... in my actual application the UDF is a
little more complex.

> SELECT A.a, COALESCE( A.b, A.c )
> FROM A LEFT JOIN B
> ON A.id = B.id ;
>
> The optimizer in SQL 2000 and SQL 2005 are different and this could be one
> of the processing differences due to an internal optimization routine called
> constant folding, which is pretty common in most modern DBMSs.
Well - in the meantime I figured that "WITH SCHEMABINDING" in the UDF
does the magic.
Henning Eiben
busitec GmbH
Consultant
e-mail: eiben@.busitec.de
+49 (251) 13335-0 Tel
+49 (251) 13335-35 Fax
Rudolf-Diesel-Strae 59
48157 Mnster
www.busitec.de
Sitz der Gesellschaft: Mnster
HR B 55 75 - Amtsgericht Mnster
USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
Geschftsfhrer: Simon Bwer, Henning Eiben, Stefan Khn, Martin Saalmann
... Gentlemen: Start your debuggers...

Query Optimization

Hi,
I have a DB-based application, which has a UDF like this:
CREATE FUNCTION fn_concat(@.A varchar(255), @.B varchar(255))
RETURNS varchar(255)
AS BEGIN
RETURN coalesce(@.A,@.B)
END
Using SQL-Server 2000 I execute the following statement:
SELECT DISTINCT
A.a, dbo.sp_concat(A.b, A.c) as x
FROM
A LEFT OUTER JOIN B ON
A.id = B.id
Since the statement is only selecting columns from table "A", it is
being optimized so that the join with table "B" is not being executed.
Because table "B" is quite large, this saves quite some execution-time.
When this statement is being executed on a SQL-Server 2005 the join is
being executed, resulting in a much longer execution-time. This seems to
be because of the UDF, because if this is being left out, the optimizer
eliminates the processing of table "B".
Background: I have a view, which consists of a lot of joins of several
tables, and I dynamically build the select-clause of the statement in my
application. Because the optimizer only processes the tables that are
actually being used in the select-statement this is an easy way to not
deal with the joins in the application itself.
But why is the use of the function changing the behavior of the 2005
optimizer?
Henning Eiben
busitec GmbH
Consultant
e-mail: eiben@.busitec.de
+49 (251) 13335-0 Tel
+49 (251) 13335-35 Fax
Rudolf-Diesel-Strae 59
48157 Mnster
www.busitec.de
Sitz der Gesellschaft: Mnster
HR B 55 75 - Amtsgericht Mnster
USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
Geschftsfhrer: Simon Bwer, Henning Eiben, Stefan Khn, Martin Saalmann
... There are 10 kinds of people. Those who know binary code, and those
who don't.I'm going to step out a limb here...
Does this not return the same result?
SELECT DISTINCT
A.a, dbo.sp_concat(A.b, A.c) as x
FROM
A
Why bother with the join if this is what you're doing?
Cheers,
Jason Lepack
On Aug 1, 5:35 am, Henning Eiben <ei...@.busitec.de> wrote:
> Hi,
> I have a DB-based application, which has a UDF like this:
> CREATE FUNCTION fn_concat(@.A varchar(255), @.B varchar(255))
> RETURNS varchar(255)
> AS BEGIN
> RETURN coalesce(@.A,@.B)
> END
> Using SQL-Server 2000 I execute the following statement:
> SELECT DISTINCT
> A.a, dbo.sp_concat(A.b, A.c) as x
> FROM
> A LEFT OUTER JOIN B ON
> A.id =3D B.id
> Since the statement is only selecting columns from table "A", it is
> being optimized so that the join with table "B" is not being executed.
> Because table "B" is quite large, this saves quite some execution-time.
> When this statement is being executed on a SQL-Server 2005 the join is
> being executed, resulting in a much longer execution-time. This seems to
> be because of the UDF, because if this is being left out, the optimizer
> eliminates the processing of table "B".
> Background: I have a view, which consists of a lot of joins of several
> tables, and I dynamically build the select-clause of the statement in my
> application. Because the optimizer only processes the tables that are
> actually being used in the select-statement this is an easy way to not
> deal with the joins in the application itself.
> But why is the use of the function changing the behavior of the 2005
> optimizer?
> --
> Henning Eiben
> busitec GmbH
> Consultant
> e-mail: ei...@.busitec.de
> +49 (251) 13335-0 Tel
> +49 (251) 13335-35 Fax
> Rudolf-Diesel-Stra=DFe 59
> 48157 M=FCnsterwww.busitec.de
> Sitz der Gesellschaft: M=FCnster
> HR B 55 75 - Amtsgericht M=FCnster
> USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
> Gesch=E4ftsf=FChrer: Simon B=F6wer, Henning Eiben, Stefan K=FChn, Martin =
Saalmann
> --
> ... There are 10 kinds of people. Those who know binary code, and those
> who don't.|||Jason Lepack wrote:
> I'm going to step out a limb here...
> Does this not return the same result?
> SELECT DISTINCT
> A.a, dbo.sp_concat(A.b, A.c) as x
> FROM
> A
> Why bother with the join if this is what you're doing?
>
actually I have a view
CREATE VIEW dbo.SomeView AS
SELECT A.*, B.*
FROM
A LEFT OUTER JOIN B ON
A.id = B.id
and my SQL-Statement looks like this:
SELECT DISTINCT
A.a, dbo.sp_concat(A.b, A.c) as x
FROM
dbo.SomeView
This way I can create the select-clause in my app, and since I'm using
the view in the from-clause, I don't have to deal with the join ...
Henning Eiben
busitec GmbH
Consultant
e-mail: eiben@.busitec.de
+49 (251) 13335-0 Tel
+49 (251) 13335-35 Fax
Rudolf-Diesel-Strae 59
48157 Mnster
www.busitec.de
Sitz der Gesellschaft: Mnster
HR B 55 75 - Amtsgericht Mnster
USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
Geschftsfhrer: Simon Bwer, Henning Eiben, Stefan Khn, Martin Saalmann
... If it wasn't for C, we would be using BASI, PASAL and OBOL!|||But the join is still there...
I have no idea what you are trying to do. You call your function
concat, which I think means concatenate, but then all you do is select
the first of the two values that isn't null, that isn't concatenation.
If you want assistance then you have to give information about what
you are actually trying to do.
Cheers,
Jason Lepack
On Aug 1, 10:01 am, Henning Eiben <ei...@.busitec.de> wrote:
> Jason Lepack wrote:
>
>
> actually I have a view
> CREATE VIEW dbo.SomeView AS
> SELECT A.*, B.*
> FROM
> A LEFT OUTER JOIN B ON
> A.id =3D B.id
> and my SQL-Statement looks like this:
> SELECT DISTINCT
> A.a, dbo.sp_concat(A.b, A.c) as x
> FROM
> dbo.SomeView
> This way I can create the select-clause in my app, and since I'm using
> the view in the from-clause, I don't have to deal with the join ...
> --
> Henning Eiben
> busitec GmbH
> Consultant
> e-mail: ei...@.busitec.de
> +49 (251) 13335-0 Tel
> +49 (251) 13335-35 Fax
> Rudolf-Diesel-Stra=DFe 59
> 48157 M=FCnsterwww.busitec.de
> Sitz der Gesellschaft: M=FCnster
> HR B 55 75 - Amtsgericht M=FCnster
> USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
> Gesch=E4ftsf=FChrer: Simon B=F6wer, Henning Eiben, Stefan K=FChn, Martin =
Saalmann
> --
> ... If it wasn't for C, we would be using BASI, PASAL and OBOL!|||Henning,
Interesting case. Add WITH SCHEMABINDING to your UDF's definition, and
all thy troubles are solved :-)
Maybe in some weird twisted way, the optimizer thinks it cannot rule out
the use of table B if it is unknown whether the UDF accesses B.
Gert-Jan
Henning Eiben wrote:
> Hi,
> I have a DB-based application, which has a UDF like this:
> CREATE FUNCTION fn_concat(@.A varchar(255), @.B varchar(255))
> RETURNS varchar(255)
> AS BEGIN
> RETURN coalesce(@.A,@.B)
> END
> Using SQL-Server 2000 I execute the following statement:
> SELECT DISTINCT
> A.a, dbo.sp_concat(A.b, A.c) as x
> FROM
> A LEFT OUTER JOIN B ON
> A.id = B.id
> Since the statement is only selecting columns from table "A", it is
> being optimized so that the join with table "B" is not being executed.
> Because table "B" is quite large, this saves quite some execution-time.
> When this statement is being executed on a SQL-Server 2005 the join is
> being executed, resulting in a much longer execution-time. This seems to
> be because of the UDF, because if this is being left out, the optimizer
> eliminates the processing of table "B".
> Background: I have a view, which consists of a lot of joins of several
> tables, and I dynamically build the select-clause of the statement in my
> application. Because the optimizer only processes the tables that are
> actually being used in the select-statement this is an easy way to not
> deal with the joins in the application itself.
> But why is the use of the function changing the behavior of the 2005
> optimizer?
> --
> Henning Eiben
> busitec GmbH
> Consultant
> e-mail: eiben@.busitec.de
> +49 (251) 13335-0 Tel
> +49 (251) 13335-35 Fax
> Rudolf-Diesel-Strae 59
> 48157 Mnster
> www.busitec.de
> Sitz der Gesellschaft: Mnster
> HR B 55 75 - Amtsgericht Mnster
> USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
> Geschftsfhrer: Simon Bwer, Henning Eiben, Stefan Khn, Martin Saalmann
> --
> ... There are 10 kinds of people. Those who know binary code, and those
> who don't.|||Gert-Jan Strik wrote:

> Henning,
> Interesting case. Add WITH SCHEMABINDING to your UDF's definition, and
> all thy troubles are solved :-)
Wow! That did it!

> Maybe in some weird twisted way, the optimizer thinks it cannot rule out
> the use of table B if it is unknown whether the UDF accesses B.
Seems that SQL2005 is more cautious than SQL2000
Henning Eiben
busitec GmbH
Consultant
e-mail: eiben@.busitec.de
+49 (251) 13335-0 Tel
+49 (251) 13335-35 Fax
Rudolf-Diesel-Strae 59
48157 Mnster
www.busitec.de
Sitz der Gesellschaft: Mnster
HR B 55 75 - Amtsgericht Mnster
USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
Geschftsfhrer: Simon Bwer, Henning Eiben, Stefan Khn, Martin Saalmann
... If it wasn't for C, we would be using BASI, PASAL and OBOL!

Query Optimization

Hi,
I have a DB-based application, which has a UDF like this:
CREATE FUNCTION fn_concat(@.A varchar(255), @.B varchar(255))
RETURNS varchar(255)
AS BEGIN
RETURN coalesce(@.A,@.B)
END
Using SQL-Server 2000 I execute the following statement:
SELECT DISTINCT
A.a, dbo.sp_concat(A.b, A.c) as x
FROM
A LEFT OUTER JOIN B ON
A.id = B.id
Since the statement is only selecting columns from table "A", it is
being optimized so that the join with table "B" is not being executed.
Because table "B" is quite large, this saves quite some execution-time.
When this statement is being executed on a SQL-Server 2005 the join is
being executed, resulting in a much longer execution-time. This seems to
be because of the UDF, because if this is being left out, the optimizer
eliminates the processing of table "B".
Background: I have a view, which consists of a lot of joins of several
tables, and I dynamically build the select-clause of the statement in my
application. Because the optimizer only processes the tables that are
actually being used in the select-statement this is an easy way to not
deal with the joins in the application itself.
But why is the use of the function changing the behavior of the 2005
optimizer?
--
Henning Eiben
busitec GmbH
Consultant
e-mail: eiben@.busitec.de
+49 (251) 13335-0 Tel
+49 (251) 13335-35 Fax
Rudolf-Diesel-Straße 59
48157 Münster
www.busitec.de
Sitz der Gesellschaft: Münster
HR B 55 75 - Amtsgericht Münster
USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
Geschäftsführer: Simon Böwer, Henning Eiben, Stefan Kühn, Martin Saalmann
--
... There are 10 kinds of people. Those who know binary code, and those
who don't.I'm going to step out a limb here...
Does this not return the same result?
SELECT DISTINCT
A.a, dbo.sp_concat(A.b, A.c) as x
FROM
A
Why bother with the join if this is what you're doing?
Cheers,
Jason Lepack
On Aug 1, 5:35 am, Henning Eiben <ei...@.busitec.de> wrote:
> Hi,
> I have a DB-based application, which has a UDF like this:
> CREATE FUNCTION fn_concat(@.A varchar(255), @.B varchar(255))
> RETURNS varchar(255)
> AS BEGIN
> RETURN coalesce(@.A,@.B)
> END
> Using SQL-Server 2000 I execute the following statement:
> SELECT DISTINCT
> A.a, dbo.sp_concat(A.b, A.c) as x
> FROM
> A LEFT OUTER JOIN B ON
> A.id =3D B.id
> Since the statement is only selecting columns from table "A", it is
> being optimized so that the join with table "B" is not being executed.
> Because table "B" is quite large, this saves quite some execution-time.
> When this statement is being executed on a SQL-Server 2005 the join is
> being executed, resulting in a much longer execution-time. This seems to
> be because of the UDF, because if this is being left out, the optimizer
> eliminates the processing of table "B".
> Background: I have a view, which consists of a lot of joins of several
> tables, and I dynamically build the select-clause of the statement in my
> application. Because the optimizer only processes the tables that are
> actually being used in the select-statement this is an easy way to not
> deal with the joins in the application itself.
> But why is the use of the function changing the behavior of the 2005
> optimizer?
> --
> Henning Eiben
> busitec GmbH
> Consultant
> e-mail: ei...@.busitec.de
> +49 (251) 13335-0 Tel
> +49 (251) 13335-35 Fax
> Rudolf-Diesel-Stra=DFe 59
> 48157 M=FCnsterwww.busitec.de
> Sitz der Gesellschaft: M=FCnster
> HR B 55 75 - Amtsgericht M=FCnster
> USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
> Gesch=E4ftsf=FChrer: Simon B=F6wer, Henning Eiben, Stefan K=FChn, Martin =Saalmann
> --
> ... There are 10 kinds of people. Those who know binary code, and those
> who don't.|||Jason Lepack wrote:
> I'm going to step out a limb here...
> Does this not return the same result?
> SELECT DISTINCT
> A.a, dbo.sp_concat(A.b, A.c) as x
> FROM
> A
> Why bother with the join if this is what you're doing?
>
actually I have a view
CREATE VIEW dbo.SomeView AS
SELECT A.*, B.*
FROM
A LEFT OUTER JOIN B ON
A.id = B.id
and my SQL-Statement looks like this:
SELECT DISTINCT
A.a, dbo.sp_concat(A.b, A.c) as x
FROM
dbo.SomeView
This way I can create the select-clause in my app, and since I'm using
the view in the from-clause, I don't have to deal with the join ...
--
Henning Eiben
busitec GmbH
Consultant
e-mail: eiben@.busitec.de
+49 (251) 13335-0 Tel
+49 (251) 13335-35 Fax
Rudolf-Diesel-Straße 59
48157 Münster
www.busitec.de
Sitz der Gesellschaft: Münster
HR B 55 75 - Amtsgericht Münster
USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
Geschäftsführer: Simon Böwer, Henning Eiben, Stefan Kühn, Martin Saalmann
... If it wasn't for C, we would be using BASI, PASAL and OBOL!|||But the join is still there...
I have no idea what you are trying to do. You call your function
concat, which I think means concatenate, but then all you do is select
the first of the two values that isn't null, that isn't concatenation.
If you want assistance then you have to give information about what
you are actually trying to do.
Cheers,
Jason Lepack
On Aug 1, 10:01 am, Henning Eiben <ei...@.busitec.de> wrote:
> Jason Lepack wrote:
> > I'm going to step out a limb here...
> > Does this not return the same result?
> > SELECT DISTINCT
> > A.a, dbo.sp_concat(A.b, A.c) as x
> > FROM
> > A
> > Why bother with the join if this is what you're doing?
> actually I have a view
> CREATE VIEW dbo.SomeView AS
> SELECT A.*, B.*
> FROM
> A LEFT OUTER JOIN B ON
> A.id =3D B.id
> and my SQL-Statement looks like this:
> SELECT DISTINCT
> A.a, dbo.sp_concat(A.b, A.c) as x
> FROM
> dbo.SomeView
> This way I can create the select-clause in my app, and since I'm using
> the view in the from-clause, I don't have to deal with the join ...
> --
> Henning Eiben
> busitec GmbH
> Consultant
> e-mail: ei...@.busitec.de
> +49 (251) 13335-0 Tel
> +49 (251) 13335-35 Fax
> Rudolf-Diesel-Stra=DFe 59
> 48157 M=FCnsterwww.busitec.de
> Sitz der Gesellschaft: M=FCnster
> HR B 55 75 - Amtsgericht M=FCnster
> USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
> Gesch=E4ftsf=FChrer: Simon B=F6wer, Henning Eiben, Stefan K=FChn, Martin =Saalmann
> --
> ... If it wasn't for C, we would be using BASI, PASAL and OBOL!|||Henning,
Interesting case. Add WITH SCHEMABINDING to your UDF's definition, and
all thy troubles are solved :-)
Maybe in some weird twisted way, the optimizer thinks it cannot rule out
the use of table B if it is unknown whether the UDF accesses B.
Gert-Jan
Henning Eiben wrote:
> Hi,
> I have a DB-based application, which has a UDF like this:
> CREATE FUNCTION fn_concat(@.A varchar(255), @.B varchar(255))
> RETURNS varchar(255)
> AS BEGIN
> RETURN coalesce(@.A,@.B)
> END
> Using SQL-Server 2000 I execute the following statement:
> SELECT DISTINCT
> A.a, dbo.sp_concat(A.b, A.c) as x
> FROM
> A LEFT OUTER JOIN B ON
> A.id = B.id
> Since the statement is only selecting columns from table "A", it is
> being optimized so that the join with table "B" is not being executed.
> Because table "B" is quite large, this saves quite some execution-time.
> When this statement is being executed on a SQL-Server 2005 the join is
> being executed, resulting in a much longer execution-time. This seems to
> be because of the UDF, because if this is being left out, the optimizer
> eliminates the processing of table "B".
> Background: I have a view, which consists of a lot of joins of several
> tables, and I dynamically build the select-clause of the statement in my
> application. Because the optimizer only processes the tables that are
> actually being used in the select-statement this is an easy way to not
> deal with the joins in the application itself.
> But why is the use of the function changing the behavior of the 2005
> optimizer?
> --
> Henning Eiben
> busitec GmbH
> Consultant
> e-mail: eiben@.busitec.de
> +49 (251) 13335-0 Tel
> +49 (251) 13335-35 Fax
> Rudolf-Diesel-Straße 59
> 48157 Münster
> www.busitec.de
> Sitz der Gesellschaft: Münster
> HR B 55 75 - Amtsgericht Münster
> USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
> Geschäftsführer: Simon Böwer, Henning Eiben, Stefan Kühn, Martin Saalmann
> --
> ... There are 10 kinds of people. Those who know binary code, and those
> who don't.|||Gert-Jan Strik wrote:
> Henning,
> Interesting case. Add WITH SCHEMABINDING to your UDF's definition, and
> all thy troubles are solved :-)
Wow! That did it!
> Maybe in some weird twisted way, the optimizer thinks it cannot rule out
> the use of table B if it is unknown whether the UDF accesses B.
Seems that SQL2005 is more cautious than SQL2000 :)
--
Henning Eiben
busitec GmbH
Consultant
e-mail: eiben@.busitec.de
+49 (251) 13335-0 Tel
+49 (251) 13335-35 Fax
Rudolf-Diesel-Straße 59
48157 Münster
www.busitec.de
Sitz der Gesellschaft: Münster
HR B 55 75 - Amtsgericht Münster
USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
Geschäftsführer: Simon Böwer, Henning Eiben, Stefan Kühn, Martin Saalmann
... If it wasn't for C, we would be using BASI, PASAL and OBOL!

Query of query

Hi,

just a question: I created a simple query (it contains a UNION) which returns tree colomuns. Since, I have to use the results of this query on another query and I prefer to avoid to use dirty ways like scripting, I was wondering it is possible to perform a select "on the fly" on the results of previous select: a kinf of "select from select"

To give an example I have this query which returns:
Q1: colA,colB,colC

Soon I run Q1, I would like to run over Q2 which is:
select * from Q1

Note: Q1 is not a table and even a view: it's just a select

Thanks if you would like to give me a trick.

DomySELECT
t.col1,
t.col2,
s.col2,
s.col3
FROM
table t inner join
(SELECT
col1,
col2,
col3
FROM
another_table) s on t.col1 = s.col1|||To make the answer more straight:

Your query Q2 would be like

SELECT * FROM (<YourSelectStatement>) Q1|||jora's is the most efficient method of solving this in terms of code. If you actually need the results of Q1 twice ("Soon I run Q1, I would like to run over Q2") and the query is processor intensive, then run it once and store the results in a temporary table or table variable. Then you can quickly join it in further statements as often as you want.

blindman|||jora's code answers some other question that has not been asked, I believe, while DB's answer is exactly what the original posting was after.

I don't get bm's comments, - are you the judge of the forum? Or your word should always be last, even if it's wrong? Any point to that?

And speaking about efficiency, DB's suggestion is actually more efficient, hands down.|||If you use the results from the first query regularly, would it not make more sense to create a view and then query the view?|||I'd say it would, and I'd even go for a function, just in case :)|||You would just want to make sure that you add an index on the table for the rows you're selecting against in your view, and then you can index your view.|||it's normally not my thing to criticize other peoples posts, but while we're at it here it goes. Sure, my join was not asked, but i didn't read about views, temporary tables and functions as well. Moreover DomyFerraro asked for a select statement on the fly.|||"SELECT * FROM (<YourSelectStatement> ) Q1"???

What the heck does that contribute?! Hey, I can make it even more efficient, djabarov: how about just <YourSelectStatement>?

Or if (as I suspect) DB gets paid by the amount of code he writes, this would be more lucrative:
"SELECT * FROM (SELECT * FROM (SELECT * FROM (<YourSelectStatement> ) Q1) Q2) Q3"

DB's solution can't be more efficient than Jora's, because it is exactly the same as Jora's, just less informative.

Jora's is the best solution for on-the-fly processing. Mine is more efficient if the dataset has to be used multiple times. A view may be usefull if the join is used in multiple procedures, though it might be less efficient because it would not be pre-compiled.

djabarov, do you think about your posts, or does your head just fall onto the keyboard while you are napping?

blindman|||ROTFLMFAO!!

blindman, you da man!|||bm, have some bananas, you're coming up with severe case of verbal diarrhea :D|||Ouch!|||Blindman, did you understand DomyFerraro's problem? See his original question, and read loud and clear:

He has a query Q1, which is a union query. he wants to use this query on-the-fly, as it would be SELECT * FROM Q1. He does not want your view, or a (temporary) table.

I gave him just this solution. So, open up your eyes!|||Originally posted by Paul Young
ROTFLMFAO!!

blindman, you da man!

IDNUWYATA!|||Originally posted by DoktorBlue
He does not want your view, or a (temporary) table.


I was the one who made the comment regarding the view. I simply meant it as an alternative, but not necessarily a "solution", and something to research if curious.|||Originally posted by DoktorBlue
IDNUWYATA!

OISTUYJF!|||Originally posted by Paul Young
OISTUYJF!

Great, this guru stuff!|||Originally posted by Seppuku
I was the one who made the comment regarding the view. I simply meant it as an alternative, but not necessarily a "solution", and something to research if curious.

Apperently only the "correct" answer may be posted and differing views or opinions are not tollerated.|||That appears to be the rule that you and bm are imposing here.|||You crack me up, DoktorBlue!
Your "solution" is meaningless, and wouldn't solve any problem EVER posted on this forum. I'll try to make this simple for you:

There is NO syntactical difference between your solution...
SELECT * FROM (select from A union select from B) Q1
...and a straight union query...
select from A union select from B
NOTHING is gained by wrapping the union statement in a subquery.

If you can't see this, and can't figure out that DomyFerrao gains NOTHING from it, then there really is no hope for you. Go take a course in hardware maintenance or something. DUH.

As far as me not understanding the problem, didn't you make yourself look foolish enough on mohan1976's posted question (http://dbforums.com/t901820.html) when you told me my posting "must be mistaken from another thread", that I was "exposing yourself incapable understanding the problem", and to "Contribute to the thread, or be quiet!" AND THEN MOHAN USED MY SOLUTION INSTEAD OF YOURS!

It is not my goal to make you look foolish. That's not why I post here, I have better things to do with my time; but I will respond to your more ridiculous comments. You seem to think I'm gunning for you, but in reality it is you who keeps shooting yourself in the foot by attacking every comment I post.

Talking to you is like trying to teach a chimp how to tie shoelaces when you know it is never going to put on a pair of sneakers anyway.

blindman|||Cool off, bm, no need for Zoo analogies here, it's just a forum, not a battle field :)

I think that if you look at the original question which you quoted yourself, - you might see that an attemp to SELECT * FROM (select * from A union select * from B) Q1 may not necessarily be so straight forward. I don't believe that DomyFerraro just wanted to select from a sub-query for the heck of it. Don't you think? Try to leave some room for the possibility that DomyFerraro simply left out a presence of WHERE clause that may require the inner query to be left within the parenthesis rather than be join with the original table as jora suggested.

Other than that, why can't we all be more constructive while trying to help each other, rather than master our wit in insulting our "opponents"?

Can it be done? At all? Without recommending to increase the dosage of certain medication or referencing WWW.DICTIONARY.COM??

People are here not to see us being ugly, but to seek help. And if one happens to know more than the other, - at the end everybody wins.

Can we all try, just once?

Thanks in advance :)|||"I don't believe that DomyFerraro just wanted to select from a sub-query for the heck of it. Don't you think? "

I absolutely agree, and that is what Jora's solution explained; how to embed the results of the UNION query in a more complex statement.

Hey! We agree! Friends now?|||I am all for it! Don't exclude DoktorBlue! He is a good guy, I happened to get to know him a little more via private email, - he has A LOT to offer, I mean it, much more than I can dream of.|||Originally posted by rdjabarov
That appears to be the rule that you and bm are imposing here.

If you had taken the time to look over my prior posts, rather than the past few threads, you would realize how utterly stupid your comment was.

I am more than happy to drop all this, but don't expect me to sit back and take crap off people with out a response. I did not start this pissing contest.

Most problems posted here have many potential solutions. The ironic thing is that in all this debate DomyFerraro has not posted back asking for more help or suggested which solution best fit his problem.|||Originally posted by blindman
"I don't believe that DomyFerraro just wanted to select from a sub-query for the heck of it. Don't you think? "

I absolutely agree, and that is what Jora's solution explained; how to embed the results of the UNION query in a more complex statement.

Blindman: I'm glad that you finally got the point, thanks to rdjabarov. I assumed everybody, including you, te be familiar with the fact, that a SELECT statement may be more complex than just SELECT * FROM x. To make the principle more clear to DomyFerraro (where are you?), I gave a straight example without any extras. You called it with many words meaningless, however, it does show in its basic form how to select from a query. Regarding to Mohan's thread: yes, he is using your solution, which isn't giving him, what he has specified. As long as he's glad, I'm glad, too.

Paul: I guess, you already found out by yourself, that this isn't about the one-and-only path to Rome, but about blindman's almost structural misunderstandings.|||Hello buddies,

first let me thank you for so big support you gave me. I had the change to verify all the suggestions which I like and here the solution which fits better for my needs (already tested):

select * from
(
select * from T1
UNION
select * from T2
)

Believe, behind the two nested and root queries there is something more complexed, but the idea it's enough for me since, I have no problem about performance.

Open to any comments.

Thanks|||Sorry, I forgot to put the alias so:

select * from
(
select * from T1
UNION
select * from T2
) Q1

and here the draft of real query (just fyi):

Select * from
(
(
select
[Fatture Prodotti uscita].IDProdottiUscita,
cast (((cast((datediff(d, [Fatture Dati].Data, [Fatture Dati].Data_finito)) as decimal )) / (datediff(d, [Fatture Dati].Data, [Turni di lavorazione operazione].Data_turno))*100) as int) as ds1,
0 ds2
from
[Fatture Dati],
[Fatture Prodotti uscita],
[Elenco Disegni],
[Operazioni per disegno],
[Turni di lavorazione operazione]
where
[Fatture Dati].IDFattura = [Fatture Prodotti uscita].IDFattura
and [Fatture Prodotti uscita].IDProdottiUscita = [Elenco Disegni].IDProdottiUscita
and [Elenco Disegni].IDDisegno = [Operazioni per disegno].IDDisegno
and [Operazioni per disegno].IDOperazione = [Turni di lavorazione operazione].IDOperazione
and [Operazioni per disegno].cc_pln ='999'
and (left([Fatture Prodotti uscita].IDProdottiUscita,1) = '5')
and datediff (d, '01/01/2003' , [Fatture Dati].data ) > 0
)
UNION
(
select
[Fatture Prodotti uscita].IDProdottiUscita,
0 as ds1,
cast (((cast((datediff(d, [Fatture Dati].Data, [Fatture Dati].Data_grezzo)) as decimal )) / (datediff(d, [Fatture Dati].Data, [Turni di lavorazione operazione].Data_turno))*100) as int) as ds2
from
[Fatture Dati],
[Fatture Prodotti uscita],
[Elenco Disegni],
[Operazioni per disegno],
[Turni di lavorazione operazione]
where
[Fatture Dati].IDFattura = [Fatture Prodotti uscita].IDFattura
and [Fatture Prodotti uscita].IDProdottiUscita = [Elenco Disegni].IDProdottiUscita
and [Elenco Disegni].IDDisegno = [Operazioni per disegno].IDDisegno
and [Operazioni per disegno].IDOperazione = [Turni di lavorazione operazione].IDOperazione
and [Operazioni per disegno].cc_pln ='129'
and (left([Fatture Prodotti uscita].IDProdottiUscita,1) = '5')
and datediff (d, '01/01/2003' , [Fatture Dati].data ) > 0
)
) Q1|||Originally posted by DomyFerraro
select * from
(
select * from T1
UNION
select * from T2
) Q1


Your table and field names sound like italo music ...:)

Blindman's point was, that you don't need the outer SELECT frame in it's basic form, if you don't use any other SELECT options like a specific SELECT clause, GROUP BYs, ORDER BYs, or using your query Q1 as an entity of joining.|||I see Blindman's point, we should also consider I need to group the results of "nested" query, so the first solution, which is more pratical, for me is readble and easy to maintain.
At this time, because I have no performance problem all around, yours sounds good.

Thanks for sure to Blindman too.

ps: Yes: it's italian as well me