"Sounds-like" queries on full-text catalogs?

Hello, All.
I need to do queries agains full-text catalogs, but I need that be performed
using a "sounds-like" criteria. Is the full-text in SQL Server 2005 able to
do that kind of query?
Cesar
Not really. You probably need to incorporate a metaphone or sonudex
expansion to do this. You might be able to use the thesaurus features here.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ronchese" <info(a)carsoftnet.com.br> wrote in message
news:Ocwr4Q6oHHA.3944@.TK2MSFTNGP02.phx.gbl...
> Hello, All.
> I need to do queries agains full-text catalogs, but I need that be
> performed using a "sounds-like" criteria. Is the full-text in SQL Server
> 2005 able to do that kind of query?
> Cesar
>
|||Ok, thanks. Do you think the Double Metaphone works well (off course, if you
know it)?
Double Metaphone URL:
http://www.codeproject.com/cs/algorithms/dmetaphone5.asp
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u1%231Rh6oHHA.4400@.TK2MSFTNGP03.phx.gbl...
> Not really. You probably need to incorporate a metaphone or sonudex
> expansion to do this. You might be able to use the thesaurus features
> here.
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ronchese" <info(a)carsoftnet.com.br> wrote in message
> news:Ocwr4Q6oHHA.3944@.TK2MSFTNGP02.phx.gbl...
>
|||Double Metaphone wouldn't be my first choice. I think the Thesaurus would
be gigantic if you tried to incorporate all of the possible phonetic
encodings into it. You might look into NYSIIS if you wanted to build your
own phonetic expansion. Here are several phonetic encoding algorithms, and
source code is also available:
http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart3.asp
Algorithms implemented there include Double Metaphone, Celko Soundex,
Daitch-Mokotoff, NYSIIS, plus a couple of edit distance algorithms.
Hey Hilary, let's build a phonetic word-breaker
"ronchese" <info(a)carsoftnet.com.br> wrote in message
news:eoVHwu6oHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Ok, thanks. Do you think the Double Metaphone works well (off course, if
> you know it)?
> Double Metaphone URL:
> http://www.codeproject.com/cs/algorithms/dmetaphone5.asp
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u1%231Rh6oHHA.4400@.TK2MSFTNGP03.phx.gbl...
>

"NOT IN" vs "MINUS"

Can anyone offer any help ?

I've got two queries...

1. SELECT mod_code
FROM SRS.Table1
WHERE mod_code NOT IN
(SELECT mod_code
FROM SRS.Table2);

2. SELECT mod_code
FROM SRS.Table1
MINUS
SELECT mod_code
FROM SRS.Table2;

And upon timing them (two tables are quite large) Query 1 takes ALOT longer than Query 2.

My problem is that I don't understand how "NOT IN" works - MINUS works by comparing the two tables and removing duplicates across the mod_code.SRS.Table1 and mod_code.SRS.Table2

But how does NOT IN work, and why is it alot slower?

thanks for your help!!Here is my answer based on the way Oracle works, other DBMSs may do things differently. I am also assuming there is a unique index on the table2 columns being compared.

With MINUS, a full scan is done on both tables and the results for table2 are removed from the results for table1.

With NOT IN, a full table scan is done on table1. For each table1 row, a lookup is then done in table2. If no row is found in table2, the table1 row is returned - at least, that is what I have found.
The reason the NOT IN is slower concerns the number of reads required to perform the query. Let's suppose the tables have the following characteristics:

TABLE1: 20,000 rows in 1000 blocks
TABLE2: 10,000 rows in 500 blocks

Reads required for minus:
Full scan of TABLE1 = 1000 blocks
+
Full scan of Table2 = 500 blocks
= 1500 reads

Reads required for NOT IN:
Full scan of TABLE1 = 1000 blocks
20,000 lookups in TABLE2 = 20,000 x (depth of index on TABLE2)
= 21,000 at least

i.e. a lot more work is done by the NOT IN query.

Here is my test example:

SQL> create table t1 as select object_id from all_objects;

Table created.

SQL> select count(*) from t1;

COUNT(*)
----
42169

SQL> create table t2 as select object_id from all_objects where rownum < 42000;

Table created.

SQL> alter table t1 add primary key(object_id);

Table altered.

SQL> alter table t2 add primary key(object_id);

Table altered.

SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> analyze table t2 compute statistics;

Table analyzed.

SQL> set timing on
SQL> select count(*) from
2 ( select object_id from t1
3 minus
4 select object_id from t2
5 )
6 /

COUNT(*)
----
171

real: 1072

SQL> select count(*) from
2 ( select object_id from t1
3 where object_id not in
4 ( select object_id from t2
5 )
6 )
7 /

COUNT(*)
----
171

real: 2143

SQL> set timing off
SQL> set autotrace on

SQL> select count(*) from
2 ( select object_id from t1
3 minus
4 select object_id from t2
5 )
6 /

COUNT(*)
----
171

Execution Plan
------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=280 Card=1)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=280 Card=84168)
3 2 MINUS
4 3 SORT (UNIQUE) (Cost=140 Card=42169 Bytes=168676)
5 4 TABLE ACCESS (FULL) OF 'T1' (Cost=10 Card=42169 By
tes=168676)

6 3 SORT (UNIQUE) (Cost=140 Card=41999 Bytes=167996)
7 6 TABLE ACCESS (FULL) OF 'T2' (Cost=10 Card=41999 By
tes=167996)

Statistics
------------------
0 recursive calls
24 db block gets
136 consistent gets
64 physical reads
0 redo size
380 bytes sent via SQL*Net to client
518 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(*) from
2 ( select object_id from t1
3 where object_id not in
4 ( select object_id from t2
5 )
6 )
7 /

COUNT(*)
----
171

Execution Plan
------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'T1' (Cost=10 Card=2109 Bytes=8
436)

4 2 INDEX (UNIQUE SCAN) OF 'SYS_C00128497' (UNIQUE) (Cost=
1 Card=1 Bytes=4)

Statistics
------------------
0 recursive calls
12 db block gets
84406 consistent gets
0 physical reads
0 redo size
405 bytes sent via SQL*Net to client
541 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed|||Andrew,

That was just the answer I was looking for!

Many thanks for your help!

Matt|||Is there a similar command to "minus" in SQL Server? I use "NOT IN" but would prefer a faster command.

Originally posted by andrewst
Here is my answer based on the way Oracle works, other DBMSs may do things differently. I am also assuming there is a unique index on the table2 columns being compared.

With MINUS, a full scan is done on both tables and the results for table2 are removed from the results for table1.

With NOT IN, a full table scan is done on table1. For each table1 row, a lookup is then done in table2. If no row is found in table2, the table1 row is returned - at least, that is what I have found.
The reason the NOT IN is slower concerns the number of reads required to perform the query. Let's suppose the tables have the following characteristics:

TABLE1: 20,000 rows in 1000 blocks
TABLE2: 10,000 rows in 500 blocks

Reads required for minus:
Full scan of TABLE1 = 1000 blocks
+
Full scan of Table2 = 500 blocks
= 1500 reads

Reads required for NOT IN:
Full scan of TABLE1 = 1000 blocks
20,000 lookups in TABLE2 = 20,000 x (depth of index on TABLE2)
= 21,000 at least

i.e. a lot more work is done by the NOT IN query.

Here is my test example:

SQL> create table t1 as select object_id from all_objects;

Table created.

SQL> select count(*) from t1;

COUNT(*)
----
42169

SQL> create table t2 as select object_id from all_objects where rownum < 42000;

Table created.

SQL> alter table t1 add primary key(object_id);

Table altered.

SQL> alter table t2 add primary key(object_id);

Table altered.

SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> analyze table t2 compute statistics;

Table analyzed.

SQL> set timing on
SQL> select count(*) from
2 ( select object_id from t1
3 minus
4 select object_id from t2
5 )
6 /

COUNT(*)
----
171

real: 1072

SQL> select count(*) from
2 ( select object_id from t1
3 where object_id not in
4 ( select object_id from t2
5 )
6 )
7 /

COUNT(*)
----
171

real: 2143

SQL> set timing off
SQL> set autotrace on

SQL> select count(*) from
2 ( select object_id from t1
3 minus
4 select object_id from t2
5 )
6 /

COUNT(*)
----
171

Execution Plan
------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=280 Card=1)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=280 Card=84168)
3 2 MINUS
4 3 SORT (UNIQUE) (Cost=140 Card=42169 Bytes=168676)
5 4 TABLE ACCESS (FULL) OF 'T1' (Cost=10 Card=42169 By
tes=168676)

6 3 SORT (UNIQUE) (Cost=140 Card=41999 Bytes=167996)
7 6 TABLE ACCESS (FULL) OF 'T2' (Cost=10 Card=41999 By
tes=167996)

Statistics
------------------
0 recursive calls
24 db block gets
136 consistent gets
64 physical reads
0 redo size
380 bytes sent via SQL*Net to client
518 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(*) from
2 ( select object_id from t1
3 where object_id not in
4 ( select object_id from t2
5 )
6 )
7 /

COUNT(*)
----
171

Execution Plan
------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'T1' (Cost=10 Card=2109 Bytes=8
436)

4 2 INDEX (UNIQUE SCAN) OF 'SYS_C00128497' (UNIQUE) (Cost=
1 Card=1 Bytes=4)

Statistics
------------------
0 recursive calls
12 db block gets
84406 consistent gets
0 physical reads
0 redo size
405 bytes sent via SQL*Net to client
541 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed|||Originally posted by acg_ray
Is there a similar command to "minus" in SQL Server? I use "NOT IN" but would prefer a faster command.
Amazingly (to me) it appears that SQL Server does not support MINUS, nor INTERSECT - according to the on-line manual here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp

The ANSI name for MINUS is EXCEPT, but SQL Server doesn't seem to have that either. I find that strange, because relational databases are all about set processing, and UNION, MINUS/EXCEPT and INTERSECT are operators that work on sets (remember those Venn diagrams at school?)|||Thanks for confirming (unfortuanately) what I already expected. I learned Oracle in school, but have always used SQL Server professionally, and I was hoping I was missing something from SQL Server... but apparently not!

Originally posted by andrewst
Amazingly (to me) it appears that SQL Server does not support MINUS, nor INTERSECT - according to the on-line manual here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp

The ANSI name for MINUS is EXCEPT, but SQL Server doesn't seem to have that either. I find that strange, because relational databases are all about set processing, and UNION, MINUS/EXCEPT and INTERSECT are operators that work on sets (remember those Venn diagrams at school?)

"Live" queries in sql 2005 ?

A colleague was telling me the other day that in Sql Server 2005 there is a
special type of query you can do, whereby it runs in the background and
brings back results automatically, whenever they change. He referred to it
as something called a "live" query, but I'm not sure that's correct
terminology.
In theory you could run such a query, and sit back and watch the results
change in Query Analyser (or wherever). For example if you were monitoring
total sales, you could literally run the query and it would "auto update" in
front of your eyes, as sales increased.
Then you could theoretically write a client application with, say, a grid
which was bound to the "live query" and updated itself magically.
One could write a client app which polled the database every 2 seconds or
whatever, but that's not as efficient as picking up data *only* when it
changes.
Obviously it depends on the client limitations, but has anyone heard of such
a thing in SQL 2005?
Thanks,
Owen"Owen" <spam@.spam.com> wrote in message
news:lZOdnfeFS9oemgbZnZ2dnUVZ8smdnZ2d@.pi
pex.net...
>A colleague was telling me the other day that in Sql Server 2005 there is a
>special type of query you can do, whereby it runs in the background and
>brings back results automatically, whenever they change. He referred to it
>as something called a "live" query, but I'm not sure that's correct
>terminology.
>
Query Notification.
Query Notifications in ADO.NET 2.0
http://msdn.microsoft.com/library/d...otification.asp
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uaX3ADllGHA.3740@.TK2MSFTNGP02.phx.gbl...
> "Owen" <spam@.spam.com> wrote in message
> news:lZOdnfeFS9oemgbZnZ2dnUVZ8smdnZ2d@.pi
pex.net...
> Query Notification.
> Query Notifications in ADO.NET 2.0
> http://msdn.microsoft.com/library/d...otification.asp
> David
Sounds about right, thanks very much indeed. !
Owen.
http://www.riptheuglybluevoidfromyourhead.com

"end of day" function?

I'm trying to write a function that returns the last millisecond of a day.
That way I can do queries like...
... WHERE entrydate >= startOfDay(getdate()) and entrydate >=
endOfDay(getdate())
Now it would be nicer if there was simply a "date" type, as opposed to
datetime, but whatever.
Anyway, I tried this...
ALTER FUNCTION dbo.EndOfDay
(@.date datetime) RETURNS datetime
BEGIN
RETURN dateadd(ms, -1, dateadd(d, 1, dbo.StartOfDay(@.date)))
END
StartOfDay works fine, I've tested it extensively. However EndOfDay returns
the start of tomorrow. Any idea why? I tried upping the -1 to -100 with no
effect, and it only starts to work when the number is => than 1 second. That
might work OK for my needs, but I'd much prefer to understand what's going o
n
here and fix it.
MauryMaury Markowitz,
I think you meant:

> ... WHERE entrydate >= startOfDay(getdate()) and entrydate >=
> endOfDay(getdate())
...
WHERE entrydate >= startOfDay(getdate()) and entrydate <= endOfDay(getdate()
)
...
for this kind of filter, it is better to use this pattern:
...
WHERE entrydate >= convert(char(8), getdate(), 112) and entrydate <
dateadd(day, 1, convert(char(8), getdate(), 112))
...
AMB
"Maury Markowitz" wrote:

> I'm trying to write a function that returns the last millisecond of a day.
> That way I can do queries like...
> ... WHERE entrydate >= startOfDay(getdate()) and entrydate >=
> endOfDay(getdate())
> Now it would be nicer if there was simply a "date" type, as opposed to
> datetime, but whatever.
> Anyway, I tried this...
> ALTER FUNCTION dbo.EndOfDay
> (@.date datetime) RETURNS datetime
> BEGIN
> RETURN dateadd(ms, -1, dateadd(d, 1, dbo.StartOfDay(@.date)))
> END
> StartOfDay works fine, I've tested it extensively. However EndOfDay return
s
> the start of tomorrow. Any idea why? I tried upping the -1 to -100 with no
> effect, and it only starts to work when the number is => than 1 second. Th
at
> might work OK for my needs, but I'd much prefer to understand what's going
on
> here and fix it.
> Maury
>|||"Alejandro Mesa" wrote:
> for this kind of filter, it is better to use this pattern:
> ...
> WHERE entrydate >= convert(char(8), getdate(), 112) and entrydate <
> dateadd(day, 1, convert(char(8), getdate(), 112))
> ...
Sure, but why doesn't it work? Why is tommorrow -1 ms still tomorrow?
Maury|||Maury Markowitz,
If you check BOL, you can read that SQL Server stores datatiem data type to
an accuracy of one three-hundredth of a second (equivalent to 3.33
milliseconds or 0.00333 seconds). So the last millisecond will be 997, 998
and 999 will be interprete as tomorrow. That is why it recommended to use th
e
other pattern.
AMB
"Maury Markowitz" wrote:

> "Alejandro Mesa" wrote:
> Sure, but why doesn't it work? Why is tommorrow -1 ms still tomorrow?
> Maury|||SQL's DATETIME datatype is only precise to 3ms so the value gets
rounded up to the following day. Use >= and < as Alejandro suggests. It
makes queries simpler and easier to read plus it's perhaps unwise to
rely on DATETIME always being stored to the nearest 3ms - maybe that
might change in the future.
David Portas
SQL Server MVP
--|||"Alejandro Mesa" wrote:
> an accuracy of one three-hundredth of a second (equivalent to 3.33
> milliseconds or 0.00333 seconds). So the last millisecond will be 997, 998
Ahhh. Thanks!
Maury|||Although I agree with the other respondents, I do always loving interesting
questions, and this is one of them.
First of all, the DATETIME data type is stored as two INT data types. The
first is a count of days from the reference date, where 0 day = 1/1/1900.
The second is a count of "ticks" since midnight, where each "tick" is
1/300th of 1 second ~ 3.33 ms.
It is my opinion that whenever precision is required, always use the
primative data types. So, try this:
CREATE FUNCTION dbo.EndofDay
(@.date AS DATETIME)
RETURNS DATETIME
AS
/*
**
** The DATETIME data type is really two
** Integer segments in binary representation.
**
** The first segment counts the number of days
** from a reference date (day 0 = 1/1/1900).
**
** The Second segment counts the number of "ticks"
** since midnight, where a "tick" is defined as 1/300th of 1 second.
**
** This function manipulates the time segment by truncating
** that segment from the original DATETIME parameter and
** replacing it with one of maximum count.
**
*/
BEGIN
DECLARE @.intDatePart AS INT
,@.intTimePart AS INT
,@.vbnDateTime AS VARBINARY(8)
,@.dtmDateTime AS DATETIME
-- Make sure the parameter was passed correctly.
SET @.dtmDateTime =
COALESCE(@.date, 0)
-- Convert the data type to one that can be character manipulated.
SET @.vbnDateTime =
CAST(@.dtmDateTime AS VARBINARY(8))
-- Slice out the first 4 characters as the date part.
SET @.intDatePart =
CAST(CAST(LEFT(@.vbnDateTime, 4) AS VARBINARY(4)) AS INT)
-- Since we want the end of the same day,
-- we set to max "tick" before the next day rollover.
-- 24 hours x 60 minutes x 60 seconds x 300 1/300ths of a second.
SET @.intTimePart = 25919999
-- Now convert our pieces back to the correct data type.
SET @.dtmDateTime =
CAST(CAST(
CAST(CAST(@.intDatePart AS VARBINARY(4)) AS VARCHAR(4)) +
CAST(CAST(@.intTimePart AS VARBINARY(4)) AS VARCHAR(4))
AS VARBINARY(8))
AS DATETIME)
RETURN @.dtmDateTime
END
Good Luck.
Sincerely,
Anthony Thomas
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:8EEE1EB7-03FD-48BC-8CF9-7B3E50B52E28@.microsoft.com...
I'm trying to write a function that returns the last millisecond of a day.
That way I can do queries like...
... WHERE entrydate >= startOfDay(getdate()) and entrydate >=
endOfDay(getdate())
Now it would be nicer if there was simply a "date" type, as opposed to
datetime, but whatever.
Anyway, I tried this...
ALTER FUNCTION dbo.EndOfDay
(@.date datetime) RETURNS datetime
BEGIN
RETURN dateadd(ms, -1, dateadd(d, 1, dbo.StartOfDay(@.date)))
END
StartOfDay works fine, I've tested it extensively. However EndOfDay returns
the start of tomorrow. Any idea why? I tried upping the -1 to -100 with no
effect, and it only starts to work when the number is => than 1 second. That
might work OK for my needs, but I'd much prefer to understand what's going
on
here and fix it.
Maury

"end of day" function?

I'm trying to write a function that returns the last millisecond of a day.
That way I can do queries like...
... WHERE entrydate >= startOfDay(getdate()) and entrydate >= endOfDay(getdate())
Now it would be nicer if there was simply a "date" type, as opposed to
datetime, but whatever.
Anyway, I tried this...
ALTER FUNCTION dbo.EndOfDay
(@.date datetime) RETURNS datetime
BEGIN
RETURN dateadd(ms, -1, dateadd(d, 1, dbo.StartOfDay(@.date)))
END
StartOfDay works fine, I've tested it extensively. However EndOfDay returns
the start of tomorrow. Any idea why? I tried upping the -1 to -100 with no
effect, and it only starts to work when the number is => than 1 second. That
might work OK for my needs, but I'd much prefer to understand what's going on
here and fix it.
MauryMaury Markowitz,
I think you meant:
> ... WHERE entrydate >= startOfDay(getdate()) and entrydate >=> endOfDay(getdate())
...
WHERE entrydate >= startOfDay(getdate()) and entrydate <= endOfDay(getdate())
...
for this kind of filter, it is better to use this pattern:
...
WHERE entrydate >= convert(char(8), getdate(), 112) and entrydate <
dateadd(day, 1, convert(char(8), getdate(), 112))
...
AMB
"Maury Markowitz" wrote:
> I'm trying to write a function that returns the last millisecond of a day.
> That way I can do queries like...
> ... WHERE entrydate >= startOfDay(getdate()) and entrydate >=> endOfDay(getdate())
> Now it would be nicer if there was simply a "date" type, as opposed to
> datetime, but whatever.
> Anyway, I tried this...
> ALTER FUNCTION dbo.EndOfDay
> (@.date datetime) RETURNS datetime
> BEGIN
> RETURN dateadd(ms, -1, dateadd(d, 1, dbo.StartOfDay(@.date)))
> END
> StartOfDay works fine, I've tested it extensively. However EndOfDay returns
> the start of tomorrow. Any idea why? I tried upping the -1 to -100 with no
> effect, and it only starts to work when the number is => than 1 second. That
> might work OK for my needs, but I'd much prefer to understand what's going on
> here and fix it.
> Maury
>|||"Alejandro Mesa" wrote:
> for this kind of filter, it is better to use this pattern:
> ...
> WHERE entrydate >= convert(char(8), getdate(), 112) and entrydate <
> dateadd(day, 1, convert(char(8), getdate(), 112))
> ...
Sure, but why doesn't it work? Why is tommorrow -1 ms still tomorrow?
Maury|||Maury Markowitz,
If you check BOL, you can read that SQL Server stores datatiem data type to
an accuracy of one three-hundredth of a second (equivalent to 3.33
milliseconds or 0.00333 seconds). So the last millisecond will be 997, 998
and 999 will be interprete as tomorrow. That is why it recommended to use the
other pattern.
AMB
"Maury Markowitz" wrote:
> "Alejandro Mesa" wrote:
> > for this kind of filter, it is better to use this pattern:
> > ...
> > WHERE entrydate >= convert(char(8), getdate(), 112) and entrydate <
> > dateadd(day, 1, convert(char(8), getdate(), 112))
> > ...
> Sure, but why doesn't it work? Why is tommorrow -1 ms still tomorrow?
> Maury|||SQL's DATETIME datatype is only precise to 3ms so the value gets
rounded up to the following day. Use >= and < as Alejandro suggests. It
makes queries simpler and easier to read plus it's perhaps unwise to
rely on DATETIME always being stored to the nearest 3ms - maybe that
might change in the future.
--
David Portas
SQL Server MVP
--|||"Alejandro Mesa" wrote:
> an accuracy of one three-hundredth of a second (equivalent to 3.33
> milliseconds or 0.00333 seconds). So the last millisecond will be 997, 998
Ahhh. Thanks!
Maury|||Although I agree with the other respondents, I do always loving interesting
questions, and this is one of them.
First of all, the DATETIME data type is stored as two INT data types. The
first is a count of days from the reference date, where 0 day = 1/1/1900.
The second is a count of "ticks" since midnight, where each "tick" is
1/300th of 1 second ~ 3.33 ms.
It is my opinion that whenever precision is required, always use the
primative data types. So, try this:
CREATE FUNCTION dbo.EndofDay
(@.date AS DATETIME)
RETURNS DATETIME
AS
/*
**
** The DATETIME data type is really two
** Integer segments in binary representation.
**
** The first segment counts the number of days
** from a reference date (day 0 = 1/1/1900).
**
** The Second segment counts the number of "ticks"
** since midnight, where a "tick" is defined as 1/300th of 1 second.
**
** This function manipulates the time segment by truncating
** that segment from the original DATETIME parameter and
** replacing it with one of maximum count.
**
*/
BEGIN
DECLARE @.intDatePart AS INT
,@.intTimePart AS INT
,@.vbnDateTime AS VARBINARY(8)
,@.dtmDateTime AS DATETIME
-- Make sure the parameter was passed correctly.
SET @.dtmDateTime =COALESCE(@.date, 0)
-- Convert the data type to one that can be character manipulated.
SET @.vbnDateTime =CAST(@.dtmDateTime AS VARBINARY(8))
-- Slice out the first 4 characters as the date part.
SET @.intDatePart =CAST(CAST(LEFT(@.vbnDateTime, 4) AS VARBINARY(4)) AS INT)
-- Since we want the end of the same day,
-- we set to max "tick" before the next day rollover.
-- 24 hours x 60 minutes x 60 seconds x 300 1/300ths of a second.
SET @.intTimePart = 25919999
-- Now convert our pieces back to the correct data type.
SET @.dtmDateTime =CAST(CAST(
CAST(CAST(@.intDatePart AS VARBINARY(4)) AS VARCHAR(4)) +
CAST(CAST(@.intTimePart AS VARBINARY(4)) AS VARCHAR(4))
AS VARBINARY(8))
AS DATETIME)
RETURN @.dtmDateTime
END
Good Luck.
Sincerely,
Anthony Thomas
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:8EEE1EB7-03FD-48BC-8CF9-7B3E50B52E28@.microsoft.com...
I'm trying to write a function that returns the last millisecond of a day.
That way I can do queries like...
... WHERE entrydate >= startOfDay(getdate()) and entrydate >=endOfDay(getdate())
Now it would be nicer if there was simply a "date" type, as opposed to
datetime, but whatever.
Anyway, I tried this...
ALTER FUNCTION dbo.EndOfDay
(@.date datetime) RETURNS datetime
BEGIN
RETURN dateadd(ms, -1, dateadd(d, 1, dbo.StartOfDay(@.date)))
END
StartOfDay works fine, I've tested it extensively. However EndOfDay returns
the start of tomorrow. Any idea why? I tried upping the -1 to -100 with no
effect, and it only starts to work when the number is => than 1 second. That
might work OK for my needs, but I'd much prefer to understand what's going
on
here and fix it.
Maury

"end of day" function?

I'm trying to write a function that returns the last millisecond of a day.
That way I can do queries like...
... WHERE entrydate >= startOfDay(getdate()) and entrydate >=
endOfDay(getdate())
Now it would be nicer if there was simply a "date" type, as opposed to
datetime, but whatever.
Anyway, I tried this...
ALTER FUNCTION dbo.EndOfDay
(@.date datetime) RETURNS datetime
BEGIN
RETURN dateadd(ms, -1, dateadd(d, 1, dbo.StartOfDay(@.date)))
END
StartOfDay works fine, I've tested it extensively. However EndOfDay returns
the start of tomorrow. Any idea why? I tried upping the -1 to -100 with no
effect, and it only starts to work when the number is => than 1 second. That
might work OK for my needs, but I'd much prefer to understand what's going on
here and fix it.
Maury
Maury Markowitz,
I think you meant:

> ... WHERE entrydate >= startOfDay(getdate()) and entrydate >=
> endOfDay(getdate())
...
WHERE entrydate >= startOfDay(getdate()) and entrydate <= endOfDay(getdate())
...
for this kind of filter, it is better to use this pattern:
...
WHERE entrydate >= convert(char(8), getdate(), 112) and entrydate <
dateadd(day, 1, convert(char(8), getdate(), 112))
...
AMB
"Maury Markowitz" wrote:

> I'm trying to write a function that returns the last millisecond of a day.
> That way I can do queries like...
> ... WHERE entrydate >= startOfDay(getdate()) and entrydate >=
> endOfDay(getdate())
> Now it would be nicer if there was simply a "date" type, as opposed to
> datetime, but whatever.
> Anyway, I tried this...
> ALTER FUNCTION dbo.EndOfDay
> (@.date datetime) RETURNS datetime
> BEGIN
> RETURN dateadd(ms, -1, dateadd(d, 1, dbo.StartOfDay(@.date)))
> END
> StartOfDay works fine, I've tested it extensively. However EndOfDay returns
> the start of tomorrow. Any idea why? I tried upping the -1 to -100 with no
> effect, and it only starts to work when the number is => than 1 second. That
> might work OK for my needs, but I'd much prefer to understand what's going on
> here and fix it.
> Maury
>
|||"Alejandro Mesa" wrote:
> for this kind of filter, it is better to use this pattern:
> ...
> WHERE entrydate >= convert(char(8), getdate(), 112) and entrydate <
> dateadd(day, 1, convert(char(8), getdate(), 112))
> ...
Sure, but why doesn't it work? Why is tommorrow -1 ms still tomorrow?
Maury
|||Maury Markowitz,
If you check BOL, you can read that SQL Server stores datatiem data type to
an accuracy of one three-hundredth of a second (equivalent to 3.33
milliseconds or 0.00333 seconds). So the last millisecond will be 997, 998
and 999 will be interprete as tomorrow. That is why it recommended to use the
other pattern.
AMB
"Maury Markowitz" wrote:

> "Alejandro Mesa" wrote:
> Sure, but why doesn't it work? Why is tommorrow -1 ms still tomorrow?
> Maury
|||SQL's DATETIME datatype is only precise to 3ms so the value gets
rounded up to the following day. Use >= and < as Alejandro suggests. It
makes queries simpler and easier to read plus it's perhaps unwise to
rely on DATETIME always being stored to the nearest 3ms - maybe that
might change in the future.
David Portas
SQL Server MVP
|||"Alejandro Mesa" wrote:
> an accuracy of one three-hundredth of a second (equivalent to 3.33
> milliseconds or 0.00333 seconds). So the last millisecond will be 997, 998
Ahhh. Thanks!
Maury
|||Although I agree with the other respondents, I do always loving interesting
questions, and this is one of them.
First of all, the DATETIME data type is stored as two INT data types. The
first is a count of days from the reference date, where 0 day = 1/1/1900.
The second is a count of "ticks" since midnight, where each "tick" is
1/300th of 1 second ~ 3.33 ms.
It is my opinion that whenever precision is required, always use the
primative data types. So, try this:
CREATE FUNCTION dbo.EndofDay
(@.date AS DATETIME)
RETURNS DATETIME
AS
/*
**
** The DATETIME data type is really two
** Integer segments in binary representation.
**
** The first segment counts the number of days
** from a reference date (day 0 = 1/1/1900).
**
** The Second segment counts the number of "ticks"
** since midnight, where a "tick" is defined as 1/300th of 1 second.
**
** This function manipulates the time segment by truncating
** that segment from the original DATETIME parameter and
** replacing it with one of maximum count.
**
*/
BEGIN
DECLARE @.intDatePart AS INT
,@.intTimePart AS INT
,@.vbnDateTime AS VARBINARY(8)
,@.dtmDateTime AS DATETIME
-- Make sure the parameter was passed correctly.
SET @.dtmDateTime =
COALESCE(@.date, 0)
-- Convert the data type to one that can be character manipulated.
SET @.vbnDateTime =
CAST(@.dtmDateTime AS VARBINARY(8))
-- Slice out the first 4 characters as the date part.
SET @.intDatePart =
CAST(CAST(LEFT(@.vbnDateTime, 4) AS VARBINARY(4)) AS INT)
-- Since we want the end of the same day,
-- we set to max "tick" before the next day rollover.
-- 24 hours x 60 minutes x 60 seconds x 300 1/300ths of a second.
SET @.intTimePart = 25919999
-- Now convert our pieces back to the correct data type.
SET @.dtmDateTime =
CAST(CAST(
CAST(CAST(@.intDatePart AS VARBINARY(4)) AS VARCHAR(4)) +
CAST(CAST(@.intTimePart AS VARBINARY(4)) AS VARCHAR(4))
AS VARBINARY(8))
AS DATETIME)
RETURN @.dtmDateTime
END
Good Luck.
Sincerely,
Anthony Thomas

"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:8EEE1EB7-03FD-48BC-8CF9-7B3E50B52E28@.microsoft.com...
I'm trying to write a function that returns the last millisecond of a day.
That way I can do queries like...
... WHERE entrydate >= startOfDay(getdate()) and entrydate >=
endOfDay(getdate())
Now it would be nicer if there was simply a "date" type, as opposed to
datetime, but whatever.
Anyway, I tried this...
ALTER FUNCTION dbo.EndOfDay
(@.date datetime) RETURNS datetime
BEGIN
RETURN dateadd(ms, -1, dateadd(d, 1, dbo.StartOfDay(@.date)))
END
StartOfDay works fine, I've tested it extensively. However EndOfDay returns
the start of tomorrow. Any idea why? I tried upping the -1 to -100 with no
effect, and it only starts to work when the number is => than 1 second. That
might work OK for my needs, but I'd much prefer to understand what's going
on
here and fix it.
Maury

"Auto Create Statistics" make queries run (really) slower

Hello,
I'm experiencing a strange problem with query performance runing on
SQL2005. The database has 10+ tables, but we need to run really
specific queries in only 1 table with these caracteristics :
- 1 million rows
- we run everyday a few thousands queries on that table, each query
is unique (adhoc plan), and not parameterizable. (we cannot optimize
this)
- rows have a lot of nvarchar data
- all queries use a lot of LIKE / NOT LIKE statement (we cannot find
any work-around to that point, Fulltext is not adequate in that case)
- when LIKE operations are performed on columns, we always create a
duplicate column to optimize some search stuff, like putting
everything in Low Case, using Latin1_General_BIN collation, ...
- we have some indexes on short nvarchar columns, only those where we
use an exact '=' statemen
- we have another index on a float column
- all usefull indexes and statistics are manually created on that
table
- the nvarchar content of the table changes only once a day. It means
we do all optimization (indexes / stats) just after the update, and
there is no change on nvarchar data until the next update (24 hours
later)
I found that when "Auto Create Statistics" is enabled on the database,
that queries are really runing slower :
- "Auto Create Statistics" enabled : 57 min to run all queries
- "Auto Create Statistics" disabled and all auto-created stats
deleted : 7 min to run the same queries
It means that queries are running 8x slower when "Auto Create
Statistics" is enabled!
Another interesting point : just after disabling "Auto Create
Statistics", the queries continue to perform slowly until I manually
delete all statistics created automatically for that table (the one
begining with "_WA_Sys_"). It could mean that it's not a stat creation
issue, but only the existence of that statistics that could change the
query plan. But in both cases, the execution plan for the same query
seems to be exactly the same (same aspect, same costs). I also tried
to enable the Async stats update : no change.
The problem is that for all the other tables in the database, the
"Auto Create Statistics" is a good thing and useful. But not for that
specific table. Two questions :
- Is it possible to disable "Auto Create Statistics" on a specific
table? (I did not find anything about that in the BOL)
- If not, is there another work-around to deal with that kind of
performance drop?
Thanks.We need the query plan with before and after to tell you why. It sounds like
there was an inaccurate estimate which might be fixed with a larger sample
than the default but that is a guess. SQL Server 2005 keeps better stats on
string column and it may be able to do a seek on a covering index in a LIKE
query especially with a larger sample. It just needs to be tested heavily.
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<pinformaticien@.yahoo.fr> wrote in message
news:8384b744-0444-4f91-a1ce-b4d302317302@.13g2000hsb.googlegroups.com...
> Hello,
> I'm experiencing a strange problem with query performance runing on
> SQL2005. The database has 10+ tables, but we need to run really
> specific queries in only 1 table with these caracteristics :
> - 1 million rows
> - we run everyday a few thousands queries on that table, each query
> is unique (adhoc plan), and not parameterizable. (we cannot optimize
> this)
> - rows have a lot of nvarchar data
> - all queries use a lot of LIKE / NOT LIKE statement (we cannot find
> any work-around to that point, Fulltext is not adequate in that case)
> - when LIKE operations are performed on columns, we always create a
> duplicate column to optimize some search stuff, like putting
> everything in Low Case, using Latin1_General_BIN collation, ...
> - we have some indexes on short nvarchar columns, only those where we
> use an exact '=' statemen
> - we have another index on a float column
> - all usefull indexes and statistics are manually created on that
> table
> - the nvarchar content of the table changes only once a day. It means
> we do all optimization (indexes / stats) just after the update, and
> there is no change on nvarchar data until the next update (24 hours
> later)
> I found that when "Auto Create Statistics" is enabled on the database,
> that queries are really runing slower :
> - "Auto Create Statistics" enabled : 57 min to run all queries
> - "Auto Create Statistics" disabled and all auto-created stats
> deleted : 7 min to run the same queries
> It means that queries are running 8x slower when "Auto Create
> Statistics" is enabled!
> Another interesting point : just after disabling "Auto Create
> Statistics", the queries continue to perform slowly until I manually
> delete all statistics created automatically for that table (the one
> begining with "_WA_Sys_"). It could mean that it's not a stat creation
> issue, but only the existence of that statistics that could change the
> query plan. But in both cases, the execution plan for the same query
> seems to be exactly the same (same aspect, same costs). I also tried
> to enable the Async stats update : no change.
> The problem is that for all the other tables in the database, the
> "Auto Create Statistics" is a good thing and useful. But not for that
> specific table. Two questions :
> - Is it possible to disable "Auto Create Statistics" on a specific
> table? (I did not find anything about that in the BOL)
> - If not, is there another work-around to deal with that kind of
> performance drop?
> Thanks.|||For what I've tried, creating then updating statistics on nvarchar
columns with the "WITH FULLSCAN" clause doesn't help. But here are
some interesting results : I setup a test server, and ran 2 times 10
queries, first time with "Auto Create Statistics" enabled, second time
with "Auto Create Statistics" disabled. Between the 2 tests, I deleted
all the automatically created statistics (the one begining with
"_WA_Sys_"), then restarted SQL server service. Here are the results
for the following query
Select * from sys.dm_exec_query_optimizer_info where counter in
('optimizations','elapsed time')
"Auto Create Statistics" enabled
optimizations 11 1
elapsed time 11 2,80751895306448
"Auto Create Statistics" disabled
optimizations 11 1
elapsed time 11 0,0665338534973798
It confirms that all the performance drop goes in optimization time
(2.8 sec average vs 0.07 sec), that finally almost doesn't otimize
anything in my case (it leads to the same execution plan is the same
is both cases). It means I need to find a way to disable / reduce that
optimization time when "Auto Create Statistics" is enabled. Any idea?
Is it possible to disable "Auto Create Statistics" on a specific
table?|||It sounds like you are right. It sounds like optimizer is spending more time
try to compile since there are more options only to come up with the same
plan. You can disable autostats on a particular table with UPDATE STATISTICS
.. WITH NORECOMPUTE.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<pinformaticien@.yahoo.fr> wrote in message
news:1e199977-1bc2-4614-ad72-036bb3111ce6@.d21g2000prf.googlegroups.com...
> For what I've tried, creating then updating statistics on nvarchar
> columns with the "WITH FULLSCAN" clause doesn't help. But here are
> some interesting results : I setup a test server, and ran 2 times 10
> queries, first time with "Auto Create Statistics" enabled, second time
> with "Auto Create Statistics" disabled. Between the 2 tests, I deleted
> all the automatically created statistics (the one begining with
> "_WA_Sys_"), then restarted SQL server service. Here are the results
> for the following query
> Select * from sys.dm_exec_query_optimizer_info where counter in
> ('optimizations','elapsed time')
> "Auto Create Statistics" enabled
> optimizations 11 1
> elapsed time 11 2,80751895306448
> "Auto Create Statistics" disabled
> optimizations 11 1
> elapsed time 11 0,0665338534973798
> It confirms that all the performance drop goes in optimization time
> (2.8 sec average vs 0.07 sec), that finally almost doesn't otimize
> anything in my case (it leads to the same execution plan is the same
> is both cases). It means I need to find a way to disable / reduce that
> optimization time when "Auto Create Statistics" is enabled. Any idea?
> Is it possible to disable "Auto Create Statistics" on a specific
> table?|||Thanks for the reply. UPDATE STATISTICS ... WITH NORECOMPUTE would
just avoid statistics to be updated. In my case, it's not the stat
update which is problematic, but the existence of the automatically
created statistics (as they badly influence the query optimizer on
that table). One solution could be to move that table on a dedicated
database and turn "Auto Create Statistics" OFF, but we would like to
avoid this solution.
I'm really surprised that SQL Server doesn't allow to disable
automatic creation of statistics on a per table basis. That could be
just really helpful in some cases.|||Ok I think I've got an interesting workaround. As we cannot disable
autocreate statistics for a specific table, the idea is to update
those unwanted stats with two clauses :
- SAMPLE 0 ROWS : to empty the statistics, so that they don't
infuence the query optimizer anymore.
- NORECOMPUTE : to avoid the "auto update stats" option to repopulate
them later
Here is the SQL statement I wrote to do this automatically on SQL 2005
(you just need to set @.dbtname correctly). It's just necessary to run
it from time to time, to ensure that new autocreated stats are
disabled.
The first tests shows exactly the same performance compared to queries
with "auto create stats" disabled.
DECLARE @.dbtname NVARCHAR(255)
SET @.dbtname = 'You_Table_Name_Here'
DECLARE c CURSOR FOR
SELECT name FROM sys.stats WHERE object_id = object_id(@.dbtname) AND
auto_created = 1
DECLARE @.statname NVARCHAR(255)
OPEN c
FETCH next FROM c INTO @.statname
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.statname
EXEC ('UPDATE STATISTICS ' + @.dbtname + ' (' + @.statname + ') WITH
SAMPLE 0 ROWS, NORECOMPUTE')
FETCH NEXT FROM c INTO @.statname
END
CLOSE c
DEALLOCATE c|||pinformaticien@.yahoo.fr wrote:
> Ok I think I've got an interesting workaround. As we cannot disable
> autocreate statistics for a specific table, the idea is to update
> those unwanted stats with two clauses :
> - SAMPLE 0 ROWS : to empty the statistics, so that they don't
> infuence the query optimizer anymore.
> - NORECOMPUTE : to avoid the "auto update stats" option to repopulate
> them later
> Here is the SQL statement I wrote to do this automatically on SQL 2005
> (you just need to set @.dbtname correctly). It's just necessary to run
> it from time to time, to ensure that new autocreated stats are
> disabled.
> The first tests shows exactly the same performance compared to queries
> with "auto create stats" disabled.
> DECLARE @.dbtname NVARCHAR(255)
> SET @.dbtname = 'You_Table_Name_Here'
> DECLARE c CURSOR FOR
> SELECT name FROM sys.stats WHERE object_id = object_id(@.dbtname) AND
> auto_created = 1
> DECLARE @.statname NVARCHAR(255)
> OPEN c
> FETCH next FROM c INTO @.statname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT @.statname
> EXEC ('UPDATE STATISTICS ' + @.dbtname + ' (' + @.statname + ') WITH
> SAMPLE 0 ROWS, NORECOMPUTE')
> FETCH NEXT FROM c INTO @.statname
> END
> CLOSE c
> DEALLOCATE c
Auto create statistics is only invoked if there are no existing
statistics for that column. So another solution to prevent automatic
statistics creation for a particular column is to manually create
statistics before the query is run that triggers the statistics
creation.
It could be as simple as:
create statistics ST_Test on dbo.test(my_column) with sample 0 rows,
norecompute
before running a query like
select * from dbo.test where my_column = 5
--
Gert-Jan

"Auto Create Statistics" make queries run (really) slower

Hello,
I'm experiencing a strange problem with query performance runing on
SQL2005. The database has 10+ tables, but we need to run really
specific queries in only 1 table with these caracteristics :
- 1 million rows
- we run everyday a few thousands queries on that table, each query
is unique (adhoc plan), and not parameterizable. (we cannot optimize
this)
- rows have a lot of nvarchar data
- all queries use a lot of LIKE / NOT LIKE statement (we cannot find
any work-around to that point, Fulltext is not adequate in that case)
- when LIKE operations are performed on columns, we always create a
duplicate column to optimize some search stuff, like putting
everything in Low Case, using Latin1_General_BIN collation, ...
- we have some indexes on short nvarchar columns, only those where we
use an exact '=' statemen
- we have another index on a float column
- all usefull indexes and statistics are manually created on that
table
- the nvarchar content of the table changes only once a day. It means
we do all optimization (indexes / stats) just after the update, and
there is no change on nvarchar data until the next update (24 hours
later)
I found that when "Auto Create Statistics" is enabled on the database,
that queries are really runing slower :
- "Auto Create Statistics" enabled : 57 min to run all queries
- "Auto Create Statistics" disabled and all auto-created stats
deleted : 7 min to run the same queries
It means that queries are running 8x slower when "Auto Create
Statistics" is enabled!
Another interesting point : just after disabling "Auto Create
Statistics", the queries continue to perform slowly until I manually
delete all statistics created automatically for that table (the one
begining with "_WA_Sys_"). It could mean that it's not a stat creation
issue, but only the existence of that statistics that could change the
query plan. But in both cases, the execution plan for the same query
seems to be exactly the same (same aspect, same costs). I also tried
to enable the Async stats update : no change.
The problem is that for all the other tables in the database, the
"Auto Create Statistics" is a good thing and useful. But not for that
specific table. Two questions :
- Is it possible to disable "Auto Create Statistics" on a specific
table? (I did not find anything about that in the BOL)
- If not, is there another work-around to deal with that kind of
performance drop?
Thanks.
We need the query plan with before and after to tell you why. It sounds like
there was an inaccurate estimate which might be fixed with a larger sample
than the default but that is a guess. SQL Server 2005 keeps better stats on
string column and it may be able to do a seek on a covering index in a LIKE
query especially with a larger sample. It just needs to be tested heavily.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<pinformaticien@.yahoo.fr> wrote in message
news:8384b744-0444-4f91-a1ce-b4d302317302@.13g2000hsb.googlegroups.com...
> Hello,
> I'm experiencing a strange problem with query performance runing on
> SQL2005. The database has 10+ tables, but we need to run really
> specific queries in only 1 table with these caracteristics :
> - 1 million rows
> - we run everyday a few thousands queries on that table, each query
> is unique (adhoc plan), and not parameterizable. (we cannot optimize
> this)
> - rows have a lot of nvarchar data
> - all queries use a lot of LIKE / NOT LIKE statement (we cannot find
> any work-around to that point, Fulltext is not adequate in that case)
> - when LIKE operations are performed on columns, we always create a
> duplicate column to optimize some search stuff, like putting
> everything in Low Case, using Latin1_General_BIN collation, ...
> - we have some indexes on short nvarchar columns, only those where we
> use an exact '=' statemen
> - we have another index on a float column
> - all usefull indexes and statistics are manually created on that
> table
> - the nvarchar content of the table changes only once a day. It means
> we do all optimization (indexes / stats) just after the update, and
> there is no change on nvarchar data until the next update (24 hours
> later)
> I found that when "Auto Create Statistics" is enabled on the database,
> that queries are really runing slower :
> - "Auto Create Statistics" enabled : 57 min to run all queries
> - "Auto Create Statistics" disabled and all auto-created stats
> deleted : 7 min to run the same queries
> It means that queries are running 8x slower when "Auto Create
> Statistics" is enabled!
> Another interesting point : just after disabling "Auto Create
> Statistics", the queries continue to perform slowly until I manually
> delete all statistics created automatically for that table (the one
> begining with "_WA_Sys_"). It could mean that it's not a stat creation
> issue, but only the existence of that statistics that could change the
> query plan. But in both cases, the execution plan for the same query
> seems to be exactly the same (same aspect, same costs). I also tried
> to enable the Async stats update : no change.
> The problem is that for all the other tables in the database, the
> "Auto Create Statistics" is a good thing and useful. But not for that
> specific table. Two questions :
> - Is it possible to disable "Auto Create Statistics" on a specific
> table? (I did not find anything about that in the BOL)
> - If not, is there another work-around to deal with that kind of
> performance drop?
> Thanks.
|||For what I've tried, creating then updating statistics on nvarchar
columns with the "WITH FULLSCAN" clause doesn't help. But here are
some interesting results : I setup a test server, and ran 2 times 10
queries, first time with "Auto Create Statistics" enabled, second time
with "Auto Create Statistics" disabled. Between the 2 tests, I deleted
all the automatically created statistics (the one begining with
"_WA_Sys_"), then restarted SQL server service. Here are the results
for the following query
Select * from sys.dm_exec_query_optimizer_info where counter in
('optimizations','elapsed time')
"Auto Create Statistics" enabled
optimizations 11 1
elapsed time 11 2,80751895306448
"Auto Create Statistics" disabled
optimizations 11 1
elapsed time 11 0,0665338534973798
It confirms that all the performance drop goes in optimization time
(2.8 sec average vs 0.07 sec), that finally almost doesn't otimize
anything in my case (it leads to the same execution plan is the same
is both cases). It means I need to find a way to disable / reduce that
optimization time when "Auto Create Statistics" is enabled. Any idea?
Is it possible to disable "Auto Create Statistics" on a specific
table?
|||It sounds like you are right. It sounds like optimizer is spending more time
try to compile since there are more options only to come up with the same
plan. You can disable autostats on a particular table with UPDATE STATISTICS
... WITH NORECOMPUTE.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<pinformaticien@.yahoo.fr> wrote in message
news:1e199977-1bc2-4614-ad72-036bb3111ce6@.d21g2000prf.googlegroups.com...
> For what I've tried, creating then updating statistics on nvarchar
> columns with the "WITH FULLSCAN" clause doesn't help. But here are
> some interesting results : I setup a test server, and ran 2 times 10
> queries, first time with "Auto Create Statistics" enabled, second time
> with "Auto Create Statistics" disabled. Between the 2 tests, I deleted
> all the automatically created statistics (the one begining with
> "_WA_Sys_"), then restarted SQL server service. Here are the results
> for the following query
> Select * from sys.dm_exec_query_optimizer_info where counter in
> ('optimizations','elapsed time')
> "Auto Create Statistics" enabled
> optimizations 11 1
> elapsed time 11 2,80751895306448
> "Auto Create Statistics" disabled
> optimizations 11 1
> elapsed time 11 0,0665338534973798
> It confirms that all the performance drop goes in optimization time
> (2.8 sec average vs 0.07 sec), that finally almost doesn't otimize
> anything in my case (it leads to the same execution plan is the same
> is both cases). It means I need to find a way to disable / reduce that
> optimization time when "Auto Create Statistics" is enabled. Any idea?
> Is it possible to disable "Auto Create Statistics" on a specific
> table?
|||Thanks for the reply. UPDATE STATISTICS ... WITH NORECOMPUTE would
just avoid statistics to be updated. In my case, it's not the stat
update which is problematic, but the existence of the automatically
created statistics (as they badly influence the query optimizer on
that table). One solution could be to move that table on a dedicated
database and turn "Auto Create Statistics" OFF, but we would like to
avoid this solution.
I'm really surprised that SQL Server doesn't allow to disable
automatic creation of statistics on a per table basis. That could be
just really helpful in some cases.
|||Ok I think I've got an interesting workaround. As we cannot disable
autocreate statistics for a specific table, the idea is to update
those unwanted stats with two clauses :
- SAMPLE 0 ROWS : to empty the statistics, so that they don't
infuence the query optimizer anymore.
- NORECOMPUTE : to avoid the "auto update stats" option to repopulate
them later
Here is the SQL statement I wrote to do this automatically on SQL 2005
(you just need to set @.dbtname correctly). It's just necessary to run
it from time to time, to ensure that new autocreated stats are
disabled.
The first tests shows exactly the same performance compared to queries
with "auto create stats" disabled.
DECLARE @.dbtname NVARCHAR(255)
SET @.dbtname = 'You_Table_Name_Here'
DECLARE c CURSOR FOR
SELECT name FROM sys.stats WHERE object_id = object_id(@.dbtname) AND
auto_created = 1
DECLARE @.statname NVARCHAR(255)
OPEN c
FETCH next FROM c INTO @.statname
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.statname
EXEC ('UPDATE STATISTICS ' + @.dbtname + ' (' + @.statname + ') WITH
SAMPLE 0 ROWS, NORECOMPUTE')
FETCH NEXT FROM c INTO @.statname
END
CLOSE c
DEALLOCATE c