Data Access Timeout In SQL Server2005
Hi, I am using SQL Sever 2005 & C#.
I met a big problem when accessing the database.
My data table has over 100 Million records, and I have created indexes
for several columns which are frequently used. But I constantly
get a SQLTimeOutException, even if I set the connection time out as high
as 200 seconds. And this time out exception is thrown about 30 seconds
after the query starts. So it seems the timeout parameter has no effect.
I wonder How could I speed up the accessing speed to avoid the time
out problem.
BTW. My C# codes work perfectly for little tables.
wxuf
[639 byte] By [
wxuf] at [2007-11-20 7:56:19]
# 1 Re: Data Access Timeout In SQL Server2005
you need to set DbCommand.CommandTimeout, not the connection timeout.
You also should reconsider the queries you apply. Also check the Database Engine Tuning Advisor ( http://www.microsoft.com/technet/prodtechnol/sql/2005/sql2005dta.mspx)
hspc at 2007-11-9 13:45:11 >
# 2 Re: Data Access Timeout In SQL Server2005
Thanks, It really helps. I made this mistake.
wxuf at 2007-11-9 13:46:12 >
# 3 Re: Data Access Timeout In SQL Server2005
Hopefully you are ALWAYS using stored proceedures with no dynamic SQL. This is the most efficient as the SQLEngine can optimize many operations.
You can also look at the estimated and actual execution plans (something that should always be done)...
If you post some SQL, and the execution plan/actuals then I can possibly provide some tuning details.
Also are you following the recommended hardware layout in that your .mdf files should be on a dedicated drive with a dedicated channel, and the log files on a different drive and controller.... This can have a HUGE impact...
# 4 Re: Data Access Timeout In SQL Server2005
Well, My table contains four attributes:
"UserId, MovieId, Score, time" each with int, int, int and DateTime as its type.
My query is simple
SELECT Score FROM VOTESCORE WHERE USERID = 1 AND TIME >= 'FROMTIME' AND TIME < 'ENDTIME'.
Here Fromtime and Endtime are time region to search between.
And I found it's very inefficient to compare the strings in sql queries.
So now I convert the "time" attribute into two new columns
"Year" and "Month" to speed up the query.
Thanks again.
wxuf at 2007-11-9 13:48:16 >
# 5 Re: Data Access Timeout In SQL Server2005
And I found it's very inefficient to compare the strings in sql queries.
So now I convert the "time" attribute into two new columns
"Year" and "Month" to speed up the query.
Isn't the TIME column a smalldatetime or datetime ? you can build efficient indexes on it. SQL Server does not save dates as strings.
hspc at 2007-11-9 13:49:17 >
# 6 Re: Data Access Timeout In SQL Server2005
A little bit of data duplication may well be a simple solution to your large number of records
The actual record you describe will probably be smaller than the Indexes SQL server creates to access the table !
I am not sure if your USERID is a simple incrementing numeric, BUT IF IT WAS, then there is nothing to stop you writing out an additional table for each set of, say, 100,000 records - add a RECID to the end of the record in the smaller sized file - this will give you a DIRECT HIT on the records in the large (enormous) file
In this way, your time will be the time taken to access 100,000 records (NOT 100 million records)
Your program will need to check the value of the USERID prior to opening the table it is contained in.
In the Large file, also add a pointer back to the smaller file eg, File nn RECID nn
I do believe this will give you a snappy result, albeit double the database size, but, with the size of you records, who would really care ?
I believe you are looking for performance - no amount of changing timeouts will help in this department - check out the stored procedure angle most definitely.
