Slow Queries

Question

Slow Queries

Neil Albrock

Newbie

Points: 2
More actions
October 10, 2007 at 10:08 am

#375612

Hi...
I am having some issues with simple SELECT queries on my SQL 2000 server.
Basically we have lots of tables, which each have 180,000+ rows and when attempting to run a SELECT * on any of them, from Query Analyzer or via ODBC from Coldfusion we are waiting 30 seconds or more for the results.
The tables have just one index, the primary key. So the exectution plan, as reported by Query Analyzer, is a clustered index scan, then the SELECT.
The weird thing is that if you link one of the tables to MS Access and run a SELECT * from the query designer, the results are virtually instant. Also, if you attach a WHERE clause it's plenty fast, as one would expect.
Any thoughts?
Am I stoopid?
Help...
Neil

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Difference in behavior. Access displays data the instant it has enough to fill one screen; SQL QA waits to have the WHOLE thing.

Try hitting the "scroll to the end" (last record) button in Access - it's going to then "stall" while it gets the rest of the data. You'll see it's not faster.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Neil Albrock Newbie Points: 2 More actions · Answer 2

Hey...

I am aware of the different way in which Access (JET more precisely) handles the query. However, if you scroll to the last record, it only takes a second to get there. So it is still a lot faster.

Are there any other factors that we could look into...?

toniupstny Hall of Fame Points: 3986 More actions · Answer 3

This may or may not be relevant depending on who/how the query is run but would getting the first group of rows using a FAST n Query Hint on the Select do any good?

John Rowan SSC Guru Points: 56440 More actions · Answer 4

Well first of all, any time you run a 'SELECT *' you get a full table scan. Your query plan shows a clustered index scan but in this case, it is scanning the entire clustered index so it is a full table scan. When you put a WHERE clause on it, the # of rows returned most likely will change dramatically. Just curious, but why run a 'SELECT *'? What could you possibly do with 180,000 + rows in QA?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

David Poluxt SSC-Addicted Points: 465 More actions · Answer 5

If a SELECT * gives you a full table scan, would a SELECT [field1], [field2] FROM MyTable be significantly faster if all you really needed was the first 2 fields? Wouldn't this still bring in all 180K records?

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 6

I know... old post... but, I've gotta ask...

Why in the the world would you do a SELECT * with no criteria on a table that contains 180,000 rows? You gonna read them all? 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Poluxt SSC-Addicted Points: 465 More actions · Answer 7

Well, I did actually read all of "War And Peace" once.;)

But, no, I'm not gonna want to read all 180K records. So let me modifiy my question:

Would SELECT [field1], [field2] from MyTable WHERE somecriteria = whatever

be significantly faster than

SELECT * from MyTable WHERE somecriteria = whatever

In both cases, you pull the same number of records; isn't that the primary determining factor for the speed of the query?

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 8

Sure... anytime you return less, it will usually return faster.

Lemme ask... are you using the GRID mode, or the TEXT mode? As you probably know, the Grid mode is substantially faster. I just did a SELECT * from a table with a million rows and it came back in 24 seconds... comes back in about 4 seconds on a 180,000 rows.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)