Query for Huge Tables

Question

Query for Huge Tables

Bilal-748563

SSC Veteran

Points: 200
More actions
June 22, 2008 at 2:41 am

#154467

Hi ALL,
I am not so good in SQL Server, I am trying to run a query which will take out some records for specific time
select * from nodes where start_time >= '2008-05-16 16:40' and start_time <= '2008-05-16 16:41'
this table is so much huge, if i run this query the application will stop working and when i stop the query application will be fine
now i want some query which will not add load on system and give me my required result
Regards,
Bilal

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What's the table's schema? What indexes are defined on it? How many rows are in the table? How many rows do you expect your query to return?

Do you absolutely need all the columns returned?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bilal-748563 SSC Veteran Points: 200 More actions · Answer 2

I expect about 1000 rows to return , and their is one type of index defined which is traffic1

each table have millions of records

any thing else ?

MannySingh SSChampion Points: 12465 More actions · Answer 3

Try covering indexes

Right Click on INDEX -> Property--> Columns and then specify the columns to be used in the index, try to choose numeric type columns for better results... This should improve Query Performance.

Also try looking into your Clustered and non-clustered indexes.. U specified Traffic1 Index.. but is it s Clustered or NON-Clustered index..?

Maninder
www.dbanation.com

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

Bilal (6/22/2008)
I expect about 1000 rows to return , and their is one type of index defined which is traffic1
each table have millions of records
any thing else ?

Yes there is...

Like Gail said, we need to know the schema of the table... best thing to do would be to generate the CREATE TABLE statement and posted it.

You identified that you had an index, but we're not clarvoiant... what is the definition of the index? It would be best if you generated the CREATE INDEX statement and posted it.

Is the short query you posted the actual query or are you doing something else in real life? Post the actual query you are testing with... if it's hundreds of lines long, that would probably be the problem 😉

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Bilal (6/22/2008)
I expect about 1000 rows to return , and their is one type of index defined which is traffic1
each table have millions of records
any thing else ?

Yup. I'd like to see the index and table definitions. Knowing the name of the index is useless, need to know what columns its defined on.

Do you absolutely have to return all the columns (SELECT *)? If not, what are the columns you need?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

1) a covering index is an inappropriate suggestion for a query that returns all columns

2) reducing the number of columns could well be appropriate and that could allow for covering index

3) as others have already said - we can't help much without the table definition (to include current indexes). My guess is that you do not have an index on this single column in the where clause and such an index would greatly speed this query due to the narrow range of time and the low number of rows you expect back

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service