SQL compact edition: queries

Showing posts with label queries. Show all posts

Friday, March 30, 2012

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 performance with NEWID()

[Expr1002] ASC))
|--Compute Scalar(DEFINE

[Expr1002]=newid()))
|--Clustered Index Scan(OBJECT

[HaxPlans].[dbo].[MyTable].[PK_MyTable]))
So, I guess you can do a little hack like this:
select * from
(
select top 10 * from mytable
) x
order by newid()
And get fewer calls to newid()
StmtText
-----
|--Sort(ORDER BY

[Expr1002] ASC))
|--Compute Scalar(DEFINE

[Expr1002]=newid()))
|--Top(10)
|--Clustered Index Scan(OBJECT

[HaxPlans].[dbo].[MyTable].[PK_MyTable]))
Oh, wait a minute, you were doing an INSERT, maybe there is something
funky about the table you're inserting into? Maybe you're really
doing large numbers than 250?
But wait another minute, if you're doing an insert, WHY ARE YOU
ORDERING THE RECORDS ANYWAY? Is the destination table "flat", with no
indexes, just really an output buffer? Well, hmm, that should WORK,
and I still don't understand in that case especially why the
performance would vary so much. Just noodling around with it.
J.
On Fri, 4 Nov 2005 13:05:25 -0800, "Dean J Garrett" <info@.amuletc.com>
wrote:
>We're experiencing very poor performance on successive runs of queries such
>as the following:
>'---
>SET NOCOUNT ON
>TRUNCATE TABLE tblSurveyTemp
>INSERT INTO tblSurveyTemp (FirstName, LastName,
>Email,Sex,Age,City,State,Country,ZipCode,Married, ChildrenAtHome,Education,Em
>ploymentStatus,Occupation,Industry,Income,Ethnici ty,DateSent)
>SELECT TOP 250 FirstName, LastName,
>Email,Sex,Age,City,State,Country,ZipCode,Married, ChildrenAtHome,Education,Em
>ploymentStatus,Occupation,Industry,Income,Ethnici ty,getdate()
>FROM tblMember t2
>WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
>('F')
>AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
>t.survey='grainactivity')
>ORDER BY NEWID()
>SELECT FirstName,LastName,Email FROM tblSurveyTemp
>'---
>
>We're using the NEWID() function to randomize the sample. CPU is always at
>100% when we run the query. The first time it runs successfully takes about
>20 seconds. Second time, maybe 1 min. Third time it timed out.
>Does anyone have any advice?
>Thank you!!!
>
|||You just need to generate random number to select 250 random records. So a
variation of following query can be used for this purpose.
select au_id,au_lname, au_fname,
convert(smallint,rand() * ascii(left(au_lname,1)) *
ascii(right(au_lname,1))) % 77 value1 from authors
order by value1
The newid() creates a unique value of type uniqueidentifier which is a
16-byte binary values. Thus the filtered records are being sorted on a very
wide column. The query suggested by me will be sorted on a smallint data type
column which takes 2 bytes only.

Poor performance with NEWID()

[Expr1002] ASC))
|--Compute Scalar(DEFINE

[Expr1002]=newid()))
|--Clustered Index Scan(OBJECT

[HaxPlans].[dbo].[MyTable].[
PK_MyTable]))
So, I guess you can do a little hack like this:
select * from
(
select top 10 * from mytable
) x
order by newid()
And get fewer calls to newid()
StmtText
----
--
|--Sort(ORDER BY

[Expr1002] ASC))
|--Compute Scalar(DEFINE

[Expr1002]=newid()))
|--Top(10)
|--Clustered Index Scan(OBJECT

[HaxPlans].[dbo].[MyTable].[
PK_MyTable]))
Oh, wait a minute, you were doing an INSERT, maybe there is something
funky about the table you're inserting into? Maybe you're really
doing large numbers than 250?
But wait another minute, if you're doing an insert, WHY ARE YOU
ORDERING THE RECORDS ANYWAY? Is the destination table "flat", with no
indexes, just really an output buffer? Well, hmm, that should WORK,
and I still don't understand in that case especially why the
performance would vary so much. Just noodling around with it.
J.
On Fri, 4 Nov 2005 13:05:25 -0800, "Dean J Garrett" <info@.amuletc.com>
wrote:
>We're experiencing very poor performance on successive runs of queries such
>as the following:
>'---
>SET NOCOUNT ON
>TRUNCATE TABLE tblSurveyTemp
>INSERT INTO tblSurveyTemp (FirstName, LastName,
> Email,Sex,Age,City,State,Country,ZipCode
,Married,ChildrenAtHome,Education,E
m
> ploymentStatus,Occupation,Industry,Incom
e,Ethnicity,DateSent)
>SELECT TOP 250 FirstName, LastName,
> Email,Sex,Age,City,State,Country,ZipCode
,Married,ChildrenAtHome,Education,E
m
> ploymentStatus,Occupation,Industry,Incom
e,Ethnicity,getdate()
>FROM tblMember t2
>WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
>('F')
>AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
>t.survey='grainactivity')
>ORDER BY NEWID()
>SELECT FirstName,LastName,Email FROM tblSurveyTemp
>'---
>
>We're using the NEWID() function to randomize the sample. CPU is always at
>100% when we run the query. The first time it runs successfully takes abou
t
>20 seconds. Second time, maybe 1 min. Third time it timed out.
>Does anyone have any advice?
>Thank you!!!
>|||You just need to generate random number to select 250 random records. So a
variation of following query can be used for this purpose.
select au_id,au_lname, au_fname,
convert(smallint,rand() * ascii(left(au_lname,1)) *
ascii(right(au_lname,1))) % 77 value1 from authors
order by value1
The newid() creates a unique value of type uniqueidentifier which is a
16-byte binary values. Thus the filtered records are being sorted on a very
wide column. The query suggested by me will be sorted on a smallint data typ
e
column which takes 2 bytes only.

Poor performance with NEWID()

We're experiencing very poor performance on successive runs of queries such
as the following:
'---
SET NOCOUNT ON
TRUNCATE TABLE tblSurveyTemp
INSERT INTO tblSurveyTemp (FirstName, LastName,
Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
ploymentStatus,Occupation,Industry,Income,Ethnicity,DateSent)
SELECT TOP 250 FirstName, LastName,
Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
ploymentStatus,Occupation,Industry,Income,Ethnicity,getdate()
FROM tblMember t2
WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
('F')
AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
t.survey='grainactivity')
ORDER BY NEWID()
SELECT FirstName,LastName,Email FROM tblSurveyTemp
'---
We're using the NEWID() function to randomize the sample. CPU is always at
100% when we run the query. The first time it runs successfully takes about
20 seconds. Second time, maybe 1 min. Third time it timed out.
Does anyone have any advice?
Thank you!!!I dont have an answer, but a question. Why would you ever want to order by
newid()?
--
TIA,
ChrisR
"Dean J Garrett" wrote:
> We're experiencing very poor performance on successive runs of queries such
> as the following:
> '---
> SET NOCOUNT ON
> TRUNCATE TABLE tblSurveyTemp
> INSERT INTO tblSurveyTemp (FirstName, LastName,
> Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
> ploymentStatus,Occupation,Industry,Income,Ethnicity,DateSent)
> SELECT TOP 250 FirstName, LastName,
> Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
> ploymentStatus,Occupation,Industry,Income,Ethnicity,getdate()
> FROM tblMember t2
> WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
> ('F')
> AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
> t.survey='grainactivity')
> ORDER BY NEWID()
> SELECT FirstName,LastName,Email FROM tblSurveyTemp
> '---
>
> We're using the NEWID() function to randomize the sample. CPU is always at
> 100% when we run the query. The first time it runs successfully takes about
> 20 seconds. Second time, maybe 1 min. Third time it timed out.
> Does anyone have any advice?
> Thank you!!!
>
>|||On Fri, 4 Nov 2005 14:12:01 -0800, ChrisR wrote:
>I dont have an answer, but a question. Why would you ever want to order by
>newid()?
Hi Chris,
The combination of TOP ... and ORDER BY NEWID() is often used to get a
pseudo-random sample. The ORDER BY NEWID() makes sure that the rows are
scrambled in an unpredictabable way; the TOP then takes only the few
rows that happen to be first.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||This method of choosing a random set of records (250 in your case) out of a
larger set of records is pretty effective when there are not many records in
the filtered select. In your case tblMember filtered by your where clause
must produce quite a few records. SQL Server has to pull those records
togther and sort them by the newid() value. Sorting a lot of records can take
a long time. To see how many records we're talking run:
SELECT COUNT(*)
FROM tblMember t2
WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
('F')
AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
t.survey='grainactivity')
In the end you may need to choose another method to choose your records
randomly.
Good luck!
-Phil
"Dean J Garrett" wrote:
> We're experiencing very poor performance on successive runs of queries such
> as the following:
> '---
> SET NOCOUNT ON
> TRUNCATE TABLE tblSurveyTemp
> INSERT INTO tblSurveyTemp (FirstName, LastName,
> Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
> ploymentStatus,Occupation,Industry,Income,Ethnicity,DateSent)
> SELECT TOP 250 FirstName, LastName,
> Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
> ploymentStatus,Occupation,Industry,Income,Ethnicity,getdate()
> FROM tblMember t2
> WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
> ('F')
> AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
> t.survey='grainactivity')
> ORDER BY NEWID()
> SELECT FirstName,LastName,Email FROM tblSurveyTemp
> '---
>
> We're using the NEWID() function to randomize the sample. CPU is always at
> 100% when we run the query. The first time it runs successfully takes about
> 20 seconds. Second time, maybe 1 min. Third time it timed out.
> Does anyone have any advice?
> Thank you!!!
>
>|||Here is the article we used to figure out this technique
http://www.windowsitpro.com/Articles/ArticleID/19842/19842.html but we don't
know if it is the best performing!
Thanks
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B2EF1E86-E0AF-4FA3-A787-67C30B2AEA32@.microsoft.com...
> I dont have an answer, but a question. Why would you ever want to order by
> newid()?
> --
> TIA,
> ChrisR
>
> "Dean J Garrett" wrote:
> > We're experiencing very poor performance on successive runs of queries
such
> > as the following:
> >
> > '---
> > SET NOCOUNT ON
> > TRUNCATE TABLE tblSurveyTemp
> >
> > INSERT INTO tblSurveyTemp (FirstName, LastName,
> >
Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
> > ploymentStatus,Occupation,Industry,Income,Ethnicity,DateSent)
> > SELECT TOP 250 FirstName, LastName,
> >
Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
> > ploymentStatus,Occupation,Industry,Income,Ethnicity,getdate()
> > FROM tblMember t2
> > WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex
in
> > ('F')
> > AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email
AND
> > t.survey='grainactivity')
> > ORDER BY NEWID()
> >
> > SELECT FirstName,LastName,Email FROM tblSurveyTemp
> > '---
> >
> >
> > We're using the NEWID() function to randomize the sample. CPU is always
at
> > 100% when we run the query. The first time it runs successfully takes
about
> > 20 seconds. Second time, maybe 1 min. Third time it timed out.
> >
> > Does anyone have any advice?
> >
> > Thank you!!!
> >
> >
> >
> >|||Very kinky.
I have no idea why you would get such differential results, if indeed
you are using the same parameters each time.
On a toy table, you can see that the newid() gets called BEFORE the
top function.
select top 10 * from mytable
order by newid()
StmtText
-----
|--Sort(TOP 10, ORDER BY:([Expr1002] ASC))
|--Compute Scalar(DEFINE:([Expr1002]=newid()))
|--Clustered Index Scan(OBJECT:([HaxPlans].[dbo].[MyTable].[PK_MyTable]))
So, I guess you can do a little hack like this:
select * from
(
select top 10 * from mytable
) x
order by newid()
And get fewer calls to newid()
StmtText
-----
|--Sort(ORDER BY:([Expr1002] ASC))
|--Compute Scalar(DEFINE:([Expr1002]=newid()))
|--Top(10)
|--Clustered Index Scan(OBJECT:([HaxPlans].[dbo].[MyTable].[PK_MyTable]))
--
Oh, wait a minute, you were doing an INSERT, maybe there is something
funky about the table you're inserting into? Maybe you're really
doing large numbers than 250?
But wait another minute, if you're doing an insert, WHY ARE YOU
ORDERING THE RECORDS ANYWAY? Is the destination table "flat", with no
indexes, just really an output buffer? Well, hmm, that should WORK,
and I still don't understand in that case especially why the
performance would vary so much. Just noodling around with it.
J.
On Fri, 4 Nov 2005 13:05:25 -0800, "Dean J Garrett" <info@.amuletc.com>
wrote:
>We're experiencing very poor performance on successive runs of queries such
>as the following:
>'---
>SET NOCOUNT ON
>TRUNCATE TABLE tblSurveyTemp
>INSERT INTO tblSurveyTemp (FirstName, LastName,
>Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
>ploymentStatus,Occupation,Industry,Income,Ethnicity,DateSent)
>SELECT TOP 250 FirstName, LastName,
>Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
>ploymentStatus,Occupation,Industry,Income,Ethnicity,getdate()
>FROM tblMember t2
>WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
>('F')
>AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
>t.survey='grainactivity')
>ORDER BY NEWID()
>SELECT FirstName,LastName,Email FROM tblSurveyTemp
>'---
>
>We're using the NEWID() function to randomize the sample. CPU is always at
>100% when we run the query. The first time it runs successfully takes about
>20 seconds. Second time, maybe 1 min. Third time it timed out.
>Does anyone have any advice?
>Thank you!!!
>|||You just need to generate random number to select 250 random records. So a
variation of following query can be used for this purpose.
select au_id,au_lname, au_fname,
convert(smallint,rand() * ascii(left(au_lname,1)) *
ascii(right(au_lname,1))) % 77 value1 from authors
order by value1
The newid() creates a unique value of type uniqueidentifier which is a
16-byte binary values. Thus the filtered records are being sorted on a very
wide column. The query suggested by me will be sorted on a smallint data type
column which takes 2 bytes only.sql

Poor performance when using Transact SQL cursor

After a recent upgrade from SQL Server 2000 to SQL Server 2005, we get
problems with certain queries using a Transact SQL cursor.
I have noticed the following:
Table with a smaller number of rows.
declare cursor completes normally.
open cursor completes normally
fetch cursor retrieves the first row from the table.
Table with a larger number of rows.
declare cursor completes normally.
open cursor builds a temporary table with information about all the rows
matching the seek conditions. (This can take some time, depending on the
number of rows)
fetch cursor retrives a row from the table, based on values from the first
row in the temporary table.
This behaviour is undesirable because the application may cancel the current
query, do something else and start a new query on the same table. This
creates a lot of overhead.
I have done several tests, and I am sure that the change in behaviour is not
governed by the number of rows returned, but solely on the number of rows in
the table. Setting the conditions such that now rows will meet the conditions
will still show execute as described above.
Can anyone tell me why it has changed, and how I can get the "old" behaviour
back, or just point me to a place where it is described.
Thanks in advance.Did you remember to update stats on all tables with FULLSCAN when you did
the migration? Also, do you really need a cursor to do what you need?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"ErikE" <ErikE@.discussions.microsoft.com> wrote in message
news:4CB99F77-9C10-4FC6-BDFF-98C1B8A022FB@.microsoft.com...
After a recent upgrade from SQL Server 2000 to SQL Server 2005, we get
problems with certain queries using a Transact SQL cursor.
I have noticed the following:
Table with a smaller number of rows.
declare cursor completes normally.
open cursor completes normally
fetch cursor retrieves the first row from the table.
Table with a larger number of rows.
declare cursor completes normally.
open cursor builds a temporary table with information about all the rows
matching the seek conditions. (This can take some time, depending on the
number of rows)
fetch cursor retrives a row from the table, based on values from the first
row in the temporary table.
This behaviour is undesirable because the application may cancel the current
query, do something else and start a new query on the same table. This
creates a lot of overhead.
I have done several tests, and I am sure that the change in behaviour is not
governed by the number of rows returned, but solely on the number of rows in
the table. Setting the conditions such that now rows will meet the
conditions
will still show execute as described above.
Can anyone tell me why it has changed, and how I can get the "old" behaviour
back, or just point me to a place where it is described.
Thanks in advance.|||What kind of cursor did you declare?
Linchi
"ErikE" wrote:
> After a recent upgrade from SQL Server 2000 to SQL Server 2005, we get
> problems with certain queries using a Transact SQL cursor.
> I have noticed the following:
> Table with a smaller number of rows.
> declare cursor completes normally.
> open cursor completes normally
> fetch cursor retrieves the first row from the table.
> Table with a larger number of rows.
> declare cursor completes normally.
> open cursor builds a temporary table with information about all the rows
> matching the seek conditions. (This can take some time, depending on the
> number of rows)
> fetch cursor retrives a row from the table, based on values from the first
> row in the temporary table.
> This behaviour is undesirable because the application may cancel the current
> query, do something else and start a new query on the same table. This
> creates a lot of overhead.
> I have done several tests, and I am sure that the change in behaviour is not
> governed by the number of rows returned, but solely on the number of rows in
> the table. Setting the conditions such that now rows will meet the conditions
> will still show execute as described above.
> Can anyone tell me why it has changed, and how I can get the "old" behaviour
> back, or just point me to a place where it is described.
> Thanks in advance.|||I have the same problem,
i updated the statistics and nothing. it gives "Transaction ended by
trigger"
it works fine on SQL 2000 with no problem.
I am using Forward Only and Read Only Cursor.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:E15A5055-5388-425A-9981-7BBCB2DF8E37@.microsoft.com...
> What kind of cursor did you declare?
> Linchi
> "ErikE" wrote:
>> After a recent upgrade from SQL Server 2000 to SQL Server 2005, we get
>> problems with certain queries using a Transact SQL cursor.
>> I have noticed the following:
>> Table with a smaller number of rows.
>> declare cursor completes normally.
>> open cursor completes normally
>> fetch cursor retrieves the first row from the table.
>> Table with a larger number of rows.
>> declare cursor completes normally.
>> open cursor builds a temporary table with information about all the rows
>> matching the seek conditions. (This can take some time, depending on the
>> number of rows)
>> fetch cursor retrives a row from the table, based on values from the
>> first
>> row in the temporary table.
>> This behaviour is undesirable because the application may cancel the
>> current
>> query, do something else and start a new query on the same table. This
>> creates a lot of overhead.
>> I have done several tests, and I am sure that the change in behaviour is
>> not
>> governed by the number of rows returned, but solely on the number of rows
>> in
>> the table. Setting the conditions such that now rows will meet the
>> conditions
>> will still show execute as described above.
>> Can anyone tell me why it has changed, and how I can get the "old"
>> behaviour
>> back, or just point me to a place where it is described.
>> Thanks in advance.|||I tried the different types according to the transact-sql extended syntax,
all with the same result.
"Linchi Shea" wrote:
> What kind of cursor did you declare?
> Linchi
> "ErikE" wrote:
> > After a recent upgrade from SQL Server 2000 to SQL Server 2005, we get
> > problems with certain queries using a Transact SQL cursor.
> > I have noticed the following:
> > Table with a smaller number of rows.
> > declare cursor completes normally.
> > open cursor completes normally
> > fetch cursor retrieves the first row from the table.
> >
> > Table with a larger number of rows.
> > declare cursor completes normally.
> > open cursor builds a temporary table with information about all the rows
> > matching the seek conditions. (This can take some time, depending on the
> > number of rows)
> > fetch cursor retrives a row from the table, based on values from the first
> > row in the temporary table.
> >
> > This behaviour is undesirable because the application may cancel the current
> > query, do something else and start a new query on the same table. This
> > creates a lot of overhead.
> >
> > I have done several tests, and I am sure that the change in behaviour is not
> > governed by the number of rows returned, but solely on the number of rows in
> > the table. Setting the conditions such that now rows will meet the conditions
> > will still show execute as described above.
> >
> > Can anyone tell me why it has changed, and how I can get the "old" behaviour
> > back, or just point me to a place where it is described.
> >
> > Thanks in advance.|||I have remembered to update stats. Cursors are only used in older
applications, so the problem is just to avoid spending time rewriting these
applications.
"Tom Moreau" wrote:
> Did you remember to update stats on all tables with FULLSCAN when you did
> the migration? Also, do you really need a cursor to do what you need?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "ErikE" <ErikE@.discussions.microsoft.com> wrote in message
> news:4CB99F77-9C10-4FC6-BDFF-98C1B8A022FB@.microsoft.com...
> After a recent upgrade from SQL Server 2000 to SQL Server 2005, we get
> problems with certain queries using a Transact SQL cursor.
> I have noticed the following:
> Table with a smaller number of rows.
> declare cursor completes normally.
> open cursor completes normally
> fetch cursor retrieves the first row from the table.
> Table with a larger number of rows.
> declare cursor completes normally.
> open cursor builds a temporary table with information about all the rows
> matching the seek conditions. (This can take some time, depending on the
> number of rows)
> fetch cursor retrives a row from the table, based on values from the first
> row in the temporary table.
> This behaviour is undesirable because the application may cancel the current
> query, do something else and start a new query on the same table. This
> creates a lot of overhead.
> I have done several tests, and I am sure that the change in behaviour is not
> governed by the number of rows returned, but solely on the number of rows in
> the table. Setting the conditions such that now rows will meet the
> conditions
> will still show execute as described above.
> Can anyone tell me why it has changed, and how I can get the "old" behaviour
> back, or just point me to a place where it is described.
> Thanks in advance.
>
>

Poor performance when using Transact SQL cursor

After a recent upgrade from SQL Server 2000 to SQL Server 2005, we get
problems with certain queries using a Transact SQL cursor.
I have noticed the following:
Table with a smaller number of rows.
declare cursor completes normally.
open cursor completes normally
fetch cursor retrieves the first row from the table.
Table with a larger number of rows.
declare cursor completes normally.
open cursor builds a temporary table with information about all the rows
matching the seek conditions. (This can take some time, depending on the
number of rows)
fetch cursor retrives a row from the table, based on values from the first
row in the temporary table.
This behaviour is undesirable because the application may cancel the current
query, do something else and start a new query on the same table. This
creates a lot of overhead.
I have done several tests, and I am sure that the change in behaviour is not
governed by the number of rows returned, but solely on the number of rows in
the table. Setting the conditions such that now rows will meet the conditions
will still show execute as described above.
Can anyone tell me why it has changed, and how I can get the "old" behaviour
back, or just point me to a place where it is described.
Thanks in advance.
Did you remember to update stats on all tables with FULLSCAN when you did
the migration? Also, do you really need a cursor to do what you need?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"ErikE" <ErikE@.discussions.microsoft.com> wrote in message
news:4CB99F77-9C10-4FC6-BDFF-98C1B8A022FB@.microsoft.com...
After a recent upgrade from SQL Server 2000 to SQL Server 2005, we get
problems with certain queries using a Transact SQL cursor.
I have noticed the following:
Table with a smaller number of rows.
declare cursor completes normally.
open cursor completes normally
fetch cursor retrieves the first row from the table.
Table with a larger number of rows.
declare cursor completes normally.
open cursor builds a temporary table with information about all the rows
matching the seek conditions. (This can take some time, depending on the
number of rows)
fetch cursor retrives a row from the table, based on values from the first
row in the temporary table.
This behaviour is undesirable because the application may cancel the current
query, do something else and start a new query on the same table. This
creates a lot of overhead.
I have done several tests, and I am sure that the change in behaviour is not
governed by the number of rows returned, but solely on the number of rows in
the table. Setting the conditions such that now rows will meet the
conditions
will still show execute as described above.
Can anyone tell me why it has changed, and how I can get the "old" behaviour
back, or just point me to a place where it is described.
Thanks in advance.
|||What kind of cursor did you declare?
Linchi
"ErikE" wrote:

> After a recent upgrade from SQL Server 2000 to SQL Server 2005, we get
> problems with certain queries using a Transact SQL cursor.
> I have noticed the following:
> Table with a smaller number of rows.
> declare cursor completes normally.
> open cursor completes normally
> fetch cursor retrieves the first row from the table.
> Table with a larger number of rows.
> declare cursor completes normally.
> open cursor builds a temporary table with information about all the rows
> matching the seek conditions. (This can take some time, depending on the
> number of rows)
> fetch cursor retrives a row from the table, based on values from the first
> row in the temporary table.
> This behaviour is undesirable because the application may cancel the current
> query, do something else and start a new query on the same table. This
> creates a lot of overhead.
> I have done several tests, and I am sure that the change in behaviour is not
> governed by the number of rows returned, but solely on the number of rows in
> the table. Setting the conditions such that now rows will meet the conditions
> will still show execute as described above.
> Can anyone tell me why it has changed, and how I can get the "old" behaviour
> back, or just point me to a place where it is described.
> Thanks in advance.
|||I have the same problem,
i updated the statistics and nothing. it gives "Transaction ended by
trigger"
it works fine on SQL 2000 with no problem.
I am using Forward Only and Read Only Cursor.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:E15A5055-5388-425A-9981-7BBCB2DF8E37@.microsoft.com...[vbcol=seagreen]
> What kind of cursor did you declare?
> Linchi
> "ErikE" wrote:
|||I tried the different types according to the transact-sql extended syntax,
all with the same result.
"Linchi Shea" wrote:
[vbcol=seagreen]
> What kind of cursor did you declare?
> Linchi
> "ErikE" wrote:
|||I have remembered to update stats. Cursors are only used in older
applications, so the problem is just to avoid spending time rewriting these
applications.
"Tom Moreau" wrote:

> Did you remember to update stats on all tables with FULLSCAN when you did
> the migration? Also, do you really need a cursor to do what you need?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "ErikE" <ErikE@.discussions.microsoft.com> wrote in message
> news:4CB99F77-9C10-4FC6-BDFF-98C1B8A022FB@.microsoft.com...
> After a recent upgrade from SQL Server 2000 to SQL Server 2005, we get
> problems with certain queries using a Transact SQL cursor.
> I have noticed the following:
> Table with a smaller number of rows.
> declare cursor completes normally.
> open cursor completes normally
> fetch cursor retrieves the first row from the table.
> Table with a larger number of rows.
> declare cursor completes normally.
> open cursor builds a temporary table with information about all the rows
> matching the seek conditions. (This can take some time, depending on the
> number of rows)
> fetch cursor retrives a row from the table, based on values from the first
> row in the temporary table.
> This behaviour is undesirable because the application may cancel the current
> query, do something else and start a new query on the same table. This
> creates a lot of overhead.
> I have done several tests, and I am sure that the change in behaviour is not
> governed by the number of rows returned, but solely on the number of rows in
> the table. Setting the conditions such that now rows will meet the
> conditions
> will still show execute as described above.
> Can anyone tell me why it has changed, and how I can get the "old" behaviour
> back, or just point me to a place where it is described.
> Thanks in advance.
>
>

Wednesday, March 28, 2012

Poor performance querying view from sproc

We are using SQL SERVER 2005.

We have been experiencing horrible performance running select queries from sprocs against views that do outer joins, despite having optimized all of the indexes.

Our solution in these cases (and arguably not the best) has been to first select the views into a temp table and then query the temp table for the desired result set. This has greatly improved the speed of which our sprocs run, but we are looking for a better solution.

Has anybody else experienced this problem?

Thanks in advance.

Hi Eric. Would it be possible for you to post some sample code that includes some of the view definitions, table structures, and sample data scripts? It's quite tough to try and guess what the issue may be.

One thing that comes to mind would be if you selecting only a subset of the columns from the view into a temp table and then joining/selecting from that multiple times, you could be bypassing many bookmark/cluster key lookups, but that is a total shot in the dark. If you can post some scripts, that would help us try and help you. You can get the DDL for the tables/indexes/views by simply scripting the appropriate tables from SSMS/QA...

HTH,

Chad

|||

Are you joining multiple views? Or just a view that joins multiple tables?

What I would do is use a trial replacement for your current implimentation where you create the join etc (as you have in the view) in the query where you actually use the view.

I am not 100% sure how views work in MSSQL, but I hope this can be of some assistance.

|||Can you please paste your stored procedure code. I suspect it is due to the selectivity of the predicates that means you are getting a poor plan. If you can past the output of show plan that would also help.|||We have experienced exactly the same thing. Queries which work fine in 1-5 seconds on SQL 2000 SP4 either run very long or never finish in hours under SQL 2005. I have posted several posts here and never gotten any responses.

We are running SQL 2005 9.0.2153 on SQL 2003 SP1 64bit. The problem seems to be related to views with RIGHT outer joins.

Here is my duplication script, which fails on 2 different SQL 2005 installations.

-- Create Test Data Table CustomerListTom and Views

USE AdventureWorks
GO

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'ViewATom'))
DROP VIEW [ViewATom]

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'ViewBTom'))
DROP VIEW [ViewBTom]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'CustomerListTom') AND type in (N'U'))
DROP TABLE CustomerListTom
GO

CREATE TABLE [dbo].[CustomerListTom](
[CustomerID] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RegionID] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FirstName] [dbo].[Name] NOT NULL,
[LastName] [dbo].[Name] NOT NULL,
[EmailAddress] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddressLine] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressCity] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressState] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressZip] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressCountry] [dbo].[Name] NOT NULL,
[Phone] [dbo].[Phone] NULL,
[BillAddressLine] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressCity] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressState] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressZip] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressCountry] [dbo].[Name] NOT NULL,
[BillPhone] [dbo].[Phone] NULL,
[ModifiedDate] [datetime] NOT NULL,
)

INSERT INTO CustomerListTom
SELECT
CustomerID = RIGHT(cu.AccountNumber,6),
RegionID = RIGHT('000'+CAST(cu.TerritoryID AS VARCHAR(3)),3),
FirstName = ct.FirstName,
LastName = ct.LastName,
EmailAddress = ct.EmailAddress,
AddressLine = ad.AddressLine1,
AddressCity = ad.City,
AddressState = sp.StateProvinceCode,
AddressZip = ad.PostalCode,
AddressCountry = sp.[Name],
Phone = ct.Phone,

BillAddressLine = ad.AddressLine1,
BillAddressCity = ad.City,
BillAddressState = sp.StateProvinceCode,
BillAddressZip = ad.PostalCode,
BillAddressCountry = sp.[Name],
BillPhone = ct.Phone,

ModifiedDate = cu.ModifiedDate
--,*
FROM Sales.Customer cu
JOIN Sales.Individual id ON id.CustomerID = cu.CustomerID
JOIN Person.Contact ct ON ct.ContactID = id.ContactID
JOIN Sales.CustomerAddress ca ON cu.CustomerID = ca.CustomerID
JOIN Person.Address ad ON ad.AddressID = ca.AddressID
JOIN Person.StateProvince sp ON sp.StateProvinceID = ad.StateProvinceID

-- Create a big enough set of data for testing
DECLARE @.i INT
SET @.i = 1
WHILE (@.i < 30)
BEGIN
INSERT INTO CustomerListTom
SELECT TOP 15 PERCENT
CustomerID = RIGHT(cu.AccountNumber,6),
RegionID = RIGHT('000'+CAST(cu.TerritoryID+@.i AS VARCHAR(3)),3),
FirstName = ct.FirstName,
LastName = ct.LastName,
EmailAddress = ct.EmailAddress,
AddressLine = ad.AddressLine1,
AddressCity = ad.City,
AddressState = sp.StateProvinceCode,
AddressZip = ad.PostalCode,
AddressCountry = sp.[Name],
Phone = ct.Phone,
BillAddressLine = ad.AddressLine1,
BillAddressCity = ad.City,
BillAddressState = sp.StateProvinceCode,
BillAddressZip = ad.PostalCode,
BillAddressCountry = sp.[Name],
BillPhone = ct.Phone,

ModifiedDate = cu.ModifiedDate + CASE WHEN @.i > 3 THEN 10 ELSE -25 END + @.i

FROM Sales.Customer cu
JOIN Sales.Individual id ON id.CustomerID = cu.CustomerID
JOIN Person.Contact ct ON ct.ContactID = id.ContactID
JOIN Sales.CustomerAddress ca ON cu.CustomerID = ca.CustomerID
JOIN Person.Address ad ON ad.AddressID = ca.AddressID
JOIN Person.StateProvince sp ON sp.StateProvinceID = ad.StateProvinceID

SET @.i = @.i + 1
END

-- Cleanup - Delete Dups for PK
DELETE FROM CustomerListTom
WHERE CustomerID+RegionID IN (
SELECT CustomerID+RegionID
FROM CustomerListTom cu
GROUP BY CustomerID, RegionID
HAVING COUNT(*) > 1)

ALTER TABLE [CustomerListTom]
ADD CONSTRAINT [PK_CustomerListTom] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC,
[RegionID] ASC
)

-- Create Views
GO
CREATE VIEW ViewATom
AS
SELECT *
FROM CustomerListTom cu
WHERE cu.RegionID = '004'
UNION
SELECT *
FROM CustomerListTom cu
WHERE CustomerID NOT IN
(SELECT CustomerID FROM CustomerListTom c2 WHERE c2.RegionID = '004')
AND (CustomerID + CONVERT(char(8), ModifiedDate, 112) + RegionID IN
(SELECT MAX(CustomerID + CONVERT(char(8), ModifiedDate, 112) + RegionID)
FROM CustomerListTom
GROUP BY CustomerID))

GO
CREATE VIEW ViewBTom
AS
SELECT DISTINCT CustomerID
FROM CustomerListTom
GO
--return

USE AdventureWorks

-- FAILURE
-- This query FAILS to return in over 15 mins, cancelled
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return

-- SOLUTIONS
-- Change WHERE TABLEB to WHERE TABLEA, this query returns in less than 1 second
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEA.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return

-- Remove RIGHT OUTER on TABLEB, this Query returns in less than 2 seconds
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return

-- Drop PK and run ORIGINAL query, returns in less than 4 seconds

SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return

-- Create PK with NONCLUSTERED and run ORIGINAL query, returns in less than 1 second

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomerListTom]') AND name = N'PK_CustomerListTom')
ALTER TABLE [dbo].[CustomerListTom] DROP CONSTRAINT [PK_CustomerListTom]
GO
ALTER TABLE [CustomerListTom]
ADD CONSTRAINT [PK_CustomerListTom] PRIMARY KEY NONCLUSTERED
(
[CustomerID] ASC,
[RegionID] ASC
)
GO

return

Poor performance on Pentium 4 against AMD machine

HI,
We had a database on an AMD machine. We ran SQL queries
there and performance were fine.
At a certain point, we moved the database to a Pentium
machine (backup and restore).
And now, on the Pentium machine we get performance on the
same SQL queries which are 2-3 time slower.
Using SQL Profiler we noticed that the disk-read takes
the majority of the time.
We tried to switch disks but no change in performance.
Does anyone have any idea of what could be the reason?
Thanks,
Danny.What disk subsystem is in use in both machines? What RAID levels, SCSI,
number of disks?
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||In addition to Tony's question, what about memory configs?
"Danny Korach" <danny.korach@.clicksoftware.com> wrote in message
news:0bb301c3609d$c50dbe10$a101280a@.phx.gbl...
> HI,
> We had a database on an AMD machine. We ran SQL queries
> there and performance were fine.
> At a certain point, we moved the database to a Pentium
> machine (backup and restore).
> And now, on the Pentium machine we get performance on the
> same SQL queries which are 2-3 time slower.
> Using SQL Profiler we noticed that the disk-read takes
> the majority of the time.
> We tried to switch disks but no change in performance.
> Does anyone have any idea of what could be the reason?
> Thanks,
> Danny.

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

Friday, March 9, 2012

Please suggest just one book - To Manivannan and others

Hi,

I need you to suggest me one book so that I can learn T-sql - beginner to advanced.

I want to start writing queries like Manivannan and others so that i can also help others

Please suggest and give me a plan. I am ready to sincerely dedicate 2-3 hours every day.

thanks

Hi,

I very much happy to hear from you.

Effective Query writing will be come from experience & learning, 2 years back I used this forum like you only. Then I started learning from most of the genius from here (Kent, Arnie, AMB, Lousie & Jayachandar, OJ), frankly still I am learning from this forum (like to thank Sankar, ggcibuc, rusag2 & rich)

Learn, Practice & one day you will become as a expert.

I really like & learnt most of the things form this book,

Inside Microsoft SQL Server 2000 by Ron Soukup & Kalen Delaney

You can search like BOOK in this forum there are lot of suggestions available.

|||

thanks so much for answering..i forgot to mention i need something for Sql 2005. Smile

please suggest for 2005, specifically for t-sql programming.

currently i dont even know to write stored proc Sad

thanks.

|||

Check out the two books of the "Inside SQL Server 2005" series written by Itzik Ben-Gan.

Friday, March 30, 2012

Poor plan choice

Poor plan choice

Poor plan choice

Poor performance with NEWID()

Poor performance with NEWID()

Poor performance with NEWID()

Poor performance when using Transact SQL cursor

Poor performance when using Transact SQL cursor

Wednesday, March 28, 2012

Poor performance querying view from sproc

Poor performance on Pentium 4 against AMD machine

Poor Performance On Cross-Server Join

Friday, March 9, 2012

Please suggest just one book - To Manivannan and others

SQL compact edition

Blog Archive

About Me