Select Statement - 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

General SQL Server Forums

New to SQL Server Programming

Select Statement

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-11-23 : 05:34:21

Hi,

I have the following select statement, on some occassions it executes fine and outputs 2164 records.

However, in other occassions it times out. What could be the problem. All databases are on the same server and hardly no one uses the server. Please help!

SELECT DISTINCT 
                      TOP (100) PERCENT t1.o_pat_id AS vision_patid, t1.o_pat_birth_yr AS BirthYear, t1.o_pat_curr_gender AS Sex, t2.prac_no, t2.prac_eid, t2.main_doctor, 
                      t2.practice_name, t2.address1, t2.address2, t2.address3, t2.town, t2.county, t2.post_code, dbo.tblProjectPatients.LoadRef, 
                      dbo.tblProjectPatients.ProjectID, dbo.tblProjectPatients.EventDt AS Event_date, dbo.tblProjectPatients.FinalEventDt, dbo.tblProjectPatients.pat_eid, 
                      dbo.tblProjectPatients.PatCategory
FROM         GPRDTech.gprdsql.TblPracDetails AS t2 INNER JOIN
                      ODSData5.dbo.o_pat AS t1 ON t2.prac_eid = t1.o_prac_uid INNER JOIN
                      dbo.tblProjectPatients ON t1.o_pat_eid = dbo.tblProjectPatients.pat_eid
ORDER BY dbo.tblProjectPatients.LoadRef

Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-23 : 05:43:26

How long does it take when it works?
Could be blocking, data not in cache, other things taking resources, ...

ry looking in sysprocesses and check for blocking and disk io.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-11-23 : 05:59:21

When it works its instantly, when you execute the results are displayed..

Why ONLY this select query, I have alot other queries which extract more complex and more records and it works instantly...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-23 : 06:02:59

with the top 100 percent it makes me think this is being used in a view so I would expect it's something to do with how it's being called.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources