Alternative for 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

General SQL Server Forums

New to SQL Server Programming

Alternative for Query

Author

Topic

asif372
Posting Yak Master

100 Posts

Posted - 2011-10-05 : 08:24:04

I am using Sql 2000 When using This Query the Speed of System gets Very Slow I want Its Alternative

SELECT TOP 100 PERCENT EmployeeCode, [Date],USERID,
(SELECT TOP 100 PERCENT t.[TimeOut]
FROM vwInBetween t
WHERE t.EmployeeCode = t1.EmployeeCode AND t.Date = t1.Date AND t.rn = t1.rn - 1) AS Timein, TimeIn AS TimeOut,
(SELECT TOP 100 PERCENT CONVERT(varchar(10), (t1.TimeIn - t.TimeOut), 108)
FROM vwInBetween t
WHERE t.EmployeeCode = t1.EmployeeCode AND t.Date = t1.Date AND t.rn = t1.rn - 1) AS Spend, 'Personal' AS Remarks
FROM dbo.vwInBetween t1
WHERE (rn <> 0) AND (TimeIn IS NOT NULL)

ORDER BY EmployeeCode, [Date], TimeIn DESC

Thanks In Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 08:31:08

[code]
SELECT TOP 100 PERCENT t1.EmployeeCode, t1.[Date],t1.USERID,
t.[TimeOut] AS Timein, t1.TimeIn AS TimeOut,
CONVERT(varchar(10), (t1.TimeIn - t.TimeOut), 108) AS Spend,
'Personal' AS Remarks
FROM dbo.vwInBetween t1
JOIN dbo.vwInBetween t
ON t.EmployeeCode = t1.EmployeeCode
ON t.Date = t1.Date
AND t.rn = t1.rn - 1
WHERE (t1.rn <> 0) AND (t1.TimeIn IS NOT NULL)
ORDER BY t1.EmployeeCode, t1.[Date], t1.TimeIn DESC
[/code]

also i dont understand why you're using TOP 100 PERCENT. if not inside view or subquery no need of TOP 100 PERCENT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

asif372
Posting Yak Master

100 Posts

Posted - 2011-10-05 : 08:48:18

But Still Getting Same issue Speed is Not Improving Kindly help
THANKS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 08:49:48

analyse the execution plan and see what are costly steps.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-05 : 08:55:13

Hi,
Try to use temp table to build data for inner query and then do inner join.

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Reporter
Starting Member

48 Posts

Posted - 2011-10-05 : 09:37:54

try this )

SELECT t.EmployeeCode, t.[Date],t.USERID,
t1.[TimeOut] AS Timein, t1.TimeIn AS TimeOut,
CONVERT(varchar(10),(t1.TimeIn-t.TimeOut),108) AS Spend,
'Personal' AS Remarks
FROM vwInBetween t full join vwInBetween t1 on t.EmployeeCode = t1.EmployeeCode AND t.Date = t1.Date AND t.rn = t1.rn - 1
WHERE (t.rn <> 0) AND (t.TimeIn IS NOT NULL)
ORDER BY t.EmployeeCode, t.[Date], t1.TimeIn DESC

lappin
Posting Yak Master

182 Posts

Posted - 2011-10-05 : 09:51:43

What fields do you have indexes on?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources