Query Question

I have a question that I will illustrate with the script below. As you can
see there two tables, airlines and routes. Each airline has a customer_rank
that indicates the preferred airline of the customer ie 1 is most preferred.
Each route has a name, cost and duration.
What I would like to do is write a query that returns one row for each route
with the columns : airline, cost and duration. I would like the airline
column to be based on the preferred carrier, irrespective of the cost and
duration.
Can someone please stop my head spinning?
Thanks, Tad
CREATE TABLE [dbo].[Airlines] (
[Airline] [char] (10) PRIMARY KEY,
[Customer_Rank] [tinyint] NOT NULL
)
CREATE TABLE [dbo].[Routes] (
[ID] [int] IDENTITY (1, 1) PRIMARY KEY,
[Airline] [char] (10) NOT NULL REFERENCES Airlines(Airline),
[Route] [char] (10) NOT NULL ,
[Cost] [numeric](18, 0) NOT NULL ,
[Duration] [numeric](18, 0) NOT NULL
)
INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
VALUES('BOAC',3)
INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
VALUES('TWA',2)
INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
VALUES('United',1)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
VALUES('BOAC', 'A2B', 150, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
VALUES('TWA', 'A2C', 200, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
VALUES('BOAC', 'B2C', 200, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
VALUES('United', 'A2B', 200, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
VALUES('United', 'D2E', 300, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
VALUES('BOAC', 'B2D', 300, 3)first of all you want to know which airline is the preferred carrier
for each route, so you have to inner join the two tables and find the
airline with the lowest (if highest substitute min by max) rank:
select route, min(customer_rank) rank
from routes r, airlines a
where a.airline=r.airline
group by route
with this query you get the the ranking of the preferred airline per
route. This output has to be inner joined with the airlines and routes
to get the desired result
select *
from airlines a,
routes r,
(
select route, min(customer_rank) rank
from routes r, airlines a
where a.airline=r.airline
group by route
) p
where a.customer_rank = p.rank
and r.route = p.route
and a.airline = r.airline
if you want to avoid an inner table (the stuff between (...)p) you
could create a view, but you will learn that in your next class.|||Hi
Thanks for the DDL and Example data, showing your expected results would
also be nice. You can either use a subquery in the where clause or a derived
table such as
SELECT A.[Customer_Rank],
R.[ID], R.[Airline], R.[Route], R.[Cost], R.[Duration]
FROM [dbo].[Routes] R
JOIN [dbo].[Airlines] A ON A.[Airline] = R.[Airline]
JOIN ( SELECT MIN(E.[Customer_Rank]) AS [Customer_Rank], D.[Route]
FROM [dbo].[Routes] D
JOIN [dbo].[Airlines] E ON D.[Airline] = E.[Airline]
GROUP BY D.[Route] ) F ON F.[Customer_Rank] = A.[Customer_Rank] AND
F.[Route] = R.[Route]
ORDER BY R.[Route], A.[Customer_Rank]
John
"Tadwick" wrote:
> I have a question that I will illustrate with the script below. As you can
> see there two tables, airlines and routes. Each airline has a customer_rank
> that indicates the preferred airline of the customer ie 1 is most preferred.
> Each route has a name, cost and duration.
> What I would like to do is write a query that returns one row for each route
> with the columns : airline, cost and duration. I would like the airline
> column to be based on the preferred carrier, irrespective of the cost and
> duration.
> Can someone please stop my head spinning?
> Thanks, Tad
>
> CREATE TABLE [dbo].[Airlines] (
> [Airline] [char] (10) PRIMARY KEY,
> [Customer_Rank] [tinyint] NOT NULL
> )
> CREATE TABLE [dbo].[Routes] (
> [ID] [int] IDENTITY (1, 1) PRIMARY KEY,
> [Airline] [char] (10) NOT NULL REFERENCES Airlines(Airline),
> [Route] [char] (10) NOT NULL ,
> [Cost] [numeric](18, 0) NOT NULL ,
> [Duration] [numeric](18, 0) NOT NULL
> )
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('BOAC',3)
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('TWA',2)
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('United',1)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('BOAC', 'A2B', 150, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('TWA', 'A2C', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('BOAC', 'B2C', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('United', 'A2B', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('United', 'D2E', 300, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('BOAC', 'B2D', 300, 3)|||Nite4Hawks and John,
You are both awesome. I am amazed to get responses with two different
techniques in such a short time. Are there pros and cons of the correlated
subquery vs derived table methods?
Thanks again, Tad
"Tadwick" wrote:
> I have a question that I will illustrate with the script below. As you can
> see there two tables, airlines and routes. Each airline has a customer_rank
> that indicates the preferred airline of the customer ie 1 is most preferred.
> Each route has a name, cost and duration.
> What I would like to do is write a query that returns one row for each route
> with the columns : airline, cost and duration. I would like the airline
> column to be based on the preferred carrier, irrespective of the cost and
> duration.
> Can someone please stop my head spinning?
> Thanks, Tad
>
> CREATE TABLE [dbo].[Airlines] (
> [Airline] [char] (10) PRIMARY KEY,
> [Customer_Rank] [tinyint] NOT NULL
> )
> CREATE TABLE [dbo].[Routes] (
> [ID] [int] IDENTITY (1, 1) PRIMARY KEY,
> [Airline] [char] (10) NOT NULL REFERENCES Airlines(Airline),
> [Route] [char] (10) NOT NULL ,
> [Cost] [numeric](18, 0) NOT NULL ,
> [Duration] [numeric](18, 0) NOT NULL
> )
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('BOAC',3)
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('TWA',2)
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('United',1)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('BOAC', 'A2B', 150, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('TWA', 'A2C', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('BOAC', 'B2C', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('United', 'A2B', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('United', 'D2E', 300, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('BOAC', 'B2D', 300, 3)sql

Query Question

I have a question that I will illustrate with the script below. As you can
see there two tables, airlines and routes. Each airline has a customer_rank
that indicates the preferred airline of the customer ie 1 is most preferred.
Each route has a name, cost and duration.
What I would like to do is write a query that returns one row for each route
with the columns : airline, cost and duration. I would like the airline
column to be based on the preferred carrier, irrespective of the cost and
duration.
Can someone please stop my head spinning?
Thanks, Tad
CREATE TABLE [dbo].[Airlines] (
[Airline] [char] (10) PRIMARY KEY,
[Customer_Rank] [tinyint] NOT NULL
)
CREATE TABLE [dbo].[Routes] (
[ID] [int] IDENTITY (1, 1) PRIMARY KEY,
[Airline] [char] (10) NOT NULL REFERENCES Airlines(Airline),
[Route] [char] (10) NOT NULL ,
[Cost] [numeric](18, 0) NOT NULL ,
[Duration] [numeric](18, 0) NOT NULL
)
INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
VALUES('BOAC',3)
INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
VALUES('TWA',2)
INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
VALUES('United',1)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Du
ration])
VALUES('BOAC', 'A2B', 150, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Du
ration])
VALUES('TWA', 'A2C', 200, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Du
ration])
VALUES('BOAC', 'B2C', 200, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Du
ration])
VALUES('United', 'A2B', 200, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Du
ration])
VALUES('United', 'D2E', 300, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Du
ration])
VALUES('BOAC', 'B2D', 300, 3)first of all you want to know which airline is the preferred carrier
for each route, so you have to inner join the two tables and find the
airline with the lowest (if highest substitute min by max) rank:
select route, min(customer_rank) rank
from routes r, airlines a
where a.airline=r.airline
group by route
with this query you get the the ranking of the preferred airline per
route. This output has to be inner joined with the airlines and routes
to get the desired result
select *
from airlines a,
routes r,
(
select route, min(customer_rank) rank
from routes r, airlines a
where a.airline=r.airline
group by route
) p
where a.customer_rank = p.rank
and r.route = p.route
and a.airline = r.airline
if you want to avoid an inner table (the stuff between (...)p) you
could create a view, but you will learn that in your next class.|||Hi
Thanks for the DDL and Example data, showing your expected results would
also be nice. You can either use a subquery in the where clause or a derived
table such as
SELECT A.[Customer_Rank],
R.[ID], R.[Airline], R.[Route], R.[Cost], R.[Duration]
FROM [dbo].[Routes] R
JOIN [dbo].[Airlines] A ON A.[Airline] = R.[Airline]
JOIN ( SELECT MIN(E.[Customer_Rank]) AS [Customer_Rank], D.[Rout
e]
FROM [dbo].[Routes] D
JOIN [dbo].[Airlines] E ON D.[Airline] = E.[Airline]
GROUP BY D.[Route] ) F ON F.[Customer_Rank] = A.[Customer_Rank]
AND
F.[Route] = R.[Route]
ORDER BY R.[Route], A.[Customer_Rank]
John
"Tadwick" wrote:

> I have a question that I will illustrate with the script below. As you ca
n
> see there two tables, airlines and routes. Each airline has a customer_ra
nk
> that indicates the preferred airline of the customer ie 1 is most preferre
d.
> Each route has a name, cost and duration.
> What I would like to do is write a query that returns one row for each rou
te
> with the columns : airline, cost and duration. I would like the airline
> column to be based on the preferred carrier, irrespective of the cost and
> duration.
> Can someone please stop my head spinning?
> Thanks, Tad
>
> CREATE TABLE [dbo].[Airlines] (
> [Airline] [char] (10) PRIMARY KEY,
> [Customer_Rank] [tinyint] NOT NULL
> )
> CREATE TABLE [dbo].[Routes] (
> [ID] [int] IDENTITY (1, 1) PRIMARY KEY,
> [Airline] [char] (10) NOT NULL REFERENCES Airlines(Airline),
> [Route] [char] (10) NOT NULL ,
> [Cost] [numeric](18, 0) NOT NULL ,
> [Duration] [numeric](18, 0) NOT NULL
> )
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('BOAC',3)
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('TWA',2)
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('United',1)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('BOAC', 'A2B', 150, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('TWA', 'A2C', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('BOAC', 'B2C', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('United', 'A2B', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('United', 'D2E', 300, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('BOAC', 'B2D', 300, 3)|||Nite4Hawks and John,
You are both awesome. I am amazed to get responses with two different
techniques in such a short time. Are there pros and cons of the correlated
subquery vs derived table methods?
Thanks again, Tad
"Tadwick" wrote:

> I have a question that I will illustrate with the script below. As you ca
n
> see there two tables, airlines and routes. Each airline has a customer_ra
nk
> that indicates the preferred airline of the customer ie 1 is most preferre
d.
> Each route has a name, cost and duration.
> What I would like to do is write a query that returns one row for each rou
te
> with the columns : airline, cost and duration. I would like the airline
> column to be based on the preferred carrier, irrespective of the cost and
> duration.
> Can someone please stop my head spinning?
> Thanks, Tad
>
> CREATE TABLE [dbo].[Airlines] (
> [Airline] [char] (10) PRIMARY KEY,
> [Customer_Rank] [tinyint] NOT NULL
> )
> CREATE TABLE [dbo].[Routes] (
> [ID] [int] IDENTITY (1, 1) PRIMARY KEY,
> [Airline] [char] (10) NOT NULL REFERENCES Airlines(Airline),
> [Route] [char] (10) NOT NULL ,
> [Cost] [numeric](18, 0) NOT NULL ,
> [Duration] [numeric](18, 0) NOT NULL
> )
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('BOAC',3)
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('TWA',2)
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('United',1)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('BOAC', 'A2B', 150, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('TWA', 'A2C', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('BOAC', 'B2C', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('United', 'A2B', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('United', 'D2E', 300, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('BOAC', 'B2D', 300, 3)

Query problem

I having trouble with the query below. I'm trying to
return the number of calls base on severity. The
#TempTable has 3 severities listed (1,2,&3). There are
no calls for severity 1, but i still want it to return a
record with a count of 0. A sample of what the query is
returning is at the bottom of this message. Please help.
select
Call.severity,
count(call.callno) as callcount
from
Call
left join Severity
On Call.Severity = Severity.severityID
where
datetimesubmitted between '01/01/2004' and '01/31/2004'
and
status <> 'CANCELLED'
group by Call.severity
severity callcount
-- --
2 4
3 25
Since you've posted no DDL or sample data I can't say for sure, but you can
try:
select
Severity.severityID,
SUM(CASE WHEN Call.Severity = Severity.severityID THEN 1 ELSE 0 END) as
callcount
from
Severity
left join Call
On Call.Severity = Severity.severityID
where
datetimesubmitted between '01/01/2004' and '01/31/2004'
and
status <> 'CANCELLED'
group by Severity.severityID
"Vic" <vduran@.specpro-inc.com> wrote in message
news:179e501c421a7$41aa2060$a001280a@.phx.gbl...
> I having trouble with the query below. I'm trying to
> return the number of calls base on severity. The
> #TempTable has 3 severities listed (1,2,&3). There are
> no calls for severity 1, but i still want it to return a
> record with a count of 0. A sample of what the query is
> returning is at the bottom of this message. Please help.
>
> select
> Call.severity,
> count(call.callno) as callcount
> from
> Call
> left join Severity
> On Call.Severity = Severity.severityID
> where
> datetimesubmitted between '01/01/2004' and '01/31/2004'
> and
> status <> 'CANCELLED'
> group by Call.severity
>
> severity callcount
> -- --
> 2 4
> 3 25
sql

query problem

Hi All,
I have a problems with my query on how to come up with my desired results.
Below are my two tables with sample data:
tableRegion
--
ColPrefix | ColRegion
--
0044 | UK - National
00441 | UK - Mobile
001213 | USA - California
001 | USA
tableNumbers
--
ColPhone
--
0044123456789
0044712345678
0012131234567
0018001234567
I want to come up with results displaying PhoneNumbers with their
coresponding Region based on their prefix like below. Any solutions?
0044123456789 | UK - National
0044712345678 | UK - Mobile
0012131234567 | USA - California
0018001234567 | USA
Looking for help on this one. Thanks in advance!
joelPlease post not only expected results, but fully defined DDL and sample
data. See http://www.aspfaq.com/etiquette.asp?id=5006 for more details.
Also, I believe either your expected results are wrong, or you sample data
is wrong, because it looks like 0044123456789 should match UK Mobile, not UK
National.
Lastly, I would highly recommend normalizing tableNumbers to have a column
for region prefix, since it is obvious that you want to use it as its own
atomic value.
You might want to consider a new naming convention for your tables and
columns as well - it is not considered "best practice" to name an item for
what it is physically, but for what it represents conceptually - i.e.
PhoneNumber instead of ColPhone, Region instead of ColRegion, etc. (Of
course, "Best Practices" for one person are "Worst Habits" for another!)
Enough of the nitpicking - I think I have a solution that works. It could
definitely be optimized, but it functions as is.
SELECT P1.ColPhone, P1.ColRegion
FROM
(SELECT PHN.ColPhone, REG.ColRegion, LEN(REG.ColPrefix) AS PrefixLength
FROM tableNumbers PHN INNER JOIN tableRegions REG
ON PHN.ColPhone LIKE REG.ColPrefix + '%') P1
INNER JOIN
(SELECT PHN.ColPhone, MAX(LEN(REG.ColPrefix)) AS PrefixLength
FROM tableNumbers PHN INNER JOIN tableRegions REG
ON PHN.ColPhone LIKE REG.ColPrefix + '%'
GROUP BY PHN.ColPhone) P2
ON P1.ColPhone = P2.ColPhone
AND P1.PrefixLength = P2.PrefixLength
This is based on the understanding that all longer prefixes identify more
specific areas than shorter prefixes. So if you had:
ColPrefix | ColRegion
001213 | USA - California
0012 | USA - West Coast
001 | USA
then 001213 would take precedence over 0012, and 0012 would take precedence
over 001.
IHTH
Jeremy Williams
"Joel Gacosta" <joel@.gacosta.net> wrote in message
news:eR6G6UFbFHA.3400@.tk2msftngp13.phx.gbl...
> Hi All,
> I have a problems with my query on how to come up with my desired results.
> Below are my two tables with sample data:
> tableRegion
> --
> ColPrefix | ColRegion
> --
> 0044 | UK - National
> 00441 | UK - Mobile
> 001213 | USA - California
> 001 | USA
> tableNumbers
> --
> ColPhone
> --
> 0044123456789
> 0044712345678
> 0012131234567
> 0018001234567
>
> I want to come up with results displaying PhoneNumbers with their
> coresponding Region based on their prefix like below. Any solutions?
> 0044123456789 | UK - National
> 0044712345678 | UK - Mobile
> 0012131234567 | USA - California
> 0018001234567 | USA
> Looking for help on this one. Thanks in advance!
> joel
>|||Thanks Jeremy! You're right I posted a wrong sample data.
Anyway I'll take your advise and try your solution. That is exactly what I
meant.
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:%23XV4LSGbFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Please post not only expected results, but fully defined DDL and sample
> data. See http://www.aspfaq.com/etiquette.asp?id=5006 for more details.
> Also, I believe either your expected results are wrong, or you sample data
> is wrong, because it looks like 0044123456789 should match UK Mobile, not
> UK
> National.
> Lastly, I would highly recommend normalizing tableNumbers to have a column
> for region prefix, since it is obvious that you want to use it as its own
> atomic value.
> You might want to consider a new naming convention for your tables and
> columns as well - it is not considered "best practice" to name an item for
> what it is physically, but for what it represents conceptually - i.e.
> PhoneNumber instead of ColPhone, Region instead of ColRegion, etc. (Of
> course, "Best Practices" for one person are "Worst Habits" for another!)
> Enough of the nitpicking - I think I have a solution that works. It could
> definitely be optimized, but it functions as is.
> SELECT P1.ColPhone, P1.ColRegion
> FROM
> (SELECT PHN.ColPhone, REG.ColRegion, LEN(REG.ColPrefix) AS PrefixLength
> FROM tableNumbers PHN INNER JOIN tableRegions REG
> ON PHN.ColPhone LIKE REG.ColPrefix + '%') P1
> INNER JOIN
> (SELECT PHN.ColPhone, MAX(LEN(REG.ColPrefix)) AS PrefixLength
> FROM tableNumbers PHN INNER JOIN tableRegions REG
> ON PHN.ColPhone LIKE REG.ColPrefix + '%'
> GROUP BY PHN.ColPhone) P2
> ON P1.ColPhone = P2.ColPhone
> AND P1.PrefixLength = P2.PrefixLength
> This is based on the understanding that all longer prefixes identify more
> specific areas than shorter prefixes. So if you had:
> ColPrefix | ColRegion
> 001213 | USA - California
> 0012 | USA - West Coast
> 001 | USA
> then 001213 would take precedence over 0012, and 0012 would take
> precedence
> over 001.
> IHTH
> Jeremy Williams
> "Joel Gacosta" <joel@.gacosta.net> wrote in message
> news:eR6G6UFbFHA.3400@.tk2msftngp13.phx.gbl...
>|||That data is pretty nasty; definately consider revising the schema to
something more workable. I went after the problem using a ranking mechanism
to select which number gets associated with which prefix. I assumed the nam
e
of the tables were [tblNumbers] and [tblRegion].
Hope this helps!
declare @.prefix_rank table (rank int, ColPrefix varchar(20), ColRegion
varchar(20))
declare @.phonelist table (ColPhone varchar(30), ColRegion varchar(20))
declare @.loop int, @.level int
select @.loop = max(len(ColPrefix)) from tblRegion
set @.level = 1
while @.loop > 0
begin
if exists(select * from tblRegion where len(ColPrefix) = @.loop)
begin
insert into @.prefix_rank (rank, ColPrefix, ColRegion)
select @.level, ColPrefix, ColRegion from tblRegion where len(ColPrefix)
= @.loop
set @.level = @.level + 1
end
set @.loop = @.loop - 1
end
set @.loop = 1
while @.loop <= @.level
begin
insert into @.phonelist (ColPhone, ColRegion)
select tn.ColPhone, pr.ColRegion
from tblNumbers tn
,@.prefix_rank pr
where tn.ColPhone like pr.ColPrefix + '%'
and tn.ColPhone not in (select ColPhone from @.phonelist)
and pr.rank = @.loop
and not exists (select pl.* from @.phonelist pl where pl.ColPhone =
tn.ColPhone)
set @.loop = @.loop + 1
end
select * from @.phonelist
"Joel Gacosta" wrote:

> Hi All,
> I have a problems with my query on how to come up with my desired results.
> Below are my two tables with sample data:
> tableRegion
> --
> ColPrefix | ColRegion
> --
> 0044 | UK - National
> 00441 | UK - Mobile
> 001213 | USA - California
> 001 | USA
> tableNumbers
> --
> ColPhone
> --
> 0044123456789
> 0044712345678
> 0012131234567
> 0018001234567
>
> I want to come up with results displaying PhoneNumbers with their
> coresponding Region based on their prefix like below. Any solutions?
> 0044123456789 | UK - National
> 0044712345678 | UK - Mobile
> 0012131234567 | USA - California
> 0018001234567 | USA
> Looking for help on this one. Thanks in advance!
> joel
>
>

Query Problem

I am using the query shown below but I am not getting the result I expect. I
want to get "Elected" only when the count of LastName field is 75% or more
of the count of when the "CG" field is equal to "Y". What did I mess up
here?
============================ =iif(Count(Fields!LastName.Value) < Count(( Fields!CG.Value)="Y")*0.75, "Not
Elected", "Elected")
============================================Hi Wayne
Count(( Fields!CG.Value)="Y"), i think you can't do this,the
architecture of the RS is such a way that it calculates all the
aggregations and operations first and then will come to the
conditional Expressions next,
here it is messed up with Expressions first .where you are unable to
get the result
As an alternate you can do this
1) Create an extra column Extra1 by going to dataset-->Fields
it must be a calculated field (not dataset field), give like this
IIF( Fields!CG.Value="Y",1,0)
2)Add another column extra2 with sum(Extra1) .
2)Go to the layout ,add extra column to the right of the column
wherever you want,give the expression like this
=iif(Count(Fields!LastName.Value) <First(Extra2), "Not
> Elected", "Elected")
i am not that much confident that it will work, but readjusting all
this expressions should work
Regards,
Raj Deep.A
Wayne Wengert wrote:
> I am using the query shown below but I am not getting the result I expect. I
> want to get "Elected" only when the count of LastName field is 75% or more
> of the count of when the "CG" field is equal to "Y". What did I mess up
> here?
> ============================> =iif(Count(Fields!LastName.Value) < Count(( Fields!CG.Value)="Y")*0.75, "Not
> Elected", "Elected")
> ============================================|||Thanks for the suggestions. I'll give that a try.
Wayne
"RajDeep" <rajalapati@.gmail.com> wrote in message
news:1160558814.067206.178500@.h48g2000cwc.googlegroups.com...
> Hi Wayne
> Count(( Fields!CG.Value)="Y"), i think you can't do this,the
> architecture of the RS is such a way that it calculates all the
> aggregations and operations first and then will come to the
> conditional Expressions next,
> here it is messed up with Expressions first .where you are unable to
> get the result
> As an alternate you can do this
> 1) Create an extra column Extra1 by going to dataset-->Fields
> it must be a calculated field (not dataset field), give like this
> IIF( Fields!CG.Value="Y",1,0)
> 2)Add another column extra2 with sum(Extra1) .
> 2)Go to the layout ,add extra column to the right of the column
> wherever you want,give the expression like this
> =iif(Count(Fields!LastName.Value) <First(Extra2), "Not
>> Elected", "Elected")
> i am not that much confident that it will work, but readjusting all
> this expressions should work
> Regards,
> Raj Deep.A
>
>
> Wayne Wengert wrote:
>> I am using the query shown below but I am not getting the result I
>> expect. I
>> want to get "Elected" only when the count of LastName field is 75% or
>> more
>> of the count of when the "CG" field is equal to "Y". What did I mess up
>> here?
>> ============================>> =iif(Count(Fields!LastName.Value) < Count(( Fields!CG.Value)="Y")*0.75,
>> "Not
>> Elected", "Elected")
>> ============================================>sql

Query Problem

Hi All,
I am having one problem with the query. Please see below the query.
Declare @.p_code_type_cd varchar (200),--3599, 6023
@.p_lang_cd varchar (10),--39
@.p_code_val varchar (2000), --67330000, 67000000
Select @.p_code_type_cd = '3599,6023', @.p_lang_cd = '39', @.p_code_val =
'67330000, 67000000'
select code_type_cd, code_val, desc_text
from code_tbl where code_type_cd in (@.p_code_type_cd)
and code_val in (@.p_code_val)and lang_cd = @.p_lang_cd
When this query is executed it produces no result. I analyzed and found
that the query which is being executed is converted as mentioned
below:-
select code_type_cd, code_val, desc_text
from code_tbl where code_type_cd in (3599,6023)
and code_val in (67330000, 67000000) and lang_cd = 39
i think it is missing few single quotes value. I am just how
to add these quotes in the query so that it becomes like..
select code_type_cd, code_val, desc_text
from code_tbl where code_type_cd in ('3599','6023')
and code_val in ('67330000','67000000')and lang_cd = '39'Hi
You can not use an array in this way without using dynamic SQL see
http://www.sommarskog.se/arrays-in-sql.html
John
"neeju" wrote:

> Hi All,
> I am having one problem with the query. Please see below the query.
> Declare @.p_code_type_cd varchar (200),--3599, 6023
> @.p_lang_cd varchar (10),--39
> @.p_code_val varchar (2000), --67330000, 67000000
> Select @.p_code_type_cd = '3599,6023', @.p_lang_cd = '39', @.p_code_val =
> '67330000, 67000000'
> select code_type_cd, code_val, desc_text
> from code_tbl where code_type_cd in (@.p_code_type_cd)
> and code_val in (@.p_code_val)and lang_cd = @.p_lang_cd
> When this query is executed it produces no result. I analyzed and found
> that the query which is being executed is converted as mentioned
> below:-
> select code_type_cd, code_val, desc_text
> from code_tbl where code_type_cd in (3599,6023)
> and code_val in (67330000, 67000000) and lang_cd = 39
> i think it is missing few single quotes value. I am just how
> to add these quotes in the query so that it becomes like..
> select code_type_cd, code_val, desc_text
> from code_tbl where code_type_cd in ('3599','6023')
> and code_val in ('67330000','67000000')and lang_cd = '39'
>|||You can also do this with table variables, with only a few minor changes to
your code:
Declare @.p_lang_cd varchar (10)
/* Original variable assignment replaced with DECLARE ... TABLE
Select @.p_code_type_cd = '3599,6023', @.p_lang_cd = '39', @.p_code_val =
'67330000, 67000000'
*/
SELECT @.p_lang_cd = '39'
DECLARE @.p_code_type_cd TABLE ( p_code_type_cd INT )
INSERT INTO @.p_code_type_cd VALUES ( '3599' )
INSERT INTO @.p_code_type_cd VALUES ( '6023' )
DECLARE @.p_code_val TABLE ( p_code_val INT )
INSERT INTO @.p_code_val VALUES ( '67330000' )
INSERT INTO @.p_code_val VALUES ( '67000000' )
/* Original query
select code_type_cd, code_val, desc_text
from code_tbl
where code_type_cd in (@.p_code_type_cd)
and code_val in (@.p_code_val)
and lang_cd = @.p_lang_cd
*/
-- Revised query
select code_type_cd, code_val, desc_text
from code_tbl
where code_type_cd in (SELECT * FROM @.p_code_type_cd)
and code_val in (SELECT * FROM @.p_code_val)
and lang_cd = @.p_lang_cd
-- Or this might be better
select code_type_cd, code_val, desc_text
from code_tbl c
INNER JOIN @.p_code_type_cd t ON c.code_type_cd = t.p_code_type_cd
INNER JOIN @.p_code_val v ON c.code_val = v.p_code_val
WHERE lang_cd = @.p_lang_cd
Untested! Let me know how you get on.
Damien
"neeju" wrote:

> Hi All,
> I am having one problem with the query. Please see below the query.
> Declare @.p_code_type_cd varchar (200),--3599, 6023
> @.p_lang_cd varchar (10),--39
> @.p_code_val varchar (2000), --67330000, 67000000
> Select @.p_code_type_cd = '3599,6023', @.p_lang_cd = '39', @.p_code_val =
> '67330000, 67000000'
> select code_type_cd, code_val, desc_text
> from code_tbl where code_type_cd in (@.p_code_type_cd)
> and code_val in (@.p_code_val)and lang_cd = @.p_lang_cd
> When this query is executed it produces no result. I analyzed and found
> that the query which is being executed is converted as mentioned
> below:-
> select code_type_cd, code_val, desc_text
> from code_tbl where code_type_cd in (3599,6023)
> and code_val in (67330000, 67000000) and lang_cd = 39
> i think it is missing few single quotes value. I am just how
> to add these quotes in the query so that it becomes like..
> select code_type_cd, code_val, desc_text
> from code_tbl where code_type_cd in ('3599','6023')
> and code_val in ('67330000','67000000')and lang_cd = '39'
>|||Thanks Damien,
Your code executes without any problem...
We have also updated the query and used temp table for storing the
values. But we were just looking for some logic
to change the values in IN operator using some TSQL function. We also
tried Replace function and replaces ',' with quotes in start and end of
both the values But that didn't produced the result.. We used like:-
Declare @.p_code_type_cd varchar (200),--3599, 6023
@.p_lang_cd varchar (10),--39
@.p_code_val varchar (2000),
@.delimeter char(1) --67330000, 67000000
-- @.test varchar (200)
Select @.delimeter = ',', @.p_code_type_cd = '3599,6023', @.p_lang_cd =
'39', @.p_code_val = '67330000, 67000000'
select code_type_cd, code_val, desc_text
from code_tbl where code_type_cd in
(''''+replace(@.p_code_type_cd,@.delimeter
,'',''))
and code_val in (@.p_code_val)and lang_cd = @.p_lang_cd
Please let me know if any other thoughts,
Thanks,
NJ|||Hi
You can not do this without resorting to dynamic SQL. See
http://www.sommarskog.se/arrays-in-sql.html
DECLARE @.p_code_type_cd varchar (200),--3599, 6023
@.p_lang_cd varchar (10),--39
@.p_code_val varchar (2000),
@.delimeter char(1) --67330000, 67000000
DECLARE @.sqlstmt nvarchar(4000)
-- @.test varchar (200)
Select @.delimeter = ',', @.p_code_type_cd = '3599,6023', @.p_lang_cd =
'39', @.p_code_val = '67330000, 67000000'
SET @.sqlstmt = 'select code_type_cd, code_val, desc_text
from code_tbl where code_type_cd in
(' + @.p_code_type_cd + ')
and code_val in (' + @.p_code_val + ')and lang_cd = @.p_lang_cd'
SELECT @.sqlstmt
EXEC sp_executesql @.sqlstmt
John
"neeju" wrote:

> Thanks Damien,
> Your code executes without any problem...
> We have also updated the query and used temp table for storing the
> values. But we were just looking for some logic
> to change the values in IN operator using some TSQL function. We also
> tried Replace function and replaces ',' with quotes in start and end of
> both the values But that didn't produced the result.. We used like:-
> Declare @.p_code_type_cd varchar (200),--3599, 6023
> @.p_lang_cd varchar (10),--39
> @.p_code_val varchar (2000),
> @.delimeter char(1) --67330000, 67000000
> -- @.test varchar (200)
> Select @.delimeter = ',', @.p_code_type_cd = '3599,6023', @.p_lang_cd =
> '39', @.p_code_val = '67330000, 67000000'
> select code_type_cd, code_val, desc_text
> from code_tbl where code_type_cd in
> (''''+replace(@.p_code_type_cd,@.delimeter
,'',''))
> and code_val in (@.p_code_val)and lang_cd = @.p_lang_cd
> Please let me know if any other thoughts,
> Thanks,
> NJ
>|||Yes John,
It appears so.. We tried no. of things but of no use..
I argued with the developers for not using dynamic sql and promised to
give some alternative statement withoug dynamic sql but nothing works.
Anyway thanks for directing to such a good article.
Thanks,
NJ|||Actually, you are missing a proper data model. Data element names like
"code_type_cd" are absurd. A code and a type are different kinds of
attributes, so you should have names like "postal_code" or
"blood_type" instead of a list of adjectives looking for a noun. Same
problem with "code_val"
What kind of code is in "code_tbl"? This name implies that you are
dealing with furniture. It must be ONE AND ONE KIND of code to be a
valid table. There is no such thing as a "Magical, Universal Does
Everything" code table in an RDBMS. Surely you have not mixed data and
metadata in a schema to build a OTLT or MUCK? Google those words and
start your research.
You also do not seem to know that SQL is compiled, so passing a string
is not like writing code on the fly in an interpreter. Without DDL and
sensible data element names, nobody can really help you. But based on
past experience, when the schema is bad, the kludge is usually dynamic
SQL.
The reason people give you that kludge is that it gets rid of you
faster than actually soving the root problems. That could take more
time and effort than we want to give away for free in a newsgroup.
Please get some real help somewhere else.|||Hi
This is where a CLR udt in SQL 2005 would probably be useful. The article
does show some methods that avoid using dynamic SQL.
John
"neeju" wrote:

> Yes John,
> It appears so.. We tried no. of things but of no use..
> I argued with the developers for not using dynamic sql and promised to
> give some alternative statement withoug dynamic sql but nothing works.
> Anyway thanks for directing to such a good article.
> Thanks,
> NJ
>sql

Query problem

I having trouble with the query below. I'm trying to
return the number of calls base on severity. The
#TempTable has 3 severities listed (1,2,&3). There are
no calls for severity 1, but i still want it to return a
record with a count of 0. A sample of what the query is
returning is at the bottom of this message. Please help.
select
Call.severity,
count(call.callno) as callcount
from
Call
left join Severity
On Call.Severity = Severity.severityID
where
datetimesubmitted between '01/01/2004' and '01/31/2004'
and
status <> 'CANCELLED'
group by Call.severity
severity callcount
-- --
2 4
3 25Since you've posted no DDL or sample data I can't say for sure, but you can
try:
select
Severity.severityID,
SUM(CASE WHEN Call.Severity = Severity.severityID THEN 1 ELSE 0 END) as
callcount
from
Severity
left join Call
On Call.Severity = Severity.severityID
where
datetimesubmitted between '01/01/2004' and '01/31/2004'
and
status <> 'CANCELLED'
group by Severity.severityID
"Vic" <vduran@.specpro-inc.com> wrote in message
news:179e501c421a7$41aa2060$a001280a@.phx.gbl...
> I having trouble with the query below. I'm trying to
> return the number of calls base on severity. The
> #TempTable has 3 severities listed (1,2,&3). There are
> no calls for severity 1, but i still want it to return a
> record with a count of 0. A sample of what the query is
> returning is at the bottom of this message. Please help.
>
> select
> Call.severity,
> count(call.callno) as callcount
> from
> Call
> left join Severity
> On Call.Severity = Severity.severityID
> where
> datetimesubmitted between '01/01/2004' and '01/31/2004'
> and
> status <> 'CANCELLED'
> group by Call.severity
>
> severity callcount
> -- --
> 2 4
> 3 25

Query problem

I having trouble with the query below. I'm trying to
return the number of calls base on severity. The
#TempTable has 3 severities listed (1,2,&3). There are
no calls for severity 1, but i still want it to return a
record with a count of 0. A sample of what the query is
returning is at the bottom of this message. Please help.
select
Call.severity,
count(call.callno) as callcount
from
Call
left join Severity
On Call.Severity = Severity.severityID
where
datetimesubmitted between '01/01/2004' and '01/31/2004'
and
status <> 'CANCELLED'
group by Call.severity
severity callcount
-- --
2 4
3 25Since you've posted no DDL or sample data I can't say for sure, but you can
try:
select
Severity.severityID,
SUM(CASE WHEN Call.Severity = Severity.severityID THEN 1 ELSE 0 END) as
callcount
from
Severity
left join Call
On Call.Severity = Severity.severityID
where
datetimesubmitted between '01/01/2004' and '01/31/2004'
and
status <> 'CANCELLED'
group by Severity.severityID
"Vic" <vduran@.specpro-inc.com> wrote in message
news:179e501c421a7$41aa2060$a001280a@.phx
.gbl...
> I having trouble with the query below. I'm trying to
> return the number of calls base on severity. The
> #TempTable has 3 severities listed (1,2,&3). There are
> no calls for severity 1, but i still want it to return a
> record with a count of 0. A sample of what the query is
> returning is at the bottom of this message. Please help.
>
> select
> Call.severity,
> count(call.callno) as callcount
> from
> Call
> left join Severity
> On Call.Severity = Severity.severityID
> where
> datetimesubmitted between '01/01/2004' and '01/31/2004'
> and
> status <> 'CANCELLED'
> group by Call.severity
>
> severity callcount
> -- --
> 2 4
> 3 25

Query Perormance (Problem bookmark)

I have a bookmark caused by the a15.Tr_type_id where condition below. I thin
k
it is because the query tries to satisfy the date condition before going to
get the a15.Tr_type_id condition. If I comment out the a15.Tr_type_id, the
bookmark disappears and performance boosts. I tried a clustered index on
a15.Tr_type_id and it improved it a bit more. Any recommendations much
appreciated?
select a14.Ra_licence_Group_desc Ra_licence_Group_desc,
a14.Ra_gender_id Ra_gender_id,
a14.RA_yr_band_HFI_id RA_yr_band_HFI_id,
a14.RA_yr_band_New_id RA_yr_band_New_id,
a12.Pr_cover_id Pr_cover_id,
count((case when a12.Pr_Group_id = 5 then a11.Vehicle_id else a11.Policy_id
end)) WJXBFS1
into #ZZT5J0302LPMD004
from Z_fat_bse_po_risk_detail a11
join Z_prt_lu_product a12
on (a11.Product_id = a12.Product_id)
join Z_POt_lu_policy a13
on (a11.Policy_id = a13.Policy_id)
join Z_RAt_lu_Rated a14
on (a11.Rated_driver_id = a14.Rated_driver_id)
join Z_TRt_lu_Trans_Subtype a15
on (a11.Tr_sub_type_id = a15.Tr_sub_type_id)
where (a12.Pr_cover_id in ('C', 'F')
and a14.Ra_gender_id in ('F', 'M')
and a14.Ra_licence_id in ('F', 'P')
and a12.Pr_Group_id in (2, 3)
and a14.RA_yr_band_New_id not in (1)
and a11.Po_tr_bus_type_id in (0)
and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a11.Cur_trn_dt between CONVERT(datetime, '2004-03-07 00:00:00', 120)
and CONVERT(datetime, '2005-03-05 00:00:00', 120)
and a15.Tr_type_id in ('HNB', 'HNC', 'HRN', 'INB', 'IRN', 'HPR')
and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Pr_Group_id in (2, 3)
and a13.Po_corp_unit_id in ('GEI')
and a11.Entered_by_id not in (7561, 7570)
and a12.Pr_cover_id in ('C', 'F', 'T')
and a14.Ra_gender_id in ('F', 'M')
and a13.Po_market_src_id not in (14))
group by a14.Ra_licence_Group_desc,
a14.Ra_gender_id,
a14.RA_yr_band_HFI_id,
a14.RA_yr_band_New_id,
a12.Pr_cover_idgolden rule, check statistics job has run before you believe an end user!
"marcmc" wrote:

> I have a bookmark caused by the a15.Tr_type_id where condition below. I th
ink
> it is because the query tries to satisfy the date condition before going t
o
> get the a15.Tr_type_id condition. If I comment out the a15.Tr_type_id, the
> bookmark disappears and performance boosts. I tried a clustered index on
> a15.Tr_type_id and it improved it a bit more. Any recommendations much
> appreciated?
> select a14.Ra_licence_Group_desc Ra_licence_Group_desc,
> a14.Ra_gender_id Ra_gender_id,
> a14.RA_yr_band_HFI_id RA_yr_band_HFI_id,
> a14.RA_yr_band_New_id RA_yr_band_New_id,
> a12.Pr_cover_id Pr_cover_id,
> count((case when a12.Pr_Group_id = 5 then a11.Vehicle_id else a11.Policy_
id
> end)) WJXBFS1
> into #ZZT5J0302LPMD004
> from Z_fat_bse_po_risk_detail a11
> join Z_prt_lu_product a12
> on (a11.Product_id = a12.Product_id)
> join Z_POt_lu_policy a13
> on (a11.Policy_id = a13.Policy_id)
> join Z_RAt_lu_Rated a14
> on (a11.Rated_driver_id = a14.Rated_driver_id)
> join Z_TRt_lu_Trans_Subtype a15
> on (a11.Tr_sub_type_id = a15.Tr_sub_type_id)
> where (a12.Pr_cover_id in ('C', 'F')
> and a14.Ra_gender_id in ('F', 'M')
> and a14.Ra_licence_id in ('F', 'P')
> and a12.Pr_Group_id in (2, 3)
> and a14.RA_yr_band_New_id not in (1)
> and a11.Po_tr_bus_type_id in (0)
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a11.Cur_trn_dt between CONVERT(datetime, '2004-03-07 00:00:00', 120)
> and CONVERT(datetime, '2005-03-05 00:00:00', 120)
> and a15.Tr_type_id in ('HNB', 'HNC', 'HRN', 'INB', 'IRN', 'HPR')
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Pr_Group_id in (2, 3)
> and a13.Po_corp_unit_id in ('GEI')
> and a11.Entered_by_id not in (7561, 7570)
> and a12.Pr_cover_id in ('C', 'F', 'T')
> and a14.Ra_gender_id in ('F', 'M')
> and a13.Po_market_src_id not in (14))
> group by a14.Ra_licence_Group_desc,
> a14.Ra_gender_id,
> a14.RA_yr_band_HFI_id,
> a14.RA_yr_band_New_id,
> a12.Pr_cover_id
>|||ps: do MVP's ever visit this forum
"marcmc" wrote:

> I have a bookmark caused by the a15.Tr_type_id where condition below. I th
ink
> it is because the query tries to satisfy the date condition before going t
o
> get the a15.Tr_type_id condition. If I comment out the a15.Tr_type_id, the
> bookmark disappears and performance boosts. I tried a clustered index on
> a15.Tr_type_id and it improved it a bit more. Any recommendations much
> appreciated?
> select a14.Ra_licence_Group_desc Ra_licence_Group_desc,
> a14.Ra_gender_id Ra_gender_id,
> a14.RA_yr_band_HFI_id RA_yr_band_HFI_id,
> a14.RA_yr_band_New_id RA_yr_band_New_id,
> a12.Pr_cover_id Pr_cover_id,
> count((case when a12.Pr_Group_id = 5 then a11.Vehicle_id else a11.Policy_
id
> end)) WJXBFS1
> into #ZZT5J0302LPMD004
> from Z_fat_bse_po_risk_detail a11
> join Z_prt_lu_product a12
> on (a11.Product_id = a12.Product_id)
> join Z_POt_lu_policy a13
> on (a11.Policy_id = a13.Policy_id)
> join Z_RAt_lu_Rated a14
> on (a11.Rated_driver_id = a14.Rated_driver_id)
> join Z_TRt_lu_Trans_Subtype a15
> on (a11.Tr_sub_type_id = a15.Tr_sub_type_id)
> where (a12.Pr_cover_id in ('C', 'F')
> and a14.Ra_gender_id in ('F', 'M')
> and a14.Ra_licence_id in ('F', 'P')
> and a12.Pr_Group_id in (2, 3)
> and a14.RA_yr_band_New_id not in (1)
> and a11.Po_tr_bus_type_id in (0)
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a11.Cur_trn_dt between CONVERT(datetime, '2004-03-07 00:00:00', 120)
> and CONVERT(datetime, '2005-03-05 00:00:00', 120)
> and a15.Tr_type_id in ('HNB', 'HNC', 'HRN', 'INB', 'IRN', 'HPR')
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Pr_Group_id in (2, 3)
> and a13.Po_corp_unit_id in ('GEI')
> and a11.Entered_by_id not in (7561, 7570)
> and a12.Pr_cover_id in ('C', 'F', 'T')
> and a14.Ra_gender_id in ('F', 'M')
> and a13.Po_market_src_id not in (14))
> group by a14.Ra_licence_Group_desc,
> a14.Ra_gender_id,
> a14.RA_yr_band_HFI_id,
> a14.RA_yr_band_New_id,
> a12.Pr_cover_id
>|||"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:E13FCDE5-D0FD-4D1F-922B-CAEC5C9A584A@.microsoft.com...
> ps: do MVP's ever visit this forum
Yes. I see 12 messages posted here since 2:09PM PST yesterday, the 9th, and
exactly half of those posts came from MVPs Mike Epprecht, Jasper Smith, and
Sue Hoegemeier.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.|||Ahh I see now. It's just that when I used to post on compact framework site
there was a little bubble with mvp which was easily noticeable. Thx.
"Stephen Dybing [MSFT]" wrote:

> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:E13FCDE5-D0FD-4D1F-922B-CAEC5C9A584A@.microsoft.com...
> Yes. I see 12 messages posted here since 2:09PM PST yesterday, the 9th, an
d
> exactly half of those posts came from MVPs Mike Epprecht, Jasper Smith, an
d
> Sue Hoegemeier.
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Please reply to the newsgroups only, thanks.
>
>|||> Ahh I see now. It's just that when I used to post on compact framework
> site
> there was a little bubble with mvp which was easily noticeable.
I guess the SQL Server MVPs are a bit more low-key :-)
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:0A8A3596-B262-4A78-93B3-861293435199@.microsoft.com...[vbcol=seagreen]
> Ahh I see now. It's just that when I used to post on compact framework
> site
> there was a little bubble with mvp which was easily noticeable. Thx.
> "Stephen Dybing [MSFT]" wrote:
>|||I just noticed...if we post through the web based news reader on Microsoft's
communities site, it puts the little MVP bubble thing by our names. My posts
earlier using Agent newsreader (over 99% of my posts) don't have the bubble
thing.
So if you want a bubble by your name, that's how you can have one!
-Sue
"Dan Guzman" wrote:

> I guess the SQL Server MVPs are a bit more low-key :-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:0A8A3596-B262-4A78-93B3-861293435199@.microsoft.com...
>
>

Query Performance (am I in the ms right forum for performance Q's

I get a massive bookmark in the execution plans when I run the below SQL to
return 23 rows of data...The volumens are as below. If I take out the
reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark disappears
and I get drastically improved performance. a11 is well indexed. A new index
was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] O
N
[dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id])
ON [PRIMARY]
GO
and it has provided many other performance gains on a number of other pieces
of SQL. How do I begin to think about/code for this lack of performance.
Don't worry I don't expect you to understand the tables or business but if
any experiences have been overcome please post.
select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
select count(*) from prt_lu_product(nolock) -- Rows: 1719900
select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
select count(*) from ITv_lu_day(nolock) -- Rows: 4831
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Duration: 0:05:02.00 - 23 rows
select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
a15.ITv_year_id year_id,
count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
a11.Policy_id end)) WJXBFS1
into #ZZT5J0300BKMD00J
from fat_bse_po_risk_detail a11
join prt_lu_product a12
on (a11.Product_id = a12.Product_id)
join POt_lu_policy a13
on (a11.Policy_id = a13.Policy_id)
join vht_lu_vehicle a14
on (a11.Vehicle_id = a14.Vehicle_id)
join ITv_lu_day a15
on (a11.Inception_date_id = a15.Inception_date_id)
where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a11.Tr_sub_type_id in (430, 433, 3530)
and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Pr_Group_id in (2, 3)
and a11.f_Ren_Flag = '4'
and a11.Po_tr_bus_type_id > 0)
group by a14.Vh_VhAll_group_id,
a15.ITv_year_id
I get a massive bookmark in the execution plans when I run the below SQL to
return 23 rows of data...The volumens are as below. If I take out the
reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark disappears
and I get drastically improved performance. a11 is well indexed. A new index
was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] O
N
[dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id])
ON [PRIMARY]
GO
and it has provided many other performance gains on a number of other pieces
of SQL. How do I begin to think about/code for this lack of performance.
Don't worry I don't expect you to understand the tables or business but if
any experiences have been overcome please post.
select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
select count(*) from prt_lu_product(nolock) -- Rows: 1719900
select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
select count(*) from ITv_lu_day(nolock) -- Rows: 4831
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Duration: 0:05:02.00 - 23 rows
select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
a15.ITv_year_id year_id,
count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
a11.Policy_id end)) WJXBFS1
into #ZZT5J0300BKMD00J
from fat_bse_po_risk_detail a11
join prt_lu_product a12
on (a11.Product_id = a12.Product_id)
join POt_lu_policy a13
on (a11.Policy_id = a13.Policy_id)
join vht_lu_vehicle a14
on (a11.Vehicle_id = a14.Vehicle_id)
join ITv_lu_day a15
on (a11.Inception_date_id = a15.Inception_date_id)
where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a11.Tr_sub_type_id in (430, 433, 3530)
and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Pr_Group_id in (2, 3)
and a11.f_Ren_Flag = '4'
and a11.Po_tr_bus_type_id > 0)
group by a14.Vh_VhAll_group_id,
a15.ITv_year_idThis query involves many tables, of which you posted no information
(other than the row count). You also did not post or properly describe
the query plan. So there is not enough information to give detailed
advice.
If removing the predicate "a11.Tr_sub_type_id in (430, 433, 3530)"
increases the performance dramatically, then check out which index is
used (for that query), and add Tr_sub_type_id to this index (or create a
new index with this definition).
The query may benefit from a nonclustered index on
POt_lu_policy(Policy_id,Po_corp_unit_id)
You mention that you added an index to the TRt_lu_Trans_Subtype table,
but this seems irrelevant, since this table is not used in the query.
You could also consider running the Index Tuning Wizard.
Other generic advice:
- Make sure your statistics are up to date
- Make sure the expressions in a join clause have the same data type
definition
- Pay special attention to the clustered index definition of the largest
table
Hope this helps,
Gert-Jan
marcmc wrote:
> I get a massive bookmark in the execution plans when I run the below SQL t
o
> return 23 rows of data...The volumens are as below. If I take out the
> reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark disappea
rs
> and I get drastically improved performance. a11 is well indexed. A new ind
ex
> was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype]
ON
> [dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id
]) ON [PRIMARY]
> GO
> and it has provided many other performance gains on a number of other piec
es
> of SQL. How do I begin to think about/code for this lack of performance.
> Don't worry I don't expect you to understand the tables or business but if
> any experiences have been overcome please post.
> select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
> select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
> select count(*) from prt_lu_product(nolock) -- Rows: 1719900
> select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
> select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
> select count(*) from ITv_lu_day(nolock) -- Rows: 4831
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> -- Duration: 0:05:02.00 - 23 rows
> select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
> a15.ITv_year_id year_id,
> count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
> a11.Policy_id end)) WJXBFS1
> into #ZZT5J0300BKMD00J
> from fat_bse_po_risk_detail a11
> join prt_lu_product a12
> on (a11.Product_id = a12.Product_id)
> join POt_lu_policy a13
> on (a11.Policy_id = a13.Policy_id)
> join vht_lu_vehicle a14
> on (a11.Vehicle_id = a14.Vehicle_id)
> join ITv_lu_day a15
> on (a11.Inception_date_id = a15.Inception_date_id)
> where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a11.Tr_sub_type_id in (430, 433, 3530)
> and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
> and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Pr_Group_id in (2, 3)
> and a11.f_Ren_Flag = '4'
> and a11.Po_tr_bus_type_id > 0)
> group by a14.Vh_VhAll_group_id,
> a15.ITv_year_id
>
> I get a massive bookmark in the execution plans when I run the below SQL t
o
> return 23 rows of data...The volumens are as below. If I take out the
> reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark disappea
rs
> and I get drastically improved performance. a11 is well indexed. A new ind
ex
> was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype]
ON
> [dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id
]) ON [PRIMARY]
> GO
> and it has provided many other performance gains on a number of other piec
es
> of SQL. How do I begin to think about/code for this lack of performance.
> Don't worry I don't expect you to understand the tables or business but if
> any experiences have been overcome please post.
> select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
> select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
> select count(*) from prt_lu_product(nolock) -- Rows: 1719900
> select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
> select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
> select count(*) from ITv_lu_day(nolock) -- Rows: 4831
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> -- Duration: 0:05:02.00 - 23 rows
> select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
> a15.ITv_year_id year_id,
> count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
> a11.Policy_id end)) WJXBFS1
> into #ZZT5J0300BKMD00J
> from fat_bse_po_risk_detail a11
> join prt_lu_product a12
> on (a11.Product_id = a12.Product_id)
> join POt_lu_policy a13
> on (a11.Policy_id = a13.Policy_id)
> join vht_lu_vehicle a14
> on (a11.Vehicle_id = a14.Vehicle_id)
> join ITv_lu_day a15
> on (a11.Inception_date_id = a15.Inception_date_id)
> where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a11.Tr_sub_type_id in (430, 433, 3530)
> and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
> and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Pr_Group_id in (2, 3)
> and a11.f_Ren_Flag = '4'
> and a11.Po_tr_bus_type_id > 0)
> group by a14.Vh_VhAll_group_id,
> a15.ITv_year_id
>sql

Query Performance (am I in the ms right forum for performance Q's

I get a massive bookmark in the execution plans when I run the below SQL to
return 23 rows of data...The volumens are as below. If I take out the
reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark disappears
and I get drastically improved performance. a11 is well indexed. A new index
was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
[dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id]) ON [PRIMARY]
GO
and it has provided many other performance gains on a number of other pieces
of SQL. How do I begin to think about/code for this lack of performance.
Don't worry I don't expect you to understand the tables or business but if
any experiences have been overcome please post.
select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
select count(*) from prt_lu_product(nolock) -- Rows: 1719900
select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
select count(*) from ITv_lu_day(nolock) -- Rows: 4831
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Duration: 0:05:02.00 - 23 rows
select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
a15.ITv_year_id year_id,
count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
a11.Policy_id end)) WJXBFS1
into #ZZT5J0300BKMD00J
from fat_bse_po_risk_detail a11
join prt_lu_product a12
on (a11.Product_id = a12.Product_id)
join POt_lu_policy a13
on (a11.Policy_id = a13.Policy_id)
join vht_lu_vehicle a14
on (a11.Vehicle_id = a14.Vehicle_id)
join ITv_lu_day a15
on (a11.Inception_date_id = a15.Inception_date_id)
where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a11.Tr_sub_type_id in (430, 433, 3530)
and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Pr_Group_id in (2, 3)
and a11.f_Ren_Flag = '4'
and a11.Po_tr_bus_type_id > 0)
group by a14.Vh_VhAll_group_id,
a15.ITv_year_id
I get a massive bookmark in the execution plans when I run the below SQL to
return 23 rows of data...The volumens are as below. If I take out the
reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark disappears
and I get drastically improved performance. a11 is well indexed. A new index
was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
[dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id]) ON [PRIMARY]
GO
and it has provided many other performance gains on a number of other pieces
of SQL. How do I begin to think about/code for this lack of performance.
Don't worry I don't expect you to understand the tables or business but if
any experiences have been overcome please post.
select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
select count(*) from prt_lu_product(nolock) -- Rows: 1719900
select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
select count(*) from ITv_lu_day(nolock) -- Rows: 4831
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Duration: 0:05:02.00 - 23 rows
select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
a15.ITv_year_id year_id,
count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
a11.Policy_id end)) WJXBFS1
into #ZZT5J0300BKMD00J
from fat_bse_po_risk_detail a11
join prt_lu_product a12
on (a11.Product_id = a12.Product_id)
join POt_lu_policy a13
on (a11.Policy_id = a13.Policy_id)
join vht_lu_vehicle a14
on (a11.Vehicle_id = a14.Vehicle_id)
join ITv_lu_day a15
on (a11.Inception_date_id = a15.Inception_date_id)
where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a11.Tr_sub_type_id in (430, 433, 3530)
and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Pr_Group_id in (2, 3)
and a11.f_Ren_Flag = '4'
and a11.Po_tr_bus_type_id > 0)
group by a14.Vh_VhAll_group_id,
a15.ITv_year_idThis query involves many tables, of which you posted no information
(other than the row count). You also did not post or properly describe
the query plan. So there is not enough information to give detailed
advice.
If removing the predicate "a11.Tr_sub_type_id in (430, 433, 3530)"
increases the performance dramatically, then check out which index is
used (for that query), and add Tr_sub_type_id to this index (or create a
new index with this definition).
The query may benefit from a nonclustered index on
POt_lu_policy(Policy_id,Po_corp_unit_id)
You mention that you added an index to the TRt_lu_Trans_Subtype table,
but this seems irrelevant, since this table is not used in the query.
You could also consider running the Index Tuning Wizard.
Other generic advice:
- Make sure your statistics are up to date
- Make sure the expressions in a join clause have the same data type
definition
- Pay special attention to the clustered index definition of the largest
table
Hope this helps,
Gert-Jan
marcmc wrote:
> I get a massive bookmark in the execution plans when I run the below SQL to
> return 23 rows of data...The volumens are as below. If I take out the
> reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark disappears
> and I get drastically improved performance. a11 is well indexed. A new index
> was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
> [dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id]) ON [PRIMARY]
> GO
> and it has provided many other performance gains on a number of other pieces
> of SQL. How do I begin to think about/code for this lack of performance.
> Don't worry I don't expect you to understand the tables or business but if
> any experiences have been overcome please post.
> select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
> select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
> select count(*) from prt_lu_product(nolock) -- Rows: 1719900
> select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
> select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
> select count(*) from ITv_lu_day(nolock) -- Rows: 4831
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> -- Duration: 0:05:02.00 - 23 rows
> select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
> a15.ITv_year_id year_id,
> count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
> a11.Policy_id end)) WJXBFS1
> into #ZZT5J0300BKMD00J
> from fat_bse_po_risk_detail a11
> join prt_lu_product a12
> on (a11.Product_id = a12.Product_id)
> join POt_lu_policy a13
> on (a11.Policy_id = a13.Policy_id)
> join vht_lu_vehicle a14
> on (a11.Vehicle_id = a14.Vehicle_id)
> join ITv_lu_day a15
> on (a11.Inception_date_id = a15.Inception_date_id)
> where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a11.Tr_sub_type_id in (430, 433, 3530)
> and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
> and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Pr_Group_id in (2, 3)
> and a11.f_Ren_Flag = '4'
> and a11.Po_tr_bus_type_id > 0)
> group by a14.Vh_VhAll_group_id,
> a15.ITv_year_id
>
> I get a massive bookmark in the execution plans when I run the below SQL to
> return 23 rows of data...The volumens are as below. If I take out the
> reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark disappears
> and I get drastically improved performance. a11 is well indexed. A new index
> was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
> [dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id]) ON [PRIMARY]
> GO
> and it has provided many other performance gains on a number of other pieces
> of SQL. How do I begin to think about/code for this lack of performance.
> Don't worry I don't expect you to understand the tables or business but if
> any experiences have been overcome please post.
> select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
> select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
> select count(*) from prt_lu_product(nolock) -- Rows: 1719900
> select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
> select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
> select count(*) from ITv_lu_day(nolock) -- Rows: 4831
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> -- Duration: 0:05:02.00 - 23 rows
> select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
> a15.ITv_year_id year_id,
> count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
> a11.Policy_id end)) WJXBFS1
> into #ZZT5J0300BKMD00J
> from fat_bse_po_risk_detail a11
> join prt_lu_product a12
> on (a11.Product_id = a12.Product_id)
> join POt_lu_policy a13
> on (a11.Policy_id = a13.Policy_id)
> join vht_lu_vehicle a14
> on (a11.Vehicle_id = a14.Vehicle_id)
> join ITv_lu_day a15
> on (a11.Inception_date_id = a15.Inception_date_id)
> where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a11.Tr_sub_type_id in (430, 433, 3530)
> and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
> and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Pr_Group_id in (2, 3)
> and a11.f_Ren_Flag = '4'
> and a11.Po_tr_bus_type_id > 0)
> group by a14.Vh_VhAll_group_id,
> a15.ITv_year_id
>

Query Performance (am I in the ms right forum for performance Q's

I get a massive bookmark in the execution plans when I run the below SQL to
return 23 rows of data...The volumens are as below. If I take out the
reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark disappears
and I get drastically improved performance. a11 is well indexed. A new index
was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
[dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id]) ON [PRIMARY]
GO
and it has provided many other performance gains on a number of other pieces
of SQL. How do I begin to think about/code for this lack of performance.
Don't worry I don't expect you to understand the tables or business but if
any experiences have been overcome please post.
select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
select count(*) from prt_lu_product(nolock) -- Rows: 1719900
select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
select count(*) from ITv_lu_day(nolock) -- Rows: 4831
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Duration: 0:05:02.00 - 23 rows
select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
a15.ITv_year_id year_id,
count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
a11.Policy_id end)) WJXBFS1
into #ZZT5J0300BKMD00J
from fat_bse_po_risk_detail a11
join prt_lu_product a12
on (a11.Product_id = a12.Product_id)
join POt_lu_policy a13
on (a11.Policy_id = a13.Policy_id)
join vht_lu_vehicle a14
on (a11.Vehicle_id = a14.Vehicle_id)
join ITv_lu_day a15
on (a11.Inception_date_id = a15.Inception_date_id)
where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a11.Tr_sub_type_id in (430, 433, 3530)
and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Pr_Group_id in (2, 3)
and a11.f_Ren_Flag = '4'
and a11.Po_tr_bus_type_id > 0)
group by a14.Vh_VhAll_group_id,
a15.ITv_year_id
I get a massive bookmark in the execution plans when I run the below SQL to
return 23 rows of data...The volumens are as below. If I take out the
reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark disappears
and I get drastically improved performance. a11 is well indexed. A new index
was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
[dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id]) ON [PRIMARY]
GO
and it has provided many other performance gains on a number of other pieces
of SQL. How do I begin to think about/code for this lack of performance.
Don't worry I don't expect you to understand the tables or business but if
any experiences have been overcome please post.
select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
select count(*) from prt_lu_product(nolock) -- Rows: 1719900
select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
select count(*) from ITv_lu_day(nolock) -- Rows: 4831
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Duration: 0:05:02.00 - 23 rows
select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
a15.ITv_year_id year_id,
count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
a11.Policy_id end)) WJXBFS1
into #ZZT5J0300BKMD00J
from fat_bse_po_risk_detail a11
join prt_lu_product a12
on (a11.Product_id = a12.Product_id)
join POt_lu_policy a13
on (a11.Policy_id = a13.Policy_id)
join vht_lu_vehicle a14
on (a11.Vehicle_id = a14.Vehicle_id)
join ITv_lu_day a15
on (a11.Inception_date_id = a15.Inception_date_id)
where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a11.Tr_sub_type_id in (430, 433, 3530)
and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Pr_Group_id in (2, 3)
and a11.f_Ren_Flag = '4'
and a11.Po_tr_bus_type_id > 0)
group by a14.Vh_VhAll_group_id,
a15.ITv_year_id
This query involves many tables, of which you posted no information
(other than the row count). You also did not post or properly describe
the query plan. So there is not enough information to give detailed
advice.
If removing the predicate "a11.Tr_sub_type_id in (430, 433, 3530)"
increases the performance dramatically, then check out which index is
used (for that query), and add Tr_sub_type_id to this index (or create a
new index with this definition).
The query may benefit from a nonclustered index on
POt_lu_policy(Policy_id,Po_corp_unit_id)
You mention that you added an index to the TRt_lu_Trans_Subtype table,
but this seems irrelevant, since this table is not used in the query.
You could also consider running the Index Tuning Wizard.
Other generic advice:
- Make sure your statistics are up to date
- Make sure the expressions in a join clause have the same data type
definition
- Pay special attention to the clustered index definition of the largest
table
Hope this helps,
Gert-Jan
marcmc wrote:
> I get a massive bookmark in the execution plans when I run the below SQL to
> return 23 rows of data...The volumens are as below. If I take out the
> reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark disappears
> and I get drastically improved performance. a11 is well indexed. A new index
> was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
> [dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id]) ON [PRIMARY]
> GO
> and it has provided many other performance gains on a number of other pieces
> of SQL. How do I begin to think about/code for this lack of performance.
> Don't worry I don't expect you to understand the tables or business but if
> any experiences have been overcome please post.
> select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
> select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
> select count(*) from prt_lu_product(nolock) -- Rows: 1719900
> select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
> select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
> select count(*) from ITv_lu_day(nolock) -- Rows: 4831
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> -- Duration: 0:05:02.00 - 23 rows
> select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
> a15.ITv_year_id year_id,
> count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
> a11.Policy_id end)) WJXBFS1
> into #ZZT5J0300BKMD00J
> from fat_bse_po_risk_detail a11
> join prt_lu_product a12
> on (a11.Product_id = a12.Product_id)
> join POt_lu_policy a13
> on (a11.Policy_id = a13.Policy_id)
> join vht_lu_vehicle a14
> on (a11.Vehicle_id = a14.Vehicle_id)
> join ITv_lu_day a15
> on (a11.Inception_date_id = a15.Inception_date_id)
> where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a11.Tr_sub_type_id in (430, 433, 3530)
> and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
> and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Pr_Group_id in (2, 3)
> and a11.f_Ren_Flag = '4'
> and a11.Po_tr_bus_type_id > 0)
> group by a14.Vh_VhAll_group_id,
> a15.ITv_year_id
>
> I get a massive bookmark in the execution plans when I run the below SQL to
> return 23 rows of data...The volumens are as below. If I take out the
> reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark disappears
> and I get drastically improved performance. a11 is well indexed. A new index
> was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
> [dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id]) ON [PRIMARY]
> GO
> and it has provided many other performance gains on a number of other pieces
> of SQL. How do I begin to think about/code for this lack of performance.
> Don't worry I don't expect you to understand the tables or business but if
> any experiences have been overcome please post.
> select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
> select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
> select count(*) from prt_lu_product(nolock) -- Rows: 1719900
> select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
> select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
> select count(*) from ITv_lu_day(nolock) -- Rows: 4831
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> -- Duration: 0:05:02.00 - 23 rows
> select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
> a15.ITv_year_id year_id,
> count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
> a11.Policy_id end)) WJXBFS1
> into #ZZT5J0300BKMD00J
> from fat_bse_po_risk_detail a11
> join prt_lu_product a12
> on (a11.Product_id = a12.Product_id)
> join POt_lu_policy a13
> on (a11.Policy_id = a13.Policy_id)
> join vht_lu_vehicle a14
> on (a11.Vehicle_id = a14.Vehicle_id)
> join ITv_lu_day a15
> on (a11.Inception_date_id = a15.Inception_date_id)
> where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a11.Tr_sub_type_id in (430, 433, 3530)
> and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
> and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Pr_Group_id in (2, 3)
> and a11.f_Ren_Flag = '4'
> and a11.Po_tr_bus_type_id > 0)
> group by a14.Vh_VhAll_group_id,
> a15.ITv_year_id
>

Query Performance

Hello SQL Gurus,
From the query below, I am using 2 TOP functions to return the desired row. I am wondering if someone can shed some light on how to AVOID using 2 TOP statements and combine into just one select query?

select TOP 1 * from (select TOP 2 Num from A order by Num) X order by Num desc

Truly Appreciate your help as this performance issue has been bugging in my head for quite some time...

Sincerely,
-Lawrence

You could write it like below:

select min(num)

from A

where num > (select min(num) from A)

-- or

select max(num) from (select top 1 num from A order by num) X

But your TOP query should perform fine if you have an index on Num column. Can you compare above queries with yours and see if there is any difference? You can compare the execution plan in query analyzer.

Query performance

The below query seems to be very slow :
select distinct a.* from test a inner join test1 b on b.col1 = a.col1 inner join test2 c on c.col2 = a.col2 where exists (select NULL from test3 d where (d.col3 = a.col3 or a.col3 is null))
All the columns involved in the WHERE clause and JOIN conditions have index. Is there any alternative available for the above which can increase the performance ?
Please advice,
Thanks,
Smitha

The first thing you need to do is get rid of the "distinct" keyword - that alone will slow things down considerably. If you have duplicate rows in your table then you should remove them. If the number of occurances of a duplicate row is important to your application, then add that as a column, and eliminate the duplicates.

The other thing it'd seem to me to help, although I haven't tested it, would be to replace your WHERE EXISTS clause with an outer join with test3 on d.col3=a.col3. I don't know your data though, so I'm not sure of the effect of that change.

|||

How slow is 'very slow'...?
How much data is there involved in the tables?
What does the query plan look like?
Which table or tables shows the largest amount of data that's been worked on?

For an alternative, you need to provide some sample data along with the desired result and a brief explanation of what the query is supposed to do.

/Kenneth

|||The other thing you would have to get rid of is the a.*, retrieve only the columns you really need.

Jose Luis
|||

What does the query plan look like?

First are you sure you need to do a DISTINCT?

See these links for some overview

http://www.sql-server-performance.com/transact_sql.asp

http://www.sql-server-performance.com/nb_select_distinct.asp

sql

Query Performance

Hi Guys,

I have a very big Database...
And This query below is taking up to 2 minutes to complete.

Some suggestions about how to make it better?

Select
s.InvcNbr Numero_nota,
v.cpnyid Cod_CRP,
v.cpnyname Nome_CRP,
i.invtid Cod_Produto,
i.descr Nome_Produto,
p.classid Cod_Classe,
p.descr Nome_Classe,
t.drcr Natureza,
t.acct Cod_Conta,
c.descr Nome_Conta,
t.sub Cod_SubConta,
su.descr Nome_SubConta,
s.custid Cod_Cliente,
s.billname Nome_Cliente,
sl.QtyPick Quantidade,
sl.CurySlsPrice Preco,
sl.curytotinvc Total,
s.OrdDate Data
from soshipline sl
left outer join soshipheader s
on ( s.shipperid = sl.shipperid and s.cpnyId = sl.cpnyId )
inner join vs_company v on s.cpnyid = v.cpnyid
inner join artran t on t.batnbr = s.arbatnbr and
t.cpnyid = s.cpnyid and
t.custid = s.custid and
t.invtid = sl.invtid
inner join inventory i on i.invtid = sl.invtid
inner join productclass p on p.classid = i.classid
inner join account c on c.acct = t.acct
inner join subacct su on su.sub = t.sub
where
t.acct like '3%'
and t.rlsed = 1
and s.User7 <> 'CANC'
and( rtrim( sl.shipperId ) + rtrim( sl.CpnyId ) + rtrim( sl.LineRef )
not In ( select rtrim( a.Origshipperid ) + rtrim( a.CpnyId ) + rTrim( a.LineRef )
from soshipLINE a inner Join soshipheader b on a.shipperid = b.shipperid
and a.cpnyId = b.CpnyId where b.user7 = 'CANC' ) )first of all, see your indexes

2-you have to many joins, look for if some tables you can make a subquery insted of a join, but only the tables that the where clause you can assure will seek by the PK, clustered index and will return only 1 row back.

3-you must have in mind that the principal select must limit your range of rows at maximun, so the subqueries will have less rows to look for.

4-try using set force plan if the principal query is not using the index u want to be used.

5-still try set showplan_all on for a better check of how the query is going to run.

hope i have helped you...

regards !!!

Originally posted by Diogo
Hi Guys,

I have a very big Database...
And This query below is taking up to 2 minutes to complete.

Some suggestions about how to make it better?

Select
s.InvcNbr Numero_nota,
v.cpnyid Cod_CRP,
v.cpnyname Nome_CRP,
i.invtid Cod_Produto,
i.descr Nome_Produto,
p.classid Cod_Classe,
p.descr Nome_Classe,
t.drcr Natureza,
t.acct Cod_Conta,
c.descr Nome_Conta,
t.sub Cod_SubConta,
su.descr Nome_SubConta,
s.custid Cod_Cliente,
s.billname Nome_Cliente,
sl.QtyPick Quantidade,
sl.CurySlsPrice Preco,
sl.curytotinvc Total,
s.OrdDate Data
from soshipline sl
left outer join soshipheader s
on ( s.shipperid = sl.shipperid and s.cpnyId = sl.cpnyId )
inner join vs_company v on s.cpnyid = v.cpnyid
inner join artran t on t.batnbr = s.arbatnbr and
t.cpnyid = s.cpnyid and
t.custid = s.custid and
t.invtid = sl.invtid
inner join inventory i on i.invtid = sl.invtid
inner join productclass p on p.classid = i.classid
inner join account c on c.acct = t.acct
inner join subacct su on su.sub = t.sub
where
t.acct like '3%'
and t.rlsed = 1
and s.User7 <> 'CANC'
and( rtrim( sl.shipperId ) + rtrim( sl.CpnyId ) + rtrim( sl.LineRef )
not In ( select rtrim( a.Origshipperid ) + rtrim( a.CpnyId ) + rTrim( a.LineRef )
from soshipLINE a inner Join soshipheader b on a.shipperid = b.shipperid
and a.cpnyId = b.CpnyId where b.user7 = 'CANC' ) )|||After you check all the indexes and the execution plan...

One area that may be slowing you down is this:

and( rtrim( sl.shipperId ) + rtrim( sl.CpnyId ) + rtrim( sl.LineRef )
not In ( select rtrim( a.Origshipperid ) + rtrim( a.CpnyId ) + rTrim( a.LineRef )
from soshipLINE a inner Join soshipheader b on a.shipperid = b.shipperid
and a.cpnyId = b.CpnyId where b.user7 = 'CANC' ) )

Try changing it to a NOT EXISTS test as follows:

and NOT EXISTS ( select *
from soshipLINE a inner Join soshipheader b on a.shipperid = b.shipperid and a.cpnyId = b.CpnyId and b.user7 = 'CANC'
where a.Origshipperid = sl.shipperId and a.CpnyId = sl.CpnyId
and a.LineRef = sl.LineRef )

btw... what version of SQL Server are you on?|||Hey Guys.
Thanx all!

I replace NOT IN with NOT EXISTS, and looke all steps you have suggested me.

Now,
The quey dont`t take 18 seconds!!

Thank Leandro and HueyStLoui!sql

Query Performance

I get a massive bookmark in the execution plans when I run the below SQL to
return 23 rows of data...The volumens are as below. If I take out the
reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark disappears
and I get drastically improved performance. a11 is well indexed. A new index
was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
[dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id]) ON [PRIMARY]
GO
and it has provided many other performance gains on a number of other pieces
of SQL. How do I begin to think about/code for this lack of performance.
Don't worry I don't expect you to understand the tables or business but if
any experiences have been overcome please post.
select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
select count(*) from prt_lu_product(nolock) -- Rows: 1719900
select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
select count(*) from ITv_lu_day(nolock) -- Rows: 4831
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Duration: 0:05:02.00 - 23 rows
select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
a15.ITv_year_id year_id,
count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
a11.Policy_id end)) WJXBFS1
into #ZZT5J0300BKMD00J
from fat_bse_po_risk_detail a11
join prt_lu_product a12
on (a11.Product_id = a12.Product_id)
join POt_lu_policy a13
on (a11.Policy_id = a13.Policy_id)
join vht_lu_vehicle a14
on (a11.Vehicle_id = a14.Vehicle_id)
join ITv_lu_day a15
on (a11.Inception_date_id = a15.Inception_date_id)
where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a11.Tr_sub_type_id in (430, 433, 3530)
and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Pr_Group_id in (2, 3)
and a11.f_Ren_Flag = '4'
and a11.Po_tr_bus_type_id > 0)
group by a14.Vh_VhAll_group_id,
a15.ITv_year_id
I get a massive bookmark in the execution plans when I run the below SQL to
return 23 rows of data...The volumens are as below. If I take out the
reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark disappears
and I get drastically improved performance. a11 is well indexed. A new index
was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
[dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id]) ON [PRIMARY]
GO
and it has provided many other performance gains on a number of other pieces
of SQL. How do I begin to think about/code for this lack of performance.
Don't worry I don't expect you to understand the tables or business but if
any experiences have been overcome please post.
select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
select count(*) from prt_lu_product(nolock) -- Rows: 1719900
select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
select count(*) from ITv_lu_day(nolock) -- Rows: 4831
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Duration: 0:05:02.00 - 23 rows
select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
a15.ITv_year_id year_id,
count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
a11.Policy_id end)) WJXBFS1
into #ZZT5J0300BKMD00J
from fat_bse_po_risk_detail a11
join prt_lu_product a12
on (a11.Product_id = a12.Product_id)
join POt_lu_policy a13
on (a11.Policy_id = a13.Policy_id)
join vht_lu_vehicle a14
on (a11.Vehicle_id = a14.Vehicle_id)
join ITv_lu_day a15
on (a11.Inception_date_id = a15.Inception_date_id)
where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a11.Tr_sub_type_id in (430, 433, 3530)
and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Pr_Group_id in (2, 3)
and a11.f_Ren_Flag = '4'
and a11.Po_tr_bus_type_id > 0)
group by a14.Vh_VhAll_group_id,
a15.ITv_year_id
Marc,
reconstruct you query, not using a11.Tr_sub_type_id in (430, 433, 3530), but
use a union (all), each with its own a11.Tr_sub_type_id = condition.
Quentin
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:19F88AA4-D9D6-4B1C-BAAD-A8DE2259F182@.microsoft.com...
>I get a massive bookmark in the execution plans when I run the below SQL to
> return 23 rows of data...The volumens are as below. If I take out the
> reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark
> disappears
> and I get drastically improved performance. a11 is well indexed. A new
> index
> was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
> [dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id]) ON [PRIMARY]
> GO
> and it has provided many other performance gains on a number of other
> pieces
> of SQL. How do I begin to think about/code for this lack of performance.
> Don't worry I don't expect you to understand the tables or business but if
> any experiences have been overcome please post.
>
> select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
> select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
> select count(*) from prt_lu_product(nolock) -- Rows: 1719900
> select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
> select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
> select count(*) from ITv_lu_day(nolock) -- Rows: 4831
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> -- Duration: 0:05:02.00 - 23 rows
> select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
> a15.ITv_year_id year_id,
> count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
> a11.Policy_id end)) WJXBFS1
> into #ZZT5J0300BKMD00J
> from fat_bse_po_risk_detail a11
> join prt_lu_product a12
> on (a11.Product_id = a12.Product_id)
> join POt_lu_policy a13
> on (a11.Policy_id = a13.Policy_id)
> join vht_lu_vehicle a14
> on (a11.Vehicle_id = a14.Vehicle_id)
> join ITv_lu_day a15
> on (a11.Inception_date_id = a15.Inception_date_id)
> where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a11.Tr_sub_type_id in (430, 433, 3530)
> and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
> and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Pr_Group_id in (2, 3)
> and a11.f_Ren_Flag = '4'
> and a11.Po_tr_bus_type_id > 0)
> group by a14.Vh_VhAll_group_id,
> a15.ITv_year_id
>
>
> I get a massive bookmark in the execution plans when I run the below SQL
> to
> return 23 rows of data...The volumens are as below. If I take out the
> reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark
> disappears
> and I get drastically improved performance. a11 is well indexed. A new
> index
> was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
> [dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id]) ON [PRIMARY]
> GO
> and it has provided many other performance gains on a number of other
> pieces
> of SQL. How do I begin to think about/code for this lack of performance.
> Don't worry I don't expect you to understand the tables or business but if
> any experiences have been overcome please post.
>
> select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
> select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
> select count(*) from prt_lu_product(nolock) -- Rows: 1719900
> select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
> select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
> select count(*) from ITv_lu_day(nolock) -- Rows: 4831
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> -- Duration: 0:05:02.00 - 23 rows
> select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
> a15.ITv_year_id year_id,
> count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
> a11.Policy_id end)) WJXBFS1
> into #ZZT5J0300BKMD00J
> from fat_bse_po_risk_detail a11
> join prt_lu_product a12
> on (a11.Product_id = a12.Product_id)
> join POt_lu_policy a13
> on (a11.Policy_id = a13.Policy_id)
> join vht_lu_vehicle a14
> on (a11.Vehicle_id = a14.Vehicle_id)
> join ITv_lu_day a15
> on (a11.Inception_date_id = a15.Inception_date_id)
> where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a11.Tr_sub_type_id in (430, 433, 3530)
> and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
> and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Pr_Group_id in (2, 3)
> and a11.f_Ren_Flag = '4'
> and a11.Po_tr_bus_type_id > 0)
> group by a14.Vh_VhAll_group_id,
> a15.ITv_year_id
>