Query process time

I have a weird issue that I just can't figure out. I have a query that runs on tables that are all located in db x. If I run the query under a different db (like use database y) then it runs in like 11 seconds. If I run the query under the db that all of the tables reside under (use database x) then it takes almost 4 minutes.

Why would it take so much longer to run under the db where all of the data/tables exists? Both dbs are on the same SQL 2005 server.

I just don't get it?

Any ideas?

S

We can't possibly give you a meaningful response without having more information. What is the query, what is the nature of the data, what is different between the two databases, etc.

Perhaps, using your description about, database X has data in it, and database Y does not?

|||If you think that execution plan will be same then why SQl statement take different time on different server. But have you checked the CPU Cost and I/O cost.

Query problem using groups

Hi
Is there any way to run a query on a pair of related tables with a one
to many relationship such that for each parent record you include the
details of just one child
e.g. Employees contains a foreign key to the identity of Departments
I want to select the department name and the first employee in the
database associated with that department
The query below would select all employees for all departments
SELECT deptname, surname, firstname from Departments inner join
Employees on Department.DeptId = Employee.DebtId
I need to select just the first employee.
Any ideas?
Thanks
Damien
How do you define which is the "first" emloyee? (Longest serving maybe? Or
most senior?) Tables have no inherent order so you have to define "first"
based on the information in your data. You could use the date they started:
SELECT deptname, surname, firstname
FROM Departments AS D
INNER JOIN Employees AS E
ON D.deptid = E.deptid
AND E.date_started =
(SELECT MIN(date_started)
FROM Employees
WHERE deptid = D.deptid)
but that may still give multiple rows if more than one person started on the
same day. Maybe you just want ANY one employee but don't really care which.
In that case just use the primary key of the Employees table in place of the
date in this query (assuning you have a single-column key).
Hope this helps.
David Portas
SQL Server MVP
|||Yes that solves it for me, thank you.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Query problem using groups

Hi
Is there any way to run a query on a pair of related tables with a one
to many relationship such that for each parent record you include the
details of just one child
e.g. Employees contains a foreign key to the identity of Departments
I want to select the department name and the first employee in the
database associated with that department
The query below would select all employees for all departments
SELECT deptname, surname, firstname from Departments inner join
Employees on Department.DeptId = Employee.DebtId
I need to select just the first employee.
Any ideas?
Thanks
DamienHow do you define which is the "first" emloyee? (Longest serving maybe? Or
most senior?) Tables have no inherent order so you have to define "first"
based on the information in your data. You could use the date they started:
SELECT deptname, surname, firstname
FROM Departments AS D
INNER JOIN Employees AS E
ON D.deptid = E.deptid
AND E.date_started = (SELECT MIN(date_started)
FROM Employees
WHERE deptid = D.deptid)
but that may still give multiple rows if more than one person started on the
same day. Maybe you just want ANY one employee but don't really care which.
In that case just use the primary key of the Employees table in place of the
date in this query (assuning you have a single-column key).
Hope this helps.
--
David Portas
SQL Server MVP
--

Query problem - top 5 validation

Hi,
I have a problem to run a query like this scenario:
i have players in a table who have scored in many matches and i want to take
the 5 latest.
The latest i want to multiply with 1.0 and then after with 0.8, 0.6, 0.4 and
finally 0.2.
playerid round score
1, 10, 122
1, 9, 123
1, 8, 222
1, 7, 333
1, 6, 222
And i want to multiply 122 * 1 + 123*0,8 + 222*0,6 + 333*0,4 + 222*0,2
Even handle if there are only 3 score.
Help, who can i do that in sql?
Regards
TWHi
It is always better to post DDL and example data see
http://www.aspfaq.com/etiquett?e.asp?id=5006
If the relationship between rounds is constant then you can use something
like:
CREATE TABLE #scores ( playerid int, [round] int, score decimal(8,3))
INSERT INTO #scores ( playerid, [round], score )
SELECT 1, 10, 122
UNION ALL SELECT 1, 9, 123
UNION ALL SELECT 1, 8, 222
UNION ALL SELECT 1, 7, 333
UNION ALL SELECT 1, 6, 222
INSERT INTO #scores ( playerid, [round], score )
SELECT 2, 9, 111
UNION ALL SELECT 2, 8, 222
UNION ALL SELECT 2, 7, 333
SELECT p.playerid,SUM(p.score*(1-(0.2*p.rank)))
FROM ( SELECT playerid,score,
(SELECT COUNT(*) FROM #scores s where s.[round] > r.[round] AND s.playerid =
r.playerid) as Rank
from #scores r ) p
where p.rank < 6
GROUP BY p.playerid
Alternatively use a table to store the multiplying factor.
John
"tw" <tw@.tactics.se> wrote in message
news:uyI$86lTFHA.616@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a problem to run a query like this scenario:
> i have players in a table who have scored in many matches and i want to
> take the 5 latest.
> The latest i want to multiply with 1.0 and then after with 0.8, 0.6, 0.4
> and finally 0.2.
> playerid round score
> 1, 10, 122
> 1, 9, 123
> 1, 8, 222
> 1, 7, 333
> 1, 6, 222
> And i want to multiply 122 * 1 + 123*0,8 + 222*0,6 + 333*0,4 + 222*0,2
> Even handle if there are only 3 score.
> Help, who can i do that in sql?
> Regards
> TW
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Here is my guess
CREATE TABLE Scorecards
(player_id INTEGER NOT NULL,
round_nbr INTEGER NOT NULL,
PRIMARY KEY (player_id, round_nbr),
score INTEGER NOT NULL);
INSERT INTO Scorecards VALUES (1, 10, 122);
INSERT INTO Scorecards VALUES (1, 9, 123);
INSERT INTO Scorecards VALUES (1, 8, 222);
INSERT INTO Scorecards VALUES (1, 7, 333);
INSERT INTO Scorecards VALUES (1, 6, 222);
INSERT INTO Scorecards VALUES (1, 5, 222);
SELECT W.player_id, SUM(W.weighted_score)
FROM (
SELECT S1.player_id,
(S1.score
* CASE WHEN round_nbr
= (SELECT MAX(round_nbr)
FROM Scorecards AS S2
WHERE S1.player_id = S2.player_id)
THEN 1.0
WHEN round_nbr
= (SELECT MAX(round_nbr)-1
FROM Scorecards AS S2
WHERE S1.player_id = S2.player_id)
THEN 0.8
WHEN round_nbr
= (SELECT MAX(round_nbr)-2
FROM Scorecards AS S2
WHERE S1.player_id = S2.player_id)
THEN 0.6
WHEN round_nbr
= (SELECT MAX(round_nbr)-3
FROM Scorecards AS S2
WHERE S1.player_id = S2.player_id)
THEN 0.4
WHEN round_nbr
= (SELECT MAX(round_nbr)-4
FROM Scorecards AS S2
WHERE S1.player_id = S2.player_id)
THEN 0.2 ELSE 0.0 END)
FROM Scorecards AS S1)
AS W(player_id, weighted_score)
GROUP BY W.player_id;
Put this in a VIEW so the system will update it for you. You can also
use the proprietary, non-portable TOP option if you do not care about
standards. It might be faster in SQL Server, but a good optimizer
would pull out the common expressions in the WHEN clauses and compute
each MAX() only once, probably getting it from an index on the
primarykey. TOP has to do a partition or sort under the covers.|||Hi John,
Im very thankful of your sql code, it works perfect.
Sorry about not post dll, it was new for me.
How should the sql string look if i have a table with multiplying
factor?
tw
*** Sent via Developersdex http://www.examnotes.net ***|||Hi
You would need to add table (this one if temporary for demonstration)
CREATE TABLE #Multipliers ( Rank int, factor decimal(8,3) )
INSERT INTO #Multipliers ( Rank, factor )
SELECT 1, 1
UNION ALL SELECT 2, 0.8
UNION ALL SELECT 3, 0.6
And change the query to something like:
SELECT p.playerid,SUM(p.score*m.factor)
FROM ( SELECT playerid,score,
(SELECT COUNT(*) FROM #scores s where s.[round] > r.[round] AND s.playerid =
r.playerid) as Rank
from #scores r ) p
JOIN #Multipliers m ON p.rank = m.rank -- restricted by number of entries
in #Multipliers
GROUP BY p.playerid
You could also fit this into Joes solution.
John
"t w" <tw@.tactics.se> wrote in message
news:uVASgDnTFHA.3980@.TK2MSFTNGP12.phx.gbl...
> Hi John,
> Im very thankful of your sql code, it works perfect.
> Sorry about not post dll, it was new for me.
> How should the sql string look if i have a table with multiplying
> factor?
> tw
> *** Sent via Developersdex http://www.examnotes.net ***

query problem

I have a table (Product) and a Column (ReleaseDate)

and trying to run this query

select Product =
CASE ReleaseDate

WHEN datediff(d,ReleaseDate,getutcdate()) < 30 THEN 'New'
WHEN datediff(d,ReleaseDate,getutcdate()) < 90 THEN 'Recent'
ELSE 'Normal' END

and get this error

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '<'.

any ideas

cheers!!!

Change to:

select Product=

CASEWHENdatediff(d,ReleaseDate,getutcdate())< 30THEN'New'

WHENdatediff(d,ReleaseDate,getutcdate())< 90THEN'Recent'

ELSE'Normal'END

FROM product

|||Thanks for that.. i got it sorted

query problem

Hi,
I have a problem to run a query like this scenario:
i have players in a table who have scored in many matches and i want to take
the 5 latest.
The latest i want to multiply with 1.0 and then after with 0.8, 0.6, 0.4 and
finally 0.2.
playerid round score
1, 10, 122
1, 9, 123
1, 8, 222
1, 7, 333
1, 6, 222
And i want to multiply 122 * 1 + 123*0,8 + 222*0,6 + 333*0,4 + 222*0,2
Even handle if there are only 3 score.
Help, who can i do that in sql?
Regards
TW
On Sun, 1 May 2005 17:05:50 +0200, tw wrote:

>Hi,
>I have a problem to run a query like this scenario:
>i have players in a table who have scored in many matches and i want to take
>the 5 latest.
>The latest i want to multiply with 1.0 and then after with 0.8, 0.6, 0.4 and
>finally 0.2.
>playerid round score
>1, 10, 122
>1, 9, 123
>1, 8, 222
>1, 7, 333
>1, 6, 222
>And i want to multiply 122 * 1 + 123*0,8 + 222*0,6 + 333*0,4 + 222*0,2
>Even handle if there are only 3 score.
>Help, who can i do that in sql?
>Regards
>TW
>
Hi TW,
The following is untested. Please see www.aspfaq.com/5006 to find out
what you should post to enable me to test my suggestions:
SELECT s.playerid, SUM (f.factor * s.score)
FROM ScoreTable AS s
INNER JOIN (SELECT 1, 1.0 UNION ALL
SELECT 2, 0.8 UNION ALL
SELECT 3, 0.6 UNION ALL
SELECT 4, 0.4 UNION ALL
SELECT 5, 0.2) AS f(round, factor)
ON round = (SELECT COUNT(*)
FROM ScoreTable AS s2
WHERE s2.playerid = s.playerid
AND s2.round >= s.round)
GROUP BY s.playerid
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Query posting wrong information

Why does this query post the wrong total_elapsed_time? Note you need to run
this query twice to see results.
select execution_count abc_exe_count,
total_elapsed_time,
qt.sql_handle,
last_execution_time,
statement_end_offset, text
from sys.dm_exec_query_stats qt
cross apply sys.dm_exec_sql_text(qt.sql_handle)
where text like '%abc_exe_count%'
order by last_execution_time descGreg,
Time is in microseconds on 2005, not milliseconds. When I run it, the
answer looks about right.
RLF
"Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
news:AB907027-DBE1-40E6-A172-C9E2794EDE7F@.microsoft.com...
> Why does this query post the wrong total_elapsed_time? Note you need to
> run
> this query twice to see results.
> select execution_count abc_exe_count,
> total_elapsed_time,
> qt.sql_handle,
> last_execution_time,
> statement_end_offset, text
> from sys.dm_exec_query_stats qt
> cross apply sys.dm_exec_sql_text(qt.sql_handle)
> where text like '%abc_exe_count%'
> order by last_execution_time desc|||Thanks for the answer. No matter how many times I read the BOL, I also read
milli, instead of micro.
"Russell Fields" wrote:
> Greg,
> Time is in microseconds on 2005, not milliseconds. When I run it, the
> answer looks about right.
> RLF
> "Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
> news:AB907027-DBE1-40E6-A172-C9E2794EDE7F@.microsoft.com...
> > Why does this query post the wrong total_elapsed_time? Note you need to
> > run
> > this query twice to see results.
> >
> > select execution_count abc_exe_count,
> > total_elapsed_time,
> > qt.sql_handle,
> > last_execution_time,
> > statement_end_offset, text
> > from sys.dm_exec_query_stats qt
> > cross apply sys.dm_exec_sql_text(qt.sql_handle)
> > where text like '%abc_exe_count%'
> > order by last_execution_time desc
>
>

Query Plan in SQL Server 2005 SP2

Hello,

The query included at the end of this post seems to use the 'wrong' index when executing and takes 2+ minutes to run. When I provide an index hint, it runs in under 1 second. This happens in both the Production and Development environments; both servers run SQL Server 2005 SP2.

When I run both the queries together in Query Analyzer (or SSMS), the Query Cost (relative to the batch) value for the query with the index hint = 83.24%.

Profiler stats for Original Query:

Duration = 130484 ms

CPU = 111141 ms

Reads = 85470

Profiler stats for Index Hint Query:

Duration = 64 ms

CPU = 687 ms

Reads = 5558

Statistics are updated every night (w/Fullscan)

Indexes are rebuilt/defragmented daily based on fragmentation levels.

What am I missing here/how do I fix this performance issue?

Doesn't Profiler take into account the "Subtree Cost" for the Index Seek (which Query Analyzer & SSMS consider to be so expensive)? What Events/Columns do I need to include in the Profiler trace to see this statistic when the query is executing?

Thanks much,

Smitha

QUERY:

select min(AccessLevel)
from Groups-- WITH(INDEX = ByClassID)
where name = 'Student Leader'
and classid in (2067,2063,2069,2070,2079,2072,2073,2074,2075,2076,2077,2073,2079,2030,2039,2032,201,2034,2035,2036,2037,201,2039,2090,
202,2092,2093,2094,2095,2096,2097,2093,202,24,2909,2902,2903,2904,2905,25,2907,2903,2909,220,229,222,223,224,225,226,227,223,229,2920,2929,2922,2923,2924,22,2926,2927,2923,2929,2930,26,2932,291,2934,2935,28,2937,291,
26,2940,2949,211,2943,2944,2945,2946,2947,2943,2949,27,2959,2952,2953,2954,2955,2956,2957,2953,2959,2960,2969,2962,2963,2964,2965,2966,2967,2963,2969,2970,2979,2972,2973,2974,2975,2976,2977,2973,2979,2930,26,2932,291,2934,2935,28,
2937,291,26,220,229,222,223,224,225,226,227,223,229,2200,2209,2202,2203,2204,2205,34736,34739,34749,34742,34743,34744,34745,34746,34747,34743,34749,3471,34759,34392,34393,34396,34397,34962,34937,3491,346,3420,3429,3423,3424,35360,35695,35696,35709,
1056,1057,1223,4107,1256,1257,1259,1269,1262,1263,1265,1267,1263,1269,1270,1272,1273,1274,1275,1276,1277,1273,1279,1230,1239,1232,121,1234,1235,1236,1237,121,1290,122,1292,1293,1294,1295,1296,1293,122,1300,1309,1302,1303,1304,1305,1306,
1307,1494,1459,1469,1462,1466,1467,1469,1470,1472,1474,1475,1477,1432,141,1435,1436,1437,1439,1494,1496,1497,142,113,115,117,113,119,1590,152,1592,1593,1594,1595,1596,1593,152,1520,1529,1522,1523,1524,1525,1527,1539,151,1534,1536,
151,1540,1543,1544,1545,1547,1572,1573,1574,1576,1577,1573,1579,1539,53969,64763,67735,67736,63967,1529,1530,1532,151,1534,1535,1536,1537,151,1539,1540,1549,1542,1543,1545,1546,1547,1543,1549,151,1559,1553,1555,1557,1553,1559,1560,1569,1562,
1563,1564,1565,1566,1567,1563,1569,1570,1579,1572,1573,1574,1575,1576,1577,1573,1579,1530,1539,1532,151,1534,1535,1536,152,1609,1602,1603,1604,1605,1606,1607,1603,1609,1690,162,1692,1693,1694,1695,1696,1697,1693,162,1629,1622,1635,1637,
1640,1642,1646,1649,1652,1657,1669,1664,1667,1679,1634,1637,1693,1703,1706,172,1795,1793,1722,1726,1764,1392,1393,1394,1395,1396,132,1320,1329,1322,1323,3629,3622,3623,3624,3625,3626,3627,3623,3629,36200,36209,36202,36203,36204,36205,36206,36207,
36203,36209,36290,37093,37094,37095,37096,37097,37093,3702,37020,37029,37022,37023,37024,37025,37026,37027,37023,37029,37030,37039,37032,3701,37034,37035,37036,37037,3701,37039,37040,37049,37042,37043,37044,37045,37046,37047,37043,37049,3701,37059,37052,37053,37054,37055,37056,37057,
37053,37059,37060,37069,37062,37063,37064,37065,37066,37067,37063,37069,37070,37079,37072,37073,37074,37075,37076,37077,37073,37079,37030,37039,37032,3701,37034,37035,37036,37037,3701,37039,37090,3702,37092,37093,37094,37095,37096,37097,37093,3702,374,37909,37902,37903,37904,37905,
375,37907,37903,37909,3720,3729,3722,3723,3724,37320,37329,37322,37323,37324,37325,37326,37327,37329,3710,3719,3712,3713,3714,3715,3716,3717,3713,37340,37349,37342,37343,37344,37345,37346,37347,37343,37349,3731,37359,37352,37353,37354,37356,37357,37353,37359,37360,37369,
37362,37363,37364,37365,37366,37367,37363,37369,3715,3716,3717,3713,3719,37340,37349,37342,37343,37344,37345,37346,37347,37343,37349,3731,37359,37352,37353,37354,37355,37356,37357,37353,37359,37360,37369,37362,37363,37364,37365,37366,37367,37363,37369,37370,37379,37372,37373,37374,
37375,37376,37377,37373,37379,3710,3719,3712,3713,3714,3715,3716,3717,3713,3719,37390,3732,37392,37393,37394,37395,37396,37397,37393,3732,374,37909,37902,37903,37904,37905,375,37907,37903,37909,3720,3729,3722,3723,3724,3725,3726,3727,3723,3729,37920,37929,37922,
37923,37924,372,37926,37927,37923,37929,37930,376,37932,3791,37934,37935,378,37937,3791,376,37940,37949,3711,37943,37944,37945,37946,37947,37943,37949,377,37959,37952,37953,37954,37955,37956,37957,37953,37959,37960,37969,37962,37963,37964,37965,37966,37967,37963,37969,37970,
37979,37972,37973,37974,37975,37976,37977,37973,37979,37930,376,37932,3791,37934,37935,378,37937,3791,376,3720,3729,3722,3723,3724,3725,3726,3727,3723,3729,1000,1009,1002,1003,1004,1005,1006,1007,1003,1009,1090,102,1092,1093,1094,1095,1096,1097,1093,
102,1020,1029,1022,1023,1024,1025,1026,1027,1023,1029,1030,1039,1032,101,1034,1035,1036,1037,101,1039,1040,1049,1042,1043,1044,1045,1046,1047,1043,1049,101,1059,1052,1053,1054,1055,1056,1057,1053,191,1934,1935,18,1937,191,16,1940,
1949,111,1943,1944,1945,1946,1947,1943,1949,17,1959,1952,1953,1954,1955,1956,1957,1953,1959,1960,1969,1962,1963,1964,1965,1966,1967,1963,1969,1970,1979,1972,1973,1974,1975,1976,1977,1973,1979,1930,16,1932,191,1934,1935,18,1937,191,
16,120,129,122,123,124,125,126,127,123,129,1200,1209,1202,1203,1204,1205,1206,1207,1203,1209,1290,122,1292,1293,1294,1295,1296,1297,1293,122,1220,1229,1222,1223,1224,1225,1226,1227,1223,1229,1230,1239,1232,121,1234,1235,1236,
1237,121,1239,1240,1249,1242,1243,1244,1245,1246,1247,1243,1249,121,1259,1252,1253,1254,1255,1256,1257,1253,1259,1260,1269,1262,1263,1264,1265,1266,1267,1263,1269,1270,1279,1272,1273,1274,1275,1276,1277,1273,1279,1230,1239,1232,121,1234,
1235,1236,1237,121,1239,1290,122,1292,1293,1294,1295,1296,1297,1293,122,1300,1309,1302,1303,1304,1305,1306,1307,1303,1309,1390,132,1392,1393,1394,1395,1396,1397,1393,132,1320,1329,1322,1323,1324,1325,1326,1327,1323,1329,110,119,112,
113,114,115,116,117,113,119,1340,1349,1342,1343,1344,1345,1346,1347,1343,1349,131,1359,1352,1353,1354,1355,1356,1357,1353,1359,1360,1369,1362,1363,1364,1365,1366,1367,1363,1369,1370,1379,1372,1373,1374,1375,1377,1373,1379,110,119,
112,113,115,116,117,113,119,132,1392,1393,1729,1723,9441,94497,94493,9442,9410,9419,9412,9413,9414,9415,9416,9417,9413,9419,94590,9452,94593,94594,94595,94596,94597,95576,97396,97909,93057,4640,4653,4654,4656,4657,4653,4659,4660,4669,4662,
4663,4664,4665,4666,4669,4670,4679,4672,4673,4675,4676,4677,4639,4632,461,4634,4635,4636,4637,4639,5290,522,5294,5295,5296,5370,90742,20953,20959,2239,2234,222,2292,2293,2294,2296,2297,222,29209,29204,29205,29206,
29207,29203,29209,29292,29294,29295,29297,29223,29225,29226,29223,29229,29230,29239,2921,29234,23903,23909,2320,24393,2521,25252,25253,25256,25253,25269,25262,25264,25266,25267,25269,25270,25279,25272,25273,25274,25276,25277,25273,25230,25239,25232,
25235,25237,2521,25239,25290,25292,25293,25294,2790,2793,272,2729,2749,2745,2746,271,2759,2753,2755,2753,2759,2760,2762,2764,2766,2769,2770,2779,2772,2777,93029,8747,8743,8749,871,8759,8752,8755,8757,8769,8764,8765,
8766,8769,8770,8779,8773,8774,8775,8776,8777,8773,8779,8730,8739,8732,871,8734,8739,8790,872,8792,8795,8797,8793,872,8300,8309,8303,8304,8306,8307,8309,8394,8396,8397,832,8322,8326,8323,810,819,812,813,
814,937952,6045,6097,93203,93206,619,617,632,6395,6400,6403,6406,6490,6497,6422,6425,6423,6432,6434,6435,6437,6457,6453,6459,6460,6463,6464,6465,6466,6469,6470,6472,6473,6475,6477,6473,6430,6432,6434,6435,6437,
641,6439,6490,642,6492,6493,6494,6495,6497,6493,642,9370,9379,9372,9373,9374,9375,9376,9377,9373,9379,6590,6592,6594,6593,6520,6523,6524,6527,6523,6529,6539,651,6534,6535,6536,6539,6540,6544,6545,6547,6549,
6559,6552,6553,6555,6556,6557,6553,6560,6569,6564,6565,6567,6563,6570,6579,6572,6573,6574,6575,6577,6573,6530,651,6534,651,6590,652,6592,6594,6596,6597,6593,652,6609,6632,661,6634,6635,6636,6637,661,6647,
6643,6659,6652,6653,6654,6655,6656,6657,6653,6659,6669,6663,6664,6665,6666,6667,6663,6670,6672,6673,6675,6676,6677,6630,6632,661,6636,661,6693,6694,6695,6696,6697,6693,6700,6702,6703,6704,11439,11440,11449,11442,
11443,11444,11669,11679,11676,11677,1161,11635,11637,11639,1162,11697,11693,11709,11709,11790,11723,11727,11732,11735,11737,11749,11744,11743,1171,11300,11306,11307,11309,1132,11977,94170,94179,94172,945454,945934,946464,946465,946466,94123,94123,94139,
94134,94135,94143,9411,94153,94155,94157,7239,7232,721,7692,7694,7696,7697,7693,7703,7704,7705,7329,7324,7326,714,716,719,7359,7352,7359,7363,7367,7379,719,712,713,714,715,716,717,713,719,7390,732,7392,
7393,7394,7395,7396,7397,7393,732,74,7909,7902,7903,7929,7924,7923,7937,7947,7959,7953,7955,7957,7959,7962,7963,7964,7966,7967,7963,7969,7932,791,7935,791,722,723,9549,9543,952600,952629,952627,952629,952657,952653,
952662,952690,952697,952797,95272,952720,952724,952729,954597,954964,955669,955790,95572,955792,95629,957069)

INDEX Definition:

CREATE INDEX ByClassID ON Groups(ClassID) INCLUDE(Name,AccessLevel)

You might want to look up some of Tom Phillips replies to a number of previous questions about lengthy IN conditions.

I'm afraid I didn't get good results. I found these two previous posts, but you have SP2 installed and I'm afraid that in this case I don't have much help to offer; sorry. :-(

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1525484&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=990564&SiteID=1

|||

Thanks Kent- I just read the posts...it's just that there is a better/faster way to execute the query but the optimzer doesn't seem to think so. I don't want to second guess the optimizer and add query hints everywhere - but 2 minutes vs under 1 second is a huge problem when a user is waiting for a screen to show... any other ideas/suggestions will be very welcome.

Thanks again.

|||

Unfortunately, we do not do a good job of optimizing queries that contain large number of value in the IN list. Part of the problem is the high compilation costs as the number of values increases. This affects plan choices also. If you provide index hint then lot of the logic is short circuited. In this case, it just so happens that using the index results in a good plan. It is hard to say if that will be case for all possible IN list values.

Next, I am not sure how you are sending the query to SQL Server. It is very inefficient to send queries like this. Is this being generated from an application? How is the user able to select this many values? There are also multiple problems apart from the performance issue. For each such unique query, there will be a unique plan because of the difference in the IN list values. This affects your plan cache size too. And it looks like the size of your SQL statement can be very large so it affects performance and also inhibits different connections from sharing the same plan.

You should consider the following options:

1. Create a temporary table and optionally index the value column

2. Use BULK INSERT or BCP or BulkCopy API to dump the IN list values into temporary table

3. Write the query to use the temporary table. The query plan can now be shared across connections and independent of the values in the temporary table

You can also create a SP that takes the IN list values as a blob (string of bytes) and parse it using SUBSTRING/numbers table. The link below describes lot of these techniques.

http://www.sommarskog.se/arrays-in-sql.html

So can you describe your query generation process more?

|||

The user selects the list of IDs. The application then creates a dynamic SQL script with these values, and passes it to the database. A lot of such t-sql has been moved from application code into stored procedures/functions but there's still some that will need to be moved eventually, and some that cannot be moved because of the nature of the functionality. For these I've been trying to replace with the sp_executesql construct to improve plan reuse.

In the meantime - this is great information. I just tested by inserting the data into a temp table and running the SELECT. Ran in under 1 second and used the best index for the query. If I can't get the code moved to the DB, I will ask that the data be placed in a table before using in the IN construct.

I made sure I planned my indexing strategy, implemented it, maintain indexes, etc but didn't quite understand why they weren't being used. This is probably the culprit for several performance issues that did not seem to make sense before this.

Thanks very much,

Smitha

|||What you describe is exactly my situation. I have NO, REPEAT NO, control over the SQL query which gets generated by the report writter. The user's can select any number of values for the IN clause. Some work, some don't.

To MS, I would point out the EXACT same query runs in 10 seconds on SQL 2000 SP4 and I cancelled the query under SQL 2005 SP1 after 1 hour. So it is definitally a problem with SQL 2005.

This needs to be fixed. I have no work around, except the have the user select less items and run the report multiple times.

|||

Thanks for responding, Tom; I know that you have had a lot of heartburn over this issue.

Bless you, Uma.

|||Thanks Kent.

I should add, this is a weekly occurance in my company since changing to SQL 2005. I have been testing SP2 and do not see it fixed in SP2 either.

This needs to be fixed, and the standard MS answer of "don't do that" doesn't fix it.

|||

I am following up internally on some of the bugs filed for this particular issue. I will find out if we are going to do anything about it in SQL Server 2008.

|||Thank you

Query Plan in SQL Server 2005 SP2

Hello,

The query included at the end of this post seems to use the 'wrong' index when executing and takes 2+ minutes to run. When I provide an index hint, it runs in under 1 second. This happens in both the Production and Development environments; both servers run SQL Server 2005 SP2.

When I run both the queries together in Query Analyzer (or SSMS), the Query Cost (relative to the batch) value for the query with the index hint = 83.24%.

Profiler stats for Original Query:

Duration = 130484 ms

CPU = 111141 ms

Reads = 85470

Profiler stats for Index Hint Query:

Duration = 64 ms

CPU = 687 ms

Reads = 5558

Statistics are updated every night (w/Fullscan)

Indexes are rebuilt/defragmented daily based on fragmentation levels.

What am I missing here/how do I fix this performance issue?

Doesn't Profiler take into account the "Subtree Cost" for the Index Seek (which Query Analyzer & SSMS consider to be so expensive)? What Events/Columns do I need to include in the Profiler trace to see this statistic when the query is executing?

Thanks much,

Smitha

QUERY:

select min(AccessLevel)
from Groups-- WITH(INDEX = ByClassID)
where name = 'Student Leader'
and classid in (2067,2063,2069,2070,2079,2072,2073,2074,2075,2076,2077,2073,2079,2030,2039,2032,201,2034,2035,2036,2037,201,2039,2090,
202,2092,2093,2094,2095,2096,2097,2093,202,24,2909,2902,2903,2904,2905,25,2907,2903,2909,220,229,222,223,224,225,226,227,223,229,2920,2929,2922,2923,2924,22,2926,2927,2923,2929,2930,26,2932,291,2934,2935,28,2937,291,
26,2940,2949,211,2943,2944,2945,2946,2947,2943,2949,27,2959,2952,2953,2954,2955,2956,2957,2953,2959,2960,2969,2962,2963,2964,2965,2966,2967,2963,2969,2970,2979,2972,2973,2974,2975,2976,2977,2973,2979,2930,26,2932,291,2934,2935,28,
2937,291,26,220,229,222,223,224,225,226,227,223,229,2200,2209,2202,2203,2204,2205,34736,34739,34749,34742,34743,34744,34745,34746,34747,34743,34749,3471,34759,34392,34393,34396,34397,34962,34937,3491,346,3420,3429,3423,3424,35360,35695,35696,35709,
1056,1057,1223,4107,1256,1257,1259,1269,1262,1263,1265,1267,1263,1269,1270,1272,1273,1274,1275,1276,1277,1273,1279,1230,1239,1232,121,1234,1235,1236,1237,121,1290,122,1292,1293,1294,1295,1296,1293,122,1300,1309,1302,1303,1304,1305,1306,
1307,1494,1459,1469,1462,1466,1467,1469,1470,1472,1474,1475,1477,1432,141,1435,1436,1437,1439,1494,1496,1497,142,113,115,117,113,119,1590,152,1592,1593,1594,1595,1596,1593,152,1520,1529,1522,1523,1524,1525,1527,1539,151,1534,1536,
151,1540,1543,1544,1545,1547,1572,1573,1574,1576,1577,1573,1579,1539,53969,64763,67735,67736,63967,1529,1530,1532,151,1534,1535,1536,1537,151,1539,1540,1549,1542,1543,1545,1546,1547,1543,1549,151,1559,1553,1555,1557,1553,1559,1560,1569,1562,
1563,1564,1565,1566,1567,1563,1569,1570,1579,1572,1573,1574,1575,1576,1577,1573,1579,1530,1539,1532,151,1534,1535,1536,152,1609,1602,1603,1604,1605,1606,1607,1603,1609,1690,162,1692,1693,1694,1695,1696,1697,1693,162,1629,1622,1635,1637,
1640,1642,1646,1649,1652,1657,1669,1664,1667,1679,1634,1637,1693,1703,1706,172,1795,1793,1722,1726,1764,1392,1393,1394,1395,1396,132,1320,1329,1322,1323,3629,3622,3623,3624,3625,3626,3627,3623,3629,36200,36209,36202,36203,36204,36205,36206,36207,
36203,36209,36290,37093,37094,37095,37096,37097,37093,3702,37020,37029,37022,37023,37024,37025,37026,37027,37023,37029,37030,37039,37032,3701,37034,37035,37036,37037,3701,37039,37040,37049,37042,37043,37044,37045,37046,37047,37043,37049,3701,37059,37052,37053,37054,37055,37056,37057,
37053,37059,37060,37069,37062,37063,37064,37065,37066,37067,37063,37069,37070,37079,37072,37073,37074,37075,37076,37077,37073,37079,37030,37039,37032,3701,37034,37035,37036,37037,3701,37039,37090,3702,37092,37093,37094,37095,37096,37097,37093,3702,374,37909,37902,37903,37904,37905,
375,37907,37903,37909,3720,3729,3722,3723,3724,37320,37329,37322,37323,37324,37325,37326,37327,37329,3710,3719,3712,3713,3714,3715,3716,3717,3713,37340,37349,37342,37343,37344,37345,37346,37347,37343,37349,3731,37359,37352,37353,37354,37356,37357,37353,37359,37360,37369,
37362,37363,37364,37365,37366,37367,37363,37369,3715,3716,3717,3713,3719,37340,37349,37342,37343,37344,37345,37346,37347,37343,37349,3731,37359,37352,37353,37354,37355,37356,37357,37353,37359,37360,37369,37362,37363,37364,37365,37366,37367,37363,37369,37370,37379,37372,37373,37374,
37375,37376,37377,37373,37379,3710,3719,3712,3713,3714,3715,3716,3717,3713,3719,37390,3732,37392,37393,37394,37395,37396,37397,37393,3732,374,37909,37902,37903,37904,37905,375,37907,37903,37909,3720,3729,3722,3723,3724,3725,3726,3727,3723,3729,37920,37929,37922,
37923,37924,372,37926,37927,37923,37929,37930,376,37932,3791,37934,37935,378,37937,3791,376,37940,37949,3711,37943,37944,37945,37946,37947,37943,37949,377,37959,37952,37953,37954,37955,37956,37957,37953,37959,37960,37969,37962,37963,37964,37965,37966,37967,37963,37969,37970,
37979,37972,37973,37974,37975,37976,37977,37973,37979,37930,376,37932,3791,37934,37935,378,37937,3791,376,3720,3729,3722,3723,3724,3725,3726,3727,3723,3729,1000,1009,1002,1003,1004,1005,1006,1007,1003,1009,1090,102,1092,1093,1094,1095,1096,1097,1093,
102,1020,1029,1022,1023,1024,1025,1026,1027,1023,1029,1030,1039,1032,101,1034,1035,1036,1037,101,1039,1040,1049,1042,1043,1044,1045,1046,1047,1043,1049,101,1059,1052,1053,1054,1055,1056,1057,1053,191,1934,1935,18,1937,191,16,1940,
1949,111,1943,1944,1945,1946,1947,1943,1949,17,1959,1952,1953,1954,1955,1956,1957,1953,1959,1960,1969,1962,1963,1964,1965,1966,1967,1963,1969,1970,1979,1972,1973,1974,1975,1976,1977,1973,1979,1930,16,1932,191,1934,1935,18,1937,191,
16,120,129,122,123,124,125,126,127,123,129,1200,1209,1202,1203,1204,1205,1206,1207,1203,1209,1290,122,1292,1293,1294,1295,1296,1297,1293,122,1220,1229,1222,1223,1224,1225,1226,1227,1223,1229,1230,1239,1232,121,1234,1235,1236,
1237,121,1239,1240,1249,1242,1243,1244,1245,1246,1247,1243,1249,121,1259,1252,1253,1254,1255,1256,1257,1253,1259,1260,1269,1262,1263,1264,1265,1266,1267,1263,1269,1270,1279,1272,1273,1274,1275,1276,1277,1273,1279,1230,1239,1232,121,1234,
1235,1236,1237,121,1239,1290,122,1292,1293,1294,1295,1296,1297,1293,122,1300,1309,1302,1303,1304,1305,1306,1307,1303,1309,1390,132,1392,1393,1394,1395,1396,1397,1393,132,1320,1329,1322,1323,1324,1325,1326,1327,1323,1329,110,119,112,
113,114,115,116,117,113,119,1340,1349,1342,1343,1344,1345,1346,1347,1343,1349,131,1359,1352,1353,1354,1355,1356,1357,1353,1359,1360,1369,1362,1363,1364,1365,1366,1367,1363,1369,1370,1379,1372,1373,1374,1375,1377,1373,1379,110,119,
112,113,115,116,117,113,119,132,1392,1393,1729,1723,9441,94497,94493,9442,9410,9419,9412,9413,9414,9415,9416,9417,9413,9419,94590,9452,94593,94594,94595,94596,94597,95576,97396,97909,93057,4640,4653,4654,4656,4657,4653,4659,4660,4669,4662,
4663,4664,4665,4666,4669,4670,4679,4672,4673,4675,4676,4677,4639,4632,461,4634,4635,4636,4637,4639,5290,522,5294,5295,5296,5370,90742,20953,20959,2239,2234,222,2292,2293,2294,2296,2297,222,29209,29204,29205,29206,
29207,29203,29209,29292,29294,29295,29297,29223,29225,29226,29223,29229,29230,29239,2921,29234,23903,23909,2320,24393,2521,25252,25253,25256,25253,25269,25262,25264,25266,25267,25269,25270,25279,25272,25273,25274,25276,25277,25273,25230,25239,25232,
25235,25237,2521,25239,25290,25292,25293,25294,2790,2793,272,2729,2749,2745,2746,271,2759,2753,2755,2753,2759,2760,2762,2764,2766,2769,2770,2779,2772,2777,93029,8747,8743,8749,871,8759,8752,8755,8757,8769,8764,8765,
8766,8769,8770,8779,8773,8774,8775,8776,8777,8773,8779,8730,8739,8732,871,8734,8739,8790,872,8792,8795,8797,8793,872,8300,8309,8303,8304,8306,8307,8309,8394,8396,8397,832,8322,8326,8323,810,819,812,813,
814,937952,6045,6097,93203,93206,619,617,632,6395,6400,6403,6406,6490,6497,6422,6425,6423,6432,6434,6435,6437,6457,6453,6459,6460,6463,6464,6465,6466,6469,6470,6472,6473,6475,6477,6473,6430,6432,6434,6435,6437,
641,6439,6490,642,6492,6493,6494,6495,6497,6493,642,9370,9379,9372,9373,9374,9375,9376,9377,9373,9379,6590,6592,6594,6593,6520,6523,6524,6527,6523,6529,6539,651,6534,6535,6536,6539,6540,6544,6545,6547,6549,
6559,6552,6553,6555,6556,6557,6553,6560,6569,6564,6565,6567,6563,6570,6579,6572,6573,6574,6575,6577,6573,6530,651,6534,651,6590,652,6592,6594,6596,6597,6593,652,6609,6632,661,6634,6635,6636,6637,661,6647,
6643,6659,6652,6653,6654,6655,6656,6657,6653,6659,6669,6663,6664,6665,6666,6667,6663,6670,6672,6673,6675,6676,6677,6630,6632,661,6636,661,6693,6694,6695,6696,6697,6693,6700,6702,6703,6704,11439,11440,11449,11442,
11443,11444,11669,11679,11676,11677,1161,11635,11637,11639,1162,11697,11693,11709,11709,11790,11723,11727,11732,11735,11737,11749,11744,11743,1171,11300,11306,11307,11309,1132,11977,94170,94179,94172,945454,945934,946464,946465,946466,94123,94123,94139,
94134,94135,94143,9411,94153,94155,94157,7239,7232,721,7692,7694,7696,7697,7693,7703,7704,7705,7329,7324,7326,714,716,719,7359,7352,7359,7363,7367,7379,719,712,713,714,715,716,717,713,719,7390,732,7392,
7393,7394,7395,7396,7397,7393,732,74,7909,7902,7903,7929,7924,7923,7937,7947,7959,7953,7955,7957,7959,7962,7963,7964,7966,7967,7963,7969,7932,791,7935,791,722,723,9549,9543,952600,952629,952627,952629,952657,952653,
952662,952690,952697,952797,95272,952720,952724,952729,954597,954964,955669,955790,95572,955792,95629,957069)

INDEX Definition:

CREATE INDEX ByClassID ON Groups(ClassID) INCLUDE(Name,AccessLevel)

You might want to look up some of Tom Phillips replies to a number of previous questions about lengthy IN conditions.

I'm afraid I didn't get good results. I found these two previous posts, but you have SP2 installed and I'm afraid that in this case I don't have much help to offer; sorry. :-(

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1525484&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=990564&SiteID=1

|||

Thanks Kent- I just read the posts...it's just that there is a better/faster way to execute the query but the optimzer doesn't seem to think so. I don't want to second guess the optimizer and add query hints everywhere - but 2 minutes vs under 1 second is a huge problem when a user is waiting for a screen to show... any other ideas/suggestions will be very welcome.

Thanks again.

|||

Unfortunately, we do not do a good job of optimizing queries that contain large number of value in the IN list. Part of the problem is the high compilation costs as the number of values increases. This affects plan choices also. If you provide index hint then lot of the logic is short circuited. In this case, it just so happens that using the index results in a good plan. It is hard to say if that will be case for all possible IN list values.

Next, I am not sure how you are sending the query to SQL Server. It is very inefficient to send queries like this. Is this being generated from an application? How is the user able to select this many values? There are also multiple problems apart from the performance issue. For each such unique query, there will be a unique plan because of the difference in the IN list values. This affects your plan cache size too. And it looks like the size of your SQL statement can be very large so it affects performance and also inhibits different connections from sharing the same plan.

You should consider the following options:

1. Create a temporary table and optionally index the value column

2. Use BULK INSERT or BCP or BulkCopy API to dump the IN list values into temporary table

3. Write the query to use the temporary table. The query plan can now be shared across connections and independent of the values in the temporary table

You can also create a SP that takes the IN list values as a blob (string of bytes) and parse it using SUBSTRING/numbers table. The link below describes lot of these techniques.

http://www.sommarskog.se/arrays-in-sql.html

So can you describe your query generation process more?

|||

The user selects the list of IDs. The application then creates a dynamic SQL script with these values, and passes it to the database. A lot of such t-sql has been moved from application code into stored procedures/functions but there's still some that will need to be moved eventually, and some that cannot be moved because of the nature of the functionality. For these I've been trying to replace with the sp_executesql construct to improve plan reuse.

In the meantime - this is great information. I just tested by inserting the data into a temp table and running the SELECT. Ran in under 1 second and used the best index for the query. If I can't get the code moved to the DB, I will ask that the data be placed in a table before using in the IN construct.

I made sure I planned my indexing strategy, implemented it, maintain indexes, etc but didn't quite understand why they weren't being used. This is probably the culprit for several performance issues that did not seem to make sense before this.

Thanks very much,

Smitha

|||What you describe is exactly my situation. I have NO, REPEAT NO, control over the SQL query which gets generated by the report writter. The user's can select any number of values for the IN clause. Some work, some don't.

To MS, I would point out the EXACT same query runs in 10 seconds on SQL 2000 SP4 and I cancelled the query under SQL 2005 SP1 after 1 hour. So it is definitally a problem with SQL 2005.

This needs to be fixed. I have no work around, except the have the user select less items and run the report multiple times.

|||

Thanks for responding, Tom; I know that you have had a lot of heartburn over this issue.

Bless you, Uma.

|||Thanks Kent.

I should add, this is a weekly occurance in my company since changing to SQL 2005. I have been testing SP2 and do not see it fixed in SP2 either.

This needs to be fixed, and the standard MS answer of "don't do that" doesn't fix it.

|||

I am following up internally on some of the bugs filed for this particular issue. I will find out if we are going to do anything about it in SQL Server 2008.

|||Thank you

Query Performance Question

I have a query that takes approximately 2 mins to run. The query is derived from two tables that have a one to many relationship. When I comment out 4 of the fields in the 'select' portion of the query, it speeds up to 18 seconds. I know that I am doin
g a bookmark lookup (this is 60% of the query time) but I am not sure how to avoid this. Any ideas?
Hi,
The covering index is only effective when the query
does a Bookmark lookup. if you have covering index the query will not hit
the source table for data. but , the disadvantage is that a covering index
increases the
number of writes when one or more of the columns in the index is updated.
This might be a issue durng insert./update and delete.
Thanks
Hari
MCDBA
"Daniel Avsec" <Daniel Avsec@.discussions.microsoft.com> wrote in message
news:1611776E-29E8-4F46-BB8F-97A8E6A32C45@.microsoft.com...
> I have a query that takes approximately 2 mins to run. The query is
derived from two tables that have a one to many relationship. When I
comment out 4 of the fields in the 'select' portion of the query, it speeds
up to 18 seconds. I know that I am doing a bookmark lookup (this is 60% of
the query time) but I am not sure how to avoid this. Any ideas?

Query Performance Question

I have a query that takes approximately 2 mins to run. The query is derived from two tables that have a one to many relationship. When I comment out 4 of the fields in the 'select' portion of the query, it speeds up to 18 seconds. I know that I am doing a bookmark lookup (this is 60% of the query time) but I am not sure how to avoid this. Any ideas?Hi,
The covering index is only effective when the query
does a Bookmark lookup. if you have covering index the query will not hit
the source table for data. but , the disadvantage is that a covering index
increases the
number of writes when one or more of the columns in the index is updated.
This might be a issue durng insert./update and delete.
Thanks
Hari
MCDBA
"Daniel Avsec" <Daniel Avsec@.discussions.microsoft.com> wrote in message
news:1611776E-29E8-4F46-BB8F-97A8E6A32C45@.microsoft.com...
> I have a query that takes approximately 2 mins to run. The query is
derived from two tables that have a one to many relationship. When I
comment out 4 of the fields in the 'select' portion of the query, it speeds
up to 18 seconds. I know that I am doing a bookmark lookup (this is 60% of
the query time) but I am not sure how to avoid this. Any ideas?

Query Performance Question

I have a query that takes approximately 2 mins to run. The query is derived
from two tables that have a one to many relationship. When I comment out 4
of the fields in the 'select' portion of the query, it speeds up to 18 seco
nds. I know that I am doin
g a bookmark lookup (this is 60% of the query time) but I am not sure how to
avoid this. Any ideas?Hi,
The covering index is only effective when the query
does a Bookmark lookup. if you have covering index the query will not hit
the source table for data. but , the disadvantage is that a covering index
increases the
number of writes when one or more of the columns in the index is updated.
This might be a issue durng insert./update and delete.
Thanks
Hari
MCDBA
"Daniel Avsec" <Daniel Avsec@.discussions.microsoft.com> wrote in message
news:1611776E-29E8-4F46-BB8F-97A8E6A32C45@.microsoft.com...
> I have a query that takes approximately 2 mins to run. The query is
derived from two tables that have a one to many relationship. When I
comment out 4 of the fields in the 'select' portion of the query, it speeds
up to 18 seconds. I know that I am doing a bookmark lookup (this is 60% of
the query time) but I am not sure how to avoid this. Any ideas?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] 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

Hi Gurus,

I have run DBCC SHOWCONTIG statement against the table X as shown below:

DBCC SHOWCONTIG (X)
GO

and received the following result:

DBCC SHOWCONTIG scanning 'X' table...
Table: 'X' (885578193); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 1400
- Extents Scanned.......................: 300
- Extent Switches.......................: 300
- Avg. Pages per Extent..................: 4.7
- Scan Density [Best Count:Actual Count]......: 50.00% [200:400]
- Logical Scan Fragmentation ..............: 21.43%
- Extent Scan Fragmentation ...............: 33.33%
- Avg. Bytes Free per Page................: 2107.1
- Avg. Page Density (full)................: 73.97%

Which one of the following is more recommended way to increase query performance against the table X?

A. Run DBCC DBREINDEX statement with high fill factor value to rebuild the clustered index.
B. Run DBCC DBREINDEX statement with low fill factor value to rebuild the clustered index.
C. Drop and re-create the clustered index by using DROP INDEX and CREATE INDEX statements with low fill factor value.
D. Drop and re-create the clustered index by using DROP INDEX and CREATE INDEX statements with high fill factor value.
E. Set the truncate log on checkpoint option for the database which contain the Employee table.

Thank youIf they are asking for high query performance, then you don't want a low fill factor, you want as much data per data page. That being said, I would choose (A). (D) would be OK too but it is one more process as you would drop the clustered, recreate it, then any non-clustereds would be rebuilt, that's three steps instead of two.

HTH|||HTH,

Thanks very much.

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
>

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_Subtyp
e] 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_idBookmark lookup means the index Query Optimizer uses does not contain the
columns needed in the query. I believe SQL is using the different index on
a11 table if you have "a11.Tr_sub_type_id in (430, 433, 3530)" in the WHERE
clause. One thing you can try is to add an index hint after the table name
in the FROM statement.
Is this why you posted the Index Tuning Wizard question?
"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_Subt
ype] 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', 12
0)
> 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

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_idMarc,
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
>