Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-09-09 : 15:43:03

I have select query which is taking more time to retrieve the data with the where condition.
Can anyone suggest best alternative query which improves the performance also.

select * from table
where Id between 1 AND 2 and and date1 between '01/01/1989' and getdate()

Thanks for your help in advance !!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-09 : 17:02:58

Are either [ID] or [date1] columns indexed? Does the table have any primary key?

Be One with the Optimizer
TG

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-09-10 : 10:05:34

Id is primary key and date1 is indexed..

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-10 : 10:51:43

So you are getting 2 rows returned? How long is it taking? Are you running this statement in a Query Window or via an application?

Be One with the Optimizer
TG

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-09-10 : 13:07:59

I am running in query window its taking around 45 seconds to get the results

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-10 : 14:12:02

can you post the plan output:


set showplan_text on
go
select * from table
where Id between 1 AND 2 and and date1 between '01/01/1989' and getdate()
go
set showplan_text off

Also, while the statement is running - when showplan_text is OFF, in another window run "sp_who2 active" to see if there is anything blocking (blkby column)

Finally, I don't think it should matter but try:

select <explicit column list>
from table
where id >=1
and id <= 2
and date1 >= '01/01/1989'
and date1 <= getdate()

Does this table have large blob column(s) ie: text, ntext, varchar(max), image, etc. ?

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-10 : 15:18:52

45 seconds.... Wow.
How many records are there in the table?
Have you simplified your situation? Are you only selecting from one table only,
or do you have another "real" query with JOINs?

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources