Returning one row takes longer! 1

[kunai]

Hi,

I've got this pretty big query (lots of fields and inner join).

Running the query takes between 2 and 4 seconds.

But, i only want to get 1 row.

I tried TOP 1 or SET ROWCOUNT 1 but those just make my query run in about 30secs!

Is there something i'm missing here? I want to get 1 row only.

Is there another way of doing this?

Thanks.

(And i thought this'd be simple!) hehe

 

[druer]

Wow! that is strange behavior. Did you rerun things multiple times each way? Is it possible that the old query was already cached and had a plan, and that the first time you ran the new ones it had to do a compile, and it happened to be at a very busy time?

 

[SQLDenis]

It shouldn't matter but try using option (fast 1)
example below

use northwind
select top 1 * FROM [Order Details] od INNER JOIN Orders o ON od.OrderID =
rderID option (fast 1)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[kunai]

The server isn't particularly busy. I did run it multiple times though. (In Query Analyzer, SQL server 2000)

It's the same query everytime, same WHERE criteria too.

Just weird.

 

[kunai]

Same with "option (fast 1)". Takes 30secs to return 1 row.

Without anything, takes 2secs to return 8 rows.

 

[vongrunt]

Strange... at least SET ROWCOUNT N should not affect exec time negatively. It is only "safety valve" that instructs engine to stop processing query after N rows reached. Exec plan itself remains unchanged.

What happens if you update stats on all tables involved?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[kunai]

What do you mean by "update stats"?

 

[SQLDenis]

UPDATE STATISTICS or sp_updatestats assuming you have statistics of course
I had a problem a couple of weeks ago and when I updated the stats it ran 100 * faster

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[kunai]

Ah. No, I don't have statistics set on my tables.

 

[Celeste025]

I know that when I run a query with no specific criteria but no sorting, it works relatively quickly. But as soon as I add sort criteria it takes a lot longer. Ive just found that sometimes, depending on what kind of criteria youre asking it for or how youre asking it to output the data, it takes longer to do that than just return all of the rows.

 

[kunai]

Thanks. Maybe you're right Celeste.

First time this happens to me though... even if this query is not the biggest i've written.

I am doing a lot of convert and cases, etc, etc.

I'll keep looking for something.

 

[mreigler]

Have you looked at the query plan to see if it changes? What is it the order by clause?

With a lot of cases and convert statements could it be a problem figuring out what the top 1 record is?

Mike Reigler
Melange Computer Services, Inc

 

[kunai]

hmmm.. didn't think of that.
The plan does change.

With my two table:

With TOP 1, the cost for Table Scan is 95% and 0%

Without TOP 1, the cost for Table Scanc is 47% and 37%. There are also more steps.

I'll take a look at that on Monday. For now i'm just omitting the TOP 1.

Thanks for your ideas guys!