No execution plan in profiler

I'm trying to track down a problem with a very simple sql insert statement.
INSERT INTO tblRecordingId SELECT 82417, MAX(RecordingId)+1 FROM
tblRecordingId
This statement has recently begun to cause timeouts (but not all the time).
When it does work, I get this plan:
Table Insert(OBJECT:([myroc].[dbo].[tblRecordingId]),
SET:([tblRecordingId].[RecordingId]=RaiseIfNull([Expr1004]),
[tblRecordingId].[AccountId]=RaiseIfNull(82440)))
|--Top(1)
|--Compute Scalar(DEFINE:([Expr1004]=[Expr1002]+1))
|--Stream
Aggregate(DEFINE:([Expr1002]=MAX([tblRec
ordingId].[RecordingId])))
|--Table Scan(OBJECT:([myroc].[dbo].[tblRecordingId]))
CPU=16, Reads=121, Writes=0, Duration=10860.
When it causes a timeout, there's no execution plan in the profiler. CPU=15,
Reads=40, Writes=0, Duration=40000.
tblRecordingId contains 2 int columns and 590 rows. There are no indexes or
keys.
I did add deadlock to the profiler but didn't get any entries. The server is
not doing much other work (cpu usage is very low.)
I also tried it in the query analyzer. The query usually executes in less
than a second, but sometimes it takes more than a minute.
Any ideas on what the problem is or hints on what to search for?
PeterMost probably a locking problem. The subquery that gets the MAX value does a
table scan. Can you add
an index on the RecordingId column?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter Laan" <plnews2000@.yahoo.se> wrote in message news:e7S7Uly0FHA.3780@.TK2MSFTNGP12.phx.
gbl...
> I'm trying to track down a problem with a very simple sql insert statement
.
> INSERT INTO tblRecordingId SELECT 82417, MAX(RecordingId)+1 FROM tblRecord
ingId
> This statement has recently begun to cause timeouts (but not all the time)
. When it does work, I
> get this plan:
> Table Insert(OBJECT:([myroc].[dbo].[tblRecordingId]),
> SET:([tblRecordingId].[RecordingId]=RaiseIfNull([Expr1004]),
> [tblRecordingId].[AccountId]=RaiseIfNull(82440)))
> |--Top(1)
> |--Compute Scalar(DEFINE:([Expr1004]=[Expr1002]+1))
> |--Stream Aggregate(DEFINE:([Expr1002]=MAX([tblRec
ordingId].[RecordingId])))
> |--Table Scan(OBJECT:([myroc].[dbo].[tblRecordingId]))
> CPU=16, Reads=121, Writes=0, Duration=10860.
> When it causes a timeout, there's no execution plan in the profiler. CPU=1
5, Reads=40, Writes=0,
> Duration=40000.
> tblRecordingId contains 2 int columns and 590 rows. There are no indexes o
r keys.
> I did add deadlock to the profiler but didn't get any entries. The server
is not doing much other
> work (cpu usage is very low.)
> I also tried it in the query analyzer. The query usually executes in less
than a second, but
> sometimes it takes more than a minute.
>
> Any ideas on what the problem is or hints on what to search for?
>
> Peter
>|||Peter Laan wrote:
> I'm trying to track down a problem with a very simple sql insert
> statement.
> INSERT INTO tblRecordingId SELECT 82417, MAX(RecordingId)+1 FROM
> tblRecordingId
> This statement has recently begun to cause timeouts (but not all the
> time). When it does work, I get this plan:
> Table Insert(OBJECT:([myroc].[dbo].[tblRecordingId]),
> SET:([tblRecordingId].[RecordingId]=RaiseIfNull([Expr1004]),
> [tblRecordingId].[AccountId]=RaiseIfNull(82440)))
> |--Top(1)
> |--Compute Scalar(DEFINE:([Expr1004]=[Expr1002]+1))
> |--Stream
> Aggregate(DEFINE:([Expr1002]=MAX([tblRec
ordingId].[RecordingId])))
> |--Table Scan(OBJECT:([myroc].[dbo].[tblRecordingId]))
> CPU=16, Reads=121, Writes=0, Duration=10860.
> When it causes a timeout, there's no execution plan in the profiler.
> CPU=15, Reads=40, Writes=0, Duration=40000.
> tblRecordingId contains 2 int columns and 590 rows. There are no
> indexes or keys.
> I did add deadlock to the profiler but didn't get any entries. The
> server is not doing much other work (cpu usage is very low.)
> I also tried it in the query analyzer. The query usually executes in
> less than a second, but sometimes it takes more than a minute.
>
> Any ideas on what the problem is or hints on what to search for?
>
> Peter
Tibor is correct. The table scan is the problem. Do you not have an
index on the RecordingID column? If not, you need to add one. Also, are
you using the MAX command to return the next key value for inserting? If
so, you really need a unique index on the column and the statement may
not guarantee unique results. I would recommend that you either use an
identity column to have SQL Server auto-generate the next key value or
you use an another table that holds the next value and update it and
grab the key when needed.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||[posted and mailed, vnligen svara i nys
Peter Laan (plnews2000@.yahoo.se) writes:
> When it causes a timeout, there's no execution plan in the profiler.
> CPU=15, Reads=40, Writes=0, Duration=40000.
This is because the timeout is something that occurs in the client that
causes it to cancel the batch, and the plan event is not generated until
the query completes.
Beware that when you get command timeouts, you must issue something
like "IF @.@.trancount > 0 ROLLBACK TRANSACTION", as any transaction
started by the batch is not rolled back automatically.
Or even better, set the command timeout to 0 which means wait forever.
Does make life simpler.

> tblRecordingId contains 2 int columns and 590 rows. There are no indexes
> or keys.
As Tibor and David said, for this type of query you must have an index.

> I did add deadlock to the profiler but didn't get any entries.
A deadlock is not a timeout. That's when two processes are waiting for
each to other release resources. As Tibor and David said, you probably
had a blocking scenario.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks to all who replied! As you correctly guessed it was a blocking
problem.
Petersql

No distinct in a select into stement ?

Dear MSSQL experts,

I use MSSQL 2000 and encountered a strange problem wqhile I tried to
use a select into statement .

If I perform the command command below I get only one dataset which has
the described properties.
If I use the same statement in a select into statement (see the second
select) I get several datasets with the described properties like I
didn't use distinct
Is there any posiibility to use destinct in a select into statement

select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
Title2, Title3 as Title3,
AggregationTitle1 as AggregationTitle1, AggregationTitle2 as
AggregationTitle2,
AggregationTitle3 as AggregationTitle3, AggregationTitle4 as
AggregationTitle4
from Variables where Title1 is not NULL or Title2 is not NULL or
Title3 is not NULL or
AggregationTitle1 is not NULL or AggregationTitle2 is not NULL or
AggregationTitle3 is not NULL or AggregationTitle4 is not NULL;

This is the same with select into :

select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
Title2, Title3 as Title3,
AggregationTitle1 as AggregationTitle1, AggregationTitle2 as
AggregationTitle2,
AggregationTitle3 as AggregationTitle3, AggregationTitle4 as
AggregationTitle4
into VarTitles from Variables where Title1 is not NULL or Title2 is
not NULL or Title3 is not NULL or
AggregationTitle1 is not NULL or AggregationTitle2 is not NULL or
AggregationTitle3 is not NULL or
AggregationTitle4 is not NULL;

Hope anyone can help.

Best regards,

Daniel Wetzler

IDaniel Wetzler wrote:
> Dear MSSQL experts,
> I use MSSQL 2000 and encountered a strange problem wqhile I tried to
> use a select into statement .
> If I perform the command command below I get only one dataset which has
> the described properties.
> If I use the same statement in a select into statement (see the second
> select) I get several datasets with the described properties like I
> didn't use distinct
> Is there any posiibility to use destinct in a select into statement
> select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
> Title2, Title3 as Title3,
> AggregationTitle1 as AggregationTitle1, AggregationTitle2 as
> AggregationTitle2,
> AggregationTitle3 as AggregationTitle3, AggregationTitle4 as
> AggregationTitle4
> from Variables where Title1 is not NULL or Title2 is not NULL or
> Title3 is not NULL or
> AggregationTitle1 is not NULL or AggregationTitle2 is not NULL or
> AggregationTitle3 is not NULL or AggregationTitle4 is not NULL;
>
> This is the same with select into :
> select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
> Title2, Title3 as Title3,
> AggregationTitle1 as AggregationTitle1, AggregationTitle2 as
> AggregationTitle2,
> AggregationTitle3 as AggregationTitle3, AggregationTitle4 as
> AggregationTitle4
> into VarTitles from Variables where Title1 is not NULL or Title2 is
> not NULL or Title3 is not NULL or
> AggregationTitle1 is not NULL or AggregationTitle2 is not NULL or
> AggregationTitle3 is not NULL or
> AggregationTitle4 is not NULL;
> Hope anyone can help.
> Best regards,
> Daniel Wetzler

The IDENTITY function makes each row unique so DISTINCT doesn't
eliminate the duplicates in this case. Interestingly, this behaviour
seems to have changed in SQL Server 2005. If I run your SELECT INTO on
2005 I get a different execution plan with the IDENTITY value computed
after DISTINCT.

For 2000 the workaround is easy. The following should insert just one
row into vartitles.

CREATE TABLE variables (title1 VARCHAR(10) NULL, title2 VARCHAR(10)
NULL, title3 VARCHAR(10) NULL, aggregationtitle1 VARCHAR(10) NULL,
aggregationtitle2 VARCHAR(10) NULL, aggregationtitle3 VARCHAR(10) NULL,
aggregationtitle4 VARCHAR(10) NULL);

INSERT INTO variables VALUES ('1','1','1','1','1','1','1');
INSERT INTO variables VALUES ('1','1','1','1','1','1','1');

SELECT IDENTITY (INT) AS id,
title1, title2, title3, aggregationtitle1, aggregationtitle2,
aggregationtitle3, aggregationtitle4
INTO VarTitles
FROM (
SELECT DISTINCT
title1, title2, title3, aggregationtitle1, aggregationtitle2,
aggregationtitle3, aggregationtitle4
FROM variables
WHERE title1 IS NOT NULL
OR title2 IS NOT NULL
OR title3 IS NOT NULL
OR aggregationtitle1 IS NOT NULL
OR aggregationtitle2 IS NOT NULL
OR aggregationtitle3 IS NOT NULL
OR aggregationtitle4 IS NOT NULL) AS V ;

SELECT * FROM vartitles ;

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Thank you very much.
This was a very helpful hint.

Best regards,

Daniel|||Minor trick to make the code easier to read and maintain:

WHERE COALESCE (title1, title2, title3) IS NOT NULL
OR COALESCE (ggregation_title1, aggregation_title2,
aggregation_title3, aggregation_title4) IS NOT NULL

Unfortunately these columns look like repeated and a really bad 1NF
problem. I have the feeling that you might have wanted to use
COALESCE() in the SELECT list to get a non-null title and non-null
aggregation_title instead of this convoluted query.|||> Minor trick to make the code easier to read and maintain:
> WHERE COALESCE (title1, title2, title3) IS NOT NULL
> OR COALESCE (ggregation_title1, aggregation_title2,
> aggregation_title3, aggregation_title4) IS NOT NULL

and... bang goes performance too other than a probable clustered index scan
/ table scan.

Consider these two statements on my 800,000 row 834MByte message table for
the nntp forums...

There is a non-clustered index on nntp_author and there is non-clustered
index on author_id

-- Query 1
select count(*)
from mb_message_detail
where nntp_author is not null
or author_id is not null

-- Query 2
select count(*)
from mb_message_detail
where coalesce( nntp_author, author_id ) is not null

Query 1 will use the index author_id and give a half reasonable plan.
Query 2 will do a clustered index scan

Out of 100%, Query 1 is 12% and Query 2 is a whopping 88%

Seriously, go and get a junior job as a programmer and get some very needed
industrial / real world experience instead of bashing people down on here,
as far as 'newbie' goes - you have room to talk.....

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145969845.021110.74070@.t31g2000cwb.googlegro ups.com...
> Minor trick to make the code easier to read and maintain:
> WHERE COALESCE (title1, title2, title3) IS NOT NULL
> OR COALESCE (ggregation_title1, aggregation_title2,
> aggregation_title3, aggregation_title4) IS NOT NULL
> Unfortunately these columns look like repeated and a really bad 1NF
> problem. I have the feeling that you might have wanted to use
> COALESCE() in the SELECT list to get a non-null title and non-null
> aggregation_title instead of this convoluted query.|||-------
Seriously, go and get a junior job as a programmer and get some very
needed
industrial / real world experience instead of bashing people down on
here,
as far as 'newbie' goes - you have room to talk.....
-------

<BIG GRIN> from on who has been the receiving side of one of CELKO's
many put-downs.

No cubes can be found error

In the data panel of the report designer component, every time I try to create or modify an MDX statement, I'm now getting a "No cubes can be found. (Microsoft.AnalysisServices.Controls)" error. Works fine on the Layout and Preview tabs. Just all of a sudden appeared. Working fine against relational sources. As a warning to all, Reporting Services and Analysis Services is definitely not a marriage made in heaven. Proceed with caution.I'm going to have to learn to delete my profile everytime something strange happens with SQL 2005. Somehow, my profile on my primary development machine was corrupted again.

No columns when using temp tables in T-SQL in OLEDB Source

We have a complicated select query that needs to build a couple temporary work tables that are then used in the final select statement (in an OLEDB Source data flow control). We can click preview and see the resultset, but if we click on the Columns view there are no columns. We can save and close the OLEDB Source control but downstream from it there are messages saying that there are no input columns. The T-SQL looks something like this (abbreviated):

SELECT fieldlist INTO #temp1 FROM table

SELECT fieldlist INTO #temp2 FROM table

SELECT fieldlist FROM table INNER JOIN #temp1 INNER JOIN #temp2

DROP TABLE #temp1; DROP TABLE #temp2

Has anyone been able to use temp tables in a source SQL statement in a data flow? Are we doing something wrong or incomplete?

Thanks, Gordy

You might try explicitly managing the creation and dropping of the needed temp table in tempdb. Impact on the server is the same, just a bit more work in the code. Then you should have a table that is indistinguishable from any other.

Ken

No columns when using temp tables in T-SQL in OLEDB Source

We have a complicated select query that needs to build a couple temporary work tables that are then used in the final select statement (in an OLEDB Source data flow control). We can click preview and see the resultset, but if we click on the Columns view there are no columns. We can save and close the OLEDB Source control but downstream from it there are messages saying that there are no input columns. The T-SQL looks something like this (abbreviated):

SELECT fieldlist INTO #temp1 FROM table

SELECT fieldlist INTO #temp2 FROM table

SELECT fieldlist FROM table INNER JOIN #temp1 INNER JOIN #temp2

DROP TABLE #temp1; DROP TABLE #temp2

Has anyone been able to use temp tables in a source SQL statement in a data flow? Are we doing something wrong or incomplete?

Thanks, Gordy

You might try explicitly managing the creation and dropping of the needed temp table in tempdb. Impact on the server is the same, just a bit more work in the code. Then you should have a table that is indistinguishable from any other.

Ken

no case statement in views + sql 7

I have a query which will be a subquery that I want to use a case statement on. (It is an outer join and I want to substitue nulls for a specific value.) I can make it work in query analyzer and can also make it work in a stored procedure, but I get a message that the case statement is not supported in views. So, I tried to make a function ,but they are not supported in SQL 7, and apparently stored procedures cannot be called inline in views, either. I can retrieve the desired recordset with exec sp_name, but I need the recordset to be used in another query and cannot figure out how to use the results from a Stored procedure as a subquery in a view. Any help is greatly appreciated.

Thanks,

Tman2Case statements are supported in views, but the GUI query designer built into Enterprise Manager cannot parse and graphically display statements using CASE. You can write your query using the Query Analyzer tool instead (no serious TSQL programmer uses the GUI).
Regardless, you do not need a CASE statement to substitute NULL for a specific value. Use the NULLIF() function instead.|||I was able to create a view in query designer that uses case, but it does not function correctly. (It defaults all values to 0). However, if I use the exact same SQL statement in query designer without it being a view, it works. (ie. create view sQL statement to create view, select * from view produces erroneous results, but SQL statement in query designer works.)

I will try the nullif function you mentioned, but htat will only work for this particular cases, and there will be many instances where I will need the case statement. Any ideas what to do? This only seems to be causing a problem in SQL 7, not the newer versions...|||Post your code if you want somebody here to review it. There could be a problem with default connection settings in Query Analyzer. The SET CONCAT NULL option, for instance, has bitten me several times...|||It probably is something like that. WHEN NULL was not identifying nulls as nulls. Any ideas, or this there something wrong with my syntax (ie must use isnull like in VB)? Thanks for all your help. I have it working now, (really can just use isnull without the case statement at all) but for academic purposes, I would be intrested to know if there is an option that changes this. I did a little research on the option you pointed out above, what a #!@.* that must have been to find...

Thanks again.

Newboe: table containing XML schemas in database

I just created a schema in the AdventureWorks DB (the place for newbies). Where are these schemas stored and what t-sql statement can list all XML Schemas defined in a database\table\column?

TIA,

Barkingdog

select x.name, t.name, c.name
from sys.column_xml_schema_collection_usages cx,
sys.tables t,
sys.columns c,
sys.xml_schema_collections x
where x.xml_collection_id = cx.xml_collection_id
and t.object_id = cx.object_id
and c.column_id = cx.column_id
and c.object_id = t.object_id

The best way to learn this (catalog views) is thru SQL Server Management Studio -> Object Explorer -> Database -> master -> Views -> System views. Just do select * from every one of them.

NEWBIE: TOP PREDICATE HELP

Hello NG
I am trying to use a declared variable of datatype INT within a SELECT
TOP n * FROM table WHERE clause ORDER BY column Statement. I keep getting an
incorrect syntax near my variable. What am I doing wrong?
IF @.varPRFI > @.varMinimumLabels
SELECT TOP @.varMinimumLabels * FROM [RestockLabels] WHERE
[PrintedCheck] = 0 AND UPPER([PRFI])='PULL FIRST'
ELSE
<do different Things>
TIAFAH
RandyTOP does not accept a variable in SQL Server 2000.
Try using SET ROWCOUNT, e.g.
SET ROWCOUNT @.varMinimumLabels
SELECT * FROM ... ORDER BY
You should also avoid SELECT * in production code.
A
"Randy" <randywfritz@.s@.nm@.r.com> wrote in message
news:eIWWAGY2FHA.2604@.TK2MSFTNGP12.phx.gbl...
> Hello NG
> I am trying to use a declared variable of datatype INT within a SELECT
> TOP n * FROM table WHERE clause ORDER BY column Statement. I keep getting
> an
> incorrect syntax near my variable. What am I doing wrong?
> IF @.varPRFI > @.varMinimumLabels
> SELECT TOP @.varMinimumLabels * FROM [RestockLabels] WHERE
> [PrintedCheck] = 0 AND UPPER([PRFI])='PULL FIRST'
> ELSE
> <do different Things>
> TIAFAH
> Randy
>|||OK TY
Now that brings up another dilemma. Let me expand - I need to get 15
Records I am looking for Priority records first if my priority records
exceed 15 then I get only 15 if they are less then 15 I need to get enough
records to make 15 - I had intended to use a UNION Query to get my 15 by the
following
IF @.varPRFI > @.varMinimumLabels
SELECT TOP @.varMinimumLabels * FROM [RestockLabels] WHERE
[PrintedCheck] = 0 AND UPPER([PRFI])='PULL FIRST'
ELSE
SET @.varMinimumLabels = @.varMinimumLabels-@.varPRFI
SELECT * FROM [RestockLabels] WHERE [PrintedCheck] = 0 AND
UPPER([PRFI])='PULL FIRST' UNION SELECT TOP @.varMinimumLabels * FROM (SELECT
* FROM [RestockLabels] WHERE [PrintedCheck] = 0 AND UPPER([PRFI])<>'PULL
FIRST')
Will SET ROWCOUNT Still accomplish this task when I remove the top predicate
form my Union SELECT Statement and will I also be able to insure that I get
my priorities - I do not have an order by clause on the union select - I
haven't gone that far yet but when I do will I lose priorities to non
priorities because my nons will be above in the ordered by result and then
getting the rowcount of the union query. I hope I made this clear enough.
Again TIAFAH
Randy
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OTh1XHY2FHA.4004@.TK2MSFTNGP09.phx.gbl...
> TOP does not accept a variable in SQL Server 2000.
> Try using SET ROWCOUNT, e.g.
> SET ROWCOUNT @.varMinimumLabels
> SELECT * FROM ... ORDER BY
> You should also avoid SELECT * in production code.
> A
>
> "Randy" <randywfritz@.s@.nm@.r.com> wrote in message
> news:eIWWAGY2FHA.2604@.TK2MSFTNGP12.phx.gbl...
SELECT
getting
>

Newbie: Timeout On IN statement

I have a query which returns the Top 100 selling products for each of the 14
categories that we sell. It takes 10 minutes to run, however. Does anyone
have a suggestion on speeding this up.
SELECT ProductID, CategoryID, Sales, Qty
FROM Sales s1
WHERE productid in
(SELECT TOP 100 productid
FROM Sales s2
WHERE (s1.productID = s2.productid)
ORDER BY s2.Sales DESC)
I have tried to sort the View "Sales" that it queries by the column Sales to
speed it up, to no effect. If I run the nested select on its own without th
e
WHERE clause, it runs in 2 seconds.Sorry, Wrong WHERE
SELECT ProductID, CategoryID, Sales, Qty
FROM Sales s1
WHERE productid in
(SELECT TOP 100 productid
FROM Sales s2
WHERE (s1.categoryID = s2.categoryid)
ORDER BY s2.Sales DESC)|||SELECT ProductID, CategoryID, Sales, Qty
FROM Sales s1
WHERE EXISTS
(SELECT *
FROM Sales s2
WHERE s1.productID = s2.productid)
dean
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:2B25C9C7-A42D-46F7-B0FF-A3E4B06EDD31@.microsoft.com...
>I have a query which returns the Top 100 selling products for each of the
>14
> categories that we sell. It takes 10 minutes to run, however. Does
> anyone
> have a suggestion on speeding this up.
> SELECT ProductID, CategoryID, Sales, Qty
> FROM Sales s1
> WHERE productid in
> (SELECT TOP 100 productid
> FROM Sales s2
> WHERE (s1.productID = s2.productid)
> ORDER BY s2.Sales DESC)
> I have tried to sort the View "Sales" that it queries by the column Sales
> to
> speed it up, to no effect. If I run the nested select on its own without
> the
> WHERE clause, it runs in 2 seconds.
>
>|||I think your query is written incorrectly. Don't you really mean:
SELECT ProductID, CategoryID, Sales, Qty
FROM Sales s1
WHERE productid in
(SELECT TOP 100 productid
FROM Sales s2
WHERE (s1.categoryID =
s2.categoryID) --Changed product to category here
ORDER BY s2.Sales DESC)
Aside from that, can you show the view definition? Is there a way to query
less tables than the view touches?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:2B25C9C7-A42D-46F7-B0FF-A3E4B06EDD31@.microsoft.com...
>I have a query which returns the Top 100 selling products for each of the
>14
> categories that we sell. It takes 10 minutes to run, however. Does
> anyone
> have a suggestion on speeding this up.
> SELECT ProductID, CategoryID, Sales, Qty
> FROM Sales s1
> WHERE productid in
> (SELECT TOP 100 productid
> FROM Sales s2
> WHERE (s1.productID = s2.productid)
> ORDER BY s2.Sales DESC)
> I have tried to sort the View "Sales" that it queries by the column Sales
> to
> speed it up, to no effect. If I run the nested select on its own without
> the
> WHERE clause, it runs in 2 seconds.
>
>|||Dean,
Won't that query simply return every row in the Sales table?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Dean" <dvitner@.nospam.gmail.com> wrote in message
news:O6lrF419FHA.1148@.tk2msftngp13.phx.gbl...
> SELECT ProductID, CategoryID, Sales, Qty
> FROM Sales s1
> WHERE EXISTS
> (SELECT *
> FROM Sales s2
> WHERE s1.productID = s2.productid)
> dean
> "Chuck" <Chuck@.discussions.microsoft.com> wrote in message
> news:2B25C9C7-A42D-46F7-B0FF-A3E4B06EDD31@.microsoft.com...
>|||an index on (CategoryID, Sales DESC, productid) might help|||SEE POST BELOW -- Chuck
"Adam Machanic" wrote:

> Dean,
> Won't that query simply return every row in the Sales table?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>

> "Dean" <dvitner@.nospam.gmail.com> wrote in message
> news:O6lrF419FHA.1148@.tk2msftngp13.phx.gbl...
>
Adam, Dean,
Dean's query did just that, but, I altered it to include the TOP 100 * ...
ORDER BY "Sales" and it returned the results in 9 seconds. (after changing
the where to reflect my mistake in the original post. Thanks to both of you
,
what a relief !
Does any one have an explanation of why EXISTS worked so much better than IN
?
Chuck Ghastin|||> Does any one have an explanation of why EXISTS worked so much better than
IN?
when you post both plans, you'll have a better chance of getting a
useful explanation|||"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:7FD2E304-00AF-452B-823A-94C1BB34BAA0@.microsoft.com...
> Dean's query did just that, but, I altered it to include the TOP 100 * ...
> ORDER BY "Sales" and it returned the results in 9 seconds. (after
> changing
> the where to reflect my mistake in the original post. Thanks to both of
> you,
> what a relief !
Can you show the query you ended up with? I'm not certain that what
you're describing is logically equivalent to your original query.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||missread it, sorry..
dean
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OnukR$19FHA.3608@.TK2MSFTNGP09.phx.gbl...
> Dean,
> Won't that query simply return every row in the Sales table?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Dean" <dvitner@.nospam.gmail.com> wrote in message
> news:O6lrF419FHA.1148@.tk2msftngp13.phx.gbl...
>