Can you tell me what is wrong with this line of SQL statement?
if not exists(select @.InitialPasswordInd = InitialPasswordInd
where signonname = @.signonname
and userpassword = @.userpassword)
goto passwordinvalid
Assuming that @.InitialPasswordInd is declare and is the same type as
"InitialPasswordInd".
It does not like @.InitialPasswordInd = InitialPasswordInd
Is this not the proper way to get values form the database?
The query should only return one record. I tried Select Distinct... but I
get the same error (Something wrong near the equals sign.
Thanks in advance for your assistance!!!!!!!!!!!!!!It may be a typo, but I didn't see an FROM clause. Let us know...
James|||You were correct, I did not have a "from" - But I still get same error.
if not exists(select @.InitialPasswordInd = InitialPasswordInd
from dbo.Signon
where signonname = @.signonname
and userpassword = @.userpassword)
goto passwordinvalid|||You can't assign to a variable in a subquery. If you want to get the value,
just
select @.InitialPasswordInd ...
If you want to use the existence in a query,
if not exists (
select InitialPasswordInd
from ...
)
You can't do both in the subquery.
SK
"CJ Silin" <cjssilin@.nospam.com> wrote in message
news:Xns9479867BCDF3Acsilinhotmailcom@.207.46.248.16...
> Can you tell me what is wrong with this line of SQL statement?
> if not exists(select @.InitialPasswordInd = InitialPasswordInd
> where signonname = @.signonname
> and userpassword = @.userpassword)
> goto passwordinvalid
> Assuming that @.InitialPasswordInd is declare and is the same type as
> "InitialPasswordInd".
> It does not like @.InitialPasswordInd = InitialPasswordInd
> Is this not the proper way to get values form the database?
> The query should only return one record. I tried Select Distinct... but I
> get the same error (Something wrong near the equals sign.
> Thanks in advance for your assistance!!!!!!!!!!!!!!|||An EXISTS test is not a data retrieval operation so you can't specify the
variable assignment in the WHERE clause. Also, you have no FROM clause.
If you need to check for data existence and retrieve data, you can check for
a NULL variable value (of a non-NULL column) following the select. For
example:
SELECT @.InitialPasswordInd = InitialPasswordInd
FROM MyTable
WHERE signonname = @.signonname
AND userpassword = @.userpassword
IF @.InitialPasswordInd IS NULL GOTO passwordinvalid
Hope this helps.
Dan Guzman
SQL Server MVP
"CJ Silin" <cjssilin@.nospam.com> wrote in message
news:Xns9479867BCDF3Acsilinhotmailcom@.207.46.248.16...
> Can you tell me what is wrong with this line of SQL statement?
> if not exists(select @.InitialPasswordInd = InitialPasswordInd
> where signonname = @.signonname
> and userpassword = @.userpassword)
> goto passwordinvalid
> Assuming that @.InitialPasswordInd is declare and is the same type as
> "InitialPasswordInd".
> It does not like @.InitialPasswordInd = InitialPasswordInd
> Is this not the proper way to get values form the database?
> The query should only return one record. I tried Select Distinct... but I
> get the same error (Something wrong near the equals sign.
> Thanks in advance for your assistance!!!!!!!!!!!!!!
Showing posts with label wrong. Show all posts
Showing posts with label wrong. Show all posts
Monday, March 26, 2012
Query Problem
Can you tell me what is wrong with this line of SQL statement?
if not exists(select @.InitialPasswordInd = InitialPasswordInd
where signonname = @.signonname
and userpassword = @.userpassword)
goto passwordinvalid
Assuming that @.InitialPasswordInd is declare and is the same type as
"InitialPasswordInd".
It does not like @.InitialPasswordInd = InitialPasswordInd
Is this not the proper way to get values form the database?
The query should only return one record. I tried Select Distinct... but I
get the same error (Something wrong near the equals sign.
Thanks in advance for your assistance!!!!!!!!!!!!!!It may be a typo, but I didn't see an FROM clause. Let us know...
James|||You were correct, I did not have a "from" - But I still get same error.
if not exists(select @.InitialPasswordInd = InitialPasswordInd
from dbo.Signon
where signonname = @.signonname
and userpassword = @.userpassword)
goto passwordinvalid|||You can't assign to a variable in a subquery. If you want to get the value,
just
select @.InitialPasswordInd ...
If you want to use the existence in a query,
if not exists (
select InitialPasswordInd
from ...
)
You can't do both in the subquery.
SK
"CJ Silin" <cjssilin@.nospam.com> wrote in message
news:Xns9479867BCDF3Acsilinhotmailcom@.20
7.46.248.16...
variable assignment in the WHERE clause. Also, you have no FROM clause.
If you need to check for data existence and retrieve data, you can check for
a NULL variable value (of a non-NULL column) following the select. For
example:
SELECT @.InitialPasswordInd = InitialPasswordInd
FROM MyTable
WHERE signonname = @.signonname
AND userpassword = @.userpassword
IF @.InitialPasswordInd IS NULL GOTO passwordinvalid
Hope this helps.
Dan Guzman
SQL Server MVP
"CJ Silin" <cjssilin@.nospam.com> wrote in message
news:Xns9479867BCDF3Acsilinhotmailcom@.20
7.46.248.16...
if not exists(select @.InitialPasswordInd = InitialPasswordInd
where signonname = @.signonname
and userpassword = @.userpassword)
goto passwordinvalid
Assuming that @.InitialPasswordInd is declare and is the same type as
"InitialPasswordInd".
It does not like @.InitialPasswordInd = InitialPasswordInd
Is this not the proper way to get values form the database?
The query should only return one record. I tried Select Distinct... but I
get the same error (Something wrong near the equals sign.
Thanks in advance for your assistance!!!!!!!!!!!!!!It may be a typo, but I didn't see an FROM clause. Let us know...
James|||You were correct, I did not have a "from" - But I still get same error.
if not exists(select @.InitialPasswordInd = InitialPasswordInd
from dbo.Signon
where signonname = @.signonname
and userpassword = @.userpassword)
goto passwordinvalid|||You can't assign to a variable in a subquery. If you want to get the value,
just
select @.InitialPasswordInd ...
If you want to use the existence in a query,
if not exists (
select InitialPasswordInd
from ...
)
You can't do both in the subquery.
SK
"CJ Silin" <cjssilin@.nospam.com> wrote in message
news:Xns9479867BCDF3Acsilinhotmailcom@.20
7.46.248.16...
quote:|||An EXISTS test is not a data retrieval operation so you can't specify the
> Can you tell me what is wrong with this line of SQL statement?
> if not exists(select @.InitialPasswordInd = InitialPasswordInd
> where signonname = @.signonname
> and userpassword = @.userpassword)
> goto passwordinvalid
> Assuming that @.InitialPasswordInd is declare and is the same type as
> "InitialPasswordInd".
> It does not like @.InitialPasswordInd = InitialPasswordInd
> Is this not the proper way to get values form the database?
> The query should only return one record. I tried Select Distinct... but I
> get the same error (Something wrong near the equals sign.
> Thanks in advance for your assistance!!!!!!!!!!!!!!
variable assignment in the WHERE clause. Also, you have no FROM clause.
If you need to check for data existence and retrieve data, you can check for
a NULL variable value (of a non-NULL column) following the select. For
example:
SELECT @.InitialPasswordInd = InitialPasswordInd
FROM MyTable
WHERE signonname = @.signonname
AND userpassword = @.userpassword
IF @.InitialPasswordInd IS NULL GOTO passwordinvalid
Hope this helps.
Dan Guzman
SQL Server MVP
"CJ Silin" <cjssilin@.nospam.com> wrote in message
news:Xns9479867BCDF3Acsilinhotmailcom@.20
7.46.248.16...
quote:sql
> Can you tell me what is wrong with this line of SQL statement?
> if not exists(select @.InitialPasswordInd = InitialPasswordInd
> where signonname = @.signonname
> and userpassword = @.userpassword)
> goto passwordinvalid
> Assuming that @.InitialPasswordInd is declare and is the same type as
> "InitialPasswordInd".
> It does not like @.InitialPasswordInd = InitialPasswordInd
> Is this not the proper way to get values form the database?
> The query should only return one record. I tried Select Distinct... but I
> get the same error (Something wrong near the equals sign.
> Thanks in advance for your assistance!!!!!!!!!!!!!!
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 desc
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
|||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...
>
>
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
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
|||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...
>
>
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
>
>
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 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 rea
d
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...
>
>sql
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 rea
d
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...
>
>sql
Friday, March 23, 2012
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.
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 youQuery 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.
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
Subscribe to:
Posts (Atom)