Run-time error '-2147217871 (80040e31)' : Timeout expired

Well,

How much records have your table?
Did you created a primary key?
Your query have any selection criteria?
If so, did you created indexes to the fields used on that criteria?
What program language are you using?
And how are you connecting to MS SQL?

Here SOME of relevant questions that you should let us know to try to help you.

Best regards,

Vitor Montalvao

Total no of rows are above 100 million
Query Analyser also takes long time to show results

Code are as follows
m_str = "select t_leac,t_suno,t_amth,convert(int, t_dbcr)  
        as t_dbcr  from ttfgld106407 nolocks where t_fyer  
        >= " & m_year1 & " and t_fyer <= " & m_year2  & " 
        and t_fprd >= " & m_month1 & " and t_fprd <= " &
        m_month2 &  and (t_leac = '191110' or t_leac  
        = '191111' or t_leac = '191112' or t_leac  
        = '191113' or t_leac = '191120' or t_leac  
        = '191130' or t_leac = '191140' or t_leac
        = '191211' or t_leac = '191310' or t_leac
        = '191311' or t_leac = '191312' or t_leac
        = '191410' or t_leac = '191412' or t_leac  
        = '191210'   ) order by t_suno"
   
rs_gld106.Open m_str, cn_baan, adOpenDynamic,
             LockOptimistic

>>Total no of rows are above 100 million
>>Query Analyser also takes long time to show results

If query analyser takes already long time, this is for sure due to missing indexes for your query. How many (average) records are returned by this query?


where t_fyer  >= " & m_year1 & "
and t_fyer <= " & m_year2  & " 
and t_fprd >= " & m_month1 & "
and t_fprd <= " & m_month2 & 
and (t_leac = '191110'
  ...
  or t_leac = '191210' )
order by t_suno"

Now, the above extract is the important part for tuning:
You need to have 1 index on (t_fyer, t_fprd and t_leac) to get a better result. Eventually, try to have t_leac field to be the first in the index, and also to have the index to be a clustered one (try out the different combinations).
I would suggest that you try this on a copy of the table if it is in production with a little bit less rows (especially if the database is already in production).

CHeers

I'm with angellll about create indexes, but I would create 4 indexes, like this:
1 - index on t_fyer
2 - index on t_fprd
3 - index on t_leac
4 - index on t_suno (clustered)
I would create a clustered index in t_suno field and take off the order by clausule in the select statement.

For my suggestion, not for tunning but for better read, I modified you Select statement (it would give you the same results):

"SELECT t_leac, t_suno, t_amth, convert(int, t_dbcr) AS t_dbcr  
FROM ttfgld106407 NOLOCKS
WHERE t_fyer  BETWEEN " & m_year1 & " AND " & m_year2  & "  AND t_fprd BETWEEN " & m_month1 & " AND " &        m_month2 &  " AND t_leac IN ('191110', '191111', '191112', '191113', '191120', '191130', '191140', '191211', '191310', '191311', '191312', '191410', '191412', '191210')"

Do you see the differences?

But like angellll told, make a copy of your table and do the tests there.

Good luck,

Vitor Montalvao

VMontalvao, I do disagree with you about the number of indexes. Although I couldn't find an article on the web straight away, my knowledge of index use is that MAXIMUM 1 index can be used per (sub)query...
Unless you can prove the contrary with an article...
CHeers

Eheheheheh,

No angellll. I can't prove nothing because I usually don't use only 1 index is this cases!
I'm talking about my experience. I never heard before about that suggestion of limit number of index by queries. If you find it pls sent me, ok?

Best regards,

Vitor Montalvao

I do not want to create any indexes as my using tables Standard ERP for retrieving info. I do not want do play around with tables.

VMontalvao:
I tried it out, and must say I learned something new: a query can take several indexes for 1 (sub)query, one for each SARG. Could be that this has changed at some point from previous versions.

However, my tests showed that this way, more CPU/IO->time is needed, as the intermediate results had to be hash-matched together. On a table with 1M rows i had both runs <1sec, but with 4 indexes it took several K rows for looking up in each index. with 1 index over all the SARG fields, only 1 row needed...

sandip_patankar:
as VMontalvao pointed out, index tuning can be independant of the application (and even should be), although basic indexing should be present. Don't worry to "break" the system, the index can only help for your queries.
You should write down the create index statement in case you have to recreate it later...
http://www.tc.umn.edu/~hause011/article/Tunage.html

CHeers

Hi angellll,

like I said before, I'm a very practice man. Unfortunally and don't have much time to read articles and books, so when I can, I do a lot of tests.

I don't know what SARG is, but I know that with 4 indexes you can use it on other queries!

And pls, don't forget about that article. Know I'm very curious to read it!

Best regards,


Vitor Montalvao

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

- Split points between angelIII  and  VMontalvao

Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
Venabili
EE Cleanup Volunteer