Query Hangs on Values that start with a number

Having a strange thing happen:
Have the following query:
SET NOCOUNT ON
DECLARE @.StartDate DateTime
DECLARE @.EndDate DateTime
SET @.StartDate = DateAdd(dd,-5,GetDate())
SET @.EndDate = GetDate()
SET NOCOUNT OFF
SET ROWCOUNT 0
SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value =
sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
FROM v_SummaryData sd
INNER JOIN Tag ON Tag.TagName = sd.TagName
WHERE SummaryDate >= @.StartDate
AND SummaryDate <= @.EndDate
AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
Duration= '86400' ORDER BY SummaryDate, TagName
SET ROWCOUNT 0
If running this query, it hangs for about 9 minutes before returning a
value. If we comment out the INNER JOIN statement, works in 1 second.
Now, here's the really big twist. If we add in a tagname of 'TI74' as
a third tag, query runs right away!
It appears that if there is more then one tagname that starts with a
number, it will hang. As soon as a tagname is present that starts with
a letter, it works great.
We are at a loss as to what could be causing the issue. Have tried re-
indexing the Tag table, but still no resolve.
Running SQL Server 2000 SP3
On Jun 12, 2:54 am, Mini67 <wor...@.gmail.com> wrote:
> Having a strange thing happen:
> Have the following query:
> SET NOCOUNT ON
> DECLARE @.StartDate DateTime
> DECLARE @.EndDate DateTime
> SET @.StartDate = DateAdd(dd,-5,GetDate())
> SET @.EndDate = GetDate()
> SET NOCOUNT OFF
> SET ROWCOUNT 0
> SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value =
> sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
> FROM v_SummaryData sd
> INNER JOIN Tag ON Tag.TagName = sd.TagName
> WHERE SummaryDate >= @.StartDate
> AND SummaryDate <= @.EndDate
> AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
> Duration= '86400' ORDER BY SummaryDate, TagName
> SET ROWCOUNT 0
> If running this query, it hangs for about 9 minutes before returning a
> value. If we comment out the INNER JOIN statement, works in 1 second.
> Now, here's the really big twist. If we add in a tagname of 'TI74' as
> a third tag, query runs right away!
> It appears that if there is more then one tagname that starts with a
> number, it will hang. As soon as a tagname is present that starts with
> a letter, it works great.
> We are at a loss as to what could be causing the issue. Have tried re-
> indexing the Tag table, but still no resolve.
> Running SQL Server 2000 SP3
Comments:
1. Why you are doing SET NOCOUNT OFF , SET ROWCOUNT 0
2. Is V_SummaryData View ? If so , table involved is index on
tagname ?
3. Try changing sd.TagName in ('20SWD','22SWD') to
Tag.TagName in ('20SWD','22SWD') and check
4. In the Where clause introduce the tablename , Instead of Duration=
'86400'
sd.Duration= '86400'

Query Hangs on Values that start with a number

Having a strange thing happen:
Have the following query:
SET NOCOUNT ON
DECLARE @.StartDate DateTime
DECLARE @.EndDate DateTime
SET @.StartDate = DateAdd(dd,-5,GetDate())
SET @.EndDate = GetDate()
SET NOCOUNT OFF
SET ROWCOUNT 0
SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value = sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
FROM v_SummaryData sd
INNER JOIN Tag ON Tag.TagName = sd.TagName
WHERE SummaryDate >= @.StartDate
AND SummaryDate <= @.EndDate
AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
Duration= '86400' ORDER BY SummaryDate, TagName
SET ROWCOUNT 0
If running this query, it hangs for about 9 minutes before returning a
value. If we comment out the INNER JOIN statement, works in 1 second.
Now, here's the really big twist. If we add in a tagname of 'TI74' as
a third tag, query runs right away!
It appears that if there is more then one tagname that starts with a
number, it will hang. As soon as a tagname is present that starts with
a letter, it works great.
We are at a loss as to what could be causing the issue. Have tried re-
indexing the Tag table, but still no resolve.
Running SQL Server 2000 SP3On Jun 12, 2:54 am, Mini67 <wor...@.gmail.com> wrote:
> Having a strange thing happen:
> Have the following query:
> SET NOCOUNT ON
> DECLARE @.StartDate DateTime
> DECLARE @.EndDate DateTime
> SET @.StartDate = DateAdd(dd,-5,GetDate())
> SET @.EndDate = GetDate()
> SET NOCOUNT OFF
> SET ROWCOUNT 0
> SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value => sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
> FROM v_SummaryData sd
> INNER JOIN Tag ON Tag.TagName = sd.TagName
> WHERE SummaryDate >= @.StartDate
> AND SummaryDate <= @.EndDate
> AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
> Duration= '86400' ORDER BY SummaryDate, TagName
> SET ROWCOUNT 0
> If running this query, it hangs for about 9 minutes before returning a
> value. If we comment out the INNER JOIN statement, works in 1 second.
> Now, here's the really big twist. If we add in a tagname of 'TI74' as
> a third tag, query runs right away!
> It appears that if there is more then one tagname that starts with a
> number, it will hang. As soon as a tagname is present that starts with
> a letter, it works great.
> We are at a loss as to what could be causing the issue. Have tried re-
> indexing the Tag table, but still no resolve.
> Running SQL Server 2000 SP3
Comments:
1. Why you are doing SET NOCOUNT OFF , SET ROWCOUNT 0
2. Is V_SummaryData View ? If so , table involved is index on
tagname ?
3. Try changing sd.TagName in ('20SWD','22SWD') to
Tag.TagName in ('20SWD','22SWD') and check
4. In the Where clause introduce the tablename , Instead of Duration='86400'
sd.Duration= '86400'

Query Hangs on Values that start with a number

Having a strange thing happen:
Have the following query:
SET NOCOUNT ON
DECLARE @.StartDate DateTime
DECLARE @.EndDate DateTime
SET @.StartDate = DateAdd(dd,-5,GetDate())
SET @.EndDate = GetDate()
SET NOCOUNT OFF
SET ROWCOUNT 0
SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value =
sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
FROM v_SummaryData sd
INNER JOIN Tag ON Tag.TagName = sd.TagName
WHERE SummaryDate >= @.StartDate
AND SummaryDate <= @.EndDate
AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
Duration= '86400' ORDER BY SummaryDate, TagName
SET ROWCOUNT 0
If running this query, it hangs for about 9 minutes before returning a
value. If we comment out the INNER JOIN statement, works in 1 second.
Now, here's the really big twist. If we add in a tagname of 'TI74' as
a third tag, query runs right away!
It appears that if there is more then one tagname that starts with a
number, it will hang. As soon as a tagname is present that starts with
a letter, it works great.
We are at a loss as to what could be causing the issue. Have tried re-
indexing the Tag table, but still no resolve.
Running SQL Server 2000 SP3On Jun 12, 2:54 am, Mini67 <wor...@.gmail.com> wrote:
> Having a strange thing happen:
> Have the following query:
> SET NOCOUNT ON
> DECLARE @.StartDate DateTime
> DECLARE @.EndDate DateTime
> SET @.StartDate = DateAdd(dd,-5,GetDate())
> SET @.EndDate = GetDate()
> SET NOCOUNT OFF
> SET ROWCOUNT 0
> SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value =
> sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
> FROM v_SummaryData sd
> INNER JOIN Tag ON Tag.TagName = sd.TagName
> WHERE SummaryDate >= @.StartDate
> AND SummaryDate <= @.EndDate
> AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
> Duration= '86400' ORDER BY SummaryDate, TagName
> SET ROWCOUNT 0
> If running this query, it hangs for about 9 minutes before returning a
> value. If we comment out the INNER JOIN statement, works in 1 second.
> Now, here's the really big twist. If we add in a tagname of 'TI74' as
> a third tag, query runs right away!
> It appears that if there is more then one tagname that starts with a
> number, it will hang. As soon as a tagname is present that starts with
> a letter, it works great.
> We are at a loss as to what could be causing the issue. Have tried re-
> indexing the Tag table, but still no resolve.
> Running SQL Server 2000 SP3
Comments:
1. Why you are doing SET NOCOUNT OFF , SET ROWCOUNT 0
2. Is V_SummaryData View ? If so , table involved is index on
tagname ?
3. Try changing sd.TagName in ('20SWD','22SWD') to
Tag.TagName in ('20SWD','22SWD') and check
4. In the Where clause introduce the tablename , Instead of Duration=
'86400'
sd.Duration= '86400'

Query goes from 1 second to 40 minutes with only 1 more record?

I have a very strange problem where I have a simple select count(*) with a handle of of joined tables that works fine and returns a count of records in under 1 second when the criteria in the where clause limits the count to 36984. But if I change the criteria to where the number of records would be 1 higher, 36985, the query takes ~40 minutes!

Here's the query

SELECT count(*)
FROM AT JOIN Al ON Al.AlID= AT.ALID
JOIN DProfile ON Al.DProfileID= DProfile.DProfileID
JOIN Label ON DProfile.LabelID = Label.LabelID
JOIN PricingTier ON PricingTier.LabelID = DProfile.LabelID
JOIN PricingTarget on PricingTarget.PricingTargetID = PricingTier.PricingTargetID
JOIN ATP ON ATP.PricingTierID = PricingTier.PricingTierID
WHERE PricingTarget.Target = 'AT'
AND PricingTier.MaxAgeInDays = 0
AND ATP.LengthMultiple = 0
AND AT.ATID > 408095
AND AT.ATID < 451199

Notice the part of the where clause in red. This is how I'm changing the number of rows that select count(*) should find.

Here's the output from showplan

|--Compute Scalar(DEFINE:([Expr1021]=CONVERT_IMPLICIT(int,[Expr1028],0)))
|--Stream Aggregate(DEFINE:([Expr1028]=Count(*)))
|--Hash Match(Inner Join, HASH:([MGN2].[dbo].[AL].[ALID])=([MGN2].[dbo].[AT].[ALID]))
|--Hash Match(Inner Join, HASH:([MGN2].[dbo].[DProfile].[DProfileID])=([MGN2].[dbo].[AL].[DProfileID]), RESIDUAL:([MGN2].[dbo].[DProfile].[DProfileID]=[MGN2].[dbo].[AL].[DProfileID]))
| |--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[PricingTier].[LabelID]=[MGN2].[dbo].[DProfile].[LabelID]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([MGN2].[dbo].[PricingTier].[PricingTargetID]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([MGN2].[dbo].[ATP].[PricingTierID]))
| | | | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[ATP].[PK_ATPID]), WHERE:([MGN2].[dbo].[ATP].[LengthMultiple]=(0)))
| | | | |--Clustered Index Seek(OBJECT:([MGN2].[dbo].[PricingTier].[PK_PricingTierID]), SEEK:([MGN2].[dbo].[PricingTier].[PricingTierID]=[MGN2].[dbo].[ATP].[PricingTierID]), WHERE:([MGN2].[dbo].[PricingTier].[MaxAgeInDays]=(0)) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([MGN2].[dbo].[PricingTarget].[PK_PricingTargetID]), SEEK:([MGN2].[dbo].[PricingTarget].[PricingTargetID]=[MGN2].[dbo].[PricingTier].[PricingTargetID]), WHERE:([MGN2].[dbo].[PricingTarget].[Target]='AT') ORDERED FORWARD)
| | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[DProfile].[PK_D_Profiles]))
| |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[AL].[PK_AL]))
|--Clustered Index Seek(OBJECT:([MGN2].[dbo].[AT].[PK_Track]), SEEK:([MGN2].[dbo].[AT].[ATID] > (408095) AND [MGN2].[dbo].[AT].[ATID] < (451199)) ORDERED FORWARD)

Now, if I change the part of the where clause from 'AND AT.ATID > 408095' to 'AND AT.ATID > 408094', increase the number returned by one, the query goes from taking 1 second to 40 minutes. Here's the showplan text that is different, when the only difference in the query is changing the number in the where clause.

|--Compute Scalar(DEFINE:([Expr1021]=CONVERT_IMPLICIT(int,[Expr1028],0)))
|--Stream Aggregate(DEFINE:([Expr1028]=Count(*)))
|--Merge Join(Inner Join, MERGE:([MGN2].[dbo].[PricingTarget].[PricingTargetID])=([MGN2].[dbo].[PricingTier].[PricingTargetID]), RESIDUAL:([MGN2].[dbo].[PricingTarget].[PricingTargetID]=[MGN2].[dbo].[PricingTier].[PricingTargetID]))
|--Clustered Index Scan(OBJECT:([MGN2].[dbo].[PricingTarget].[PK_PricingTargetID]), WHERE:([MGN2].[dbo].[PricingTarget].[Target]='AT') ORDERED FORWARD)
|--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[AL].[ALID]=[MGN2].[dbo].[AT].[ALID]))
|--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[ATPrice].[PricingTierID]=[MGN2].[dbo].[PricingTier].[PricingTierID]))
| |--Sort(ORDER BY:([MGN2].[dbo].[PricingTier].[PricingTargetID] ASC))
| | |--Hash Match(Inner Join, HASH:([MGN2].[dbo].[DProfile].[DProfileID])=([MGN2].[dbo].[AL].[DProfileID]), RESIDUAL:([MGN2].[dbo].[DProfile].[DProfileID]=[MGN2].[dbo].[AL].[DProfileID]))
| | |--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[PricingTier].[LabelID]=[MGN2].[dbo].[DProfile].[LabelID]))
| | | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[PricingTier].[PK_PricingTierID]), WHERE:([MGN2].[dbo].[PricingTier].[MaxAgeInDays]=(0)) ORDERED FORWARD)
| | | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[DProfile].[PK_D_Profiles]))
| | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[AL].[PK_AL]))
| |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[ATPrice].[PK_ATPriceID]), WHERE:([MGN2].[dbo].[ATPrice].[LengthMultiple]=(0)))
|--Clustered Index Seek(OBJECT:([MGN2].[dbo].[AT].[PK_Track]), SEEK:([MGN2].[dbo].[AT].[ATID] > (408094) AND [MGN2].[dbo].[AT].[ATID] < (451199)) ORDERED FORWARD)

I tried increasing the size of the TempDB from the default of 8 MB to 1000 MB for both the files, but it behaves the exact same way. I'm at a loss as to what is causing this dramatic difference for such a simple query. Anyone have an idea?

Thanks!

By the way, this is on SQL Server 2005, if that helps any.|||

Jim:

Good post. I appreciate your inclusion of the plan and the research you put into your question. It appears to me that you are at a plan "crossover" point. What I mean is that you are at the point where the mere addition of one more record to the output range causes the query optimizer to choose a different query plan. In your plan I see many "clustered index scans". A cover index might be appropriate to improve the performance of your query. Can you post the indexes, keys and unique constraints of all of your tables?

What follows below is academic. I am not sure whether I need to include this or not because it looks like you have already done this with your testing, so it looks like you already have a good handle on this. If this is of no interest, just skip the rest.

Dave

I created a mock-up table with 32767 rows of data. One of the columns is a "testDate" column with an associated index. After creating the table I performed a number of select queries with SHOWPLAN_TEXT turned on so that I could discover the filter criteria at which the query plan switched from "INDEX SEEK" to "CLUSTERED INDEX SCAN". I found that when I switch the filter date from "1/31/7" to "1/30/7" that the plan switched. Therefore I ran these two queries with SHOWPLAN _TEXT turned on to illustrate the query plan "crossover" point. I think that your query has a similar issue. My main question to you is was it your intent to find the crossover or is this something that jumped up and bit you? OUCH!

if exists
( select 0 from sysobjects
where type = 'U'
and id = object_id ('dbo.crossoverTest')
)
drop table dbo.crossoverTest
go

create table dbo.crossoverTest
( rid integer
constraint pk_crossoverTest primary key,
filler char (200),
testDate datetime
)
go

create index testDate_ndx
on dbo.crossoverTest (testDate)
go

insert into dbo.crossoverTest
select iter,
'Filler',
dateadd (mi, -17*iter, convert(datetime, '2/3/7'))
from (
select 256*b.number + a.number as iter
from master.dbo.spt_values a (nolock)
inner join master.dbo.spt_values b (nolock)
on a.[name] is null
and b.[name] is null
and b.number <= 127
and a.number <= 255
and 256*b.number + a.number > 0
) as small_iterator

go

update statistics dbo.crossoverTest
go

set showplan_text on
go

select rid,
left (filler, 10) as Filler,
testDate
from crossoverTest
where testDate >= '1/31/7'

go

-- StmtText
-- --
-- |--Compute Scalar(DEFINE:([Expr1002]=substring(Convert([crossoverTest].[filler]), 1, 10)))
-- |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[crossoverTest]) WITH PREFETCH)
-- |--Index Seek(OBJECT:([tempdb].[dbo].[crossoverTest].[testDate_ndx]), SEEK:([crossoverTest].[testDate] >= 'Jan 31 2007 12:00AM') ORDERED FORWARD)

-- Table 'crossoverTest'. Scan count 1, logical reads 813, physical reads 0, read-ahead reads 0.

go

select rid,
left (filler, 10) as Filler,
testDate
from crossoverTest
where testDate >= '1/30/7'

go

-- StmtText
--
-- |--Compute Scalar(DEFINE:([Expr1002]=substring(Convert([crossoverTest].[filler]), 1, 10)))
-- |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[crossoverTest].[pk_crossoverTest]), WHERE:([crossoverTest].[testDate]>='Jan 30 2007 12:00AM'))

-- Table 'crossoverTest'. Scan count 1, logical reads 913, physical reads 0, read-ahead reads 0.

go

set showplan_text off
go

|||

Thanks Dave,

Ultimately, a simple index was the issue. What perplexed me was just that the difference between the queries when it returned one more row was soooo dramatic. And to answer your question, I had just come upon the crossover issue through trial and error. I hadnt noticed the query plans were different until I got to the point that 1 row was the difference between success and failure.

Query execution time using an ORMapper

Hello there!
I have a strange problem regarding the query execution time of a
parameterised query executed over sp_executesql. We use an ORMapper
(NHibernate) which generates the query, so it is not necessary to discuss the
use of sp_executesql. If I run the query in SQL Server Management Studio it
is much faster than executing it over the application. Looking at Profiler
the same query needs in MMS 6000 reads and 500 ms and over the application
nearly 200.000 reads and 30.000 ms.
I have no idea why this happens because the database doesnâ't change at all
and I profile on database level. So Iâ'm confused that it depends on the
execution source. The count of database sites in the data cache is constant
and the query plan cache is hit also in both executions.
Any ideas? Thanks
Rob"RobRoma" <RobRoma@.discussions.microsoft.com> wrote in message
news:EB7FF480-3C92-48AA-8867-FF2C30E55767@.microsoft.com...
> Hello there!
> I have a strange problem regarding the query execution time of a
> parameterised query executed over sp_executesql. We use an ORMapper
> (NHibernate) which generates the query, so it is not necessary to discuss
> the
> use of sp_executesql. If I run the query in SQL Server Management Studio
> it
> is much faster than executing it over the application. Looking at Profiler
> the same query needs in MMS 6000 reads and 500 ms and over the application
> nearly 200.000 reads and 30.000 ms.
> I have no idea why this happens because the database doesn't change at all
> and I profile on database level. So I'm confused that it depends on the
> execution source. The count of database sites in the data cache is
> constant
> and the query plan cache is hit also in both executions.
>
sp_executesql is fine, the issue is that a parameterized query is just
different than a non-parameterized one. You should expect a different plan
for expensive queries.
Test, analyze and optimize the query in Management Studio using
sp_executesql.
David

Query execution time using an ORMapper

Hello there!
I have a strange problem regarding the query execution time of a
parameterised query executed over sp_executesql. We use an ORMapper
(NHibernate) which generates the query, so it is not necessary to discuss th
e
use of sp_executesql. If I run the query in SQL Server Management Studio it
is much faster than executing it over the application. Looking at Profiler
the same query needs in MMS 6000 reads and 500 ms and over the application
nearly 200.000 reads and 30.000 ms.
I have no idea why this happens because the database doesn’t change at all
and I profile on database level. So I’m confused that it depends on the
execution source. The count of database sites in the data cache is constant
and the query plan cache is hit also in both executions.
Any ideas? Thanks
Rob"RobRoma" <RobRoma@.discussions.microsoft.com> wrote in message
news:EB7FF480-3C92-48AA-8867-FF2C30E55767@.microsoft.com...
> Hello there!
> I have a strange problem regarding the query execution time of a
> parameterised query executed over sp_executesql. We use an ORMapper
> (NHibernate) which generates the query, so it is not necessary to discuss
> the
> use of sp_executesql. If I run the query in SQL Server Management Studio
> it
> is much faster than executing it over the application. Looking at Profiler
> the same query needs in MMS 6000 reads and 500 ms and over the application
> nearly 200.000 reads and 30.000 ms.
> I have no idea why this happens because the database doesn't change at all
> and I profile on database level. So I'm confused that it depends on the
> execution source. The count of database sites in the data cache is
> constant
> and the query plan cache is hit also in both executions.
>
sp_executesql is fine, the issue is that a parameterized query is just
different than a non-parameterized one. You should expect a different plan
for expensive queries.
Test, analyze and optimize the query in Management Studio using
sp_executesql.
David

Query behaviour - (subquery results)

Hi there,
I'm experiencing some very strange effects when I trying to include a
subquery. I shall demonstrate with a simple example...
create table test
(ind int,
seq int,
message varchar(255))
insert into test (ind,seq, message) values
(1,1,'date=01/06/2006')
insert into test (ind,seq, message) values
(2,1,'date=1/12/2005')
insert into test (ind,seq, message) values
(2,2,'test')
insert into test (ind,seq, message) values
(2,3,'date=2/12/2005')
The column IND is theoretically a foreign key, the SEQ is a primary key. A
quick explanation is that this is a comment table from a main table (main
table being an 'order' table and this being a 'order comment' table.. the
relationship being (order) 1:m (comment) But for this example this doesn't
really matter.
So here are 2 queries.
select * from test t1
where t1.ind =1
and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
This one simple extracts the date from the text string. It works OK. I've
had to include the IND =1 to avoid the date conversion error. It just shows
that the conversion works.
results
IND SEQ Message
1 1 date=01/06/2006
The second query...
select max(seq) from test t2
where t2.message like 'date=%'
group by ind
This is extrating the highest 'SEQ' for each 'IND'. ie the last comment
(that has got a date component) for each order
results
SEQ
1
3
So thats OK.
Now the fun starts when I try to combine the two...
select * from test t1
where t1.seq in (select max(seq) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%'
group by t2.ind)
where convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
This causes a
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'where'.
So, its implying the date format is incorrect. If I remove the convert :-
select * from test t1
where t1.seq in (select max(seq) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%'
group by t2.ind)
then the results are OK -
IND SEQ Message
1 1 date=01/06/2006
2 3 date=2/12/2005
Any help please?
thanks
Simon(...)
--Replace the where with and AND -->
AND convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
HTH, jens Suessmeyer.|||Sorry my bad typo... I meant AND... The query should have been
select * from test t1
where t1.seq in (select max(seq) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%'
group by t2.ind)
and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
this results in the error. This isn't just a syntax error.
Thanks though
"Jens" wrote:

> (...)
> --Replace the where with and AND -->
> AND convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
> 10)),103) > getdate()
> HTH, jens Suessmeyer.
>|||A flaw in the substring function:
...(SUBSTRING(Message, CHARINDEX('=',Message) + 1, 10)),103) > getdate()
Maybe that's it.
ML
ML
http://milambda.blogspot.com/|||I don't think this is it...
If I do a
select ind , seq, SUBSTRING(message, CHARINDEX('=',message,0)+1, 10)
from test
I get the results..
IND SEQ converted date
1 1 01/06/2006
2 1 1/12/2005
2 2 test
2 3 2/12/2005
So this shows that 3 of the rows can be converted into datetime (103 style).
I think that the problem is that the convert is being done on the whole data
set before approriate rows are excluded.
If I turn this into an inline view then I get the same error.
select * from (
select * from test t1
where t1.seq in (select max(seq) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%'
group by t2.ind)) test
where convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
<bte this is where my original typo came from ;-) >
help !
"ML" wrote:

> A flaw in the substring function:
> ...(SUBSTRING(Message, CHARINDEX('=',Message) + 1, 10)),103) > getdate()
> Maybe that's it.
>
> ML
>
> ML
> --
> http://milambda.blogspot.com/|||I've had a quick look at this and get the same odd result.
The Where clause is being applied to all the contents of the input table.
You can see this in the showplan and can confirm it by removing the row
without a date.
Regards,
Craig
"s_clarke" wrote:
> I don't think this is it...
> If I do a
> select ind , seq, SUBSTRING(message, CHARINDEX('=',message,0)+1, 10)
> from test
> I get the results..
> IND SEQ converted date
> 1 1 01/06/2006
> 2 1 1/12/2005
> 2 2 test
> 2 3 2/12/2005
>
> So this shows that 3 of the rows can be converted into datetime (103 style
).
> I think that the problem is that the convert is being done on the whole da
ta
> set before approriate rows are excluded.
> If I turn this into an inline view then I get the same error.
> select * from (
> select * from test t1
> where t1.seq in (select max(seq) from test t2
> where t2.ind = t1.ind
> and t2.message like 'date=%'
> group by t2.ind)) test
> where convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
> 10)),103) > getdate()
> <bte this is where my original typo came from ;-) >
> help !
>
> "ML" wrote:
>

Query behaviour

Hi there,
I wonder if one of you worthy folks can help me out with some strange behaviour exhibited by a piece of SQL. Its my first post here , so please be gentle. :)

Here is my simple example :-

<my test table>

create table test
(ind int,
message varchar(255))

insert into test (ind, message) values
(1,'date=01/06/2006')

insert into test (ind, message) values
(1,'date=20/12/2005')
insert into test (ind, message) values
(2,'test')

The first query is

select * from test t1
where t1.ind in (select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%' )

fine... 2 rows

second query

select * from test t1
where t1.ind =1
and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10)),103) > getdate()

fine same 2 rows...

but If I try to combine the 2 clauses in

select * from test t1
where t1.ind in (select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%' )
and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10)),103) > getdate()

I get a
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Please can anyone help me on this?

thanks

Simonproblem is you try to convert MESSAGE (varchar) to datetime datatype but you have 'test' inserted in your table
so 'test' string can not be converted to datetime datatype

select SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) from test

01/06/2006
20/12/2005
test

then this fails:

select convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10)),103) from test

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.|||Hi there,

thanks for the prompt reply. I've already considered this...

the second query eliminates the bad data row...

select * from test t1
where t1.ind =1
and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10)),103) > getdate()

The nested query in query 1 is trying to do the same thing - but doesn't work for some reason

thanks

Simon|||Hmmm,
I played with it but it's mystery to me. Who can explain this:

table and data:

create table test
(ind int,
message varchar(255))

insert into test (ind, message) values
(1,'date=01/06/2006')
insert into test (ind, message) values
(1,'date=20/12/2005')
insert into test (ind, message) values
(2,'test')

now I have 2 statements which returns the same data:

select *
from test
where message like 'date=%'

ind message
--------
1 date=01/06/2006
1 date=20/12/2005

select * from test t1
where t1.ind in (select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%')

ind message
--------
1 date=01/06/2006
1 date=20/12/2005

when I use first select as subquery (temp table) it runs fine:

select *
from
(
select *
from test
where message like 'date=%'
) test
where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103) > getdate()

ind message
--------
1 date=01/06/2006
1 date=20/12/2005

when I use secnd select as subquery it fails:

select *
from
(
select * from test t1
where t1.ind in (select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%')
) test
where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103) > getdate()

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

problem is in where clause:

...
where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103) > getdate()

but why?|||her we go----

select *
from test t1
where
message like 'date=%'
and t1.ind=(select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%' )
and CONVERT(datetime,(SUBSTRING(message, CHARINDEX('=',message,0)+1, 10)),103)>getdate()|||that's nice but you missed my point. it worked also with 3rd select I posted. What I'd like to know is why 4th statement fails if select statement is the same:

select * from
(
...
) where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103)

only difference (comparing to 3rd) is subquery. but it returns the same result... for both (3rd and 4th statement)
so I'd say: I run same query against same data but result is not the same.|||see this two codes first

--this will work
select *
from test t1
where
message like 'date=%'
and t1.ind=(select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%' )
and CONVERT(datetime,(SUBSTRING(message, CHARINDEX('=',message,0)+1, 10)),103)>getdate()

--this will not work
select *
from test t1
where
t1.ind=(select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%' )
and CONVERT(datetime,(SUBSTRING(message, CHARINDEX('=',message,0)+1, 10)),103)>getdate()
and message like 'date=%'


the reason is sql engine filter data based on first condition(ie messege like 'date=%') then it process 'convert' clause in the where clause

Insecond case it doing the opposite|||ok so what's happening in this statement?

select *
from
(
select * from test t1
where t1.ind in (select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%')
) test
where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103)

I assume first of all it convert Message to datetime. But it should convert just
01/06/2006
20/12/2005
as subquery returns just those two records. there's no reason for failure or am I wrong?|||ok so what's happening in this statement?

select *
from
(
select * from test t1
where t1.ind in (select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%')
) test
where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103)

I assume first of all it convert Message to datetime. But it should convert just
01/06/2006


20/12/2005
as subquery returns just those two records. there's no reason for failure or am I wrong?


take the execution plan of that query(Ctrl+L) and see the Argument: in table scan.U can see how sql server query is processing.

I welcome more comments from SQL server gurus|||I finally found the answer to this. I got it from MS (via MSDN)

"The query engine is free to evaluate predicates in whatever order it deems fit. If you need to control the order, you can do so via a CASE expression (but this may slow things down a bit)... try:

select * from (
select * from test t1
where t1.seq in (select max(seq) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%'
group by t2.ind)) test
where
CASE WHEN message like 'date=%' THEN
CASE WHEN convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
THEN 1
ELSE 0
END
ELSE 0
END = 1"

While not a solution as such a good work around. The key to the above is query predicates - the workaround forces this. So, good enough for me.

thanks

Simon

Hope this helps others out :)