Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I having a database table with around 200K records in my SQL Server. When I want to select the records from table to do a simple report but it takes me around 4 to 5 minutes to execute finish the query. And I receive an error message :

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.


However, the same query is execute very fast in my own computer but is slow in another computer.

The SQL query is as below:

SQL
Select Region as Regions, count(vehicleplatenumber) As [Total of Region]
From VehicleTransactionLog
Where IsDeleted = 0
AND (DateTimeIn >= '2013-02-01' AND DateTimeIn <= '2013-09-14')
Group by Region


So, why the query is execute so slow in other computer and how to solve this problem.

Thanks in advanced.
Posted
Comments
Homero Rivera 7-Nov-13 22:40pm    
Where is the database? In your own computer or in a server? What are the network settings of the other computers where you run the query? Are they really linked? Have you check the ip address of the server and then try to ping it from the other computers to see if you get a response?
sandra_tay 7-Nov-13 22:47pm    
The database is in the server. The network setting are not linked. I had try to ping and all is working correctly.
I try to add in index into the table but seems like not really help because it will affect my insert query speed. Do you have any idea about how to solve this?
Homero Rivera 7-Nov-13 22:56pm    
Differential diagnosis; we'll try different tests, and depending on the results we'll find what's going on.
On one of ther "outter" computers, send a much simpler query to the server; for this you need to make sure that you are connected to the target server and target database, and then just send the next query "select 1".
This should only return "1", but coming out of the server. If this dramatically simple query takes a lot of time, as the other one, then you got a networking issue.
Thava Rajan 7-Nov-13 23:46pm    
what is your connection string format, is that a express version or any thing else did you mention the port in the connection string please specify it

If there are no Network issues then try creating an Index for DateTimeIn field and see if the performance improves
 
Share this answer
 
Hi,
Their are several factor which affect a query performance
like
1) Processors,
2) RAM
3) execution plan

I would like to say here you can try to run query in SQL server management studio and check the query plan if still taking time then try to create index on DateTimeIn column.

Check SQL Server DMV commands for detail if still problem exists.
http://indiandotnet.wordpress.com/2010/06/17/how-to-determine-which-index-will-be-helpful-in-sql-server-database-table/[^]
 
Share this answer
 
Comments
sandra_tay 11-Nov-13 3:02am    
How to solve it if it is a processors or RAM problem?
Rajat-Indiandotnet 11-Nov-13 3:13am    
Either you can use MAXDOP option .

It would be great if you please run your query

And get query from below post I will give better response after seeing the result of below link query.
http://indiandotnet.wordpress.com/2011/09/05/how-to-find-what-is-currently-running-on-your-sql-server/

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900