SQL Server Performance Forum – Threads Archive
SQL Timeout in VB6 ASP ADO
I’m having a timout issue with SQL 2000 in a VB app. I have a VLDB OLTP. The table that will grow the most, continuously is t_trend.currently storing 3 days of info at 17 million rows, need to store 3 months data = 30 * 17 million = 510 million rows, but the rows are small. The inserts are slowing down already and I’m getting database timeouts in ASP ADO. I have 2 tables. t_PollData and t_Trend. I collect the data into t_PollData and then copy it to t_Trend, and delete from t_PollData.
This happens about 1200 rows at a time, 24 x 7. Here is the ASP statement that does the insert
strSQL = "Insert into t_Trend Select ModScanID, PDUPanelID, CTNo, PollDateTime, CTmA, CTWatts From t_PollData"
DataCmd.Execute strSQL Here is the table schema, there is a single clustered index on ID. CREATE TABLE [dbo].[t_Trend] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ModScanID] [int] NULL ,
[PDUPanelID] [int] NULL ,
[CTNo] [int] NULL ,
[PollDateTime] [datetime] NULL ,
[CTmA] [int] NULL ,
[CTWatts] [int] NULL
) ON [PRIMARY] We are getting timeouts on the insert. What can I do to speed things up / extend the timeout ? PBasile
Hi,
i would suggest to read Article Section of the sitehttp://www.sql-server-performance.com/visual_basic_performance.asp
and vyaskn.tripod.com/watch_your_timeouts.htm
and googlehttp://www.google.co.in/search?hs=6…S:official&q=vb+sql+timeout&btnG=Search&meta= HTH
Regards
Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami
————————
http://hemantgirisgoswami.blogspot.com
Perhaps file growth factor is set as a percentage, and the file size is already so big that it takes a lot of time to add x% of file size. How many rows are you inserting at a time?
inserting about 1200 rows at a time. PBasile
Yes, but what about the other suggestion.
SQL data file size is 300 MB and growth is set to 10% PBasile
Why don’t you increase the data file size to 1 or 2 gb at a time and then continue the process. (as what Adriaan referred above). Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
set the connection timeout to 0 (for infinite value) or set the timeout to acceptable value (you decide what is acceptable value to you). You can specify the timeout value on the openrecordset function. If VB programming won’t work, try the SQL side. Depending on the requirements, you may try the following:
1) Use a stored procedure, invoke it async and return to VB. The appearance of fast may not necessarily mean you have to perform all the back load before letting the user continue using the interface. Using this technique, you may let the user continue the work while working in the background. Of course, if you need the result to continue, that is another story. You decide which is which.
2) set a working thread on the SQL Server side, add a message to que, and let the working thread pickup the message and process the information. Again, assuming you don’t need the result to continue
3) use partitioned view, try inserting records on new tables (you may determine if it is weekly/montly/quarterly/yearly). Numerous thread in this site deal with the partitioned view. The code below can help in creating quick table. Use alter statement to enforce the check constraint. SELECT TOP 0 * INTO t_Trend_200607
FROM dbo.t_Trend
The suggestion above may or may not help you. It depends on what your business requirements are.
But timeouts occur when process can’t get the resources needed within a specified time.
Check if you need to:
(1) set a longer time,
(2) expedite the running process in releasing resources, or
(3) let another process wait for the resource while the current process move on or all of the above. Hope this helps.
May the Almighty God bless us all!
www.empoweredinformation.com
]]>