SQl Query taking time to execute

1

Hi

I have written the below query in visual basic & then through loop i fill grid. System is connected through Wi-Fi & it is at a distance of 4-5 km from server. It sometimes takes long time to display records in grid
rstrans.Open "select * from test where status='P' order by todate desc", cnn2, adOpenDynamic, adLockOptimistic, adCmdText

Thanks

2

Run the query in SSMS or in Visual Studio T-SQL Editor and observe the results and execution time. The point is you need to isolate the lag to the query or the application.

3

Don't use *
use only the required columns
if required create index on status and todate

4

@viggneshwar good general advice

5

How many records in the table/what indexes do you have in place? Also I question that your performance issue may not be in the query execution, but in the looping you reference. As gbritton mentioned, run it in sql server alone without your loop and see what your response time is. Might not be SQL at all but the looping.

6

Is " status='P' " an unusual or temporary condition? If so, create a filtered index on that condition.