Poorly Performing Unicode Query

The main performance issue is performaing the "N" function infront of the strings in the where clause. I get 20x-45x perfromance boost by removing it, but unfortunately my reporting tool doesn't allow me that option.

Anything I can do on the DB end to solve this? The DB is not unicode.

tia,

paul

SELECT

MAX(Dim9s.FunctionDescription) [Dim9s_FunctionDescription],

MAX(Dim9s.BU_Description) [Dim9s_BU_Description],

SUM(convert(decimal(18,2),ixvTransAx.GLS_AMOUNTMST)) [GLS_AMOUNTMST]

FROM ixvLedgerBalanceDimTransAx AS ixvTransAx

JOIN ixvDim9s AS Dim9s ON (ixvTransAx.Dim9Id=Dim9s.Id)

LEFT OUTER JOIN ixvAccounts AS Accounts ON (ixvTransAx.AccountId=Accounts.Id)

JOIN ixvCompany AS CompanyId ON (ixvTransAx.CompanyId=CompanyId.Id)

WHERE 1=1

AND ((1=1)) AND ((Dim9s.Client ='M99') OR Dim9s.Client IS NULL)

AND ((1=1)) AND ((Accounts.Client ='M99') OR Accounts.Client IS NULL)

AND ((Accounts.Code BETWEEN N' 5000' AND N' 6796') AND (Accounts.ACCOUNTPLTYPE != N'^AC_Total^'))

AND ((Dim9s.BU_Description = N'PI/Dwights') AND (Dim9s.OrganizationalViewDescription = N'2006 Current'))

AND ((1=1))

AND (CompanyId.Code = N'56')

AND ((ixvTransAx.TransDate >= '2006-3-1') AND (ixvTransAx.TransDate < '2006-4-1'))

AND NOT (ixvTransAx.GLS_AMOUNTMST IS NULL)

GROUP BY Dim9s.FunctionDescription, Dim9s.BU_Description

Hi,

I recommend that you post your question on the Transact-SQL forum. The Documentation forum is usually not the best spot for code-specific questions.

Regards,

Gail

Poor query performance with: set statistics time on (SQL2K5)

Hi,
I try this query:
--
use AdventureWorks
go
select c.*,o.* from sales.salesorderheader o join sales.customer c
on o.customerid=c.customerid
go
set statistics time on
go
-- try the query again
select c.*,o.* from sales.salesorderheader o join sales.customer c
on o.customerid=c.customerid
--
the second query after setting "statistics time" is very slow on my
computer. I'd like to know if anybody else has the same problem?
Thanks in advance,
LeilaI tested it on 2005 SP1 using Management Studio. I ran it as give,
and with the order reversed. I also added select getdate() lines to
give a measure of elapsed time. Results were returned to a grid.
With statistics time on the query took a bit more than twice as long
to run.
Roy Harvey
Beacon Falls, CT
On Wed, 3 May 2006 03:39:17 +0430, "Leila" <Leilas@.hotpop.com> wrote:

>Hi,
>I try this query:
>--
>use AdventureWorks
>go
>select c.*,o.* from sales.salesorderheader o join sales.customer c
>on o.customerid=c.customerid
>go
>set statistics time on
>go
>-- try the query again
>select c.*,o.* from sales.salesorderheader o join sales.customer c
>on o.customerid=c.customerid
>--
>the second query after setting "statistics time" is very slow on my
>computer. I'd like to know if anybody else has the same problem?
>Thanks in advance,
>Leila|||Leila and Roy,
I can also reproduce this behavior. I don't have an answer
or workaround, but I'd suggest one of you file it as a bug
at http://lab.msdn.microsoft.com/productfeedback/
Steve Kass
Drew University
Leila wrote:

>Hi,
>I try this query:
>--
>use AdventureWorks
>go
>select c.*,o.* from sales.salesorderheader o join sales.customer c
>on o.customerid=c.customerid
>go
>set statistics time on
>go
>-- try the query again
>select c.*,o.* from sales.salesorderheader o join sales.customer c
>on o.customerid=c.customerid
>--
>the second query after setting "statistics time" is very slow on my
>computer. I'd like to know if anybody else has the same problem?
>Thanks in advance,
>Leila
>
>
>|||On Wed, 03 May 2006 00:00:42 -0400, Steve Kass <skass@.drew.edu> wrote:
>Leila and Roy,
>I can also reproduce this behavior. I don't have an answer
>or workaround, but I'd suggest one of you file it as a bug
>at http://lab.msdn.microsoft.com/productfeedback/
>Steve Kass
>Drew University
Yikes.
Just for the record, I've been using statistics io on a lot over the
last year or so, and on SQL2000 it generally had a small impact, maybe
5 to 10 percent.
Josh

Poor query performance with: set statistics time on (SQL2K5)

Hi,
I try this query:
--
use AdventureWorks
go
select c.*,o.* from sales.salesorderheader o join sales.customer c
on o.customerid=c.customerid
go
set statistics time on
go
-- try the query again
select c.*,o.* from sales.salesorderheader o join sales.customer c
on o.customerid=c.customerid
--
the second query after setting "statistics time" is very slow on my
computer. I'd like to know if anybody else has the same problem?
Thanks in advance,
LeilaI tested it on 2005 SP1 using Management Studio. I ran it as give,
and with the order reversed. I also added select getdate() lines to
give a measure of elapsed time. Results were returned to a grid.
With statistics time on the query took a bit more than twice as long
to run.
Roy Harvey
Beacon Falls, CT
On Wed, 3 May 2006 03:39:17 +0430, "Leila" <Leilas@.hotpop.com> wrote:
>Hi,
>I try this query:
>--
>use AdventureWorks
>go
>select c.*,o.* from sales.salesorderheader o join sales.customer c
>on o.customerid=c.customerid
>go
>set statistics time on
>go
>-- try the query again
>select c.*,o.* from sales.salesorderheader o join sales.customer c
>on o.customerid=c.customerid
>--
>the second query after setting "statistics time" is very slow on my
>computer. I'd like to know if anybody else has the same problem?
>Thanks in advance,
>Leila|||Leila and Roy,
I can also reproduce this behavior. I don't have an answer
or workaround, but I'd suggest one of you file it as a bug
at http://lab.msdn.microsoft.com/productfeedback/
Steve Kass
Drew University
Leila wrote:
>Hi,
>I try this query:
>--
>use AdventureWorks
>go
>select c.*,o.* from sales.salesorderheader o join sales.customer c
>on o.customerid=c.customerid
>go
>set statistics time on
>go
>-- try the query again
>select c.*,o.* from sales.salesorderheader o join sales.customer c
>on o.customerid=c.customerid
>--
>the second query after setting "statistics time" is very slow on my
>computer. I'd like to know if anybody else has the same problem?
>Thanks in advance,
>Leila
>
>
>|||On Wed, 03 May 2006 00:00:42 -0400, Steve Kass <skass@.drew.edu> wrote:
>Leila and Roy,
>I can also reproduce this behavior. I don't have an answer
>or workaround, but I'd suggest one of you file it as a bug
>at http://lab.msdn.microsoft.com/productfeedback/
>Steve Kass
>Drew University
Yikes.
Just for the record, I've been using statistics io on a lot over the
last year or so, and on SQL2000 it generally had a small impact, maybe
5 to 10 percent.
Josh

Poor query performance with: set statistics time on (SQL2K5)

Hi,
I try this query:
--
use AdventureWorks
go
select c.*,o.* from sales.salesorderheader o join sales.customer c
on o.customerid=c.customerid
go
set statistics time on
go
-- try the query again
select c.*,o.* from sales.salesorderheader o join sales.customer c
on o.customerid=c.customerid
--
the second query after setting "statistics time" is very slow on my
computer. I'd like to know if anybody else has the same problem?
Thanks in advance,
LeilaI tested it on 2005 SP1 using Management Studio. I ran it as give,
and with the order reversed. I also added select getdate() lines to
give a measure of elapsed time. Results were returned to a grid.
With statistics time on the query took a bit more than twice as long
to run.
Roy Harvey
Beacon Falls, CT
On Wed, 3 May 2006 03:39:17 +0430, "Leila" <Leilas@.hotpop.com> wrote:

>Hi,
>I try this query:
>--
>use AdventureWorks
>go
>select c.*,o.* from sales.salesorderheader o join sales.customer c
>on o.customerid=c.customerid
>go
>set statistics time on
>go
>-- try the query again
>select c.*,o.* from sales.salesorderheader o join sales.customer c
>on o.customerid=c.customerid
>--
>the second query after setting "statistics time" is very slow on my
>computer. I'd like to know if anybody else has the same problem?
>Thanks in advance,
>Leila|||Leila and Roy,
I can also reproduce this behavior. I don't have an answer
or workaround, but I'd suggest one of you file it as a bug
at http://lab.msdn.microsoft.com/productfeedback/
Steve Kass
Drew University
Leila wrote:

>Hi,
>I try this query:
>--
>use AdventureWorks
>go
>select c.*,o.* from sales.salesorderheader o join sales.customer c
>on o.customerid=c.customerid
>go
>set statistics time on
>go
>-- try the query again
>select c.*,o.* from sales.salesorderheader o join sales.customer c
>on o.customerid=c.customerid
>--
>the second query after setting "statistics time" is very slow on my
>computer. I'd like to know if anybody else has the same problem?
>Thanks in advance,
>Leila
>
>
>|||On Wed, 03 May 2006 00:00:42 -0400, Steve Kass <skass@.drew.edu> wrote:
>Leila and Roy,
>I can also reproduce this behavior. I don't have an answer
>or workaround, but I'd suggest one of you file it as a bug
>at http://lab.msdn.microsoft.com/productfeedback/
>Steve Kass
>Drew University
Yikes.
Just for the record, I've been using statistics io on a lot over the
last year or so, and on SQL2000 it generally had a small impact, maybe
5 to 10 percent.
Joshsql

Poor plan choice

Hello,
A couple days ago one of our queries suddenly started to perform
abyssmally. The query is pretty straightforward - it joins several
tables all on foreign keys and includes a GROUP BY with COUNT(*). I
looked over the query plan and it looked a little odd so I tried
cutting the query down to see where the issue might be. I eventually
came up with the following:
SELECT COUNT(*)
FROM Table1 T1
INNER JOIN Table2 T2 ON T2.table2_id = T1.table2_id
WHERE T1.my_date = '2004-11-18'
The table and column names have been changed to protect the innocent,
but that is the exact format of the tables. Table1 has about 35M
records. Table2 has about 6.5M records. For the date in question,
Table1 has about 165K records.
There is a non-clustered index on T1.my_date and there is a clustered
index on T2.table2_id.
The query plan for this simple query does an index seek on T1.my_date
as I expected then it does a bookmark lookup (presumably because it
needs T1.table2_id). It then includes parallelism, a hash, and then a
bitmap creation. Meanwhile, it does an index scan using an index on
Table2 that includes a single column that isn't even mentioned in the
query(?!?!). It then uses parallelism and does a hash match/inner
join.
I've done UPDATE STATISTICS using WITH FULLSCAN for both tables and
I've done a DBCC CHECKTABLE on both tables. Neither had any effect. I
also tried to force the query to use the clustered index for Table2.
For the simple query above it doesn't seem to help performance as the
clustered index scan has a very large cost to it (I'm not sure that I
entirely understand why). In the original query it helps substantially
though. Instead of joining the 6.5M records to a lookup table first it
joins it to Table1 first, which cuts down the number of records to the
165K before going about with other joins.
What I'm looking for is any advice on other things that I can look at
or any ideas on why SQL Server might be making these kinds of choices.
I would have thought that the simple query above would have performed
much better than it is currently (~30-35 seconds). I realize that
there has to be a bookmark lookup, but I was still expecting a quick
response from the server based on the indexes.
Because of the table sizes, etc. I don't expect anyone to reproduce my
results, so please don't ask me to provide DDL for all of the tables
involved. If you have some ideas or even just guesses great, if not
then that's ok too.
Thanks,
-Tom.
Thomas, see inline
"Thomas R. Hummel" wrote:
> Hello,
> A couple days ago one of our queries suddenly started to perform
> abyssmally. The query is pretty straightforward - it joins several
> tables all on foreign keys and includes a GROUP BY with COUNT(*). I
> looked over the query plan and it looked a little odd so I tried
> cutting the query down to see where the issue might be. I eventually
> came up with the following:
> SELECT COUNT(*)
> FROM Table1 T1
> INNER JOIN Table2 T2 ON T2.table2_id = T1.table2_id
> WHERE T1.my_date = '2004-11-18'
> The table and column names have been changed to protect the innocent,
> but that is the exact format of the tables. Table1 has about 35M
> records. Table2 has about 6.5M records. For the date in question,
> Table1 has about 165K records.
> There is a non-clustered index on T1.my_date and there is a clustered
> index on T2.table2_id.
Consider adding a nonclustered index on T1(my_date,table2_id). This will
prevent the (quite expensive) bookmark lookups.

> The query plan for this simple query does an index seek on T1.my_date
> as I expected then it does a bookmark lookup (presumably because it
> needs T1.table2_id). It then includes parallelism, a hash, and then a
> bitmap creation. Meanwhile, it does an index scan using an index on
> Table2 that includes a single column that isn't even mentioned in the
> query(?!?!). It then uses parallelism and does a hash match/inner
> join.
Apparently SQL-Server estimates that the parallel plan will be faster.
If you expect differently, then you could add the hint OPTION (MAXDOP 1)
to force the serial plan.
Since the index on T2(table2_id) is clustered it is very wide at the
page level. In this case, SQL-Server estimates that it is faster to scan
a nonclustered index of table T2 (which also includes the clustered
index key) than it is to seek (or partially scan) the clustered index
for the estimated rows of the query.
Hope this helps,
Gert-Jan

> I've done UPDATE STATISTICS using WITH FULLSCAN for both tables and
> I've done a DBCC CHECKTABLE on both tables. Neither had any effect. I
> also tried to force the query to use the clustered index for Table2.
> For the simple query above it doesn't seem to help performance as the
> clustered index scan has a very large cost to it (I'm not sure that I
> entirely understand why). In the original query it helps substantially
> though. Instead of joining the 6.5M records to a lookup table first it
> joins it to Table1 first, which cuts down the number of records to the
> 165K before going about with other joins.
> What I'm looking for is any advice on other things that I can look at
> or any ideas on why SQL Server might be making these kinds of choices.
> I would have thought that the simple query above would have performed
> much better than it is currently (~30-35 seconds). I realize that
> there has to be a bookmark lookup, but I was still expecting a quick
> response from the server based on the indexes.
> Because of the table sizes, etc. I don't expect anyone to reproduce my
> results, so please don't ask me to provide DDL for all of the tables
> involved. If you have some ideas or even just guesses great, if not
> then that's ok too.
> Thanks,
> -Tom.
|||I fully agree. OPTION (MAXDOP 1) should resolve the problem. In my
experience, UPDATE STATISTICS would temporaily fix it. And index hint,
query hint would also force a right plan (not a best practice though). If
it's from a stored procedure, WITH RECOMPILE would also fix it (not a best
practice).
Gary
SELECT COUNT(*)
> FROM Table1 T1
> INNER JOIN Table2 T2 ON T2.table2_id = T1.table2_id
> WHERE T1.my_date = '2004-11-18'
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:419E4013.6DBC44D@.toomuchspamalready.nl...[vbcol=seagreen]
> Thomas, see inline
> "Thomas R. Hummel" wrote:
> Consider adding a nonclustered index on T1(my_date,table2_id). This will
> prevent the (quite expensive) bookmark lookups.
>
> Apparently SQL-Server estimates that the parallel plan will be faster.
> If you expect differently, then you could add the hint OPTION (MAXDOP 1)
> to force the serial plan.
> Since the index on T2(table2_id) is clustered it is very wide at the
> page level. In this case, SQL-Server estimates that it is faster to scan
> a nonclustered index of table T2 (which also includes the clustered
> index key) than it is to seek (or partially scan) the clustered index
> for the estimated rows of the query.
> Hope this helps,
> Gert-Jan
|||Thanks for the suggestions. I had thought of the nonclustered index,
but while that would help with the pared down query that I came up
with, it wouldn't help with the underlying query because that one
involves a lot of additional columns. I'm still considering a covering
index, but I'm not sure why it should be necessary. Due to the number
of columns in the query as well as the number of rows in the table,
I'm a little hesitant to create a covering index.
I may try changing the clustered index for Table1. Right now it is on
an identity column (not my design...) that is also being used as a
surrogate primary key. It makes more sense to me to have that on the
date column since most reports run off of that date column and use
date ranges. This should prevent bookmark lookups for these large
groups of rows as well. When looking up by the primary key it is
usually to grab one row anyway.
Thanks!
-Tom.
Gert-Jan Strik <sorry@.toomuchspamalready.nl> wrote in message news:<419E4013.6DBC44D@.toomuchspamalready.nl>...[vbcol=seagreen]
> Thomas, see inline
> "Thomas R. Hummel" wrote:
> Consider adding a nonclustered index on T1(my_date,table2_id). This will
> prevent the (quite expensive) bookmark lookups.
>
> Apparently SQL-Server estimates that the parallel plan will be faster.
> If you expect differently, then you could add the hint OPTION (MAXDOP 1)
> to force the serial plan.
> Since the index on T2(table2_id) is clustered it is very wide at the
> page level. In this case, SQL-Server estimates that it is faster to scan
> a nonclustered index of table T2 (which also includes the clustered
> index key) than it is to seek (or partially scan) the clustered index
> for the estimated rows of the query.
> Hope this helps,
> Gert-Jan

Poor plan choice

Hello,
A couple days ago one of our queries suddenly started to perform
abyssmally. The query is pretty straightforward - it joins several
tables all on foreign keys and includes a GROUP BY with COUNT(*). I
looked over the query plan and it looked a little odd so I tried
cutting the query down to see where the issue might be. I eventually
came up with the following:
SELECT COUNT(*)
FROM Table1 T1
INNER JOIN Table2 T2 ON T2.table2_id = T1.table2_id
WHERE T1.my_date = '2004-11-18'
The table and column names have been changed to protect the innocent,
but that is the exact format of the tables. Table1 has about 35M
records. Table2 has about 6.5M records. For the date in question,
Table1 has about 165K records.
There is a non-clustered index on T1.my_date and there is a clustered
index on T2.table2_id.
The query plan for this simple query does an index seek on T1.my_date
as I expected then it does a bookmark lookup (presumably because it
needs T1.table2_id). It then includes parallelism, a hash, and then a
bitmap creation. Meanwhile, it does an index scan using an index on
Table2 that includes a single column that isn't even mentioned in the
query(?!?!). It then uses parallelism and does a hash match/inner
join.
I've done UPDATE STATISTICS using WITH FULLSCAN for both tables and
I've done a DBCC CHECKTABLE on both tables. Neither had any effect. I
also tried to force the query to use the clustered index for Table2.
For the simple query above it doesn't seem to help performance as the
clustered index scan has a very large cost to it (I'm not sure that I
entirely understand why). In the original query it helps substantially
though. Instead of joining the 6.5M records to a lookup table first it
joins it to Table1 first, which cuts down the number of records to the
165K before going about with other joins.
What I'm looking for is any advice on other things that I can look at
or any ideas on why SQL Server might be making these kinds of choices.
I would have thought that the simple query above would have performed
much better than it is currently (~30-35 seconds). I realize that
there has to be a bookmark lookup, but I was still expecting a quick
response from the server based on the indexes.
Because of the table sizes, etc. I don't expect anyone to reproduce my
results, so please don't ask me to provide DDL for all of the tables
involved. If you have some ideas or even just guesses great, if not
then that's ok too.
Thanks,
-Tom.Thomas, see inline
"Thomas R. Hummel" wrote:
> Hello,
> A couple days ago one of our queries suddenly started to perform
> abyssmally. The query is pretty straightforward - it joins several
> tables all on foreign keys and includes a GROUP BY with COUNT(*). I
> looked over the query plan and it looked a little odd so I tried
> cutting the query down to see where the issue might be. I eventually
> came up with the following:
> SELECT COUNT(*)
> FROM Table1 T1
> INNER JOIN Table2 T2 ON T2.table2_id = T1.table2_id
> WHERE T1.my_date = '2004-11-18'
> The table and column names have been changed to protect the innocent,
> but that is the exact format of the tables. Table1 has about 35M
> records. Table2 has about 6.5M records. For the date in question,
> Table1 has about 165K records.
> There is a non-clustered index on T1.my_date and there is a clustered
> index on T2.table2_id.
Consider adding a nonclustered index on T1(my_date,table2_id). This will
prevent the (quite expensive) bookmark lookups.

> The query plan for this simple query does an index seek on T1.my_date
> as I expected then it does a bookmark lookup (presumably because it
> needs T1.table2_id). It then includes parallelism, a hash, and then a
> bitmap creation. Meanwhile, it does an index scan using an index on
> Table2 that includes a single column that isn't even mentioned in the
> query(?!?!). It then uses parallelism and does a hash match/inner
> join.
Apparently SQL-Server estimates that the parallel plan will be faster.
If you expect differently, then you could add the hint OPTION (MAXDOP 1)
to force the serial plan.
Since the index on T2(table2_id) is clustered it is very wide at the
page level. In this case, SQL-Server estimates that it is faster to scan
a nonclustered index of table T2 (which also includes the clustered
index key) than it is to seek (or partially scan) the clustered index
for the estimated rows of the query.
Hope this helps,
Gert-Jan

> I've done UPDATE STATISTICS using WITH FULLSCAN for both tables and
> I've done a DBCC CHECKTABLE on both tables. Neither had any effect. I
> also tried to force the query to use the clustered index for Table2.
> For the simple query above it doesn't seem to help performance as the
> clustered index scan has a very large cost to it (I'm not sure that I
> entirely understand why). In the original query it helps substantially
> though. Instead of joining the 6.5M records to a lookup table first it
> joins it to Table1 first, which cuts down the number of records to the
> 165K before going about with other joins.
> What I'm looking for is any advice on other things that I can look at
> or any ideas on why SQL Server might be making these kinds of choices.
> I would have thought that the simple query above would have performed
> much better than it is currently (~30-35 seconds). I realize that
> there has to be a bookmark lookup, but I was still expecting a quick
> response from the server based on the indexes.
> Because of the table sizes, etc. I don't expect anyone to reproduce my
> results, so please don't ask me to provide DDL for all of the tables
> involved. If you have some ideas or even just guesses great, if not
> then that's ok too.
> Thanks,
> -Tom.|||I fully agree. OPTION (MAXDOP 1) should resolve the problem. In my
experience, UPDATE STATISTICS would temporaily fix it. And index hint,
query hint would also force a right plan (not a best practice though). If
it's from a stored procedure, WITH RECOMPILE would also fix it (not a best
practice).
Gary
SELECT COUNT(*)
> FROM Table1 T1
> INNER JOIN Table2 T2 ON T2.table2_id = T1.table2_id
> WHERE T1.my_date = '2004-11-18'
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:419E4013.6DBC44D@.toomuchspamalready.nl...[vbcol=seagreen]
> Thomas, see inline
> "Thomas R. Hummel" wrote:
> Consider adding a nonclustered index on T1(my_date,table2_id). This will
> prevent the (quite expensive) bookmark lookups.
>
> Apparently SQL-Server estimates that the parallel plan will be faster.
> If you expect differently, then you could add the hint OPTION (MAXDOP 1)
> to force the serial plan.
> Since the index on T2(table2_id) is clustered it is very wide at the
> page level. In this case, SQL-Server estimates that it is faster to scan
> a nonclustered index of table T2 (which also includes the clustered
> index key) than it is to seek (or partially scan) the clustered index
> for the estimated rows of the query.
> Hope this helps,
> Gert-Jan
>|||Thanks for the suggestions. I had thought of the nonclustered index,
but while that would help with the pared down query that I came up
with, it wouldn't help with the underlying query because that one
involves a lot of additional columns. I'm still considering a covering
index, but I'm not sure why it should be necessary. Due to the number
of columns in the query as well as the number of rows in the table,
I'm a little hesitant to create a covering index.
I may try changing the clustered index for Table1. Right now it is on
an identity column (not my design...) that is also being used as a
surrogate primary key. It makes more sense to me to have that on the
date column since most reports run off of that date column and use
date ranges. This should prevent bookmark lookups for these large
groups of rows as well. When looking up by the primary key it is
usually to grab one row anyway.
Thanks!
-Tom.
Gert-Jan Strik <sorry@.toomuchspamalready.nl> wrote in message news:<419E4013.6DBC44D@.toomuch
spamalready.nl>...[vbcol=seagreen]
> Thomas, see inline
> "Thomas R. Hummel" wrote:
> Consider adding a nonclustered index on T1(my_date,table2_id). This will
> prevent the (quite expensive) bookmark lookups.
>
> Apparently SQL-Server estimates that the parallel plan will be faster.
> If you expect differently, then you could add the hint OPTION (MAXDOP 1)
> to force the serial plan.
> Since the index on T2(table2_id) is clustered it is very wide at the
> page level. In this case, SQL-Server estimates that it is faster to scan
> a nonclustered index of table T2 (which also includes the clustered
> index key) than it is to seek (or partially scan) the clustered index
> for the estimated rows of the query.
> Hope this helps,
> Gert-Jan
>

Poor plan choice

Hello,
A couple days ago one of our queries suddenly started to perform
abyssmally. The query is pretty straightforward - it joins several
tables all on foreign keys and includes a GROUP BY with COUNT(*). I
looked over the query plan and it looked a little odd so I tried
cutting the query down to see where the issue might be. I eventually
came up with the following:
SELECT COUNT(*)
FROM Table1 T1
INNER JOIN Table2 T2 ON T2.table2_id = T1.table2_id
WHERE T1.my_date = '2004-11-18'
The table and column names have been changed to protect the innocent,
but that is the exact format of the tables. Table1 has about 35M
records. Table2 has about 6.5M records. For the date in question,
Table1 has about 165K records.
There is a non-clustered index on T1.my_date and there is a clustered
index on T2.table2_id.
The query plan for this simple query does an index seek on T1.my_date
as I expected then it does a bookmark lookup (presumably because it
needs T1.table2_id). It then includes parallelism, a hash, and then a
bitmap creation. Meanwhile, it does an index scan using an index on
Table2 that includes a single column that isn't even mentioned in the
query(?!?!). It then uses parallelism and does a hash match/inner
join.
I've done UPDATE STATISTICS using WITH FULLSCAN for both tables and
I've done a DBCC CHECKTABLE on both tables. Neither had any effect. I
also tried to force the query to use the clustered index for Table2.
For the simple query above it doesn't seem to help performance as the
clustered index scan has a very large cost to it (I'm not sure that I
entirely understand why). In the original query it helps substantially
though. Instead of joining the 6.5M records to a lookup table first it
joins it to Table1 first, which cuts down the number of records to the
165K before going about with other joins.
What I'm looking for is any advice on other things that I can look at
or any ideas on why SQL Server might be making these kinds of choices.
I would have thought that the simple query above would have performed
much better than it is currently (~30-35 seconds). I realize that
there has to be a bookmark lookup, but I was still expecting a quick
response from the server based on the indexes.
Because of the table sizes, etc. I don't expect anyone to reproduce my
results, so please don't ask me to provide DDL for all of the tables
involved. If you have some ideas or even just guesses great, if not
then that's ok too.
Thanks,
-Tom.Thomas, see inline
"Thomas R. Hummel" wrote:
> Hello,
> A couple days ago one of our queries suddenly started to perform
> abyssmally. The query is pretty straightforward - it joins several
> tables all on foreign keys and includes a GROUP BY with COUNT(*). I
> looked over the query plan and it looked a little odd so I tried
> cutting the query down to see where the issue might be. I eventually
> came up with the following:
> SELECT COUNT(*)
> FROM Table1 T1
> INNER JOIN Table2 T2 ON T2.table2_id = T1.table2_id
> WHERE T1.my_date = '2004-11-18'
> The table and column names have been changed to protect the innocent,
> but that is the exact format of the tables. Table1 has about 35M
> records. Table2 has about 6.5M records. For the date in question,
> Table1 has about 165K records.
> There is a non-clustered index on T1.my_date and there is a clustered
> index on T2.table2_id.
Consider adding a nonclustered index on T1(my_date,table2_id). This will
prevent the (quite expensive) bookmark lookups.
> The query plan for this simple query does an index seek on T1.my_date
> as I expected then it does a bookmark lookup (presumably because it
> needs T1.table2_id). It then includes parallelism, a hash, and then a
> bitmap creation. Meanwhile, it does an index scan using an index on
> Table2 that includes a single column that isn't even mentioned in the
> query(?!?!). It then uses parallelism and does a hash match/inner
> join.
Apparently SQL-Server estimates that the parallel plan will be faster.
If you expect differently, then you could add the hint OPTION (MAXDOP 1)
to force the serial plan.
Since the index on T2(table2_id) is clustered it is very wide at the
page level. In this case, SQL-Server estimates that it is faster to scan
a nonclustered index of table T2 (which also includes the clustered
index key) than it is to seek (or partially scan) the clustered index
for the estimated rows of the query.
Hope this helps,
Gert-Jan
> I've done UPDATE STATISTICS using WITH FULLSCAN for both tables and
> I've done a DBCC CHECKTABLE on both tables. Neither had any effect. I
> also tried to force the query to use the clustered index for Table2.
> For the simple query above it doesn't seem to help performance as the
> clustered index scan has a very large cost to it (I'm not sure that I
> entirely understand why). In the original query it helps substantially
> though. Instead of joining the 6.5M records to a lookup table first it
> joins it to Table1 first, which cuts down the number of records to the
> 165K before going about with other joins.
> What I'm looking for is any advice on other things that I can look at
> or any ideas on why SQL Server might be making these kinds of choices.
> I would have thought that the simple query above would have performed
> much better than it is currently (~30-35 seconds). I realize that
> there has to be a bookmark lookup, but I was still expecting a quick
> response from the server based on the indexes.
> Because of the table sizes, etc. I don't expect anyone to reproduce my
> results, so please don't ask me to provide DDL for all of the tables
> involved. If you have some ideas or even just guesses great, if not
> then that's ok too.
> Thanks,
> -Tom.|||I fully agree. OPTION (MAXDOP 1) should resolve the problem. In my
experience, UPDATE STATISTICS would temporaily fix it. And index hint,
query hint would also force a right plan (not a best practice though). If
it's from a stored procedure, WITH RECOMPILE would also fix it (not a best
practice).
Gary
SELECT COUNT(*)
> FROM Table1 T1
> INNER JOIN Table2 T2 ON T2.table2_id = T1.table2_id
> WHERE T1.my_date = '2004-11-18'
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:419E4013.6DBC44D@.toomuchspamalready.nl...
> Thomas, see inline
> "Thomas R. Hummel" wrote:
> >
> > Hello,
> >
> > A couple days ago one of our queries suddenly started to perform
> > abyssmally. The query is pretty straightforward - it joins several
> > tables all on foreign keys and includes a GROUP BY with COUNT(*). I
> > looked over the query plan and it looked a little odd so I tried
> > cutting the query down to see where the issue might be. I eventually
> > came up with the following:
> >
> > SELECT COUNT(*)
> > FROM Table1 T1
> > INNER JOIN Table2 T2 ON T2.table2_id = T1.table2_id
> > WHERE T1.my_date = '2004-11-18'
> >
> > The table and column names have been changed to protect the innocent,
> > but that is the exact format of the tables. Table1 has about 35M
> > records. Table2 has about 6.5M records. For the date in question,
> > Table1 has about 165K records.
> >
> > There is a non-clustered index on T1.my_date and there is a clustered
> > index on T2.table2_id.
> Consider adding a nonclustered index on T1(my_date,table2_id). This will
> prevent the (quite expensive) bookmark lookups.
> > The query plan for this simple query does an index seek on T1.my_date
> > as I expected then it does a bookmark lookup (presumably because it
> > needs T1.table2_id). It then includes parallelism, a hash, and then a
> > bitmap creation. Meanwhile, it does an index scan using an index on
> > Table2 that includes a single column that isn't even mentioned in the
> > query(?!?!). It then uses parallelism and does a hash match/inner
> > join.
> Apparently SQL-Server estimates that the parallel plan will be faster.
> If you expect differently, then you could add the hint OPTION (MAXDOP 1)
> to force the serial plan.
> Since the index on T2(table2_id) is clustered it is very wide at the
> page level. In this case, SQL-Server estimates that it is faster to scan
> a nonclustered index of table T2 (which also includes the clustered
> index key) than it is to seek (or partially scan) the clustered index
> for the estimated rows of the query.
> Hope this helps,
> Gert-Jan
> > I've done UPDATE STATISTICS using WITH FULLSCAN for both tables and
> > I've done a DBCC CHECKTABLE on both tables. Neither had any effect. I
> > also tried to force the query to use the clustered index for Table2.
> > For the simple query above it doesn't seem to help performance as the
> > clustered index scan has a very large cost to it (I'm not sure that I
> > entirely understand why). In the original query it helps substantially
> > though. Instead of joining the 6.5M records to a lookup table first it
> > joins it to Table1 first, which cuts down the number of records to the
> > 165K before going about with other joins.
> >
> > What I'm looking for is any advice on other things that I can look at
> > or any ideas on why SQL Server might be making these kinds of choices.
> > I would have thought that the simple query above would have performed
> > much better than it is currently (~30-35 seconds). I realize that
> > there has to be a bookmark lookup, but I was still expecting a quick
> > response from the server based on the indexes.
> >
> > Because of the table sizes, etc. I don't expect anyone to reproduce my
> > results, so please don't ask me to provide DDL for all of the tables
> > involved. If you have some ideas or even just guesses great, if not
> > then that's ok too.
> >
> > Thanks,
> > -Tom.|||Thanks for the suggestions. I had thought of the nonclustered index,
but while that would help with the pared down query that I came up
with, it wouldn't help with the underlying query because that one
involves a lot of additional columns. I'm still considering a covering
index, but I'm not sure why it should be necessary. Due to the number
of columns in the query as well as the number of rows in the table,
I'm a little hesitant to create a covering index.
I may try changing the clustered index for Table1. Right now it is on
an identity column (not my design...) that is also being used as a
surrogate primary key. It makes more sense to me to have that on the
date column since most reports run off of that date column and use
date ranges. This should prevent bookmark lookups for these large
groups of rows as well. When looking up by the primary key it is
usually to grab one row anyway.
Thanks!
-Tom.
Gert-Jan Strik <sorry@.toomuchspamalready.nl> wrote in message news:<419E4013.6DBC44D@.toomuchspamalready.nl>...
> Thomas, see inline
> "Thomas R. Hummel" wrote:
> >
> > Hello,
> >
> > A couple days ago one of our queries suddenly started to perform
> > abyssmally. The query is pretty straightforward - it joins several
> > tables all on foreign keys and includes a GROUP BY with COUNT(*). I
> > looked over the query plan and it looked a little odd so I tried
> > cutting the query down to see where the issue might be. I eventually
> > came up with the following:
> >
> > SELECT COUNT(*)
> > FROM Table1 T1
> > INNER JOIN Table2 T2 ON T2.table2_id = T1.table2_id
> > WHERE T1.my_date = '2004-11-18'
> >
> > The table and column names have been changed to protect the innocent,
> > but that is the exact format of the tables. Table1 has about 35M
> > records. Table2 has about 6.5M records. For the date in question,
> > Table1 has about 165K records.
> >
> > There is a non-clustered index on T1.my_date and there is a clustered
> > index on T2.table2_id.
> Consider adding a nonclustered index on T1(my_date,table2_id). This will
> prevent the (quite expensive) bookmark lookups.
> > The query plan for this simple query does an index seek on T1.my_date
> > as I expected then it does a bookmark lookup (presumably because it
> > needs T1.table2_id). It then includes parallelism, a hash, and then a
> > bitmap creation. Meanwhile, it does an index scan using an index on
> > Table2 that includes a single column that isn't even mentioned in the
> > query(?!?!). It then uses parallelism and does a hash match/inner
> > join.
> Apparently SQL-Server estimates that the parallel plan will be faster.
> If you expect differently, then you could add the hint OPTION (MAXDOP 1)
> to force the serial plan.
> Since the index on T2(table2_id) is clustered it is very wide at the
> page level. In this case, SQL-Server estimates that it is faster to scan
> a nonclustered index of table T2 (which also includes the clustered
> index key) than it is to seek (or partially scan) the clustered index
> for the estimated rows of the query.
> Hope this helps,
> Gert-Jan
> > I've done UPDATE STATISTICS using WITH FULLSCAN for both tables and
> > I've done a DBCC CHECKTABLE on both tables. Neither had any effect. I
> > also tried to force the query to use the clustered index for Table2.
> > For the simple query above it doesn't seem to help performance as the
> > clustered index scan has a very large cost to it (I'm not sure that I
> > entirely understand why). In the original query it helps substantially
> > though. Instead of joining the 6.5M records to a lookup table first it
> > joins it to Table1 first, which cuts down the number of records to the
> > 165K before going about with other joins.
> >
> > What I'm looking for is any advice on other things that I can look at
> > or any ideas on why SQL Server might be making these kinds of choices.
> > I would have thought that the simple query above would have performed
> > much better than it is currently (~30-35 seconds). I realize that
> > there has to be a bookmark lookup, but I was still expecting a quick
> > response from the server based on the indexes.
> >
> > Because of the table sizes, etc. I don't expect anyone to reproduce my
> > results, so please don't ask me to provide DDL for all of the tables
> > involved. If you have some ideas or even just guesses great, if not
> > then that's ok too.
> >
> > Thanks,
> > -Tom.

Poor Performing Query

Here is the situation: I have a process that auto-generates reports and e-mails them to end users (no, we are not allowed to use SQL Mail due to security issues and the fact that we are a Lotus shop). The process runs the query, saves the data to a text file and then mails the file as an attachment.

If the particular report to be run has multiple recipients, we make multiple passes (ie, we run the same query multiple times and send each recipient a "personalized" version of the report).

The issue is that one of my auto reports fails for just the first recipient. Subsequent recipients all receive the report normally, but the first recipient consistently fails to receive the data.

When I run the T-SQL multiple times in Query analyzer, I get the following results:

Pass 1: 87 seconds
Pass 2: 2 seconds
Pass 3: 3 seconds

I know that the process that we wrote (which is wrapped in a DLL) uses an ADO connection/command timeout setting of 60 seconds (both properties are set to the same value).

The SQL is not a stored proc. When I run the Index tuning wizard, no additional indexes are recommended.

I'm struggling to determine the next step.

1. I am loathe to increase the command timeout setting, since I have already done that once (from 30 seconds to one minute). I'm pretty sure this will only defer final resolution.

2. I suppose I could create a stored proc (so that it doesn't have to recompile the execution plan each time), but that would make this one report different from the hundreds of other reports that my super users have created; it would also mean that my super users would not be able to control the layout of the report without coming to me.

3. I have a feeling I should be using query hints or some such, but I am completely unfamiliar with these optimization tools.

Is there another approach that I am overlooking?

Regards,

Hugh ScottIf these are similar then I would guess that for the first call it has to read the data from disk. For subsequent calls the data is in memory so is much quicker.

Maybe put in a dummy call first?|||Yes, the calls are identical. I agree with you that on subsequent passes, the data is being read from memory. What has me puzzled is what I can do about it. I have considered putting in a dummy call first (as you suggested), but I wanted to try something a little more orthodox first.

Do you think that adding memory to the server would help? The server currently has 2.6 GB of memory. The database itself is a little over 30 GB now. I have run performance monitor on the server. While pages/sec does spike during this operation, it usually averages around 4.5. It does not seem sufficiently spiky to warrant $4,000 for additional memory.

Some more information:

There are three tables involved:

table a left join view b left join table c

Table a: 5.4 million rows (~ 4 GB)
View b: 1.3 million rows (~ 2.3 GB)
View c: 15 rows

View b uses the UNION operator and thus cannot be indexed.
Table a is indexed six ways from Sunday.

Thanks again,

Hugh Scott

Originally posted by nigelrivett
If these are similar then I would guess that for the first call it has to read the data from disk. For subsequent calls the data is in memory so is much quicker.

Maybe put in a dummy call first?|||What's your UNION view code? UNION queries are very handy, and I've used them many times myself, but just as often I've seen them used unnecessarily. Perhaps there is a more efficient method of combining your view code directly in your procedure code.

blindman|||UNION views can be indexed. Also, have you tried to see what you get on IO stats ON when doing a SELECT from just that view using WHERE that would match your JOIN?|||Views can be indexed, but unless you use a clustered index then the index must be recreated each time the view is called, and you lose the benefit of indexes on the underlying tables.

Clustered indexes on views can greatly boost performance, but they cannot be used on UNION queries. From books-online:

A view must meet these requirements before you can create a clustered index on it:
.
.
.
The SELECT statement in the view cannot contain these Transact-SQL syntax elements:
.
.
.UNION operator.

blindman|||blindman: thanks for the reminder. UNION is not allowed in indexed views. But you're also referring to non-clustered indexes. I haven't found any info that would suggest that a non-clustered index on a view needs to be rebuilt each time the view is called. Can you enlighten me here?

hmscott: adding memory will not resolve 87-second processing time on the first call. Adding indexes to base tables may.|||Normally the results of a view are not stored in the database, but if a clustered index is created on the view then the results are stored just like a table, and the values in the view are updated whenever the values in the underlying table are updated. (This of course adds overhead to processing changes on the underlying tables.) The clustered index is necessary in order to update the values on the view.

Long story short, if you don't use a clustered index then the results are not stored and thus any indexing must be recreated each time.

Honestly, I don't know whether non-clustered indexes are maintained on views that also have clustered indexes. I think I also read somewhere that you can't create an index on a view unless it also has a clustered index, so maybe UNION queries can't have indexes at all.

It's also possible that the optimizer might be smart enough to apply filters from the procedure to the underlying tables prior to creating the UNION view when it is called, but this probably depends on a lot of factors.

I've just seen too many instances when a UNION view was used instead of a more appropriate WHERE clause criteria.

blindmansql

Poor Performing Query

Hi
The answer to this one will be v simple for someone, alas my memory fails
me, what I am after is the term for whats I am experiencing. I have a stored
procedure which has a list of paramaters passed to it, one of these
parameters is set to NULL in the procedure header, within the code at the
begining is a IF NULL statement which then gives the variable a value. This
causes havoc with the execution plan as it believes a NULL value will be use
d
but the procedure uses the value assigned to in the IF NULL statement and th
e
time to execute can be significantly longer using the default. I am aware of
this and I know there is a term associated to this but for the life of me I
can't remember what its called.
anyoneParameter Sniffing:
http://www.google.co.uk/groups?as_e...lic.sqlserver.*
David Portas
SQL Server MVP
--|||"Parameter sniffing". The optimizer sniffs the parameter for the proc when i
t is to create a proc
plan. Apart from that, a DML statement has no context. When the optimizer lo
ok at a batch/proc it
only cares about the SELECT, INSERT, UPDATE ad DELETE statements. From that,
you can probably
realize that the SET command haven't executed and the value you passed to th
e proc is the one used
by the optimizer (parameter sniffing).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MysticMart" <MysticMart@.discussions.microsoft.com> wrote in message
news:6A43A4CB-F8DE-41C8-B53E-FB830599E976@.microsoft.com...
> Hi
> The answer to this one will be v simple for someone, alas my memory fails
> me, what I am after is the term for whats I am experiencing. I have a stor
ed
> procedure which has a list of paramaters passed to it, one of these
> parameters is set to NULL in the procedure header, within the code at the
> begining is a IF NULL statement which then gives the variable a value. Thi
s
> causes havoc with the execution plan as it believes a NULL value will be u
sed
> but the procedure uses the value assigned to in the IF NULL statement and
the
> time to execute can be significantly longer using the default. I am aware
of
> this and I know there is a term associated to this but for the life of me
I
> can't remember what its called.
> anyone

Poor performance with functions in selects

Hi

We have the following query that we were trying to execute and it performs very poorly...

SELECT ITEM_ID,

PART_NO,

PART_TITLE,

dbo.SFDB_NVL_VARCHAR(DRAWING_NO_DISP,'N/A') AS DRAWING_NO,

dbo.SFDB_NVL_VARCHAR(DRAWING_CHG_DISP,'N/A') AS DRAWING_CHG,

DRAWING_NO_DISP,

DRAWING_CHG_DISP,

REV_SOURCE,

ITEM_ID AS ASSY_ITEM_ID,

dbo.SFDB_NVL_VARCHAR(DRAWING_NO_DISP,'N/A') AS ASSY_DRAWING_NO,

SFMFG.SFDB_NVL_VARCHAR(DRAWING_CHG_DISP,'N/A') AS ASSY_DRAWING_CHG

FROM SFSQA_CHAD_PART_DSPTCH_DSP_SEL

ORDER BY PART_NO

The function SFDB_NVL_VARCHAR is nothing but a replication of nvl function of oracle.

CREATE

FUNCTION [dbo].[SFDB_NVL_VARCHAR]

(

@.VI_SOURCE VARCHAR(4000),

@.VI_VALU_IF_NULL VARCHAR(4000)

)

RETURNS VARCHAR(4000)

AS

BEGIN

IF ISNULL(@.VI_SOURCE,'') =''

RETURN @.VI_VALU_IF_NULL

RETURN @.VI_SOURCE

END

Now if the same query was re-written to remove the function and instead use a CASE WHEN block the performance is up significantly....

SELECT ITEM_ID,

PART_NO,

PART_TITLE,

CASE WHEN DRAWING_NO_DISP IS NULL THEN 'N/A'

WHEN DRAWING_NO_DISP = '' THEN 'N/A'

ELSE DRAWING_NO_DISP

END DRAWING_NO,

CASE WHEN DRAWING_CHG_DISP IS NULL THEN 'N/A'

WHEN DRAWING_CHG_DISP = '' THEN 'N/A'

ELSE DRAWING_CHG_DISP

END DRAWING_CHG,

DRAWING_NO_DISP,

DRAWING_CHG_DISP,

REV_SOURCE,

ITEM_ID AS ASSY_ITEM_ID,

CASE WHEN DRAWING_NO_DISP IS NULL THEN 'N/A'

WHEN DRAWING_NO_DISP= '' THEN 'N/A'

ELSE DRAWING_NO_DISP

END ASSY_DRAWING_NO,

CASE WHEN DRAWING_CHG_DISP IS NULL THEN 'N/A'

WHEN DRAWING_CHG_DISP = '' THEN 'N/A'

ELSE DRAWING_CHG_DISP

END ASSY_DRAWING_CHG

FROM SFSQA_CHAD_PART_DSPTCH_DSP_SEL

ORDER BY PART_NO

Now the execution plan in both cases shows that cost of execution of the funtion is 0%. This is weird. How can function perform so badly for as simple as the one I mentioned,.

Regards

Imtiaz

Calling functions in a SELECT statement is itself not a very good idea for the same reason that for every row that was fetched in the SELECT result, SQL Server has to execute the function (for which it has to check for an available execution plan, find the most optimum plan, use it, then return the result).|||

Tiaz:

I think Dinakar is right. I am afraid that this is just "the nature of the beast". Code that does not use functions will tend to execute better. Here are a couple of threads earlier this year that discussed around the issues of functions; reviewing these might be helpful.

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

Dave

|||But even with such a function the execution should be fast...What is happening here...?|||

Well, I also just noticed this line:

SFMFG.SFDB_NVL_VARCHAR(DRAWING_CHG_DISP,'N/A') AS ASSY_DRAWING_CHG

Is this a typo in which you really want DBO?

|||OKAT THAT WAS A TYPO.....I HAD THAT SCHEMA BUT REMOVED IT NOT TO CONFUSE U FOLKS....IT IS DBO

Poor Performance On Cross-Server Join

Was running cross-server queries between SQL65 and SQL2000, query ran fine. Upgraded the SQL65 server to SQL2000, the cross-server queries starting running extremly slow. Nothing has changed but the upgrade to SQL200, can some one help why my cross-server queries are taking so long.perhaps delete & recreate the linked server definition (if it is the same one as created to the 6.5 server)|||Actually, after my search for info on this found nothing, I dropped and recreated all the links between the two servers and that did fix the issue.

So your advice was a good one

Poor performance for business day calculation from aspfaq sample

Greeting, below is the complete SQL taken from aspfaq.com (retrieved
from this newsgroup I believe) The query takes about two minutes to
run. Does anybody have a better set based way (sub-second response) to
determine business days?

CREATE TABLE dbo.Calendar

(

dt SMALLDATETIME NOT NULL PRIMARY KEY
CLUSTERED, -- Date value

IsWeekday BIT,
-- Is this date a weekday (M -
F)

IsHoliday BIT,
-- Is this date a holiday

Y SMALLINT,
-- Year the date falls in

FY SMALLINT,
-- Fiscal Year (needed?)

Q TINYINT,
-- Quarter date falls in

M TINYINT,
-- Numeric month of date

D TINYINT,
-- Numeric day of date

DW TINYINT,
-- Numeric DayOfWeek
(Sunda=1,Monday=2)

MonthName VARCHAR(9),
-- String name of month

DayName VARCHAR(9),
-- String name of day

W TINYINT
-- Week number

)

GO

-- Start & End Dates

DECLARE @.StartDate DATETIME

DECLARE @.EndDate DATETIME

SET @.StartDate = DATEADD(d, -1, '20000101')

SET @.EndDate = DATEADD(d, -1, '20300101')

-- Total number of dates to generate

DECLARE @.Days INT

SET @.Days = DATEDIFF(DAY, @.StartDate, @.EndDate)

-- Create temporary Numbers table

CREATE TABLE #Numbers

(

Number INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED

)

-- Insert a number into our temp table for each date to be generated

WHILE COALESCE(SCOPE_IDENTITY(), 0) <= @.Days

BEGIN

INSERT #Numbers DEFAULT VALUES

END

-- Generate a date for each day in our timespan

INSERT Calendar(dt)

SELECT DATEADD(DAY, Number, @.StartDate)

FROM #Numbers

WHERE Number <= @.Days

ORDER BY Number

-- Remove the temporary Numbers table

DROP TABLE #Numbers

GO

-- Update other columns

UPDATE dbo.Calendar SET

IsWeekday = CASE WHEN DATEPART(DW, dt) IN (1, 7) THEN 0
ELSE 1 END,

IsHoliday = 0,

Y = YEAR(dt),

FY = YEAR(dt),

Q = CASE

WHEN MONTH(dt) <= 3 THEN 1

WHEN MONTH(dt) <= 6 THEN 2

WHEN MONTH(dt) <= 9 THEN 3

ELSE 4 END,

M = MONTH(dt),

D = DAY(dt),

DW = DATEPART(DW, dt),

MonthName = DATENAME(MONTH, dt),

DayName = DATENAME(DW, dt),

W = DATEPART(WK, dt)

-- Query in question (takes almost 2 minutes to execute and return a
value)

SELECT

C.dt

FROM

Calendar C

WHERE

C.IsWeekDay = 1

AND C.IsHoliday = 0

AND 9 = (SELECT COUNT(*) FROM Calendar C2 WHERE C2.dt >=
GETDATE() AND C2.dt <= C.dt AND C2.IsWeekDay = 1 AND C2.IsHoliday = 0 )Here's an easy one:

SELECT
CASE
WHEN ([Date] % 7) > 1 THEN 'Business day'
ELSE 'Weekend day'
END AS "IsBusinessDay"
FROM table

The symbol % here is the modulo operator.
The remainder of dividing Date by 7 returns
0 for Saturday, 1 for Sunday and up to 6 for Friday.
Remainders from 2 to 6 correspond to Monday to Friday.

GeoSynch

"pb648174" <google@.webpaul.net> wrote in message
news:1113345847.550164.212820@.l41g2000cwc.googlegr oups.com...
> Greeting, below is the complete SQL taken from aspfaq.com (retrieved
> from this newsgroup I believe) The query takes about two minutes to
> run. Does anybody have a better set based way (sub-second response) to
> determine business days?
> CREATE TABLE dbo.Calendar
> (
> dt SMALLDATETIME NOT NULL PRIMARY KEY
> CLUSTERED, -- Date value
> IsWeekday BIT,
> -- Is this date a weekday (M -
> F)
> IsHoliday BIT,
> -- Is this date a holiday
> Y SMALLINT,
> -- Year the date falls in
> FY SMALLINT,
> -- Fiscal Year (needed?)
> Q TINYINT,
> -- Quarter date falls in
> M TINYINT,
> -- Numeric month of date
> D TINYINT,
> -- Numeric day of date
> DW TINYINT,
> -- Numeric DayOfWeek
> (Sunda=1,Monday=2)
> MonthName VARCHAR(9),
> -- String name of month
> DayName VARCHAR(9),
> -- String name of day
> W TINYINT
> -- Week number
> )
> GO
>
> -- Start & End Dates
> DECLARE @.StartDate DATETIME
> DECLARE @.EndDate DATETIME
> SET @.StartDate = DATEADD(d, -1, '20000101')
> SET @.EndDate = DATEADD(d, -1, '20300101')
>
> -- Total number of dates to generate
> DECLARE @.Days INT
> SET @.Days = DATEDIFF(DAY, @.StartDate, @.EndDate)
>
> -- Create temporary Numbers table
> CREATE TABLE #Numbers
> (
> Number INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED
> )
>
> -- Insert a number into our temp table for each date to be generated
> WHILE COALESCE(SCOPE_IDENTITY(), 0) <= @.Days
> BEGIN
> INSERT #Numbers DEFAULT VALUES
> END
>
> -- Generate a date for each day in our timespan
> INSERT Calendar(dt)
> SELECT DATEADD(DAY, Number, @.StartDate)
> FROM #Numbers
> WHERE Number <= @.Days
> ORDER BY Number
>
> -- Remove the temporary Numbers table
> DROP TABLE #Numbers
> GO
>
> -- Update other columns
> UPDATE dbo.Calendar SET
> IsWeekday = CASE WHEN DATEPART(DW, dt) IN (1, 7) THEN 0
> ELSE 1 END,
> IsHoliday = 0,
> Y = YEAR(dt),
> FY = YEAR(dt),
> Q = CASE
> WHEN MONTH(dt) <= 3 THEN 1
> WHEN MONTH(dt) <= 6 THEN 2
> WHEN MONTH(dt) <= 9 THEN 3
> ELSE 4 END,
> M = MONTH(dt),
> D = DAY(dt),
> DW = DATEPART(DW, dt),
> MonthName = DATENAME(MONTH, dt),
> DayName = DATENAME(DW, dt),
> W = DATEPART(WK, dt)
>
>
> -- Query in question (takes almost 2 minutes to execute and return a
> value)
> SELECT
> C.dt
> FROM
> Calendar C
> WHERE
> C.IsWeekDay = 1
> AND C.IsHoliday = 0
> AND 9 = (SELECT COUNT(*) FROM Calendar C2 WHERE C2.dt >=
> GETDATE() AND C2.dt <= C.dt AND C2.IsWeekDay = 1 AND C2.IsHoliday = 0 )|||We already have the isWeekday column in the table - what we need to
know is, how do I add 9 business days to a particular date efficiently?|||On 12 Apr 2005 15:44:07 -0700, pb648174 wrote:

> Greeting, below is the complete SQL taken from aspfaq.com (retrieved
> from this newsgroup I believe) The query takes about two minutes to
> run. Does anybody have a better set based way (sub-second response) to
> determine business days?
[snip]
> -- Query in question (takes almost 2 minutes to execute and return a
> value)
> SELECT
> C.dt
> FROM
> Calendar C
> WHERE
> C.IsWeekDay = 1
> AND C.IsHoliday = 0
> AND 9 = (SELECT COUNT(*) FROM Calendar C2 WHERE C2.dt >=
> GETDATE() AND C2.dt <= C.dt AND C2.IsWeekDay = 1 AND C2.IsHoliday = 0 )

It looks to me like you're looking for the day that is nine business days
from today. At least, once I let the query above run for the two minutes, I
got April 26th when I ran it today (April 13th).

If that's so, then you *know* ahead of time that that day will be greater
than today and less than 60 days from today, right? Unless there's some
weird span of sixty consecutive holidays, anyway. So you can add that fact
to the main where clause, and that will speed it up mightily:

SELECT
C.dt
FROM
Calendar C
WHERE
C.IsWeekDay = 1
AND C.IsHoliday = 0
AND C.dt BETWEEN GETDATE() AND DATEADD(d,60,GETDATE())
AND 9 = (
SELECT COUNT(*) FROM Calendar C2
WHERE C2.dt >= GETDATE()
AND C2.dt <= C.dt
AND C2.IsWeekDay = 1
AND C2.IsHoliday = 0)

Another thing you can do is to add indexes to the IsWeekDay and IsHoliday
columns (which requires changing them from BIT to TINYINT, since BIT can't
be indexed). Or even add a covering index on (DT, IsWeekDay, IsHoliday).
But that won't be necessary if you bound the main SELECT as I did -- I got
subsecond performance for the query above.|||That does execute much faster, but 50 is an arbitray number, and since
I will need to use it to schedule events up to one year or even
multiple years in the future, that won't work. In particular, for a set
of scheduled tasks which have a startdate and a lagdays column, I need
to calculate the end date for those tasks based on the above calendar
with business days/holidays entered. How would I do that with the above
query without the "bounding" limitation, returned as a set and with
decent performance?|||Here are some ideas:

1. This is fast but unfortunately in SQL2000 it can't be parameterized
without dynamic SQL:

SELECT MAX(dt)
FROM
(SELECT TOP 9 dt
FROM Calendar
WHERE dt >= CURRENT_TIMESTAMP
AND isweekday = 1
AND isholiday = 0
ORDER BY dt) AS T

2. This one can be parameterized but I wouldn't generally recommend it
because it relies on undocumented behaviour:

DECLARE @.days INTEGER, @.dt DATETIME

SET @.days = 9
SET ROWCOUNT @.days

SELECT @.dt = dt
FROM Calendar
WHERE dt >= CURRENT_TIMESTAMP
AND isweekday = 1
AND isholiday = 0
ORDER BY dt

SELECT @.dt

3. Extending Ross's suggestion, it shouldn't be difficult to calculate
a sensible upper bound for the query, even based on larger date ranges:

SELECT C.dt
FROM Calendar C
WHERE C.isweekday = 1
AND C.isholiday = 0
AND C.dt BETWEEN GETDATE() AND DATEADD(d,@.days*0.30+60.0,GETDATE())
AND 9 = (
SELECT COUNT(*) FROM Calendar C2
WHERE C2.dt >= GETDATE()
AND C2.dt <= C.dt
AND C2.IsWeekDay = 1
AND C2.IsHoliday = 0)

I think this last method is the best option, together with Ross's
suggestions on index improvements.

--
David Portas
SQL Server MVP
--|||And given that I have a table Called ScheduleTask with columns
StartDate & Duration, how do I for a group of rows, calculate the item
with the largest end date if the duration is based on the above
business days?

Why did you use 30%? Shouldn't it be 70% since I assume you are taking
out what you are guessing will be the weekends and holidays and adding
in a buffer of 60 days?|||On 14 Apr 2005 07:09:32 -0700, pb648174 wrote:

> That does execute much faster, but 50 is an arbitray number, and since
> I will need to use it to schedule events up to one year or even
> multiple years in the future, that won't work. In particular, for a set
> of scheduled tasks which have a startdate and a lagdays column, I need
> to calculate the end date for those tasks based on the above calendar
> with business days/holidays entered. How would I do that with the above
> query without the "bounding" limitation, returned as a set and with
> decent performance?

If you need to get sets, I think the best idea is to number the
business-days in the calendar table.

ALTER dbo.Calendar
ADD BusinessDayNum INT NOT NULL DEFAULT (0)

UPDATE dbo.calendar
SET BusinessDayNum = (
SELECT COUNT(*) FROM calendar C2
WHERE c2.isWeekday=1 and c2.isHoliday=0
AND C2.dt <= Calendar.dt
)
WHERE isWeekday=1 AND isHoliday=0

CREATE INDEX idx_Cal_BDN ON dbo.Calendar (BusinessDayNum)

Now you can do this:

CREATE TABLE #myTable
( startDate DATETIME NOT NULL, DurationDays INT NOT NULL)

INSERT #myTable VALUES ('2003-05-14',10)
INSERT #myTable VALUES ('2003-05-15',12)
INSERT #myTable VALUES ('2003-05-16',14)
INSERT #myTable VALUES ('2004-05-14',10)
INSERT #myTable VALUES ('2004-05-15',12)
INSERT #myTable VALUES ('2004-05-16',14)

SELECT T.startDate, T.DurationDays, C2.dt "endDate"
FROM dbo.Calendar C2, dbo.Calendar C1, #myTable T
WHERE C1.DT = T.startDate
AND C2.BusinessDayNum = C1.BusinessDayNum + T.DurationDays

startDate DurationDays endDate
-------- ---- -------
2003-05-14 00:00:00.000 10 2003-05-28 00:00:00
2003-05-15 00:00:00.000 12 2003-06-02 00:00:00
2003-05-16 00:00:00.000 14 2003-06-05 00:00:00
2004-05-14 00:00:00.000 10 2004-05-28 00:00:00
2004-05-15 00:00:00.000 12 2000-01-18 00:00:00
2004-05-16 00:00:00.000 14 2000-01-20 00:00:00

Unfortunately, you will need to have ALL of your holidays set before you do
this, and if your holidays change you should redo the UPDATE.|||That's why this won't work... We will actually be storing the holidays
per user in a separate table. We are basically trying to duplicate MS
Project functionality and are now thinking we should do it all in
application logic instead of SQL, i.e. port in all the task data, do
all the calculations and then do a couple hundred updates for all of
the task data. SQL gurus, please show me a way to not have to do that...|||"pb648174" <google@.webpaul.net> wrote in message
news:1113487772.592286.157190@.z14g2000cwz.googlegr oups.com...
> That does execute much faster, but 50 is an arbitray number, and since
> I will need to use it to schedule events up to one year or even
> multiple years in the future, that won't work. In particular, for a set
> of scheduled tasks which have a startdate and a lagdays column, I need
> to calculate the end date for those tasks based on the above calendar
> with business days/holidays entered. How would I do that with the above
> query without the "bounding" limitation, returned as a set and with
> decent performance?

Maybe try something like:
AND C.dt BETWEEN startdate AND DATEADD(d,3*duration,startdate)

Good Luck,
Jim|||The design for the Calendar table you have is not that good -- BIT
flags and other proprietary datatypes are a mess. There is no
Julianized day number, teh weekday numbers are nto ISO Standard, etc.

Sit down and design a good encoding for the type of day -- weekend,
holiday, workday, etc. A calendar table for US Secular holidays can be
built from the data at this website, so you will get the three-day
weekends:

http://www.smart.net/~mmontes/ushols.html

I would consider using a spreadsheet to get the raw data, since they
usually have good temporal functions. Try a query like this:

SELECT MIN(C1.cal_date)
FROM Calendar AS C1
WHERE @.workday_count
= (SELECT SUM(CASE WHEN C1.cal_date
BETWEEN CURRENT_TIMESTAMP
AND DATEADD(D,
(@.workday_count *10), CURRENT_TIMESTAMP)
AND day_type = 'workday'
THEN 1 ELSE 0 END);

I am assuming that the cal_date column is a clustered primary key so
the optimizer will know to stop and not scan the rest of the table on
the first match. Multiplying the count by ten is probably excessive,
but certainly safe. if that is too slow, change it to 2 or 3.|||I think this is a sign that SQL just isn't going to be able to handle
this kind of job effectively. There will be user holidays added to this
as well, so it is possible that someone could be out for two weeks or
more and that when adding 1 business day, *10 will not be sufficient. I
thought since I got this example from one of the regular poster's web
site that it would be straightforward to implement but that seems to
not be the case. I think we are just going to pull all the data in and
do the calculation in C# and then write all the data back out. Thanks
for the help everyone.|||I am getting a little lost here. The auxiliary Calendar table has
nothing to do with any particular user; it is for the enterprise as a
whole. A personal timesheet is a differrent matter, since each
employee's will be different.

Did you want to have code that says something like when day X is a
Moslem holiday and employee Y is a Moslem, then X is not a work day for
him? Ramada runs for a lunar month, for example, so if you do:

WHERE @.workday_count
= (SELECT SUM(CASE WHEN C1.cal_date
BETWEEN CURRENT_TIMESTAMP
AND DATEADD(D,
(@.workday_count *10 + 30), CURRENT_TIMESTAMP)
AND secular_day_type = 'workday'
AND relgion_day_type <> 'Moslem'
THEN 1 ELSE 0 END)

You know that you are safe. this same pattern can be extended to
include Mr. Y's vacation, sick and personal leave days with a join to
his personal timesheet. the predicate in the WHEN just gets uglier,
but so will any procedural code that has to have the same logic inside
a loop with IF-THEN statements. Timekeeping is not simple.|||The biggest problem with the query you posted is that the * 10 is not
sufficient for the previously posted reason. What I am saying about the
holidays is that we have an indeterminate amount of users and each will
have their own set of holidays so there will be a separate table for
those holidays and that the Calendar table would have the "master" or
default list of holidays.|||"pb648174" <google@.webpaul.net> wrote in message
news:1113826655.615952.214820@.o13g2000cwo.googlegr oups.com...
> The biggest problem with the query you posted is that the * 10 is not
> sufficient for the previously posted reason. What I am saying about the
> holidays is that we have an indeterminate amount of users and each will
> have their own set of holidays so there will be a separate table for
> those holidays and that the Calendar table would have the "master" or
> default list of holidays.

If you have a calendar table with all of the dates and day types:
(Dt, daytype)

And you have a user table with dates and day types only for those days that
are different
(User, Dt, daytype)

Then you can get bob's calendar with

Select Dt, Coalesce(u.daytype,m.daytype) as daytype
from calendar as m left join usercalendar as u on m.dt = u.dt
Where u.user = 'bob'

Regards,
Jim|||Table joins I don't have a problem with.. My problem is the following,
which I have mentioned in each of the past couple replies:

"There will be user holidays added to this
as well, so it is possible that someone could be out for two weeks or
more and that when adding 1 business day, *10 will not be sufficient."

and

"The biggest problem with the query you posted is that the * 10 is not
sufficient for the previously posted reason"

because a person could have two weeks of vacation, a month of sick
leave, etc. I need something that performs well and doesn't include
some "hacked" factor to make it perform well. The first hack was the
Calendar table, which made this "* x" factor necessary as an additional
hack...

So far I don't see any indication that SQL will be able to handle this
kind of task.|||OK here goes:

1. Create a table (temp or static with ONLY working days and a
contiguous numbering sequence between one day and the next working day)
- that makes it very easy to do the offset.

2. adding the user holiday is easy (find the number of days holiday
between the 2 dates and recalculate)

Sample code (written assuming UK date format - should work with US):

SET NOCOUNT ON

DECLARE @.StartDate datetime
DECLARE @.DaysOffset int

SET @.StartDate = '2005-04-20'
SET @.DaysOffset = 5

DECLARE @.DateWithOffsetNoUserHolidays datetime
DECLARE @.TempDay datetime
DECLARE @.nSequence int
DECLARE @.UserWorkingDayHolidays int
DECLARE @.DateWithOffset datetime

SET @.UserWorkingDayHolidays = 0

CREATE TABLE #WorkingDays ([Date] smalldatetime NOT NULL, [sequence] int
NOT NULL)

SET @.nSequence = 0
SET @.TempDay = '2005-01-01'

WHILE @.TempDay < '2006-12-31'
BEGIN

IF DATEPART(dw, @.TempDay) BETWEEN 2 AND 6 -- AND NOT EXISTS(SELECT 1
FROM BankHolidays WHERE [Date] = @.TempDay
BEGIN
INSERT INTO #WorkingDays VALUES(@.TempDay, @.nSequence)
SET @.nSequence = @.nSequence + 1
END

SELECT @.TempDay = DATEADD(dd,1, @.TempDay)
END

SELECT @.DateWithOffsetNoUserHolidays = [Date]
FROM #WorkingDays
WHERE [Sequence] = (SELECT [Sequence] + @.DaysOffset FROM #WorkingDays
WHERE [Date] = @.StartDate)

/*Get number of user holidays during period between @.StartDate and
@.DateWithOffset and redo:
e.g.
SELECT @.UserWorkingDayHolidays = Count(*)
FROM UserHolidays
WHERE Date BETWEEN @.StartDate AND @.DateWithOffsetNoUserHolidays AND
User = ...
*/

SELECT @.DateWithOffset = [Date]
FROM #WorkingDays
WHERE [Sequence] = (SELECT [Sequence] + @.DaysOffset +
@.UserWorkingDayHolidays FROM #WorkingDays WHERE [Date] = @.StartDate)

DROP TABLE #WorkingDays

SELECT @.DateWithOffset

*** Sent via Developersdex http://www.developersdex.com ***|||My last effort was flawed for the user holiday element of your
requirement. Here's a better effort, but doesn't work for calculating
bussiness days in the past - you'd have to change the penultimate select
for that.

Change @.StartDate and @.BusinessDaysOffset to change the inputs. Should
be sub-second even for a 10

The script build a temporary table of working days the first time it's
run - better to have a permanent table reallty.

Duncan

SET NOCOUNT ON

DECLARE @.StartDate datetime
DECLARE @.BusinessDaysOffset int

SET @.StartDate = '2005-04-21'
SET @.BusinessDaysOffset = 2

IF @.BusinessDaysOffset < 1
RAISERROR('Offset must be > 0',16,1)

/* Create sample temp table for user holidays */
IF OBJECT_ID('tempdb..#UserHolidays') IS NULL
BEGIN
CREATE TABLE #UserHolidays (UserId int NOT NULL, HolidayDate
smalldatetime)

/* Add tomorrow as a holiday for user 1 - date must not contain time -
so that's why there's so many CASTs */
INSERT INTO #UserHolidays VALUES (1, CAST(CAST(CAST(DATEADD(d, 1,
getdate()) as real) as int) as smalldatetime))
END

DECLARE @.TempDay datetime
DECLARE @.nSequence int

/* Get date without time element */
SET @.StartDate = CAST(CAST(CAST(@.StartDate as real) as int) as
smalldatetime)

IF OBJECT_ID('tempdb..#WorkingDays') IS NULL
BEGIN
/* better to use a permanent table as the data doesn't change very
often - but this is just an example */
CREATE TABLE #WorkingDays ([Date] smalldatetime NOT NULL, [sequence]
int NOT NULL)
CREATE UNIQUE INDEX ID_WorkingDays_Date ON #WorkingDays ([Date])

SET @.nSequence = 0
SET @.TempDay = '2005-01-01'

WHILE @.TempDay < '2030-12-31'
BEGIN
/* don't add Saturday or Sunday - or bank holidays from table */
IF DATEPART(dw, @.TempDay) BETWEEN 2 AND 6 /* AND NOT EXISTS(SELECT
1 FROM BankHolidays WHERE [Date] = @.TempDay) */
BEGIN
INSERT INTO #WorkingDays VALUES(@.TempDay, @.nSequence)
SET @.nSequence = @.nSequence + 1
END

SELECT @.TempDay = DATEADD(dd,1, @.TempDay)
END
END
/*Temp table populated */

/* Create temp table to contain working days for the user and after date
specified*/
CREATE TABLE #UserWorkingDays ([Date] smalldatetime NOT NULL)

CREATE UNIQUE INDEX ID_UserWorkingDays_Date ON #UserWorkingDays ([Date])

/*Limit the number of rows to the number of working days required */
SET ROWCOUNT @.BusinessDaysOffset

/* Insert working days after current one where not joined to user's
holiday table*/
INSERT INTO #UserWorkingDays ([Date])
SELECT WD.[Date]
FROM #WorkingDays AS WD
LEFT JOIN #UserHolidays AS UH ON WD.[Date] = UH.HolidayDate AND
UH.UserId = 1
WHERE UH.HolidayDate IS NULL AND [Date] > @.StartDate
ORDER BY WD.[Date]

/* Get last working day */
SELECT MAX([Date])
FROM #UserWorkingDays

DROP TABLE #UserWorkingDays

*** Sent via Developersdex http://www.developersdex.com ***|||I had tried the rowcount solution previously and it did not work for
some (unposted, due to other queries around the calculations and we
will eventually need time) reasons. We have given up on doing this in
SQL and are doing it in C# quite successfully - thank you everyone for
the help.sql

Poor performace re-attaching DB after a detach

I was real surprised to see the query performance goes real bad once I
detach a database and turn around the re-attach the database. I did the
usual detach with checking the 'Update Statistics'.
I have confirmed that doing a detach without updating the statistics and
then re-attaching does not change the query plan or degrade the performance.
This is counter-intuitive and has me worried because accurate statistics
should give the best performance not the worst.
Can anyone comment on this behavior ? Thanks, Mac
I have used SQL Server for almost 5 years now and have never
seen the behavior you describe. Are you sure you are accurately
testing the speed up on detach? You may want to review the profiler
results to see exactly what is happening.
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.learncsharp.net/home/listings.aspx
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:d0ii9v$1qf6$1@.si05.rsvl.unisys.com...
>I was real surprised to see the query performance goes real bad once I
> detach a database and turn around the re-attach the database. I did the
> usual detach with checking the 'Update Statistics'.
> I have confirmed that doing a detach without updating the statistics and
> then re-attaching does not change the query plan or degrade the
> performance.
> This is counter-intuitive and has me worried because accurate statistics
> should give the best performance not the worst.
> Can anyone comment on this behavior ? Thanks, Mac
>
|||Thanks for responding. I did check the query plan before and after
detach/re-attach and the query plan changed for worse after I did an Update
Statistics. I have two tables involved in the query. Both tables have
primary keys.
Table A has a very small population where as Table B has a very large
population.
Before setting update statistics, the query plan shows that Table A is
processed as table scan followed by Table B as indexed search. After the
update statistics, the query plan starts table scan on Table B followed by
indexed search on table A and hence the poor performance.
The point I see here is that the query optimizer does not check the
population size to choose the right table to start the table scan. I
suppose I have no choice but use query Hint to tell optimizer how to
proceed.
I should say that in the join query I have, there is no constant key value
to use. So, one table has to be picked as table scan. Had the query
optimizer selected table A as table scan, it would have retrieved the value
it needs to do the index search on Table B.
"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:ue02Td3IFHA.576@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> I have used SQL Server for almost 5 years now and have never
> seen the behavior you describe. Are you sure you are accurately
> testing the speed up on detach? You may want to review the profiler
> results to see exactly what is happening.
> --
> 2005 Microsoft MVP C#
> Robbe Morris
> http://www.robbemorris.com
> http://www.learncsharp.net/home/listings.aspx
>
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:d0ii9v$1qf6$1@.si05.rsvl.unisys.com...
the
>
|||It might be a cacheing artifact. I'd bet that detatching the database
flushes the database pages from cache (or at least makes them
immediately available for flushing). Does the query plan change when
you re-attach or does the performance just go south? Does the slowness
persist or does performance pick back up after a while?
Mac Vazehgoo wrote:
> *I was real surprised to see the query performance goes real bad once
> I
> detach a database and turn around the re-attach the database. I did
> the
> usual detach with checking the 'Update Statistics'.
> I have confirmed that doing a detach without updating the statistics
> and
> then re-attaching does not change the query plan or degrade the
> performance.
> This is counter-intuitive and has me worried because accurate
> statistics
> should give the best performance not the worst.
> Can anyone comment on this behavior ? Thanks, Mac *
revdrwebb
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message946476.html
|||Have you tried updating the stats WITH FULLSCAN?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:d0iur4$22m3$1@.si05.rsvl.unisys.com...
Thanks for responding. I did check the query plan before and after
detach/re-attach and the query plan changed for worse after I did an Update
Statistics. I have two tables involved in the query. Both tables have
primary keys.
Table A has a very small population where as Table B has a very large
population.
Before setting update statistics, the query plan shows that Table A is
processed as table scan followed by Table B as indexed search. After the
update statistics, the query plan starts table scan on Table B followed by
indexed search on table A and hence the poor performance.
The point I see here is that the query optimizer does not check the
population size to choose the right table to start the table scan. I
suppose I have no choice but use query Hint to tell optimizer how to
proceed.
I should say that in the join query I have, there is no constant key value
to use. So, one table has to be picked as table scan. Had the query
optimizer selected table A as table scan, it would have retrieved the value
it needs to do the index search on Table B.
"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:ue02Td3IFHA.576@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> I have used SQL Server for almost 5 years now and have never
> seen the behavior you describe. Are you sure you are accurately
> testing the speed up on detach? You may want to review the profiler
> results to see exactly what is happening.
> --
> 2005 Microsoft MVP C#
> Robbe Morris
> http://www.robbemorris.com
> http://www.learncsharp.net/home/listings.aspx
>
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:d0ii9v$1qf6$1@.si05.rsvl.unisys.com...
the
>
|||Yes. The query plan changes such that it does not start the query
processing with the less populated table.
I am going to use update stats with FULLSCAN as suggested by others to see
if that helps. Thanks.
"revdrwebb" <revdrwebb.1lk0p1@.mail.webservertalk.com> wrote in message
news:revdrwebb.1lk0p1@.mail.webservertalk.com...
> It might be a cacheing artifact. I'd bet that detatching the database
> flushes the database pages from cache (or at least makes them
> immediately available for flushing). Does the query plan change when
> you re-attach or does the performance just go south? Does the slowness
> persist or does performance pick back up after a while?
> Mac Vazehgoo wrote:
>
> --
> revdrwebb
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message946476.html
>

Poor performace re-attaching DB after a detach

I was real surprised to see the query performance goes real bad once I
detach a database and turn around the re-attach the database. I did the
usual detach with checking the 'Update Statistics'.
I have confirmed that doing a detach without updating the statistics and
then re-attaching does not change the query plan or degrade the performance.
This is counter-intuitive and has me worried because accurate statistics
should give the best performance not the worst.
Can anyone comment on this behavior ? Thanks, MacI have used SQL Server for almost 5 years now and have never
seen the behavior you describe. Are you sure you are accurately
testing the speed up on detach? You may want to review the profiler
results to see exactly what is happening.
--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.learncsharp.net/home/listings.aspx
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:d0ii9v$1qf6$1@.si05.rsvl.unisys.com...
>I was real surprised to see the query performance goes real bad once I
> detach a database and turn around the re-attach the database. I did the
> usual detach with checking the 'Update Statistics'.
> I have confirmed that doing a detach without updating the statistics and
> then re-attaching does not change the query plan or degrade the
> performance.
> This is counter-intuitive and has me worried because accurate statistics
> should give the best performance not the worst.
> Can anyone comment on this behavior ? Thanks, Mac
>|||Thanks for responding. I did check the query plan before and after
detach/re-attach and the query plan changed for worse after I did an Update
Statistics. I have two tables involved in the query. Both tables have
primary keys.
Table A has a very small population where as Table B has a very large
population.
Before setting update statistics, the query plan shows that Table A is
processed as table scan followed by Table B as indexed search. After the
update statistics, the query plan starts table scan on Table B followed by
indexed search on table A and hence the poor performance.
The point I see here is that the query optimizer does not check the
population size to choose the right table to start the table scan. I
suppose I have no choice but use query Hint to tell optimizer how to
proceed.
I should say that in the join query I have, there is no constant key value
to use. So, one table has to be picked as table scan. Had the query
optimizer selected table A as table scan, it would have retrieved the value
it needs to do the index search on Table B.
"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:ue02Td3IFHA.576@.TK2MSFTNGP15.phx.gbl...
> I have used SQL Server for almost 5 years now and have never
> seen the behavior you describe. Are you sure you are accurately
> testing the speed up on detach? You may want to review the profiler
> results to see exactly what is happening.
> --
> 2005 Microsoft MVP C#
> Robbe Morris
> http://www.robbemorris.com
> http://www.learncsharp.net/home/listings.aspx
>
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:d0ii9v$1qf6$1@.si05.rsvl.unisys.com...
> >I was real surprised to see the query performance goes real bad once I
> > detach a database and turn around the re-attach the database. I did
the
> > usual detach with checking the 'Update Statistics'.
> >
> > I have confirmed that doing a detach without updating the statistics and
> > then re-attaching does not change the query plan or degrade the
> > performance.
> > This is counter-intuitive and has me worried because accurate statistics
> > should give the best performance not the worst.
> >
> > Can anyone comment on this behavior ? Thanks, Mac
> >
> >
>|||It might be a cacheing artifact. I'd bet that detatching the database
flushes the database pages from cache (or at least makes them
immediately available for flushing). Does the query plan change when
you re-attach or does the performance just go south? Does the slowness
persist or does performance pick back up after a while?
Mac Vazehgoo wrote:
> *I was real surprised to see the query performance goes real bad once
> I
> detach a database and turn around the re-attach the database. I did
> the
> usual detach with checking the 'Update Statistics'.
> I have confirmed that doing a detach without updating the statistics
> and
> then re-attaching does not change the query plan or degrade the
> performance.
> This is counter-intuitive and has me worried because accurate
> statistics
> should give the best performance not the worst.
> Can anyone comment on this behavior ? Thanks, Mac *
revdrwebb
---
Posted via http://www.webservertalk.com
---
View this thread: http://www.webservertalk.com/message946476.html|||Have you tried updating the stats WITH FULLSCAN?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:d0iur4$22m3$1@.si05.rsvl.unisys.com...
Thanks for responding. I did check the query plan before and after
detach/re-attach and the query plan changed for worse after I did an Update
Statistics. I have two tables involved in the query. Both tables have
primary keys.
Table A has a very small population where as Table B has a very large
population.
Before setting update statistics, the query plan shows that Table A is
processed as table scan followed by Table B as indexed search. After the
update statistics, the query plan starts table scan on Table B followed by
indexed search on table A and hence the poor performance.
The point I see here is that the query optimizer does not check the
population size to choose the right table to start the table scan. I
suppose I have no choice but use query Hint to tell optimizer how to
proceed.
I should say that in the join query I have, there is no constant key value
to use. So, one table has to be picked as table scan. Had the query
optimizer selected table A as table scan, it would have retrieved the value
it needs to do the index search on Table B.
"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:ue02Td3IFHA.576@.TK2MSFTNGP15.phx.gbl...
> I have used SQL Server for almost 5 years now and have never
> seen the behavior you describe. Are you sure you are accurately
> testing the speed up on detach? You may want to review the profiler
> results to see exactly what is happening.
> --
> 2005 Microsoft MVP C#
> Robbe Morris
> http://www.robbemorris.com
> http://www.learncsharp.net/home/listings.aspx
>
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:d0ii9v$1qf6$1@.si05.rsvl.unisys.com...
> >I was real surprised to see the query performance goes real bad once I
> > detach a database and turn around the re-attach the database. I did
the
> > usual detach with checking the 'Update Statistics'.
> >
> > I have confirmed that doing a detach without updating the statistics and
> > then re-attaching does not change the query plan or degrade the
> > performance.
> > This is counter-intuitive and has me worried because accurate statistics
> > should give the best performance not the worst.
> >
> > Can anyone comment on this behavior ? Thanks, Mac
> >
> >
>|||Let us know how that turns out.
--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.learncsharp.net/home/listings.aspx
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:d0iur4$22m3$1@.si05.rsvl.unisys.com...
> Thanks for responding. I did check the query plan before and after
> detach/re-attach and the query plan changed for worse after I did an
> Update
> Statistics. I have two tables involved in the query. Both tables have
> primary keys.
> Table A has a very small population where as Table B has a very large
> population.
> Before setting update statistics, the query plan shows that Table A is
> processed as table scan followed by Table B as indexed search. After the
> update statistics, the query plan starts table scan on Table B followed
> by
> indexed search on table A and hence the poor performance.
> The point I see here is that the query optimizer does not check the
> population size to choose the right table to start the table scan. I
> suppose I have no choice but use query Hint to tell optimizer how to
> proceed.
> I should say that in the join query I have, there is no constant key value
> to use. So, one table has to be picked as table scan. Had the query
> optimizer selected table A as table scan, it would have retrieved the
> value
> it needs to do the index search on Table B.
>
> "Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
> news:ue02Td3IFHA.576@.TK2MSFTNGP15.phx.gbl...
>> I have used SQL Server for almost 5 years now and have never
>> seen the behavior you describe. Are you sure you are accurately
>> testing the speed up on detach? You may want to review the profiler
>> results to see exactly what is happening.
>> --
>> 2005 Microsoft MVP C#
>> Robbe Morris
>> http://www.robbemorris.com
>> http://www.learncsharp.net/home/listings.aspx
>>
>> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
>> news:d0ii9v$1qf6$1@.si05.rsvl.unisys.com...
>> >I was real surprised to see the query performance goes real bad once I
>> > detach a database and turn around the re-attach the database. I did
> the
>> > usual detach with checking the 'Update Statistics'.
>> >
>> > I have confirmed that doing a detach without updating the statistics
>> > and
>> > then re-attaching does not change the query plan or degrade the
>> > performance.
>> > This is counter-intuitive and has me worried because accurate
>> > statistics
>> > should give the best performance not the worst.
>> >
>> > Can anyone comment on this behavior ? Thanks, Mac
>> >
>> >
>>
>|||Yes. The query plan changes such that it does not start the query
processing with the less populated table.
I am going to use update stats with FULLSCAN as suggested by others to see
if that helps. Thanks.
"revdrwebb" <revdrwebb.1lk0p1@.mail.webservertalk.com> wrote in message
news:revdrwebb.1lk0p1@.mail.webservertalk.com...
> It might be a cacheing artifact. I'd bet that detatching the database
> flushes the database pages from cache (or at least makes them
> immediately available for flushing). Does the query plan change when
> you re-attach or does the performance just go south? Does the slowness
> persist or does performance pick back up after a while?
> Mac Vazehgoo wrote:
> > *I was real surprised to see the query performance goes real bad once
> > I
> > detach a database and turn around the re-attach the database. I did
> > the
> > usual detach with checking the 'Update Statistics'.
> >
> > I have confirmed that doing a detach without updating the statistics
> > and
> > then re-attaching does not change the query plan or degrade the
> > performance.
> > This is counter-intuitive and has me worried because accurate
> > statistics
> > should give the best performance not the worst.
> >
> > Can anyone comment on this behavior ? Thanks, Mac *
>
> --
> revdrwebb
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message946476.html
>|||I am afraid the update stats WITH FULLSCAN did not improve the performance.
I will post a close example of what I have just so you all know what the
problem might be.
Thanks for the suggestions.
"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:u86uSE5IFHA.2744@.TK2MSFTNGP15.phx.gbl...
> Let us know how that turns out.
> --
> 2005 Microsoft MVP C#
> Robbe Morris
> http://www.robbemorris.com
> http://www.learncsharp.net/home/listings.aspx
>
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:d0iur4$22m3$1@.si05.rsvl.unisys.com...
> > Thanks for responding. I did check the query plan before and after
> > detach/re-attach and the query plan changed for worse after I did an
> > Update
> > Statistics. I have two tables involved in the query. Both tables have
> > primary keys.
> >
> > Table A has a very small population where as Table B has a very large
> > population.
> > Before setting update statistics, the query plan shows that Table A is
> > processed as table scan followed by Table B as indexed search. After
the
> > update statistics, the query plan starts table scan on Table B followed
> > by
> > indexed search on table A and hence the poor performance.
> >
> > The point I see here is that the query optimizer does not check the
> > population size to choose the right table to start the table scan. I
> > suppose I have no choice but use query Hint to tell optimizer how to
> > proceed.
> >
> > I should say that in the join query I have, there is no constant key
value
> > to use. So, one table has to be picked as table scan. Had the query
> > optimizer selected table A as table scan, it would have retrieved the
> > value
> > it needs to do the index search on Table B.
> >
> >
> >
> > "Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
> > news:ue02Td3IFHA.576@.TK2MSFTNGP15.phx.gbl...
> >> I have used SQL Server for almost 5 years now and have never
> >> seen the behavior you describe. Are you sure you are accurately
> >> testing the speed up on detach? You may want to review the profiler
> >> results to see exactly what is happening.
> >>
> >> --
> >> 2005 Microsoft MVP C#
> >> Robbe Morris
> >> http://www.robbemorris.com
> >> http://www.learncsharp.net/home/listings.aspx
> >>
> >>
> >>
> >> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> >> news:d0ii9v$1qf6$1@.si05.rsvl.unisys.com...
> >> >I was real surprised to see the query performance goes real bad once I
> >> > detach a database and turn around the re-attach the database. I did
> > the
> >> > usual detach with checking the 'Update Statistics'.
> >> >
> >> > I have confirmed that doing a detach without updating the statistics
> >> > and
> >> > then re-attaching does not change the query plan or degrade the
> >> > performance.
> >> > This is counter-intuitive and has me worried because accurate
> >> > statistics
> >> > should give the best performance not the worst.
> >> >
> >> > Can anyone comment on this behavior ? Thanks, Mac
> >> >
> >> >
> >>
> >>
> >
> >
>