ElvasLion
asked on
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
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
Éric Moreau
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.
what is your sql statement?
ASKER
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.
What about the Execution plan?
the sql matters very much. it it isn't good it will run very slow.
Use SQLSetConnectAttr to set SQL_ATTR_CONNECTION_TIMEOU
See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlsetconnectattr.asp
See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlsetconnectattr.asp
ASKER
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
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
Do you have an index on Field3?
Can't you have a Where clause to minimize the number of lines retreived?
Can't you have a Where clause to minimize the number of lines retreived?
ASKER
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
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
I've solved the problem, the problem was in the connection i've used:
set conn= new adodb.connection
conn.connectionstring="Dsn
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
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.
_ _ _
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.
ASKER
i'm using ado but the connection is made trough odbc.
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.
ASKER
Ok jrspano i'm going to follow your advise thank you very much.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.