• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 721
  • Last Modified:

connection timeout in SQL Server w/ ADODB Conn

i give up.  I'm running a series of update queries with an ADODB connection, w/ SQL Server 2000.  I keep hitting a connection timeout, even though I thought I set up the connection to not allow that.

My connection string is:
Provider=MSDASQL;Driver={SQL Server};Server=(LOCAL);ConnectionTimeout=0;CommandTimeout=0;Database=ESense;

I'm running an update query, which does take a time on the larger tables (10million+ recs). Maybe I should split my updates field by field, but I don't mind walking away and letting it run, and I would think it's faster overall to update the fields all at once in each table.  

A sample of the SQL is:
update t1 set t1.AvgUnitSz = t2.fld1, t1.TotHHs = t2.fld2, t1.AvgHHSz = t2.fld3, t1.POC = t2.fld4
    from tblBaseBG t1 inner join (select bg_id, ROUND(avg(CAST(UnitSize AS DECIMAL)),2) fld1,
    count(Ind_ID1) fld2, ROUND(avg(CAST(Hhcount AS DECIMAL)),2) fld3,
    sum(case when POC='Y' then 1 else 0 END) fld4 from tblLL_HH_AL group by bg_id) t2
    on t1.NAME = t2.bg_id;
0
dougfosterNYC
Asked:
dougfosterNYC
  • 7
  • 6
1 Solution
 
Ted BouskillSenior Software DeveloperCommented:
Are you executing that query as a stored procedure?  If not, that alone will improve performance immensely because the connection will be less chatty and the query can be stored in an compiled execution plan.

Also, using DECIMAL is considerably slower than DECIMAL(10,3) or similiar.  SQL can optimize the query better if you specifically cast the DECIMAL to an appropriate precision and scale.

In fact, you would be better off using CAST(UnitSize AS FLOAT)
0
 
Ted BouskillSenior Software DeveloperCommented:
Also, without knowing your network configuration, some routers that use NAT translation will reset sessions after 1 hour.  So, the network itself could be breaking the connection.
0
 
dougfosterNYCAuthor Commented:
I'm running SQL Server on the same machine as the query, so the network isn't the issue.

The complication is that I'm running this query 51 times.  I have 51 different t2 files (these are U.S. states and each record is a household), and so I'm linking the State file with a census block group file one at a time and running the update.  Would it make sense to set up a SP and can I have the attached table be a @variable?

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
dougfosterNYCAuthor Commented:
BTW.  I simplified the query so that it is running only one count update and it is still blowing up on California, the biggest state (18 million records).  

But didn't I set up the connection string so that it wouldn't time out with CommandTimeout=0?

Last SQL that gave a connection timeout:
update t1 set t1.TotHHs = t2.fld1 from tblBaseBG t1 inner join (select bg_id, count(Ind_ID1) fld1 from tblLL_HH_AL group by bg_id) t2 on t1.NAME = t2.bg_id;
0
 
Ted BouskillSenior Software DeveloperCommented:
Yes I think you should use a stored procedure and do the following in the body of the stored procedure:

SET NOCOUNT ON; -- BIG time saving

-- NOTE Declaring the object owner dbo is another excellent time saver
SELECT bg_id, COUNT(Ind_ID1) IDCount INTO #IDCounts FROM dbo.tblLL_HH_AL GROUP BY bg_id;

UPDATE t1 SET t1.TotHHs = (SELECT IDCount FROM #IDCounts WHERE bg_id = t1.NAME) FROM dbo.tblBaseBG t1;

DROP TABLE #IDCounts

NOTE: Some of these tips are from this URL
http://www.sql-server-performance.com/articles/per/query_tuning_p2.aspx
0
 
dougfosterNYCAuthor Commented:
Thanks.  I need an example to understand.  The update sql I put on the original post is dynamically created, so it can be very different, and I'm running it 51 times, joining a different table each time.

So I have an ADODB connection and executing the SQL.  Will it help to have a USP and have the SQL code created as a variable and then running it?  Can I get an example how I would set up the base USP?

Thanks.
0
 
dougfosterNYCAuthor Commented:
I tried setting the nocount on and running update query in a SP, but it is still timing out.

Your code above I think is putting the data into a temp table.  I haven't used temp tables.  Is this more efficient?

I should take a step back here and ask an overall question.  I have some big tables, like 18 million records.  So the nested update query seems not to be efficient.  Is there a better way?  I may just break it up for each block group.  So in the query below, instead of having the subselect grouped, I would specify the groupID and loop through each one.  This may take a while, but it may not give me a connection timeout and I can let it run overnight, which is fine....

update t1 set t1.TotHHs = t2.fld1 from tblBaseBG t1 inner join (select bg_id, count(Ind_ID1) fld1 from tblLL_HH_AL group by bg_id) t2 on t1.NAME = t2.bg_id;
0
 
Ted BouskillSenior Software DeveloperCommented:
In this case I think the nested query will be faster because it can be cached and yes it is a temporary table.  I've used a nested query to make huge improvements to queries in the past.

Can you give me some examples of the different table queries and I'll rough in a stored procedure for you.
0
 
dougfosterNYCAuthor Commented:
Thanks.

A simple one is like above:
update t1 set t1.TotHHs = t2.fld1 from tblBaseBG t1 inner join (select bg_id, count(Ind_ID1) fld1 from tblLL_HH_AL group by bg_id) t2 on t1.NAME = t2.bg_id;

It could have a lot more sums, counts and averages:
update t1 set t1.AvgUnitSz = t2.fld1, t1.TotHHs = t2.fld2, t1.AvgHHSz = t2.fld3, t1.POC = t2.fld4 from tblBaseBG t1 inner join (select bg_id, ROUND(avg(CAST(UnitSize AS DECIMAL)),2) fld1, count(Ind_ID1) fld2, ROUND(avg(CAST(Hhcount AS DECIMAL)),2) fld3, sum(case when POC='Y' then 1 else 0 END) fld4 from tblLL_HH_CA group by bg_id) t2 on t1.NAME = t2.bg_id;

It is looping through 51 tables which I would be able to input.  I also have the "set @fldSQL " fields (e.g. t1.AvgUnitSz = t2.fld1, ...etc.) as a string.  I also have the nested select fields (e.g. count(Ind_ID1) fld2, ...etc.) as a string to input

something like ?
update t1 set @sql1 from @table1 t1 inner join (select @sql2 from @table2 group by @keyfield2) t2 on t1.@keyfield1 = t2.@keyfield2 ;

0
 
Ted BouskillSenior Software DeveloperCommented:
Have you used SQL query analyzer to compare the execution time of the query that I rewrote using the temporary table and the nested query compared to your query?

I'm convinced that my query will run substantially faster.

So you are building a dynamic string then using EXEC or sp_execute to run the query?  That could be another performance bottleneck.

I'd time one using the dynamic query string and one without in SQL query analyzer.

However, the key point is that I am convinced the JOIN is added tremendous overhead.  Use a temporary table and the nest query should help because I think there will be less locking.
0
 
dougfosterNYCAuthor Commented:
It did run pretty well, 3 minutes for the state of Alabama.  Can you give me a basic SP where I can input the variables with the info in my last note?  That would be all I need.

Thanks.....
0
 
Ted BouskillSenior Software DeveloperCommented:
Sorry, I misplaced this email!

Anyway, there are two ways to go with this.  You can write one stored procedure that accepts table and column names as parameters, the use concatonation to build a query string then use the EXEC function.  For example:

CREATE PROCEDURE dbo.SelectFromTable(@inTblName SYSNAME) AS
BEGIN
  DECLARE @sqlCmd VARCHAR(1024)
  SET @sqlCmd = 'SELECT * FROM '+@inTblName
  EXEC(@sqlCmd)
END
0
 
dougfosterNYCAuthor Commented:
That was a long pause, but that's what I was asking for.  Thanks......
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now