Select from 25 million Rows

Question

Post reply

Select from 25 million Rows

The_SQL_DBA

SSChampion

Points: 10494
More actions
August 5, 2008 at 8:58 pm

#126908

I have a table that has over 25million rows. When I do a select by mentioning a set of fields from the number of fields available on the table,I use just the 'select * from table where condition1, condition2...conditionx>0criteria but this process is getting deadlocked and also, other processes are getting timed out.It might be because there are many fields with value>0 ( critierax). What is the best possible solution in this case.I want to implement a fast running query. Is there a way to create a temp table and then do a select from it..any ideas are welcome...Thanks in Advance!!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Viewing 7 posts - 1 through 6 (of 6 total)

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

paksyeet SSCommitted Points: 1596 More actions · Answer 1

paksyeet

SSCommitted

Points: 1596

August 5, 2008 at 9:40 pm

#853600

index..

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Index... maybe a partitioned table, to boot.

--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)

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

As appointed by previous posts... have you considered to build an index serving your query's predicate?

Here is what I wanted to say... you do not do "select *" in a production environment... never, ever. Bad, very bad mojo.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Philip Barry SSCrazy Points: 2702 More actions · Answer 4

I would also look at using an index, and not use *, however, with you mentioning deadlocking, maybe you could use:

SELECT *

FROM table_name

WITH(NOLOCK)

WHERE .....

There is a tool with SQL 2005 you could use. With the query open in SSMS, the 4th button to the right of the Execute button is 'Analyze Query in Database Engine Tuning Advisor' and it may give you an idea of the indexes (and possibly statistics) to use and the code to create them.

Regards,

Phil

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 5

i have a scheduled job that kicks in every week.Does reindexing against this table.So I dont think Indexing should be an issue. Well I have worked on with the application developers to optimize the query and now it takes 30 seconds to run compared to 4.5 minutes before. thnx guys...:)

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

I'm glad you fixed it.

Just a side note. The fact that you rebuild your indexes in a weekly basis means nothing, how if you are rebuilding once and again indexes that do not serve your query? 😉

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.