Timeout Expired

I'm using an ODBC connection to SQL Server.
I make querys that return sums from the database normaly those querys don't have more than 5.000 rows.
My App Works fine up to 5.000.000 millions records in the database.
My problem is that when the records increase i get the error timeout expired.
This is what i have done to try solve the problem:
1st:I configured the pooling database to don't pool.
2nd:In the SQL server i've tryied and change the property query timeout in the connection tab from 600 to 0 but when i run the server taht property is still in the same value, i have an option that says running values but when i click it everything becomes enabled false.

PS1:I'm using SQL Server 2000

PS2:50 Points bonus for quick answer

PS3:As fallow up to this question i woul'd like to know what configurations shoul'd i change in the sql server, to optimize the work, i only do select querys, i never change values. This is obviously another question so is extra 75 points to whoever answer it.


THANKS
LVL 1
ElvasLionAsked:
Who is Participating?
 
MoondancerConnect With a Mentor Commented:
I have refunded your points and will now move this to our PAQ.

Moondancer
Community Support Moderator @ Experts Exchange
0
 
Éric MoreauSenior .Net ConsultantCommented:
Use the Query Analyzer. Copy your query there. From the Query menu, select "Show execution plan". Now run your query. When the execution is done, have a look at the plan generated, search for the highest percentage and see if you can add indexes to help the SQL parser.
0
 
jrspanoCommented:
what is your sql statement?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
ElvasLionAuthor Commented:
doesn't matter the sql statement the problem is the timeout, i reajusted the query timeout to 0 and it works fine in sql now but from vb i still get the timeout message.
0
 
Éric MoreauSenior .Net ConsultantCommented:
What about the Execution plan?
0
 
jrspanoCommented:
the sql matters very much.  it it isn't good it will run very slow.
0
 
JeffGriggCommented:
Use SQLSetConnectAttr to set SQL_ATTR_CONNECTION_TIMEOUT.  If zero doesn't work, try a very large value.

See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlsetconnectattr.asp
0
 
ElvasLionAuthor Commented:
My sql is an normal sql statement that selects a few sums from a table like this:

SELECT SUM(Field1), AS Xpto1, SUM(Field2) AS Xpto2, Field3 FROM MyTable GROUP BY Field3

As you can see this is not the problem, the problem is the table that has up to 12.000.000 Records.
I'm going to explain it again:

First i set the query timeout in the sql server to 0 and then i've chosen don't pool in the connection pooling from the odbc. This way i managed to do querys from within the sql server and from linked tables in access. My problem is in my VB app because it works fine up to 5.000.000 recors, and if i pass that limit it just gives me ODBC Error - Timeout Expired.


Please JeffGrigg could you give me an example of hoe i shoul'd use that API.

THANKS
0
 
Éric MoreauSenior .Net ConsultantCommented:
Do you have an index on Field3?

Can't you have a Where clause to minimize the number of lines retreived?
0
 
ElvasLionAuthor Commented:
I think i've already posted this yesterday don't know why it isn't here.

I've solved the problem, the problem was in the connection i've used:
 
set conn= new adodb.connection
conn.connectionstring="Dsn=MyDsn"
conn.commandtimeout=0
conn.open

As you can see the sql and the indexes are irrelevant what i wanted was being able to do a select that can take all the time needed.

Thank you all but i'm going to delete the question in 2 days if nobody opposes.

Thank you all again
0
 
JeffGriggCommented:
Sure; go ahead and delete this; none of these comments really answers your question.
_ _ _

Hmmm...  Interesting:
Your question about my SQLSetConnectAttr posting is also missing.  I *KNOW* it was here before.
_ _ _

You said you were using ODBC.  Now you're using ADO.  No wonder my "SQLSetConnectAttr" comment was puzzling.
0
 
ElvasLionAuthor Commented:
i'm using ado but the connection is made trough odbc.
0
 
jrspanoCommented:
don't delete it post a question in community support and ask them to make it a PAQ and refund your points.  Your solution can benefit other users.
0
 
ElvasLionAuthor Commented:
Ok jrspano i'm going to follow your advise thank you very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.