Hi,
We're using the SQL Server Full Text Engine to implement a search engine. Users can enter queries which are then executed using the CONTAINS statement. I can't find any documentation that indicates the maximum allowable size of the contains_search_conditi
on clause. Our users' search terms can be quite complex with many Boolean predicates. As a result they can become quite long: several hundred or even a few thousand characters long. What is the upper size limit?
Many thanks,
Dan
Dan,
There was an early bug in SQL Server 2000 RTM that limited the max character
length to 1023 (1K -1), but it was fixed in SP1.
I believe that the max character limit was extended to the max length of
char/varchar datatype length of 8000.
Regards,
John
"Dan Crow" <anonymous@.discussions.microsoft.com> wrote in message
news:A46B5192-072F-4DF0-8D9E-F44D955EDB58@.microsoft.com...
> Hi,
> We're using the SQL Server Full Text Engine to implement a search engine.
Users can enter queries which are then executed using the CONTAINS
statement. I can't find any documentation that indicates the maximum
allowable size of the contains_search_condition clause. Our users' search
terms can be quite complex with many Boolean predicates. As a result they
can become quite long: several hundred or even a few thousand characters
long. What is the upper size limit?
> Many thanks,
> Dan
Showing posts with label queries. Show all posts
Showing posts with label queries. Show all posts
Monday, March 19, 2012
Saturday, February 25, 2012
maxdop 1 doesn't seem to work inside batch
I have several dynamically-generated queries that I would like to run with
the option(maxdop 1). However, if I put the queries with the option in eithe
r
Exec or sp_executesql, the option is ignored and the query runs with
parallelism (I've seen the degrees of parallelism and execution plan for thi
s
in profiler). Is there any way to do this without having to configure the
server setting?
These queries actually run inside a stored procedure, so if there is a
maxdop setting for the entire stored procedure and all the batches it
executes, that would be even better.Can you post the actual sp? The hint should affect everything in the
statement butnot the entire sp.
Andrew J. Kelly SQL MVP
"Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
news:FDD1E453-77BF-45DF-A73C-6F39D9EDB6FC@.microsoft.com...
>I have several dynamically-generated queries that I would like to run with
> the option(maxdop 1). However, if I put the queries with the option in
> either
> Exec or sp_executesql, the option is ignored and the query runs with
> parallelism (I've seen the degrees of parallelism and execution plan for
> this
> in profiler). Is there any way to do this without having to configure the
> server setting?
> These queries actually run inside a stored procedure, so if there is a
> maxdop setting for the entire stored procedure and all the batches it
> executes, that would be even better.|||tBackground: this query is fetching data from views (vStatsX). Each view is
the union of a simple SELECT from two similar tables. The query with the
maxdop option is executed WITH the option enabled in query analyzer(QA). If
I
enclose the query in an EXECUTE or sp_ExecuteSQL in QA the option is ignored
.
As I am calling this query in the sp using sp_ExecuteSQL, I'm running into
the same problem where the option is ignored.
Here is the generated query, from the profiler SP:StmtStarting:
SELECT @.Completed=@.Completed +IsNull(Count(Sup.SessionID),0),
@.Duration=@.Duration+IsNull(sum(Sup.Seconds),0)
FROM vStats4 Sup LEFT JOIN vStats1 S1 on Sup.SessionID=S1.SuperSessionID
LEFT JOIN vStats2 S2 on Sup.SessionID=S2.SuperSessionID
LEFT JOIN vStats3 S3 on Sup.SessionID=S3.SuperSessionID
WHERE Sup.date between @.dateStart and @.dateEnd and Sup.Escalated=0 and
Sup.IsAbandoned=0
AND IsNull(S1.Escalated,0)=0 AND IsNull(S2.Escalated,0)=0 AND
IsNull(S3.Escalated,0)=0
AND (Sup.Problems>0 or Sup.Questions>0 or Sup.AMSSEarches>0 OR S1.Problems>0
or S1.Questions>0 or S1.AMSSearches>0
OR S2.Problems>0 or S2.Questions>0 or S2.AMSSearches>0 OR
S3.Problems>0 or S3.Questions>0 or S3.AMSSearches>0)
OPTION(MAXDOP 1)
Here's the Profiler Output following the SP:stmtStarting:
Degree of Parallelism (7.0 Insert)
Execution Tree
--
Compute Scalar(DEFINE:([Expr1027]=[@.Completed]+i
snull([Expr1025], 0),
[Expr1028]=[@.duration]+isnull([Expr1026]
, 0)))
|--Compute Scalar(DEFINE:([Expr1025]=Convert([globa
lagg1030]),
[Expr1026]=If ([globalagg1032]=0) then NULL else [globalagg1034]))
|--Stream Aggregate(DEFINE:([globalagg1030]=SUM([p
artialagg1029]),
[globalagg1032]=SUM([partialagg1031]), [globalagg1034]=SUM([partialagg1033])
))
|--Concatenation
|--Compute Scalar(DEFINE:([partialagg1029]=[partial
agg1029]))
| |--Stream Aggregate(DEFINE:([partialagg1029]=Count
(*),
[partialagg1033]=SUM([Stats1].[Seconds])))
| |--Clustered Index
Scan(OBJECT:([noHoldCust18].[dbo].[Stats1].[MSCCSPK_20041015040614432]),
WHERE:((((([Stats1].[SuperSessionID]=NULL AND [Stats1].[Date]>=[@.dateStart])
AND [Stats1].[Date]<=[@.dateEnd]) AND (([Stats1].[Problems]>0 OR
[Stats1].[Questions]>0) OR [Stats1].[AMSSearches]>0)) AND
Convert([Stats1].[Escalated])=0) AND Convert([Stats1].[IsAbandoned])=0))
|--Compute Scalar(DEFINE:([partialagg1029]=[partial
agg1029]))
|--Stream Aggregate(DEFINE:([partialagg1029]=Count
(*),
[partialagg1033]=SUM([Stats1Archive].[Seconds])))
|--Clustered Index
Scan(OBJECT:([noHoldCust18].[dbo].[Stats1Archive].[MSCCSPK_20041015040714695]),
WHERE:((((([Stats1Archive].[SuperSessionID]=NULL AND
[Stats1Archive].[Date]>=[@.dateStart]) AND [Stats1Archive].[Date]<=[@.dateEnd])
AND (([Stats1Archive].[Problems]>0 OR [Stats1Archive].[Questions]>0) OR
[Stats1Archive].[AMSSearches]>0)) AND Convert([Stats1Archive].[Escalated])=0)
AND Convert([Stats1Archive].[IsAbandoned])=0))|||Here is how I am executing the query from within the sp:
exec sp_Executesql @.strSQL,N'@.Completed int OUTPUT,@.duration int output,
@.DateStart datetime, @.DateEnd datetime',
@.Completed output,@.duration output,@.DateStart,@.DateEnd|||I think I need more sleep...
It looks like the option IS being used in the batch calls. I reviewed the
execution plan generated when I purposely omit the option in my query and it
is different than the execution plan generated when I include the option.
This execution plan (without the option) explicitly mentions parallelism
whereas the other one does not. I'm obviously not an expert in this area but
I hope I'm on the right track now.
ompute Scalar(DEFINE:([Expr1102]=[@.Completed]+i
snull([Expr1100], 0),
[Expr1103]=[@.duration]+isnull([Expr1101]
, 0)))
|--Compute Scalar(DEFINE:([Expr1100]=Convert([globa
lagg1105]),
[Expr1101]=If ([globalagg1107]=0) then NULL else [globalagg1109]))
|--Stream Aggregate(DEFINE:([globalagg1105]=SUM([p
artialagg1104]),
[globalagg1107]=SUM([partialagg1104]), [globalagg1109]=SUM([partialagg1108])
))
|--Parallelism(Gather Streams)
|--Compute Scalar(DEFINE:([partialagg1104]=[partial
agg1104]))
etc...|||Yes I saw no indication of parallelism in the other plan. Just a note that
your where clause is almost forcing index scans. You might want to see if
you can't optimize that query.
Andrew J. Kelly SQL MVP
"Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
news:5F333045-B692-415F-9DFC-2B21FABBA6EB@.microsoft.com...
>I think I need more sleep...
> It looks like the option IS being used in the batch calls. I reviewed the
> execution plan generated when I purposely omit the option in my query and
> it
> is different than the execution plan generated when I include the option.
> This execution plan (without the option) explicitly mentions parallelism
> whereas the other one does not. I'm obviously not an expert in this area
> but
> I hope I'm on the right track now.
> ompute Scalar(DEFINE:([Expr1102]=[@.Completed]+i
snull([Expr1100], 0),
> [Expr1103]=[@.duration]+isnull([Expr1101]
, 0)))
> |--Compute Scalar(DEFINE:([Expr1100]=Convert([globa
lagg1105]),
> [Expr1101]=If ([globalagg1107]=0) then NULL else [globalagg1109]))
> |--Stream Aggregate(DEFINE:([globalagg1105]=SUM([p
artialagg1104]),
> [globalagg1107]=SUM([partialagg1104]),
> [globalagg1109]=SUM([partialagg1108])))
> |--Parallelism(Gather Streams)
> |--Compute
> Scalar(DEFINE:([partialagg1104]=[partial
agg1104]))
> etc...
>
the option(maxdop 1). However, if I put the queries with the option in eithe
r
Exec or sp_executesql, the option is ignored and the query runs with
parallelism (I've seen the degrees of parallelism and execution plan for thi
s
in profiler). Is there any way to do this without having to configure the
server setting?
These queries actually run inside a stored procedure, so if there is a
maxdop setting for the entire stored procedure and all the batches it
executes, that would be even better.Can you post the actual sp? The hint should affect everything in the
statement butnot the entire sp.
Andrew J. Kelly SQL MVP
"Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
news:FDD1E453-77BF-45DF-A73C-6F39D9EDB6FC@.microsoft.com...
>I have several dynamically-generated queries that I would like to run with
> the option(maxdop 1). However, if I put the queries with the option in
> either
> Exec or sp_executesql, the option is ignored and the query runs with
> parallelism (I've seen the degrees of parallelism and execution plan for
> this
> in profiler). Is there any way to do this without having to configure the
> server setting?
> These queries actually run inside a stored procedure, so if there is a
> maxdop setting for the entire stored procedure and all the batches it
> executes, that would be even better.|||tBackground: this query is fetching data from views (vStatsX). Each view is
the union of a simple SELECT from two similar tables. The query with the
maxdop option is executed WITH the option enabled in query analyzer(QA). If
I
enclose the query in an EXECUTE or sp_ExecuteSQL in QA the option is ignored
.
As I am calling this query in the sp using sp_ExecuteSQL, I'm running into
the same problem where the option is ignored.
Here is the generated query, from the profiler SP:StmtStarting:
SELECT @.Completed=@.Completed +IsNull(Count(Sup.SessionID),0),
@.Duration=@.Duration+IsNull(sum(Sup.Seconds),0)
FROM vStats4 Sup LEFT JOIN vStats1 S1 on Sup.SessionID=S1.SuperSessionID
LEFT JOIN vStats2 S2 on Sup.SessionID=S2.SuperSessionID
LEFT JOIN vStats3 S3 on Sup.SessionID=S3.SuperSessionID
WHERE Sup.date between @.dateStart and @.dateEnd and Sup.Escalated=0 and
Sup.IsAbandoned=0
AND IsNull(S1.Escalated,0)=0 AND IsNull(S2.Escalated,0)=0 AND
IsNull(S3.Escalated,0)=0
AND (Sup.Problems>0 or Sup.Questions>0 or Sup.AMSSEarches>0 OR S1.Problems>0
or S1.Questions>0 or S1.AMSSearches>0
OR S2.Problems>0 or S2.Questions>0 or S2.AMSSearches>0 OR
S3.Problems>0 or S3.Questions>0 or S3.AMSSearches>0)
OPTION(MAXDOP 1)
Here's the Profiler Output following the SP:stmtStarting:
Degree of Parallelism (7.0 Insert)
Execution Tree
--
Compute Scalar(DEFINE:([Expr1027]=[@.Completed]+i
snull([Expr1025], 0),
[Expr1028]=[@.duration]+isnull([Expr1026]
, 0)))
|--Compute Scalar(DEFINE:([Expr1025]=Convert([globa
lagg1030]),
[Expr1026]=If ([globalagg1032]=0) then NULL else [globalagg1034]))
|--Stream Aggregate(DEFINE:([globalagg1030]=SUM([p
artialagg1029]),
[globalagg1032]=SUM([partialagg1031]), [globalagg1034]=SUM([partialagg1033])
))
|--Concatenation
|--Compute Scalar(DEFINE:([partialagg1029]=[partial
agg1029]))
| |--Stream Aggregate(DEFINE:([partialagg1029]=Count
(*),
[partialagg1033]=SUM([Stats1].[Seconds])))
| |--Clustered Index
Scan(OBJECT:([noHoldCust18].[dbo].[Stats1].[MSCCSPK_20041015040614432]),
WHERE:((((([Stats1].[SuperSessionID]=NULL AND [Stats1].[Date]>=[@.dateStart])
AND [Stats1].[Date]<=[@.dateEnd]) AND (([Stats1].[Problems]>0 OR
[Stats1].[Questions]>0) OR [Stats1].[AMSSearches]>0)) AND
Convert([Stats1].[Escalated])=0) AND Convert([Stats1].[IsAbandoned])=0))
|--Compute Scalar(DEFINE:([partialagg1029]=[partial
agg1029]))
|--Stream Aggregate(DEFINE:([partialagg1029]=Count
(*),
[partialagg1033]=SUM([Stats1Archive].[Seconds])))
|--Clustered Index
Scan(OBJECT:([noHoldCust18].[dbo].[Stats1Archive].[MSCCSPK_20041015040714695]),
WHERE:((((([Stats1Archive].[SuperSessionID]=NULL AND
[Stats1Archive].[Date]>=[@.dateStart]) AND [Stats1Archive].[Date]<=[@.dateEnd])
AND (([Stats1Archive].[Problems]>0 OR [Stats1Archive].[Questions]>0) OR
[Stats1Archive].[AMSSearches]>0)) AND Convert([Stats1Archive].[Escalated])=0)
AND Convert([Stats1Archive].[IsAbandoned])=0))|||Here is how I am executing the query from within the sp:
exec sp_Executesql @.strSQL,N'@.Completed int OUTPUT,@.duration int output,
@.DateStart datetime, @.DateEnd datetime',
@.Completed output,@.duration output,@.DateStart,@.DateEnd|||I think I need more sleep...
It looks like the option IS being used in the batch calls. I reviewed the
execution plan generated when I purposely omit the option in my query and it
is different than the execution plan generated when I include the option.
This execution plan (without the option) explicitly mentions parallelism
whereas the other one does not. I'm obviously not an expert in this area but
I hope I'm on the right track now.
ompute Scalar(DEFINE:([Expr1102]=[@.Completed]+i
snull([Expr1100], 0),
[Expr1103]=[@.duration]+isnull([Expr1101]
, 0)))
|--Compute Scalar(DEFINE:([Expr1100]=Convert([globa
lagg1105]),
[Expr1101]=If ([globalagg1107]=0) then NULL else [globalagg1109]))
|--Stream Aggregate(DEFINE:([globalagg1105]=SUM([p
artialagg1104]),
[globalagg1107]=SUM([partialagg1104]), [globalagg1109]=SUM([partialagg1108])
))
|--Parallelism(Gather Streams)
|--Compute Scalar(DEFINE:([partialagg1104]=[partial
agg1104]))
etc...|||Yes I saw no indication of parallelism in the other plan. Just a note that
your where clause is almost forcing index scans. You might want to see if
you can't optimize that query.
Andrew J. Kelly SQL MVP
"Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
news:5F333045-B692-415F-9DFC-2B21FABBA6EB@.microsoft.com...
>I think I need more sleep...
> It looks like the option IS being used in the batch calls. I reviewed the
> execution plan generated when I purposely omit the option in my query and
> it
> is different than the execution plan generated when I include the option.
> This execution plan (without the option) explicitly mentions parallelism
> whereas the other one does not. I'm obviously not an expert in this area
> but
> I hope I'm on the right track now.
> ompute Scalar(DEFINE:([Expr1102]=[@.Completed]+i
snull([Expr1100], 0),
> [Expr1103]=[@.duration]+isnull([Expr1101]
, 0)))
> |--Compute Scalar(DEFINE:([Expr1100]=Convert([globa
lagg1105]),
> [Expr1101]=If ([globalagg1107]=0) then NULL else [globalagg1109]))
> |--Stream Aggregate(DEFINE:([globalagg1105]=SUM([p
artialagg1104]),
> [globalagg1107]=SUM([partialagg1104]),
> [globalagg1109]=SUM([partialagg1108])))
> |--Parallelism(Gather Streams)
> |--Compute
> Scalar(DEFINE:([partialagg1104]=[partial
agg1104]))
> etc...
>
Subscribe to:
Posts (Atom)