OpenEuphoria: Forum: odbc resources

1. odbc resources

Posted by Craig Welch <craig at singmail.com> Nov 14, 2005
636 views

I'm experimenting with odbc, with SQL Server behind it. I'm running a 
program to read in a 30,000 record file line by line, and insert it into 
  a table. (I know there are better ways of doing that, but it's a 
learning exercise, both for odbc and SQL).

The data are the test records that come with the Tsunami database.

As the record count increases, the PC slows considerably, paging goes 
through the roof, and eventually it's processing 4 transactions per 
second. It starts at about 500 per second.

I put in an abend statement after 10,000 records. Here is a section of 
the dump that makes me wonder:

handle_odbc = {9501740,9501900,-1}
     handle_children = {
                         {2},
                         {3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,

etc ... to the limit that ex.err will display.

Here's the pertinent section of the program (simplified, error routines 
stripped out):

<EUCODE>
counter = 1
void = initODBC()
hconn = openConnectionODBC("blues","sa","" )
void = execDirectODBC (hconn, "USE blues")
		
while 1 do

	fh = open("C:\\EUPHORIA\\odbc\\test_data.txt", "r")
	lNextLine = gets(fh)
	counter = counter +1
	if counter > 10000 then ? 1/0 end if
	
	while sequence(lNextLine) do

		while lNextLine[length(lNextLine)] = '\n' or 				 
lNextLine[length(lNextLine)] = '\r' do
			lNextLine = lNextLine[1..length(lNextLine)-1]
		end while

		sql = "INSERT tsunami VALUES"
		& "("
		& "'" & lNextLine[1..6] & "',"
		& "'" & lNextLine[7..30] & "',"
		& "'" & lNextLine[31..49] & "',"
		& "'" & lNextLine[50..79] & "',"
		& "'" & lNextLine[80..95] & "',"
		& "'" & lNextLine[96..200] & "'"
		&")"

		void = execDirectODBC (hconn, sql)

		lNextLine = gets(fh)
		end while
	
		close(fh)
		?1/0
end while
cleanUpODBC()
</EUCODE>

Any thoughts?

-- 
Craig

new topic » topic index » view message » categorize

2. Re: odbc resources

Posted by Matt Lewis <matthewwalkerlewis at gmail.com> Nov 14, 2005
591 views

Craig Welch wrote:
> 
> I'm experimenting with odbc, with SQL Server behind it. I'm running a 
> program to read in a 30,000 record file line by line, and insert it into 
>   a table. (I know there are better ways of doing that, but it's a 
> learning exercise, both for odbc and SQL).
> 
> The data are the test records that come with the Tsunami database.
> 
> As the record count increases, the PC slows considerably, paging goes 
> through the roof, and eventually it's processing 4 transactions per 
> second. It starts at about 500 per second.
> 
> I put in an abend statement after 10,000 records. Here is a section of 
> the dump that makes me wonder:
> 
> handle_odbc = {9501740,9501900,-1}
>      handle_children = {
>                          {2},
>                          {3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
> 3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
> 3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
> 3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
> 3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
> 3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
> 3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
> 3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
> 3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
> 3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
> 3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
> 3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
> 3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
> 
> etc ... to the limit that ex.err will display.

> 
> Any thoughts?

OK, I think I know what's happening.  I'm not reusing the bookkeeping 
sequence handle_children properly.

Here's what the end of allocHandleODBC() should look like:

if parent then
    	-- try to reuse the children handles:
    	ix = find( -1, handle_children[parent] )
    	if not ix then
    		handle_children[parent] &= id
    	else
    		handle_children[parent][ix] = id
    	end if
        
        handle_parent[id] = parent
    end if
    
    return id
end function

...and the for-loop in the middle of freeHandleODBC():

for i = 1 to length( handle_children[id] ) do
        freeHandleODBC( handle_children[id][i] )
        
        -- mark this as ready for reuse
        handle_children[id][i] = -1
    end for

Matt Lewis

new topic » goto parent » topic index » view message » categorize

3. Re: odbc resources

Posted by Craig Welch <craig at singmail.com> Nov 16, 2005
576 views

Matt Lewis wrote:

 >OK, I think I know what's happening. I'm not reusing the bookkeeping
 >sequence handle_children properly.
 >
 ><snip>
 >...and the for-loop in the middle of freeHandleODBC():
 >}}}
<eucode>
 > for i = 1 to length( handle_children[id] ) do
 > freeHandleODBC( handle_children[id][i] )
 >
 > -- mark this as ready for reuse
 > handle_children[id][i] = -1
 > end for
 ></eucode>
{{{

 >
Thanks Matt, but that didn=92t do it.

As far as I can see, that bit of code is never being executed, because
above it is:
<EUCODE>
handle = getHandleODBC( id )
if not handle then
return
end if
</EUCODE>

This is always the case (not handle). My trace has find id = 3, handle =
= 0.
This seems to be because in function getHandleODBC:
<EUCODE>
if id > length( handle_odbc ) or handle_odbc[id] = -1 then
return 0
end if
</EUCODE>

Thus a handle of =961 will always appear to be *not* handle ...

I don=92t know if I=92m on the right track, but I=92d appreciate your furth=
er
advice.

Thanks,

--
Craig

new topic » goto parent » topic index » view message » categorize

4. Re: odbc resources

Posted by Matt Lewis <matthewwalkerlewis at gmail.com> Nov 16, 2005
624 views

Craig Welch wrote:
> 
> Matt Lewis wrote:
> 
>  >OK, I think I know what's happening. I'm not reusing the bookkeeping
>  >sequence handle_children properly.
>  >
>  ><snip>
>  >...and the for-loop in the middle of freeHandleODBC():
>  >}}}
<eucode>
>  > for i = 1 to length( handle_children[id] ) do
>  > freeHandleODBC( handle_children[id][i] )
>  >
>  > -- mark this as ready for reuse
>  > handle_children[id][i] = -1
>  > end for
>  ></eucode>
{{{

>  >
> Thanks Matt, but that didn't do it.
> 

Whoops.  The code I posted clears out a handle's children, but doesn't 
clear a child from the parent.  Here's a corrected version of 
freeHandleODBC():

global procedure freeHandleODBC( integer id )
    atom handle
    integer ix, parent

    if id < 0 then
        id = - id
    end if
    
    if not id then
        id = current_handle
    end if
    
    handle = getHandleODBC( id )
    if not handle then
        return
    end if
    
    for i = 1 to length( handle_children[id] ) do
        freeHandleODBC( handle_children[id][i] )
        
        -- mark this as ready for reuse
        handle_children[id][i] = -1
    end for
    
    -- free it up in its parent's handle_children sequence
    parent = handle_parent[id]
    if parent then
    	ix = find( id, handle_children[parent] )
    	if ix then
    		handle_children[parent][ix] = -1
    	end if
    end if
    
    if handle_type[id] = SQL_HANDLE_STMT then
        freeStmt( id, SQL_RESET_PARAMS )
        freeStmt( id, SQL_UNBIND ) 
    end if
    
    VOID = c_func( SQLFreeHandle, { handle_type[id], handle_odbc[id] } )
    handle_odbc[id] = -1
    
    if id = current_handle then
        current_handle = 0
    end if
    
end procedure

Matt Lewis

new topic » goto parent » topic index » view message » categorize

5. Re: odbc resources

Posted by Craig Welch <craig at singmail.com> Nov 17, 2005
584 views

Matt Lewis wrote:

>Whoops.  The code I posted clears out a handle's children, but doesn't 
>clear a child from the parent.  Here's a corrected version of 
>freeHandleODBC():
>
Yep, that fixed it! Forcing a dump after 500,000 transactions shows:

handle_odbc = {9501740,9501900,-1}
handle_children = {{2},{-1},{}}

Transactions per second ranged from 30 to 400 throughout, and no 
excessive paging.

Many thanks!

-- 
Craig

new topic » goto parent » topic index » view message » categorize

6. Re: odbc resources

Posted by Craig Welch <craig at singmail.com> Nov 17, 2005
594 views

Next ...

I'm trying to stress test, and have run up the number of inserts to 500,000

When the program ends, it crashes on the final freeHandleODBC(), called 
from cleanUpODBC( )

Edited ex.err:

C:\EUPHORIA\odbc\odbc.e:1258 in procedure freeHandleODBC()
Your program has run out of memory.
One moment please...
    id = 2
    handle = <no value>
    ix = <no value>
    parent = <no value>
    i = <no value>

... called from C:\EUPHORIA\odbc\odbc.e:1264 in procedure freeHandleODBC()  
    id = 1
    handle = 8917664
    ix = <no value>
    parent = <no value>
    i = 1

... called from C:\EUPHORIA\odbc\odbc.e:1264 in procedure freeHandleODBC()  
    id = 2
    handle = 8917832
    ix = <no value>
    parent = <no value>
    i = 1
--------------------------------------
CW Note: The line numbers in my odbc.e are
now different to yours. My line 1264 is:
freeHandleODBC( handle_children[id][i] )
--------------------------------------

<snip many, many such lines>

... (skipping 12058519 levels)

Global & Local Variables

 C:\EUPHORIA\include\machine.e:
    mem = 701392
    check_calls = 1

  C:\EUPHORIA\odbc\odbc.e:
    odbc32 = 1949433856
    kernel32 = 2088763392
    xlstrlen = 0
    henv = 1
    current_handle = 0
    max_records = 1
    VOID = 0
    last_datatype = {}
    handle_odbc = {8917664,8917832,-1}
    handle_children = {
                        {2},
                        {-1},
                        {}
                      }
    handle_type = {1,2,3}
    handle_parent = {0,1,2}
    handle_params = {
                      {{},{}},
                      {{},{}},
                      {{},{}}
                    }
    handle_cols = {{},{},{}}
    last_connect_string = {}


 C:\EUPHORIA\odbc\test_odbc.exw:
    lNextLine = -1
    hconn = 2
    void = {{}}
    sql = {73'I',78'N',83'S',69'E',82'R',84'T',32' ',116't',115's',117'u',
110'n',97'a',109'm',105'i',32' ',86'V',65'A',76'L',85'U',69'E',83'S',40'(',
39''',32' ',51'3',48'0',48'0',48'0',48'0',39''',44',',39''',71'G',82'R',
65'A',78'N',65'A',84'T',72'H',32' ',32' ',32' ',32' ',32' ',32' ',32' ',
32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',39''',44',',
39''',66'B',82'R',69'E',84'T',84'T',32' ',32' ',32' ',32' ',32' ',32' ',
32' ',32' ',32' ',32' ',32' ',32' ',32' ',78'N',39''',44',',39''',54'6',
55'7',52'4',50'2',32' ',87'W',32' ',83'S',73'I',71'G',72'H',84'T',73'I',
78'N',71'G',32' ',84'T',82'R',32' ',32' ',32' ',32' ',32' ',32' ',32' ',
32' ',32' ',32' ',32' ',32' ',39''',44',',39''',70'F',76'L',79'O',82'R',
69'E',78'N',67'C',69'E',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',
39''',44',',39''',75'K',89'Y',52'4',49'1',48'0',52'4',50'2',32' ',32' ',
32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',
32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',
32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',
32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',
32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',
32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',
32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',
32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',32' ',
39''',41')'}
    data = 3
    fh = 3
    counter = 510000
    ok = <no value>
    hstmt = 3
    time1 = 421.55
    time2 = 421.55
    calc = 23.72197841

Any thoughts?

-- 
Craig

new topic » goto parent » topic index » view message » categorize

7. Re: odbc resources

Posted by Matt Lewis <matthewwalkerlewis at gmail.com> Nov 17, 2005
662 views

Craig Welch wrote: > > Next ... > > I'm trying to stress > > When the program > from cleanUpODBC( )

Oops.  I think this freed, but didn't take this:

end


Matt Lewis

test, and have run up the number of inserts to 500,000 ends, it crashes on the final freeHandleODBC(), called happens because I changed the way children handles were this into account when the parent gets freed. Try style="color:#0000FF;">global procedure freeHandleODBC( integer id ) atom handle integer ix, parent if id < 0 then id = - id end if if not id then id = current_handle end if handle = getHandleODBC( id ) if not handle then return end if for i = 1 to length( handle_children[id] ) do -- make sure the children weren't already freed if handle_children[id][i] != -1 then freeHandleODBC( handle_children[id][i] ) -- mark this as ready for reuse handle_children[id][i] = -1 end if end for -- free it up in its parent's handle_children sequence parent = handle_parent[id] if parent then ix = find( id, handle_children[parent] ) if ix then handle_children[parent][ix] = -1 end if end if if handle_type[id] = SQL_HANDLE_STMT then freeStmt( id, SQL_RESET_PARAMS ) freeStmt( id, SQL_UNBIND ) end if VOID = c_func( SQLFreeHandle, { handle_type[id], handle_odbc[id] } ) handle_odbc[id] = -1 if id = current_handle then current_handle = 0 end if procedure

new topic » goto parent » topic index » view message » categorize

8. Re: odbc resources

Posted by Craig Welch <craig at singmail.com> Nov 17, 2005
573 views
Last edited Nov 18, 2005

Matt Lewis wrote:

>Oops.  I think this happens because I changed the way children handles were
>freed, but didn't take this into account when the parent gets freed.  Try
>this:
>  
>
That seems to have fixed everything.

I've run a million updates, and all seems fine, including the final cleanup.

Thanks,

-- 
Craig

new topic » goto parent » topic index » view message » categorize

9. Re: odbc resources

Posted by Craig Welch <craig at singmail.com> Nov 18, 2005
568 views

Craig Welch wrote:

> I've run a million updates, and all seems fine, including the final 
> cleanup. 

Running 5 million updates runs well, with speeds between 800 and 1,800 
transactions per second.

That's not too shabby at all! I don't think I need to test this aspect 
of ODBC/SQL any further ...

-- 
Craig

new topic » goto parent » topic index » view message » categorize

10. Re: odbc resources

Posted by cklester <cklester at yahoo.com> Nov 18, 2005
569 views

Craig Welch wrote:
> Craig Welch wrote:
> > I've run a million updates, and all seems fine, including the final 
> > cleanup. 
> Running 5 million updates runs well, with speeds between 800 and 1,800 
> transactions per second.
> 
> That's not too shabby at all! I don't think I need to test this aspect 
> of ODBC/SQL any further ...

What's your set up, in case EuSQL+EDB can't handle the traffic?

-=ck
"Programming in a state of EUPHORIA."
http://www.cklester.com/euphoria/

new topic » goto parent » topic index » view message » categorize

11. Re: odbc resources

Posted by Craig Welch <craig at singmail.com> Nov 20, 2005
635 views

cklester wrote:

>>Running 5 million updates runs well, with speeds between 800 and 1,800 
>>transactions per second.

>What's your set up, in case EuSQL+EDB can't handle the traffic?

Both of these PCs give very similar results:

SERVER
Windows 98 SE (build 4.10.2222)
2.40 gigahertz Intel Pentium 4
8 kilobyte primary memory cache
512 kilobyte secondary memory cache
20.47 Gigabytes Hard Drive Free Space
512 Megabytes Installed Memory

LAPTOP
Windows XP Home Edition Service Pack 2 (build 2600)
1.87 gigahertz Intel Pentium M
64 kilobyte primary memory cache
2048 kilobyte secondary memory cache
Bus Clock: 133 megahertz
66.27 Gigabytes Hard Drive Free Space
1016 Megabytes Installed Memory

-- 
Craig

new topic » goto parent » topic index » view message » categorize

OpenEuphoria

1. odbc resources

2. Re: odbc resources

3. Re: odbc resources

4. Re: odbc resources

5. Re: odbc resources

6. Re: odbc resources

7. Re: odbc resources

8. Re: odbc resources

9. Re: odbc resources

10. Re: odbc resources

11. Re: odbc resources

Search

Include:

Quick Links

User menu

Misc Menu