Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
[Microsoft][ODBC SQL Server Driver]Timeout expired 2
- Thread starter UncleCake
- Start date
-
1
- #2
You MIGHT need a longer connection - more than likely though (from my experience) this happens more when the query is not performming really well (ex. pulling too many rows, using a table without an index...). Try executing the query in Query Analyzer and look at the exectution plan & statistics (under the Query menu on the tool bar) and see which part of your query is taking the longest.
a VERY slick tool which will tell you ALOT.
let me know if I need to show you further.
a VERY slick tool which will tell you ALOT.
let me know if I need to show you further.
-
1
- #3
If you post the code, maybe we can help you optimize it. Just making the timeout value longer is usually a bad way to fix this.
Some things to consider when optimizing: indexes on the base tables, getting rid of any cursors, using stored procedures instead of calling the information directly from the user interface, limiting the information asked for to only the necessary fields (don't use select * from Table1 when all you need is two fields, for instance), making sure that you don't have an infinite loop.
Additionally, it may not be the actual code which is slowing you down, but triggers on the table (applies to insert, update and delete statements only).
Other things which can speed up performance include moving the transaction logs and indexes to separate physical drives with separate drive controllers. Also, depending on the number of records you have, you may want to partition your tables across several drives or have an active and an archive table to allow for few records to be processed for most queries. Or if your structure is very normalized, you may need to denormalize cut down on the number of required joins to extract data.
If your slow process is adding many records at a time, you may want to consider batch processing rather than doing all in one step.
Some things to consider when optimizing: indexes on the base tables, getting rid of any cursors, using stored procedures instead of calling the information directly from the user interface, limiting the information asked for to only the necessary fields (don't use select * from Table1 when all you need is two fields, for instance), making sure that you don't have an infinite loop.
Additionally, it may not be the actual code which is slowing you down, but triggers on the table (applies to insert, update and delete statements only).
Other things which can speed up performance include moving the transaction logs and indexes to separate physical drives with separate drive controllers. Also, depending on the number of records you have, you may want to partition your tables across several drives or have an active and an archive table to allow for few records to be processed for most queries. Or if your structure is very normalized, you may need to denormalize cut down on the number of required joins to extract data.
If your slow process is adding many records at a time, you may want to consider batch processing rather than doing all in one step.
I am having the same problem. The table (TBL) I am pulling records from has about 3.9 million records and about 60 fields for each record. I have 4 non-clustered indexes on fields A, B, C, and D. There are no other indexes on the table.
A being a unique field to the record, but not a unique index
B and C are fields with varying amounts of duplicate values amoung the records (anywhere from no dups, to 50, to 10,000+)
D is a field that groups the records into bundles of about 100,000.
If I run a query that pulls 1 to a couple thousand records, the results show in a matter of seconds. If I run the query to pull out all records with a specific D value (100k) it hangs and doesn't produce any results. I have waited over a half hour to get the results from that kind of query.
If I run a query for a particular A value that is very common (common like the name 'Smith' in searching for last names.) it also hangs.
Any help would be greatly appreciated.
Thanks
Jay
A being a unique field to the record, but not a unique index
B and C are fields with varying amounts of duplicate values amoung the records (anywhere from no dups, to 50, to 10,000+)
D is a field that groups the records into bundles of about 100,000.
If I run a query that pulls 1 to a couple thousand records, the results show in a matter of seconds. If I run the query to pull out all records with a specific D value (100k) it hangs and doesn't produce any results. I have waited over a half hour to get the results from that kind of query.
If I run a query for a particular A value that is very common (common like the name 'Smith' in searching for last names.) it also hangs.
Any help would be greatly appreciated.
Thanks
Jay
- Status
Similar threads
- Locked
- Question
- Locked
- Question
